Sql server connection using Azure active directory Access Token

There are few steps you need to follow to successfully connect to sql server database using App authentication access token.

1) Set yourself@domain.com as Admin in Azure Portal
2) Login to SSMS using yourself@domain.com

3) select database that you want to connect and execute below sql command
this will creates a user with Azure Active Directory mapped to the App Service Principal

CREATE USER [yourAppName] FOR EXTERNAL PROVIDER; 

4) In C# app write logic to get access token to open connection to sql server using yourAppName and secrets
Like below code

private string GetConnectionString() { var builder = new SqlConnectionStringBuilder { DataSource = _sqlConfigs.DatabaseServer, InitialCatalog = _sqlConfigs.DatabaseName, ConnectTimeout = _sqlConfigs.ConnectionTimeoutInSeconds, ConnectRetryCount = _sqlConfigs.ConnectionRetryCount //Below both property only in use if you are using System.data.sqlClient version 4.5.1 or lower //,UserID = _sqlConfigs.DbClientId, //Password = _sqlConfigs.DbClientSecret }; return builder.ConnectionString; }

public async Task GetAccessTokenAsync(string clientId, string clientSecret, string authority, string resource, string scope) { var authContext = new AuthenticationContext(authority, TokenCache.DefaultShared); var clientCred = new ClientCredential(clientId, clientSecret); var result = await authContext.AcquireTokenAsync(resource, clientCred); if (result == null) { throw new InvalidOperationException("error"); } return result.AccessToken; }

public async Task GetSqlConnectionAsync(string tenantId, string clientId, string clientSecret, string dbServer, string dbName)
{
    var authority = string.Format("https://login.windows.net/{0}", tenantId);
    var resource = "https://database.windows.net/";
    var scope = "";
    var token = await GetAccessTokenAsync(clientId, clientSecret, authority, resource, scope);

   var connectionString = GetConnectionString();
   var sqlConnection = new SqlConnection(connectionString){ AccessToken = accessToken };
   sqlConnection.Open();
   return sqlConnection;
}


You can now use GetSqlConnectionAsync method and write your logic to play in database.

Happy coding 😊
Please write comment if you like this or want to know more.