Azure SQL - AAD Authentication from you client application in user context

When we try to migrate our IaaS SQL to Azure SQL along with other code changes & refactoring, one of the most prominent things that come into the picture is Authentication through a client application (web app or windows form, excel-add-ins...). With IaaS/OnPrem SQL we could use Windows Authentication to let your client application interact with the SQL server with the user's context. But to Azure SQL we can't do the Windows Authentication and also we should avoid SQL-based login (user/password). 

To login to Azure SQL, it's always preferable to use Azure Active Directory-based authentication (integrated flow, MFA, or AAD based password). Now the problem comes how to achieve this with your client application. 

The solution to this problem statement is, use AAD app with delegated permission to access the users' claim [email, sid] in access token & configure it based on your client (e.g. windows form, web application)

Here are the related steps:

• Create an AAD App [e.g.: TestAzureSQLConnWithUserContext]
• Once App is created, 
• Go its API permission and click on "Add permission" 

•  Click on the "Add a permission" it will open Request API permission. Here select the "APIs my organization uses". and search for "Azure SQL Database"

•  In permission, types select "Delegated permissions" & mark "user_impersonation"

• Click on "Add permission" and API permission tab it will be shown like this. No admin consent granted yet

•  Now in your configuration permission section of the "API permission" tab you can see 
• Azure SQl Database => user_impersonation"
• Make sure you get admin consent for this. [Contact your AAD admin], If you're already the AAD admin you can grant admin consent. 

Click on Grand admin cosent it will open a pop-up, like this. 

One we click on yes (granted admin consent), it will be shown like this with green colour. 

We've got the "Azure SQL Database" delegated permission for our AAD app now let's configure it to support our client. 

• Assume our client is a windows application:
• Go to AAD App => Authentication tab
• Click on "Add a platform" & select "Mobile & desktop application"
• Now select the "desktop.srf" options (sth like this: https://login.live.com/oauth20_desktop.srf) 
• Save

With this, we've configured our AAD app to Azure SQL Database & Windows Desktop client let's configure the token. 

• Go to AAD app => Token configuration
• Add Optional claim 
• Select "Access" => and check "acct, email. sid" & other attributes which you need in claim
• Select "ID" => check "acc, email, sid" & other attributes that you need in a claim. 
• Click "Add"

With this, we are good with Azure Active Directory configuration which will help you to log in to Azure SQL with the user's context. 

Now in your client application, using this created AAD app's (client-id) get the access token, and log in to the SQL server which will allow users to interact with your related Azure SQL server and if the user has permission to your database, he can easily access it using your client. 

The client application (windows form) code sample to get the access token: 

public string GetDBAccessToken()
{
	string authEndPoint = "https://login.microsoftonline.com/{0}";
	string resourceUri = "https://database.windows.net/";
	string redirectUri = "https://login.live.com/oauth20_desktop.srf";
	string clientId = "your-aad-app-client-id";
	string tenantId = "your-aad-apps-tenant-id";
	
	string authority = string.Format(CultureInfo.InvariantCulture, authEndPoint, tenantId);
	var authContext = new AuthenticationContext(authority);
	string accessToken;
	try
	{
		// Try to get from Cache, if it fails then in catch we will try to find the 
		accessToken = authContext.AcquireTokenSilentAsync(resourceUri, clientId)
								.ConfigureAwait(false)
								.GetAwaiter()
								.GetResult()
								.AccessToken;



	}
	catch (AdalSilentTokenAcquisitionException ex)
	{
		// Now for first time it will prompt user for interactive login. 
		accessToken = authContext.AcquireTokenAsync(resourceUri, clientId, new Uri(redirectUri), new PlatformParameters(PromptBehavior.Auto))
								 .ConfigureAwait(false)
								 .GetAwaiter()
								 .GetResult()
								 .AccessToken;
	}

	return accessToken;
}
      
      

It will prompt users a login screen to get the user's consent and once authentication is done, return the access token to the client application. With this access token let's try to create the SQL Connection and we're good to login to Azure SQL with the user's context.