Connect to Azure SQL with Azure AD authentication and SqlClient
Applies to:
.NET Framework
.NET Core
.NET Standard
This article describes how to connect to Azure SQL data sources by using Azure Active Directory (Azure AD) authentication from a .NET application with SqlClient.
Overview
Azure AD authentication uses identities in Azure AD to access Azure SQL data sources such as Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Microsoft.Data.SqlClient namespace allows client applications to specify Azure AD credentials in different authentication modes when they're connecting to Azure SQL Database. To use Azure AD authentication, you must configure your Azure SQL data source. For more information, see Configure and manage Azure AD authentication with Azure SQL.
When you set the Authentication
connection property in the connection string, the client can choose a preferred Azure AD authentication mode according to the value provided:
The earliest Microsoft.Data.SqlClient version supports
Active Directory Password
for .NET Framework, .NET Core, and .NET Standard. It also supportsActive Directory Integrated
authentication andActive Directory Interactive
authentication for .NET Framework.Starting with Microsoft.Data.SqlClient 2.0.0, support for
Active Directory Integrated
authentication andActive Directory Interactive
authentication has been extended across .NET Framework, .NET Core, and .NET Standard.A new
Active Directory Service Principal
authentication mode is also added in SqlClient 2.0.0. It makes use of the client ID and secret of a service principal identity to accomplish authentication.More authentication modes are added in Microsoft.Data.SqlClient 2.1.0, including
Active Directory Device Code Flow
andActive Directory Managed Identity
(also known asActive Directory MSI
). These new modes enable the application to acquire an access token to connect to the server.
For information about Azure AD authentication beyond what the following sections describe, see Connecting to SQL Database by using Azure Active Directory authentication.
Setting Azure Active Directory authentication
When the application is connecting to Azure SQL data sources by using Azure AD authentication, it needs to provide a valid authentication mode. The following table lists the supported authentication modes. The application specifies a mode by using the Authentication
connection property in the connection string.
Value | Description | Microsoft.Data.SqlClient version |
---|---|---|
Active Directory Password | Authenticate with an Azure AD identity by using a username and password | 1.0+ |
Active Directory Integrated | Authenticate with an Azure AD identity by using integrated authentication | 2.0.0+1 |
Active Directory Interactive | Authenticate with an Azure AD identity by using interactive authentication | 2.0.0+1 |
Active Directory Service Principal | Authenticate with an Azure AD identity by using the client ID and secret of a service principal identity | 2.0.0+ |
Active Directory Device Code Flow | Authenticate with an Azure AD identity by using Device Code Flow mode | 2.1.0+ |
Active Directory Managed Identity, Active Directory MSI |
Authenticate with an Azure AD identity by using system-assigned or user-assigned managed identity | 2.1.0+ |
Active Directory Default | Authenticate with an Azure AD identity by using password-less and non-interactive mechanisms including Managed Identities, Visual Studio Code, Visual Studio, Azure CLI, etc. | 3.0.0+ |
1 Before Microsoft.Data.SqlClient 2.0.0, Active Directory Integrated
, and Active Directory Interactive
authentication modes are supported only on .NET Framework.
Using Active Directory Password authentication
Active Directory Password
authentication mode supports authentication to Azure data sources with Azure AD for native or federated Azure AD users. When you're using this mode, user credentials must be provided in the connection string. The following example shows how to use Active Directory Password
authentication.
// Use your own server, database, user ID, and password.
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Password; Encrypt=True; Database=testdb; User Id=user@domain.com; Password=***";
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
}
Using Active Directory Integrated authentication
To use Active Directory Integrated
authentication mode, you need to federate the on-premises Active Directory instance with Azure AD in the cloud. You can do federation by using Active Directory Federation Services (AD FS), for example.
When you're signed in to a domain-joined machine, you can access Azure SQL data sources without being prompted for credentials with this mode. You can't specify username and password in the connection string for .NET Framework applications. Username is optional in the connection string for .NET Core and .NET Standard applications. You can't set the Credential
property of SqlConnection in this mode.
The following code snippet is an example of when Active Directory Integrated
authentication is in use.
// Use your own server and database.
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Integrated; Encrypt=True; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
conn.Open();
}
// User ID is optional for .NET Core and .NET Standard.
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory Integrated; Encrypt=True; Database=testdb; User Id=user@domain.com";
using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
conn.Open();
}
Using Active Directory Interactive authentication
Active Directory Interactive
authentication supports multi-factor authentication technology to connect to Azure SQL data sources. If you provide this authentication mode in the connection string, an Azure authentication screen will appear and ask the user to enter valid credentials. You can't specify the password in the connection string.
You can't set the Credential
property of SqlConnection in this mode. With Microsoft.Data.SqlClient 2.0.0 and later, username is allowed in the connection string when you're in interactive mode.
The following example shows how to use Active Directory Interactive
authentication.
// Use your own server, database, and user ID.
// User ID is optional.
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb; User Id=user@domain.com";
using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
conn.Open();
}
// User ID is not provided.
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory Interactive; Encrypt=True; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
conn.Open();
}
Using Active Directory Service Principal authentication
In Active Directory Service Principal
authentication mode, the client application can connect to Azure SQL data sources by providing the client ID and secret of a service principal identity. Service principal authentication involves:
- Setting up an app registration with a secret.
- Granting permissions to the app in the Azure SQL Database instance.
- Connecting with the correct credential.
The following example shows how to use Active Directory Service Principal
authentication.
// Use your own server, database, app ID, and secret.
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Service Principal; Encrypt=True; Database=testdb; User Id=AppId; Password=secret";
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
}
Using Active Directory Device Code Flow authentication
With Microsoft Authentication Library for .NET (MSAL.NET), Active Directory Device Code Flow
authentication enables the client application to connect to Azure SQL data sources from devices and operating systems that don't have an interactive web browser. Interactive authentication will be performed on another device. For more information about device code flow authentication, see OAuth 2.0 Device Code Flow.
When this mode is in use, you can't set the Credential
property of SqlConnection
. Also, the username and password must not be specified in the connection string.
The following code snippet is an example of using Active Directory Device Code Flow
authentication.
Note
The timeout for Active Directory Device Code Flow
defaults to the connection's Connect Timeout
setting. Make sure to specify a Connect Timeout
that provides enough time to go through the device code flow authentication process.
// Use your own server and database and increase Connect Timeout as needed for device code flow.
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Device Code Flow; Encrypt=True; Database=testdb; Connect Timeout=180;";
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
}
Using Active Directory Managed Identity authentication
Managed Identities for Azure resources is the new name for the service formerly known as Managed Service Identity (MSI). When a client application uses an Azure resource to access an Azure service that supports Azure AD authentication, you can use managed identities to authenticate by providing an identity for the Azure resource in Azure AD. You can then use that identity to obtain access tokens. This authentication method can eliminate the need to manage credentials and secrets.
There are two types of managed identities:
- System-assigned managed identity is created on a service instance in Azure AD. It's tied to the lifecycle of that service instance.
- User-assigned managed identity is created as a standalone Azure resource. It can be assigned to one or more instances of an Azure service.
For more information about managed identities, see About managed identities for Azure resources.
Since Microsoft.Data.SqlClient 2.1.0, the driver supports authentication to Azure SQL Database, Azure Synapse Analytics, and Azure SQL Managed Instance by acquiring access tokens via managed identity. To use this authentication, specify either Active Directory Managed Identity
or Active Directory MSI
in the connection string, and no password is required. You can't set the Credential
property of SqlConnection
in this mode either.
For a user-assigned managed identity, the client id of the managed identity must be provided when using Microsoft.Data.SqlClient v3.0 or newer. If using Microsoft.Data.SqlClient v2.1, the object id of the managed identity must be provided.
The following example shows how to use Active Directory Managed Identity
authentication with a system-assigned managed identity.
// For system-assigned managed identity
// Use your own values for Server and Database.
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Encrypt=True; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
conn.Open();
}
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory MSI; Encrypt=True; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
conn.Open();
}
The following example demonstrates Active Directory Managed Identity
authentication with a user-assigned managed identity with Microsoft.Data.SqlClient v3.0 onwards.
// For user-assigned managed identity
// Use your own values for Server, Database, and User Id.
// With Microsoft.Data.SqlClient v3.0+
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Encrypt=True; User Id=ClientIdOfManagedIdentity; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
conn.Open();
}
// With Microsoft.Data.SqlClient v3.0+
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory MSI; Encrypt=True; User Id=ClientIdOfManagedIdentity; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
conn.Open();
}
The following example demonstrates Active Directory Managed Identity
authentication with a user-assigned managed identity with Microsoft.Data.SqlClient v2.1.
// For user-assigned managed identity
// Use your own values for Server, Database, and User Id.
// With Microsoft.Data.SqlClient v2.1
string ConnectionString1 = @"Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Encrypt=True; User Id=ObjectIdOfManagedIdentity; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString1)) {
conn.Open();
}
// With Microsoft.Data.SqlClient v2.1
string ConnectionString2 = @"Server=demo.database.windows.net; Authentication=Active Directory MSI; Encrypt=True; User Id=ObjectIdOfManagedIdentity; Database=testdb";
using (SqlConnection conn = new SqlConnection(ConnectionString2)) {
conn.Open();
}
Using Active Directory Default authentication
This authentication mode widens the possibilities of user authentication, extending login solutions to the client environment, Visual Studio Code, Visual Studio, Azure CLI etc.
With this authentication mode, the driver acquires a token by passing "DefaultAzureCredential" from the Azure Identity library to acquire an access token. This mode attempts to use these credential types to acquire an access token in the following order:
- EnvironmentCredential
- Enables authentication to Azure Active Directory using client and secret, or username and password, details configured in the following environment variables: AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, AZURE_CLIENT_CERTIFICATE_PATH, AZURE_USERNAME, AZURE_PASSWORD (More details)
- ManagedIdentityCredential
- Attempts authentication to Azure Active Directory using a managed identity that has been assigned to the deployment environment. "Client Id" of "User Assigned Managed Identity" is read from the "User Id" connection property.
- SharedTokenCacheCredential
- Authenticates using tokens in the local cache shared between Microsoft applications.
- VisualStudioCredential
- Enables authentication to Azure Active Directory using data from Visual Studio
- VisualStudioCodeCredential
- Enables authentication to Azure Active Directory using data from Visual Studio Code.
- AzureCliCredential
- Enables authentication to Azure Active Directory using Azure CLI to obtain an access token.
Note
InteractiveBrowserCredential is disabled in the driver implementation of "Active Directory Default", and "Active Directory Interactive" is the only option available to acquire a token using MFA/Interactive authentication.
Further customization options are not available at the moment.
The following example shows how to use Active Directory Default authentication.
// Use your own server, database
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Default; Encrypt=True; Database=testdb;";
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
}
Customizing Active Directory authentication
Besides using the Active Directory authentication built into the driver, Microsoft.Data.SqlClient 2.1.0 and later provide applications the option to customize Active Directory authentication. The customization is based on the ActiveDirectoryAuthenticationProvider
class, which is derived from the SqlAuthenticationProvider
abstract class.
During Active Directory authentication, the client application can define its own ActiveDirectoryAuthenticationProvider
class by either:
- Using a customized callback method.
- Passing an application client ID to the MSAL library via SqlClient driver for fetching access tokens.
The following example displays how to use a custom callback when Active Directory Device Code Flow
authentication is in use.
using System;
using System.Threading.Tasks;
using Microsoft.Identity.Client;
using Microsoft.Data.SqlClient;
namespace CustomAuthenticationProviderExamples
{
public class Program
{
public static void Main()
{
SqlAuthenticationProvider authProvider = new ActiveDirectoryAuthenticationProvider(CustomDeviceFlowCallback);
SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryDeviceCodeFlow, authProvider);
using (SqlConnection sqlConnection = new SqlConnection("Server=<myserver>.database.windows.net;Authentication=Active Directory Device Code Flow;Database=<db>;"))
{
sqlConnection.Open();
Console.WriteLine("Connected successfully!");
}
}
private static Task CustomDeviceFlowCallback(DeviceCodeResult result)
{
// Provide custom logic to process result information and read device code.
Console.WriteLine(result.Message);
return Task.FromResult(0);
}
}
}
With a customized ActiveDirectoryAuthenticationProvider
class, a user-defined application client ID can be passed to SqlClient when a supported Active Directory authentication mode is in use. Supported Active Directory authentication modes include Active Directory Password
, Active Directory Integrated
, Active Directory Interactive
, Active Directory Service Principal
, and Active Directory Device Code Flow
.
The application client ID is also configurable via SqlAuthenticationProviderConfigurationSection
or SqlClientAuthenticationProviderConfigurationSection
. The configuration property applicationClientId
applies to .NET Framework 4.6+ and .NET Core 2.1+.
The following code snippet is an example of using a customized ActiveDirectoryAuthenticationProvider
class with a user-defined application client ID when Active Directory Interactive
authentication is in use.
using System;
using Microsoft.Data.SqlClient;
namespace CustomAuthenticationProviderExamples
{
public class Program
{
public static void Main()
{
// Supported for all authentication modes supported by ActiveDirectoryAuthenticationProvider
ActiveDirectoryAuthenticationProvider provider = new ActiveDirectoryAuthenticationProvider("<application_client_id>");
if (provider.IsSupported(SqlAuthenticationMethod.ActiveDirectoryInteractive))
{
SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryInteractive, provider);
}
using (SqlConnection sqlConnection = new SqlConnection("Server=<myserver>.database.windows.net;Authentication=Active Directory Interactive;Database=<db>;"))
{
sqlConnection.Open();
Console.WriteLine("Connected successfully!");
}
}
}
}
The following example shows how to set an application client ID through a configuration section.
<configuration>
<configSections>
<section name="SqlClientAuthenticationProviders"
type="Microsoft.Data.SqlClient.SqlClientAuthenticationProviderConfigurationSection, Microsoft.Data.SqlClient" />
</configSections>
<SqlClientAuthenticationProviders applicationClientId ="<GUID>" />
</configuration>
<!--or-->
<configuration>
<configSections>
<section name="SqlAuthenticationProviders"
type="Microsoft.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, Microsoft.Data.SqlClient" />
</configSections>
<SqlAuthenticationProviders applicationClientId ="<GUID>" />
</configuration>
Support for a custom SQL authentication provider
Given more flexibility, the client application can also use its own provider for Active Directory authentication instead of using the ActiveDirectoryAuthenticationProvider
class. The custom authentication provider needs to be a subclass of SqlAuthenticationProvider
with overridden methods. It then must register the custom provider, overriding one or more of the existing Active Directory*
authentication methods.
The following example shows how to use a new authentication provider for Active Directory Device Code Flow
authentication.
using System;
using System.Threading.Tasks;
using Microsoft.Identity.Client;
using Microsoft.Data.SqlClient;
namespace CustomAuthenticationProviderExamples
{
/// <summary>
/// Example demonstrating creating a custom device code flow authentication provider and attaching it to the driver.
/// This is helpful for applications that wish to override the Callback for the Device Code Result implemented by the SqlClient driver.
/// </summary>
public class CustomDeviceCodeFlowAzureAuthenticationProvider : SqlAuthenticationProvider
{
public override async Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
{
string clientId = "my-client-id";
string clientName = "My Application Name";
string s_defaultScopeSuffix = "/.default";
string[] scopes = new string[] { parameters.Resource.EndsWith(s_defaultScopeSuffix) ? parameters.Resource : parameters.Resource + s_defaultScopeSuffix };
IPublicClientApplication app = PublicClientApplicationBuilder.Create(clientId)
.WithAuthority(parameters.Authority)
.WithClientName(clientName)
.WithRedirectUri("https://login.microsoftonline.com/common/oauth2/nativeclient")
.Build();
AuthenticationResult result = await app.AcquireTokenWithDeviceCode(scopes,
deviceCodeResult => CustomDeviceFlowCallback(deviceCodeResult)).ExecuteAsync();
return new SqlAuthenticationToken(result.AccessToken, result.ExpiresOn);
}
public override bool IsSupported(SqlAuthenticationMethod authenticationMethod) => authenticationMethod.Equals(SqlAuthenticationMethod.ActiveDirectoryDeviceCodeFlow);
private Task CustomDeviceFlowCallback(DeviceCodeResult result)
{
Console.WriteLine(result.Message);
return Task.FromResult(0);
}
}
public class Program
{
public static void Main()
{
// Register our custom authentication provider class to override Active Directory Device Code Flow
SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryDeviceCodeFlow, new CustomDeviceCodeFlowAzureAuthenticationProvider());
using (SqlConnection sqlConnection = new SqlConnection("Server=<myserver>.database.windows.net;Authentication=Active Directory Device Code Flow;Database=<db>;"))
{
sqlConnection.Open();
Console.WriteLine("Connected successfully!");
}
}
}
}
In addition to improving the Active Directory Interactive
authentication experience, Microsoft.Data.SqlClient 2.1.0 and later provide the following APIs for client applications to customize interactive authentication and device code flow authentication.
public class ActiveDirectoryAuthenticationProvider
{
// For .NET Framework targeted applications only
// Sets a reference to the current System.Windows.Forms.IWin32Window that triggers the browser to be shown.
// Used to center the browser pop-up onto this window.
public void SetIWin32WindowFunc(Func<IWin32Window> iWin32WindowFunc);
// For .NET Standard targeted applications only
// Sets a reference to the ViewController (if using Xamarin.iOS), Activity (if using Xamarin.Android) IWin32Window, or IntPtr (if using .NET Framework).
// Used for invoking the browser for Active Directory Interactive authentication.
public void SetParentActivityOrWindowFunc(Func<object> parentActivityOrWindowFunc);
// For .NET Framework, .NET Core, and .NET Standard targeted applications
// Sets a callback method that's invoked with a custom web UI instance that will let the user sign in with Azure AD, present consent if needed, and get back the authorization code.
// Applicable when working with Active Directory Interactive authentication.
public void SetAcquireAuthorizationCodeAsyncCallback(Func<Uri, Uri, CancellationToken, Task<Uri>> acquireAuthorizationCodeAsyncCallback);
// For .NET Framework, .NET Core, and .NET Standard targeted applications
// Clears cached user tokens from the token provider.
public static void ClearUserTokenCache();
}
See also
Feedback
Submit and view feedback for