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:
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(); } } }
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.