Connect to SQL Azure Using Active Directory Authentication

Microsoft offers a cloud based database service called SQL Azure. This article contains information on how to connect to an SQL Azure database using Active Directory authentication via Java / JDBC.

SQL Azure now offers users the ability to connect using identities in Azure Active Directory. This is an alternative to using SQL Server Authentication to authenticate to the database. Users can authenticate by passing a user id and password, or by using integrated Windows authentication. See below for information on each option.

This article will assume you are using Microsoft's SQL Server JDBC driver to connect. Version 6.2 or later is required to support all features mentioned in this article. This article will also assume that the Azure Active Directory configuration has already been completed.

Connect to SQL Azure Using a User and Password

To connect to SQL Azure using Active Directory authentication with a user and password via JDBC, the Azure Active Directory Library for Java and its dependencies are required. This is an open source library that contains the Java classes needed to authenticate against Azure Active Directory. The Azure Active Directory Library for Java is maintained on github and can be found here: https://github.com/AzureAD/azure-activedirectory-library-for-java

After adding the sqljdbc*.jar file (the Microsoft SQL Server JDBC Driver jar file), the adal4j.jar file (the Azure Active Directory Library for Java jar file), and the adal4j dependency jars to the Java classpath, the Java call to authenticate using Azure Active Directory is possible. Listed below are some example parameters required to make the connection:

Example Active Directory User: testuser@acme.onmicrosoft.com

JDBC URL: jdbc:sqlserver://sampledatabase.database.windows.net;databaseName=test;authentication=ActiveDirectoryPassword;selectMethod=cursor

Notice the "authentication" property is set to "ActiveDirectoryPassword". This indicates to the driver to authenticate using the Azure Active Directory user and password.

Driver Class:: com.microsoft.sqlserver.jdbc.SQLServerDriver

This is the driver class for the Microsoft SQL Server JDBC driver.

Connect to SQL Azure Using Active Directory Integrated Security

To connect to SQL Azure using Active Directory Integrated security, the connection must be made from a domain joined machine that is federated with Azure Active Directory. A database user representing your Azure Active Directory principal, or one of the groups the user belongs to, needs to exist in the database and have the CONNECT permission.

The following need to be installed on the machine in order to use the Active Directory Integrated Security authentication option:

Active Directory Authentication Library for SQL Server. This is the adalsql.dll file. This library can be downloaded from here:

http://www.microsoft.com/en-us/download/details.aspx?id=48742.

The installer will install the adalsql.dll file in the appropriate system folders on your machine.

SQL Server JDBC Driver Authentication Library. This is the sqljdbc_auth.dll. This dll file needs to be in the path for the Java virtual machine. The sqljdbc_auth.dll can be copied into the "bin" folder of the Java runtime environment. There are 32-bit and 64-bit versions of the dll included with the Microsoft SQL Server JDBC driver. Make sure to use the correct version for your machine.

Once the above libraries are obtained, the java call to authenticate via Active Directory Integrated security can be maade. Listed below are some example parameters required to make the connection:

JDBC URL: jdbc:sqlserver://sampledatabase.database.windows.net;databaseName=test;authentication=ActiveDirectoryIntegrated;selectMethod=cursor

Notice the "authentication" property is set to "ActiveDirectoryIntegrated". This indicates to the driver to authenticate using integrated security.

Driver Class:: com.microsoft.sqlserver.jdbc.SQLServerDriver

This is the driver class for the Microsoft SQL Server JDBC driver.