Azure AD Service Principal authentication to SQL DB – Code Sample

The following application provides an example of using Service Principal (SP) to and connect to 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 SP . Please note that the same mechanism applies to Managed Instance and .

For more information on enable authentication for SQL DB see Use Azure Active Directory Authentication for authentication with SQL.

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 = “”; // 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 = “”; //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 How to: Use the portal to create an Azure AD application and service principal that can access resources
  2. Find the Server name and Database name
    serverName = “”; // server name:
    (e.g. string serverName = “”;)
    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

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

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

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 = ""; // server name i.e.
            // string databaseName = ""; //Database name  i.e. test
            // string clientId = ""; // application id of the service principal
            // string aadTenantId = ""; //AAD tenant id
            // string clientSecretKey = "secretKey"; // AAD app secret key

            string serverName = "";
            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 = "{0}";
            string ResourceId = "";

            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);


                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();

                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);




This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.