Connect to SQL using "microsoft.data.sqlclient " with managed identity and MSAL nugets (Azure.Identity)

Question

I found this article around sqlconnection with managed identity https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/app-service/tutorial-connect-msi-sql-database.md

BUT this article is using "System.Data.SqlClient" which is retired now (not recommended to use). Can someone redirect me to similar document which is using "Microsoft.Data.SqlClient"?

Answer 1

The question contains the answer. The provided article is the "source code" for the official documentation which is here. It shows us how to connect to Azure SQL with a managed identity using both System.Data.SqlClient and Microsoft.Data.SqlClient.

The main difference is that Microsoft.Data.SqlClient understands the new connection string property Authentication=Active Directory MODE while System.Data.SqlClient does not (it would throw "Keyword not supported: 'authentication'.") and the access token must be set in code. The MODE can be Managed Identity, Interactive, Default, Password, and some others. Here are some usage examples:

const string srv = "YourServer.database.windows.net";
const string db = "YourDatabase";

// 1. The System.Data.SqlClient way.
string connStr = $"Server={srv};Database={db};";
using System.Data.SqlClient.SqlConnection conn1 = new(connStr);
Azure.Core.TokenRequestContext context = new(new[] { "https://database.windows.net/.default" });
// A. Opens up a browser, asks for Azure AD user credentials. Use only locally.
conn1.AccessToken = new Azure.Identity.InteractiveBrowserCredential().GetToken(context).Token;
// B. Connects to 169.254.169.254:80 on the current Azure resource to collect a token. Use only in Azure.
conn1.AccessToken = new Azure.Identity.ManagedIdentityCredential().GetToken(context).Token;
// C. Smart, slow, non-interactive, works both in Azure and locally.
// Scans many endpoints - the current Azure resource, environment vars,
// Visual Studio[ Code], Azure CLI or PowerShell - and usees any of them.
conn1.AccessToken = new Azure.Identity.DefaultAzureCredential().GetToken(context).Token;

// 2. The equivalent Microsoft.Data.SqlClient way. Same comments apply, see A.B.C. above.
connStr = $"Server={srv};Database={db};Authentication=Active Directory Interactive;";
connStr = $"Server={srv};Database={db};Authentication=Active Directory Managed Identity;";
connStr = $"Server={srv};Database={db};Authentication=Active Directory Default;";
// D. Pass explicit Azure AD username and password.
connStr = $"Server={srv};Database={db};Authentication=Active Directory Password;User [email protected];Password=YourPass;";
using Microsoft.Data.SqlClient.SqlConnection conn2 = new(connStr);