In our recent article published on this forum, we have described the steps required to facilitate interactive access to Azure SQL Database by relying on Azure Active Directory. Such an approach allows you to connect to Azure SQL Database from SQL Server Management Studio, SQL Server Data Tools, or the sqlcmd command line utility with your Azure Active Directory or Active Directory (in the synchronization and federation scenarios) credentials. However, it is also possible to leverage Azure Active Directory when connecting to Azure SQL Database from applications or services in a non-interactive manner, by relying on token-based authentication. In this article, we will explore this capability and provide a high-level description of its implementation.
Similar to the functionality we described in our previous articles, the token-based authentication places additional requirements on the components that participate in the authentication process. In particular, your Azure SQL Database must be hosted in an Azure subscription associated with the Azure Active Directory tenant that will generate the authentication token. The Azure SQL Database must also operate within a V12 server instance. In addition, the application you want to authentiate must be based on .NET 4.6 or higher and reference Azure Active Directory Authentication Library for SQL Server (ADALSQL.dll). Once these conditions are satisfied, you will need to take the following steps in order to accomplish your objective:
- Register your application with Azure Active Directory as the Web Application and/or Web API. This automatically generates the client ID, uniquely identifying the application within the current tenant. This must be the same tenant that is associated with the subscription hosting the Azure SQL Database. You can locate the client ID value on the CONFIGURATION tab of the application within the Azure classic portal.
- Create a contained database user within the Azure SQL Database with the name matching the name of the application you registered in Azure Active Directory. To accomplish this, you would log on to the Azure SQL Database with the credentials of the Azure Active Directory server admin and run the CREATE USER application_name FROM EXTERNAL PROVIDER T-SQL statement.
- On the computer where you are developing the application, generate a self-signed certificate. You can use the makecert.exe utility included in the Windows 10 Software Development Kitfor this purpose. Once you install the Windows 10 SDK, launch the Command Prompt, switch to the directory where you intend to temporarily store the certificate, and run the following (you can use a different value of the CN attribute of the certificate as well as the name of the corresponding cer file):
"%ProgramFiles(x86)%/Windows Kits/8.1/bin/x64/makecert.exe" -r -pe -n "CN=my_app_Cert" -ss My -len 2048 my_app_Cert.cer
This will generate the my_app_Cert.cer file containing the public key. The corresponding private key will be automatically placed in the private certificate store of your user account.
- Add the public key of the certificate to the application registered in Azure Active Directory. To accomplish this, you should install the Azure Active Directory PowerShell Module on your development computer and run the following script (where my_app_Cert.cer is the name of the .cer file you created in the previous step and client_ID is the client identifier generated in the first step):
#at this point, you will be prompted to authenticate to the Azure Active Directory tenant
$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate
$rawCert = $cert.GetRawCertData()
$certValue = [System.Convert]::ToBase64String($rawCert);
New-MsolServicePrincipalCredential -AppPrincipalId "client_ID" -Type asymmetric -Value $certValue -Usage verify
This will create a new certificate-based credential to the Azure Active Directory security principal associated with the application you registered in the first step of this procedure. As the result, you will be able to configure your application to authenticate to Azure Active Directory tenant by relying on the private key of this certificate.
- Reference the Azure Active Directory application and certificate settings in the application configuration and code. Assuming that you are developing a standard service .NET-based application, you would modify its app.config file by modifying the following entries:
- the value of the ida:Tenant entry should be replaced with the name of your Azure Active Directory tenant
- the value of the ida:ClientId entry should be replaced with the value of the client ID you generated in the first step of this procedure
- the value of the ida:Cert_Name entry should be replaced with the CN entry of the self-signed certificate you generated in the third step of this procedure
In the Program.cs file, you would need to also set the values of the builder["Data Source"] and builder["Initial Catalog"] parameters to point respectively to the target Azure SQL Server name and the target Azure SQL Database name.
For a sample application that illustrates this approach, refer to SQL Server Security Blog.
See all articles by Marcin Policht