Setup a Microsoft Azure SQL Database Service Principal (Versions: 9.0+)

Versions: 8.0+

  Note: This article describes "Method 4" in the article Configuring Connections: Microsoft Azure SQL Database.

Connecting to Azure SQL Database using this method requires an application/client ID and secret provided by the Azure App registration feature. This method of authentication is supported if your on-premise Active Directory is federated with Azure Active Directory. This method requires additional configurations and may need to be done with the help of an Azure and/or Network administrator in your organization.

  Note: This method requires deployment of SQL JDBC driver 10.2 or higher, which may differ from the SQL Server driver that is bundled with QuerySurge .

Prerequisites:

• App registration of QuerySurge and client secret generation in the Azure Portal
• Grant Application/Service Principal Object access to Azure SQL Data sources
• On-premise Active Directory Federation Services (ADFS) federated with Azure Active Directory in the cloud
• Microsoft Authentication Library (MSAL) for Java and its dependencies for JDBC Driver 9.1 and above, or Microsoft Azure Active Directory Authentication Library (ADAL) for Java and its dependencies for driver versions prior to JDBC Driver 9.1.
• Integrated authentication dll for the SQL Server driver
  
  

App registration of QuerySurge and client secret generation the Azure Portal

Steps:

1. Log into the Azure portal (https://portal.azure.com/)
   
   
2. Open app registrations
   
   
   
3. Create a new registration
   
   
   
4. Enter a name for the new app registration. We recommend using "QuerySurge Integration" as its easy to identify the purpose of the entry.
   
   
     Note: Supported Account Types limits what accounts can access the API. We recommend using "Accounts in this organizational directory only" which is the most restrictive. For more information on supported account types click "Help me choose..." on the Azure page. 

5. Click register
   
   
6. On your new app registration, make note of the values for "Application (client) ID" and "Directory (tenant) ID". These values will be utilized in QuerySurge to setup your connection. 
   
   
   
7. Select "Certificates and secrets"
   
   
   
8. Generate a new client secret
   
   
9. Enter a description for the secret and an expiration time. Once completed, click Add.
   
   
     Note: If expiration time is utilized, you will need to regenerate your secret upon expiration or the QuerySurge integration will not work. 
   
   
10. Copy the value field. This is your client secret and will be utilized in QuerySurge to setup your connection. 
    
    
      Note: Client secrets are only displayed on creation. Once you navigate off this screen you will be unable to extract this secret again. If lost, a new secret will need to be generated. 

Grant Application/Service Principal Object access to Azure SQL Data

User creation in Azure SQL Database on behalf of Azure AD applications (service principals) is supported by Azure Active Directory (Azure AD). Depending on your organization’s security policies, access may be granted to Azure SQL databases in multiple ways.

The T-SQL command listed below will add an Azure AD application to the SQL database. (Replace "Azure_AD_Object” with the display name of your App registration). The user can then be granted permissions to access the necessary data.

CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER

Azure AD Applications can also be added to resource groups as detailed in the following article: Azure Active Directory service principal with Azure SQL

Download and Deploy the Integrated authentication dll for the SQL Server driver to your QuerySurge Agent(s):

  Note: This method requires SQL Server JDBC driver version 10.2+, which may differ from the SQL Server driver that is bundled with QuerySurge.

Instructions for downloading the Integrated authentication dll for the SQL Server driver can be found here.

Download and Deploy the additional authentication library and its dependencies to your QuerySurge Agent(s):

In order for QuerySurge to be able to authenticate users with Azure Active Directory, there is an additional authentication library, provided by Microsoft, that will need to be deployed to ALL QuerySurge Agents that will be used for this Connection. Depending on which version of the SQL Server JDBC driver you are using, you will either need the Microsoft Authentication Library for Java (MSAL) or the Microsoft Azure Active Directory Authentication Library for Java (ADAL).

  Note: This method requires SQL Server JDBC driver version 10.2+, which may differ from the SQL Server driver that is bundled with QuerySurge.

Additionally, each of these libraries have several dependencies, which will also need to be downloaded and deployed to your QuerySurge Agent(s). These libraries can be downloaded here for ADAL, and here for MSAL. All of their respective dependencies are also listed at those locations. 

Alternatively, these libraries, along with their respective dependencies, can be downloaded using Maven, or by using an online Maven dependency tool such as the one here by entering the following entry:

For ADAL:

<dependency>
   <groupId>com.microsoft.azure</groupId>
   <artifactId>adal4j</artifactId>
   <version>1.6.3</version>
</dependency>

For MSAL:

<dependency>
   <groupId>com.microsoft.azure</groupId>
   <artifactId>msal4j</artifactId>
   <version>1.10.0</version>
</dependency>

Once you have gathered the library and dependencies that you require, you can deploy them to your QuerySurge Agent(s) following instructions here for Windows Agents, and here for Linux Agents.

Configure the Connection in QuerySurge

Once you have downloaded the required additional authentication libraries and dependencies, downloaded and deployed the integrated authentication dll for the SQL Server driver, and configured your QuerySurge Agent(s) to run as a valid user in your Azure Active directory, you can create the Connection in QuerySurge. Because this method will require additional connection URL properties, you will need to use the use the Connection Extensibility option in the Connection Wizard. Instructions for setting up a Connection using this option can be found here.

The Driver class path for this Connection is the following:

com.microsoft.sqlserver.jdbc.SQLServerDriver

In order to enable authentication using Azure Active Directory Service Principal, the following property will have to be set in your Connection URL 

Authentication=ActiveDirectoryServicePrincipal;

Additionally, Azure SQL Database enforces TLS communications by default, so the following properties will likely have to be used as well:

encrypt=true;
trustServerCertificate=false;
hostNameInCertificate=*.database.windows.net;

Using the above ConnectionURL properties, the final Connection URL you will need to use should look like the following example:

jdbc:sqlserver://{SQLServerName}.database.windows.net:1433;database={DatabaseName};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;Authentication=ActiveDirectoryServicePrincipal;

Your Client/Application ID and Client Secret can be entered into the Username and Password fields respectively in the Connection Wizard, as seen in the following image.