Articles in this section

See more

Configuring Connections: Microsoft Azure SQL Database

Connections to Microsoft Azure SQL Database can be made in QuerySurge, similarly to on-premise Microsoft SQL Server - using the bundled SQL Server JDBC driver. However, when connecting to Azure SQL Database, there are several authentication mechanisms, using Azure Active Directory, that can be utilized which will require additional steps to configure. Those authentication methods that are supported in QuerySurge, as well as the steps require to configure them are discussed below.

More information about connecting to Azure SQL Database using Azure Active Directory can be found here.

Supported Azure SQL Database Authentication Methods

Method 1. SQL Username/Password

Connecting to Azure SQL Database using this method can be done the same way as when connecting to on-premise SQL Server - only a username and password is used to authenticate, and no additional configuration is required. To Configure a Connection to Azure SQL Database using the SQLPassword authentication mechanism, you can follow the guide here.

 

Method 2. Azure Active Directory Username/Password

Connecting to Azure SQL Database using this method is similar to connecting to on-premise SQL Server using SQL Username/Password Authentication, only this allows you to authenticate users in your Azure Active Directory. This method of Authentication requires additional configurations, and may need to be done with the help of an Azure and/or Network administrator in your organization. 

Prerequisites:

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: The version of the SQL Server JDBC driver varies depending on your version of QuerySurge.  You will need to match the authentication library to the installed version of the SQL Server JDBC driver.  For example, if your version of QuerySurge comes with SQL Server JDBC driver version 6.4 bundled, then you will need to use Microsoft Azure Active Directory Authentication Library for Java (ADAL).  But if your version of QuerySurge comes with SQL Server JDBC driver version 12.2 (or greater) bundled, then you will need to use Microsoft Authentication Library for Java (MSAL).

 

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, 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 Username/Password, the following property will have to be set in your Connection URL 

authentication=ActiveDirectoryPassword;

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 (Replacing example values with those needed for your environment):

jdbc:sqlserver://{ExampleSQLServer}.database.windows.net:1433;database={ExampleDatabaseName};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;authentication=ActiveDirectoryPassword

Your Username and Password can be entered into the given fields in the Connection Wizard, as seen in the following image.

azuresql1.png

 

Method 3. Azure Active Directory Integrated

Connecting to Azure SQL Database using this method is similar to connecting to on-premise SQL Server using Windows Authentication, only this allows you to authenticate using a local Windows user account without having to present any additional credentials. This method of authentication is only 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. 

Prerequisites:

 

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: QuerySurge comes with SQL Server JDBC driver version 6.4 bundled, so if you are using the bundled driver, then you will need to use Microsoft Azure Active Directory Authentication Library for Java (ADAL).

 

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.

 

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

 

Note: QuerySurge comes with SQL Server JDBC driver version 6.4 bundled, so if you are using the bundled driver, then you will need to obtain this by downloading SQL Server JDBC driver version 6.4, and copying it from that download.

 

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

 

Configure the Connection in QuerySurge

Once you have downloaded the required additional authentication libraries and dependencies, downloaded and deployed the integrated authentication dll (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 Integrated, the following property will have to be set in your Connection URL 

Authentication=ActiveDirectoryIntegrated;

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=ActiveDirectoryIntegrated

Your Username and Password can be entered into the given fields in the Connection Wizard, as seen in the following image.

azuresql2.png

Method 4. Azure SQL Database Service Principal

Because this method has an extended setup, we have broken it out into a separate Knowledge Base article: Setup a Microsoft Azure SQL Database Service Principal.

 

 

 

 

Related articles