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.
Before building and running the code sample, perform the following steps:
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-porta...
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";)
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.
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