Azure AD Service Principal authentication to SQL DB - Code Sample

The following application provides an example of using Azure AD Service Principal (SP) to authenticate and connect to Azure SQL database. This application measures the time it takes to obtain an access token, total time it takes to establish a connection, and time it takes to run a query. The application can be used to troubleshoot delays during each phase of the connection and query process. In addition, this code sample can display the content of the access token obtained using Azure AD SP authentication. Please note that the same authentication mechanism applies to Managed Instance and SQL DW.

For more information on how to enable Azure AD authentication for SQL DB see  
https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

Before building and running the code sample, perform the following steps:

1. Create a Service Principal in Azure AD for your service and obtained the following information required to execute the code sample below
   a.  Application ID of the Service Principal (SP)
       clientId = "<appId>"; // Application ID of the SP
       (e.g. string clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";)
   b. Copy the “Display Name” of your application which will be used in step 3)
      (e.g.”debugapp” as a “Display Name” for the app above)
   c.  Azure AD tenant ID
        aadTenantId = "<tenantId>"; //Azure AD tenant ID
        (e.g. string aadTenantId = "xxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxxx";)
   d.  Client (SP) secret key
        clientSecretKey = "secretKey"; // Application secret key 
        (e.g. string clientSecretKey = "xxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxx/xxx";)
   To obtain above information follow the steps indicated in the link below
   https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal
2. Find the Azure SQL Server name and Database name
   serverName = "<serverName>"; // server name: myserver.database.windows.net
   (e.g. string serverName = "myserver.database.windows.net";)
   databaseName = "<databaseName>" // database name: test;
   (e.g. string databaseName = "test";)
3. Using SSMS to connect to SQL DB (e.g. “test”) as an Azure AD user with proper Azure AD permissions (e.g. Azure AD admin for SQL DB), create an application user from step 1 above. Execute the T-SQL statement create user command “create user [app display name] from external provider”.
   Example using “debugapp” as a display name form step1
       create user [debugapp] from external provider.
   Note that the create user command grants this user a connect permission to the database, which is sufficient enough to execute the sample program below.
4. Copy and execute the program indicated below

Below is an example of the program output.

Please note that the token information displaying the access token was commented out in the program output
//Display a token

//Console.WriteLine("This is your token: " + authenticationResult.AccessToken);

If a token display is enabled (as it is in the program below), it can be copied and decoded into a readable form with claims, using https://jwt.ms/

.

Below is a C# version of the application called Program.cs

To obtain the nuget package “Microsoft.IdentityModel.Clients.ActiveDirectory”, use the link below

https://www.nuget.org/api/v2/package/Microsoft.IdentityModel.Clients.ActiveDirectory/4.5.1.

using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AADTest
{
    class Program
    {
        static void Main(string[] args)
        {
         
            // Examples for the input parameters
            // string serverName = "<serverName>"; // server name i.e. sqlxx.database.windows.net
            // string databaseName = "<databaseName>"; //Database name  i.e. test 
            // string clientId = "<appId>"; // application id of the service principal
            // string aadTenantId = "<tenantId>"; //AAD tenant id
            // string clientSecretKey = "secretKey"; // AAD app secret key

            string serverName = "myserver.database.windows.net"; 
            string databaseName = "test";
            string clientId = "xxxxxx-xxxxx-xxxxx-xxxx-xxxx"; 
            string aadTenantId = "xxxxxx-xxxxxx-xxxxxx-xxxxxx-xxxxxxxx";
            string clientSecretKey = "xxxxx/xxxxxx/xxxxx";

            string sqlConnectionString = String.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", serverName, databaseName);

            string AadInstance = "https://login.windows.net/{0}";
            string ResourceId = "https://database.windows.net/";


            AuthenticationContext authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
            ClientCredential clientCredential = new ClientCredential(clientId, clientSecretKey);

            DateTime startTime = DateTime.Now;
            Console.WriteLine("Time " + String.Format("{0:mm:ss.fff}", startTime));

            AuthenticationResult authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

            DateTime endTime = DateTime.Now;
            Console.WriteLine("Got token at " + String.Format("{0:mm:ss.fff}", endTime));

            Console.WriteLine("Total time to get token in milliseconds " + (endTime - startTime).TotalMilliseconds);

            using (var conn = new SqlConnection(sqlConnectionString))
            {
                conn.AccessToken = authenticationResult.AccessToken;

                startTime = DateTime.Now;
                Console.WriteLine("Starting to open connection at " + String.Format("{0:mm:ss.fff}", startTime));

                //Display a token
                Console.WriteLine("This is your token: " + authenticationResult.AccessToken);

                conn.Open();

                endTime = DateTime.Now;
                Console.WriteLine("Got connection at " + String.Format("{0:mm:ss.fff}", endTime));

                Console.WriteLine("Total time to establish connection in milliseconds " + (endTime - startTime).TotalMilliseconds);

                startTime = DateTime.Now;
                Console.WriteLine("Starting to run query at " + String.Format("{0:mm:ss.fff}", startTime));

                using (var cmd = new SqlCommand("SELECT 1", conn))
                {
                    var result = cmd.ExecuteScalar();
                    Console.WriteLine(result.ToString());
                }

                endTime = DateTime.Now;
                Console.WriteLine("Completing running query at " + String.Format("{0:mm:ss.fff}", endTime));
                Console.WriteLine("Total time to execute query in milliseconds " + (endTime - startTime).TotalMilliseconds);
            }

            Console.ReadKey();
        }
    }
}