Azure SQL Databases & Azure AD Authentication in Client Apps

Published on 26 July 2017

It's not uncommon that you encounter older desktop applications that communicate directly with a SQL server and use windows integrated authentication to secure access to a database. If you want to move the database to Azure SQL PaaS you'll have to adjust your client app's code and perform some additional steps in Azure.

There are two articles that provide all the steps required to achieve this:

The purpose of this blog post is to highlight the most important steps that helped me migrate the client authentication logic in no time.

CREATE USER [john.doe@mycompany.com] FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'db_datareader', 'john.doe@mycompany.com'
--add more permissions here; works for AAD groups as well

//### A minimal C# console example ###
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace AADSQL
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            new Program()
                .MainAsync()
                .GetAwaiter()
                .GetResult();
        }

        private async Task MainAsync()
        {
            await ConnectToAzureSqlDb();
        }

        private async Task ConnectToAzureSqlDb()
        {
            string AadInstance = "https://login.windows.net/{0}";
            string ResourceId = "https://database.windows.net/";

            string clientId = "25D5EDE1-5BEB-441E-8F9C-E82D1CD34DC2"; //your AppId here
            string aadTenantId = "company.onmicrosoft.com";
            AuthenticationContext authenticationContext =
              new AuthenticationContext(string.Format(AadInstance, aadTenantId));

            AuthenticationResult authenticationResult = await
              authenticationContext.AcquireTokenAsync(ResourceId,
                                                      clientId, new Uri("http://localhost"), new PlatformParameters(PromptBehavior.Always));

            string sqlConnectionString = "Data Source=yourdbserver.database.windows.net;Initial Catalog=YourDB; Connect Timeout= 30";

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

                using (SqlCommand cmd = new SqlCommand("SELECT TOP 10 * from Table", conn))
                {
                    var reader = await cmd.ExecuteReaderAsync();
                    while (reader.Read())
                    {
                        //get your data
                    }
                }
            }
        }
    }
}