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.
- Provision an Azure Active Directory administrator for your Azure SQL Server by using the Azure portal
- Register an Azure AD application (try the search field if the API does not show up in the portal at once)
- Admin consent via direct link is a must, even if you pressed the “grant permission” button in the portal
- Implement the client code (UPDATE: see the minimal console example below)
- Create SQL user identities for the users of your client apps with some T-SQL as shown below
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
}
}
}
}
}
}