Configure and manage Microsoft Entra authentication with Azure SQL
Applies to:
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
This article shows you how to create and populate a Microsoft Entra tenant and use Microsoft Entra ID (formerly Azure Active Directory) with Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. For an overview, see Microsoft Entra authentication.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Microsoft Entra authentication methods
Microsoft Entra ID supports the following authentication methods:
- Microsoft Entra cloud-only identities
- Microsoft Entra hybrid identities that support:
- Cloud authentication with two options coupled with seamless single sign-on (SSO)
- Microsoft Entra password hash authentication
- Microsoft Entra pass-through authentication
- Federated authentication
- Cloud authentication with two options coupled with seamless single sign-on (SSO)
For more information on Microsoft Entra authentication methods and which one to choose, see Choose the correct authentication method for your Microsoft Entra hybrid identity solution.
For more information on Microsoft Entra hybrid identities, setup, and synchronization, see:
- Password hash authentication - Implement password hash synchronization with Microsoft Entra Connect Sync
- Pass-through authentication - Microsoft Entra pass-through authentication
- Federated authentication - Deploying Active Directory Federation Services in Azure and Microsoft Entra Connect and federation
Create and populate a Microsoft Entra tenant
Create a Microsoft Entra tenant and populate it with users and groups. Microsoft Entra tenants can be managed entirely within Azure or used for the federation of an on-premises Active Directory Domain Service.
For more information, see:
- What is Microsoft Entra ID?
- Integrating your on-premises identities with Microsoft Entra ID
- Add your domain name to Microsoft Entra ID
- What is Federation with Microsoft Entra ID?
- Directory synchronization with Microsoft Entra ID
- Manage Microsoft Entra ID using Windows PowerShell
- Hybrid Identity Required Ports and Protocols.
Associate or add an Azure subscription to Microsoft Entra ID
Associate your Azure subscription to Microsoft Entra ID by making the directory a trusted directory for the Azure subscription hosting the database. For details, see Associate or add an Azure subscription to your Microsoft Entra tenant.
Use the directory switcher in the Azure portal to switch to the subscription associated with the domain.
Important
Every Azure subscription has a trust relationship with a Microsoft Entra instance. It trusts that directory to authenticate users, services, and devices. Multiple subscriptions can trust the same directory, but a subscription trusts only one directory. This trust relationship that a subscription has with a directory is unlike that of a subscription with all other resources in Azure (websites, databases, and so on), which are more like child resources of a subscription. If a subscription expires, then access to those other resources associated with the subscription also stops. However, the directory remains in Azure, and you can associate another subscription with that directory and continue to manage the directory users. For more information about resources, see Understanding resource access in Azure. To learn more about this trusted relationship, see How to associate or add an Azure subscription to Microsoft Entra ID.
Microsoft Entra admin with a server in SQL Database
Each logical server in Azure (which hosts SQL Database or Azure Synapse) starts with a single server administrator account that is the administrator of the entire server. Create a second administrator account as a Microsoft Entra account. This principal is created as a contained database user in the master
database of the server. Administrator accounts are members of the db_owner role in every user database, and each user database is entered as the dbo user. For more information about administrator accounts, see Managing Databases and Logins.
The Microsoft Entra administrator must be configured for both the primary and the secondary servers when using Microsoft Entra ID with geo-replication. If a server doesn't have a Microsoft Entra administrator, then Microsoft Entra logins and users receive a Cannot connect
to a server error.
Note
Users not based on a Microsoft Entra account (including the server administrator account) can't create Microsoft Entra-based users, because they don't have permission to validate proposed database users with Microsoft Entra ID.
Provision Microsoft Entra admin (SQL Managed Instance)
Important
Only follow these steps if you are provisioning an Azure SQL Managed Instance. This operation can only be executed by Global Administrator or a Privileged Role Administrator in Microsoft Entra ID.
You can assign the Directory Readers role to a group in Microsoft Entra ID. The group owners can then add the managed instance identity as a member of this group, which allows you to provision a Microsoft Entra admin for the SQL Managed Instance. For more information on this feature, see Directory Readers role in Microsoft Entra for Azure SQL.
Your SQL Managed Instance needs permission to read Microsoft Entra ID to accomplish tasks such as authentication of users through security group membership or creation of new users. For this to work, you must grant the SQL Managed Instance permission to read Microsoft Entra ID. You can do this using the Azure portal or PowerShell.
Azure portal
To grant your SQL Managed Instance read permissions to Microsoft Entra ID using the Azure portal, sign in as a Global Administrator and follow these steps:
In the Azure portal, in the upper-right corner select your account, and then choose Switch directories to confirm which directory is your Current directory. Switch directories, if necessary.
Choose the correct Microsoft Entra directory as the Current directory.
This step links the subscription associated with Microsoft Entra ID to the SQL Managed Instance, ensuring the Microsoft Entra tenant and SQL Managed Instance use the same subscription.
Now, you can choose your Microsoft Entra admin for your SQL Managed Instance. For that, go to your managed instance resource in the Azure portal and select Microsoft Entra admin under Settings.
Select the banner on top of the Microsoft Entra admin page and grant permission to the current user.
After the operation succeeds, the following notification will show up in the top-right corner:
On the Microsoft Entra admin page, select Set admin from the navigation bar to open the Microsoft Entra ID pane.
On the Microsoft Entra ID pane, search for a user, check the box next to the user or group to be an administrator, and then press Select to close the pane and go back to the Microsoft Entra admin page for your managed instance.
The Microsoft Entra ID pane shows all members and groups within your current directory. Grayed-out users or groups can't be selected because they aren't supported as Microsoft Entra administrators. See the list of supported admins in Microsoft Entra features and limitations. Azure role-based access control (Azure RBAC) applies only to the Azure portal and isn't propagated to SQL Database, SQL Managed Instance, or Azure Synapse.
From the navigation bar of the Microsoft Entra admin page for your managed instance, select Save to confirm your Microsoft Entra administrator.
The process of changing the administrator might take several minutes. Then the new administrator appears in the Microsoft Entra admin box.
The Object ID is displayed next to the admin name for Microsoft Entra users and groups. For applications (service principals), the Application ID is displayed.
After provisioning a Microsoft Entra admin for your SQL Managed Instance, you can begin to create Microsoft Entra server principals (logins) with the CREATE LOGIN syntax. For more information, see SQL Managed Instance overview.
Tip
To remove an Admin later, at the top of the Microsoft Entra admin page, select Remove admin, then select Save.
PowerShell
To use PowerShell to grant your SQL Managed Instance read permissions to Microsoft Entra ID, run this script:
# This script grants "Directory Readers" permission to a service principal representing the SQL Managed Instance.
# It can be executed only by a user who is a member of the **Global Administrator** or **Privileged Roles Administrator** role.
Import-Module Microsoft.Graph.Authentication
$managedInstanceName = "<ManagedInstanceName>" # Enter the name of your managed instance
$tenantId = "<TenantId>" # Enter your tenant ID
Connect-MgGraph -TenantId $tenantId -Scopes "RoleManagement.ReadWrite.Directory"
# Get Microsoft Entra "Directory Readers" role and create if it doesn't exist
$roleName = "Directory Readers"
$role = Get-MgDirectoryRole -Filter "DisplayName eq '$roleName'"
if ($role -eq $null) {
# Instantiate an instance of the role template
$roleTemplate = Get-MgDirectoryRoleTemplate -Filter "DisplayName eq '$roleName'"
New-MgDirectoryRoleTemplate -RoleTemplateId $roleTemplate.Id
$role = Get-MgDirectoryRole -Filter "DisplayName eq '$roleName'"
}
# Get service principal for your SQL Managed Instance
$roleMember = Get-MgServicePrincipal -Filter "DisplayName eq '$managedInstanceName'"
$roleMember.Count
if ($roleMember -eq $null) {
Write-Output "Error: No service principal with name '$($managedInstanceName)' found, make sure that managedInstanceName parameter was entered correctly."
exit
}
if (-not ($roleMember.Count -eq 1)) {
Write-Output "Error: Multiple service principals with name '$($managedInstanceName)'"
Write-Output $roleMember | Format-List DisplayName, Id, AppId
exit
}
# Check if service principal is already member of Directory Readers role
$isDirReader = Get-MgDirectoryRoleMember -DirectoryRoleId $role.Id -Filter "Id eq '$($roleMember.Id)'"
if ($isDirReader -eq $null) {
# Add principal to Directory Readers role
Write-Output "Adding service principal '$($managedInstanceName)' to 'Directory Readers' role..."
$body = @{
"@odata.id"= "https://graph.microsoft.com/v1.0/directoryObjects/{$($roleMember.Id)}"
}
New-MgDirectoryRoleMemberByRef -DirectoryRoleId $role.Id -BodyParameter $body
Write-Output "'$($managedInstanceName)' service principal added to 'Directory Readers' role."
} else {
Write-Output "Service principal '$($managedInstanceName)' is already member of 'Directory Readers' role."
}
PowerShell for SQL Managed Instance
To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. See How to install and configure Azure PowerShell for detailed information.
Important
Azure SQL Managed Instance still supports the PowerShell Azure Resource Manager (RM) module, but all future development is for the Az.Sql module. The AzureRM module will receive bug fixes until at least December 2020. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. For more about their compatibility, see Introducing the new Azure PowerShell Az module.
To provision a Microsoft Entra admin, execute the following Azure PowerShell commands:
- Connect-AzAccount
- Select-AzSubscription
The cmdlets used to provision and manage Microsoft Entra admin for your SQL Managed Instance are listed in the following table:
Cmdlet name | Description |
---|---|
Set-AzSqlInstanceActiveDirectoryAdministrator | Provisions a Microsoft Entra administrator for the SQL Managed Instance in the current subscription. (Must be from the current subscription) |
Remove-AzSqlInstanceActiveDirectoryAdministrator | Removes a Microsoft Entra administrator for the SQL Managed Instance in the current subscription. |
Get-AzSqlInstanceActiveDirectoryAdministrator | Returns information about a Microsoft Entra administrator for the SQL Managed Instance in the current subscription. |
The following command gets information about a Microsoft Entra administrator for a SQL Managed Instance named ManagedInstance01 associated with a resource group named ResourceGroup01.
Get-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01"
The following command provisions a Microsoft Entra administrator group named DBAs for the SQL Managed Instance named ManagedInstance01. This server is associated with the resource group ResourceGroup01.
Set-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01" -DisplayName "DBAs" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353b"
The following command removes the Microsoft Entra administrator for the SQL Managed Instance named ManagedInstanceName01 associated with the resource group ResourceGroup01.
Remove-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstanceName01" -Confirm -PassThru
Provision Microsoft Entra admin (SQL Database)
Important
Only follow these steps if you are provisioning a server for SQL Database or Azure Synapse.
The following two procedures show you how to provision a Microsoft Entra administrator for your server in the Azure portal and by using PowerShell.
Azure portal
In the Azure portal, in the upper-right corner, select your account and then choose Switch directory to open the Directories + subscriptions page. Choose the Microsoft Entra directory, which contains your Azure SQL Database or Azure Synapse Analytics as the Current directory.
Search for SQL servers and select the logical server for your Azure SQL Database.
Note
On this page, before you select SQL servers, you can select the star next to the name to favorite the category and add SQL servers to the left navigation menu.
Consider also visiting your Azure SQL dashboard.
On the SQL server page, select Microsoft Entra ID.
On the Microsoft Entra ID page, select Set admin to open the Microsoft Entra ID pane
On the Microsoft Entra ID pane, search for a user and then select the user or group to be an administrator. Use Select to confirm your choice and close the pane to return to your logical server's Microsoft Entra ID page. (The Microsoft Entra ID pane shows all members and groups of your current directory. Grayed-out users or groups can't be selected because they aren't supported as Microsoft Entra administrators. See the list of supported admins in the Microsoft Entra features and limitations section of Use Microsoft Entra authentication with SQL Database or Azure Synapse.) Azure role-based access control (Azure RBAC) applies only to the portal and isn't propagated to the server.
At the top of the Microsoft Entra ID page for your logical server, select Save.
The Object ID is displayed next to the admin name for Microsoft Entra users and groups. For applications (service principals), the Application ID is displayed.
The process of changing the administrator might take several minutes. Then the new administrator appears in the Microsoft Entra admin field.
Note
When setting up the Microsoft Entra admin, the new admin name (user or group) can't already be present in the virtual master
database as a server authentication user. If present, the Microsoft Entra admin setup fails and rolls back, indicating such an admin (name) already exists. Since a server authentication user isn't part of Microsoft Entra ID, any effort to connect to the server using Microsoft Entra authentication fails.
To remove the admin later, at the top of the Microsoft Entra ID page, select Remove admin, then select Save. This disables Microsoft Entra authentication for your logical server.
PowerShell for SQL Database and Azure Synapse
To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. See How to install and configure Azure PowerShell for detailed information. To provision a Microsoft Entra admin, execute the following Azure PowerShell commands:
- Connect-AzAccount
- Select-AzSubscription
Cmdlets used to provision and manage Microsoft Entra admin for SQL Database and Azure Synapse:
Cmdlet name | Description |
---|---|
Set-AzSqlServerActiveDirectoryAdministrator | Provisions a Microsoft Entra administrator for the server hosting SQL Database or Azure Synapse. (Must be from the current subscription) |
Remove-AzSqlServerActiveDirectoryAdministrator | Removes a Microsoft Entra administrator for the server hosting SQL Database or Azure Synapse. |
Get-AzSqlServerActiveDirectoryAdministrator | Returns information about a Microsoft Entra administrator currently configured for the server hosting SQL Database or Azure Synapse. |
Use PowerShell command get-help to see more information for each of these commands. For example, get-help Set-AzSqlServerActiveDirectoryAdministrator
.
The following script provisions a Microsoft Entra administrator group named DBA_Group (object ID 40b79501-b343-44ed-9ce7-da4c8cc7353f
) for the demo_server server in a resource group named Group-23:
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" -DisplayName "DBA_Group"
The DisplayName input parameter accepts either the Microsoft Entra ID display name or the User Principal Name. For example, DisplayName="John Smith"
and DisplayName="johns@contoso.com"
. For Microsoft Entra groups only the Microsoft Entra ID display name is supported.
Note
The Azure PowerShell command Set-AzSqlServerActiveDirectoryAdministrator
doesn't prevent you from provisioning Microsoft Entra admins for unsupported users. An unsupported user can be provisioned but can't connect to a database.
The following example uses the optional ObjectID:
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" `
-DisplayName "DBA_Group" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353f"
Note
The ObjectID is required when the DisplayName is not unique. To retrieve the ObjectID and DisplayName values, you can view the properties of a user or group in the Microsoft Entra ID section of the Azure Portal.
The following example returns information about the current Microsoft Entra admin for the server:
Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" | Format-List
The following example removes a Microsoft Entra administrator:
Remove-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server"
Note
You can also provision a Microsoft Entra Administrator by using the REST APIs. For more information, see Service Management REST API Reference and Operations for Azure SQL Database Operations for Azure SQL Database
Configure your client computers
Note
System.Data.SqlClient uses the Azure Active Directory Authentication Library (ADAL), which is deprecated. If you're using the System.Data.SqlClient namespace for Microsoft Entra authentication, migrate applications to Microsoft.Data.SqlClient and the Microsoft Authentication Library (MSAL). For more information, see Using Microsoft Entra authentication with SqlClient.
If you must continue using ADAL.DLL in your applications, you can use the links in this section to install the latest ODBC or OLE DB driver, which contain the latest ADAL.DLL library.
On all client machines from which your applications or users connect to SQL Database or Azure Synapse using Microsoft Entra identities, you must install the following software:
- .NET Framework 4.6 or later from https://msdn.microsoft.com/library/5a4x27ek.aspx.
- Microsoft Authentication Library (MSAL) or Microsoft Authentication Library for SQL Server (ADAL.DLL). Below are the download links to install the latest SSMS, ODBC, and OLE DB driver that contains the ADAL.DLL library.
You can meet these requirements by:
- Installing the latest version of SQL Server Management Studio or SQL Server Data Tools meets the .NET Framework 4.6 requirement.
- SSMS installs the x86 version of ADAL.DLL.
- SSDT installs the amd64 version of ADAL.DLL.
- The latest Visual Studio from Visual Studio Downloads meets the .NET Framework 4.6 requirement but doesn't install the required amd64 version of ADAL.DLL.
Create contained users mapped to Microsoft Entra identities
This section reviews the requirements and important considerations to use Microsoft Entra authentication with Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse.
Microsoft Entra authentication with SQL Database and Azure Synapse requires using contained database users based on a Microsoft Entra identity. A contained database user doesn't have a login in the
master
database, and maps to an identity in the Microsoft Entra ID associated with the database. The Microsoft Entra identity can be an individual user account, group, or application. For more information about contained database users, see Contained Database Users- Making Your Database Portable. For more information about creating contained database users based on Microsoft Entra identities, see CREATE USER (Transact-SQL).Because SQL Managed Instance supports Microsoft Entra server principals (logins), using contained database users isn't required. This lets you create logins from Microsoft Entra users, groups, or applications. This means you can authenticate with your SQL Managed Instance using the Microsoft Entra server login rather than a contained database user. For more information, see SQL Managed Instance overview. For syntax on creating Microsoft Entra server principals (logins), see CREATE LOGIN.
Database users (except administrators) can't create a database using the Azure portal. Microsoft Entra roles aren't propagated to the database in SQL Database, SQL Managed Instance, or Azure Synapse. Microsoft Entra roles manage Azure resources and don't apply to database permissions. For example, the SQL Server Contributor role doesn't grant access to connect to the database in SQL Database, SQL Managed Instance, or Azure Synapse. The access permission must be granted directly in the database using Transact-SQL statements.
You can't directly create a database user for an identity managed in a different Microsoft Entra tenant than the one associated with your Azure subscription. However, users in other directories can be imported into the associated directory as external users. They can then be used to create contained database users that can access the SQL Database. External users can also gain access through membership in Microsoft Entra groups that contain database users.
Special characters like colon
:
or ampersand&
when included as user names in the T-SQLCREATE LOGIN
andCREATE USER
statements aren't supported.
Important
Microsoft Entra users and service principals (Microsoft Entra applications) that are members of more than 2048 Microsoft Entra security groups are not supported to login into the database in SQL Database, SQL Managed Instance, or Azure Synapse.
To create a Microsoft Entra ID-based contained database user (other than the server administrator that owns the database), connect to the database with a Microsoft Entra identity as a user with at least the ALTER ANY USER permission. In the following T-SQL example, Microsoft_Entra_principal_name
can be the user principal name of a Microsoft Entra user or the display name for a Microsoft Entra group.
CREATE USER [<Microsoft_Entra_principal_name>] FROM EXTERNAL PROVIDER;
Examples: To create a contained database user representing a Microsoft Entra federated or managed domain user:
CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
To create a contained database user representing a Microsoft Entra group, provide the display name of the group:
CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
To create a contained database user representing an application that connects using a Microsoft Entra token:
CREATE USER [appName] FROM EXTERNAL PROVIDER;
The CREATE USER ... FROM EXTERNAL PROVIDER
command requires SQL access to Microsoft Entra ID (the "external provider") on behalf of the logged-in user. Sometimes, circumstances arise that cause Microsoft Entra ID to return an exception to SQL.
- You might encounter SQL error 33134, which contains the Microsoft Entra ID-specific error message. The error usually says that access is denied, that the user must enroll in MFA to access the resource, or that access between first-party applications must be handled via preauthorization. In the first two cases, the issue is usually caused by Conditional Access policies that are set in the user's Microsoft Entra tenant: they prevent the user from accessing the external provider. Updating the Conditional Access policies to allow access to the application '00000003-0000-0000-c000-000000000000' (the application ID of the Microsoft Graph API) should resolve the issue. If the error says access between first-party applications must be handled via preauthorization, the issue is because the user is signed in as a service principal. The command should succeed if it's executed by a user instead.
- If you receive a Connection Timeout Expired, you might need to set the
TransparentNetworkIPResolution
parameter of the connection string to false. For more information, see Connection timeout issue with .NET Framework 4.6.1 - TransparentNetworkIPResolution.
Important
Removing the Microsoft Entra administrator for the server prevents any Microsoft Entra authentication user from connecting to the server. If necessary, a SQL Database administrator can drop unusable Microsoft Entra users manually.
When you create a database user, that user receives the CONNECT permission and can connect to that database as a member of the PUBLIC role. Initially, the only permissions available to the user are granted to the PUBLIC role and to any Microsoft Entra groups where they're a member. Granting permissions to Microsoft Entra-based contained database users operates the same way as granting permission to any other type of user. It's recommended to grant permissions to database roles and add users to those roles rather than directly granting permissions to individual users. For more information, see Database Engine Permission Basics. For more information about special SQL Database roles, see Managing Databases and Logins in Azure SQL Database. A federated domain user account that is imported into a managed domain as an external user, must use the managed domain identity.
Microsoft Entra users are marked in the database metadata with type E (EXTERNAL_USER) and for groups with type X (EXTERNAL_GROUPS). For more information, see sys.database_principals.
Connect to the database using SSMS or SSDT
To confirm the Microsoft Entra administrator is properly set up, connect to the master
database using the Microsoft Entra administrator account.
To create a Microsoft Entra-based contained database user, connect to the database with a Microsoft Entra identity with access to the database and at least the ALTER ANY USER
permission.
Use a Microsoft Entra identity to connect using SSMS or SSDT
The following procedures show you how to connect to SQL Database with a Microsoft Entra identity using SQL Server Management Studio (SSMS) or SQL Server Database Tools (SSDT).
Microsoft Entra ID - Integrated
Use this method if you're logged into Windows using your Microsoft Entra credentials from a federated domain, or a managed domain configured for seamless single sign-on for pass-through and password hash authentication. For more information, see Microsoft Entra seamless single sign-on.
Start SSMS or SSDT and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Azure Active Directory - Integrated. No need to enter a password because your existing credentials are presented for the connection.
Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to.
Microsoft Entra ID - Password
Use this method when connecting with a Microsoft Entra principal name using the Microsoft Entra managed domain. You can also use it for federated accounts without access to the domain, for example, when working remotely.
Use this method to authenticate to the database in SQL Database or the SQL Managed Instance with Microsoft Entra cloud-only identity users, or those who use Microsoft Entra hybrid identities. This method supports users who want to use their Windows credential, but their local machine isn't joined with the domain (for example, using remote access). In this case, a Windows user can indicate their domain account and password, and can authenticate to the database in SQL Database, the SQL Managed Instance, or Azure Synapse.
Start SSMS or SSDT and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Azure Active Directory - Password.
In the User name box, type your Microsoft Entra user name in the format
username\@domain.com
. User names must be an account from Microsoft Entra ID or an account from a managed or federated domain with Microsoft Entra ID.In the Password box, type your user password for the Microsoft Entra account or managed/federated domain account.
Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to. (See the graphic in the previous option.)
Microsoft Entra ID - Universal with MFA
Use this method for interactive authentication with multifactor authentication (MFA), with the password being requested interactively. This method can be used to authenticate to databases in SQL Database, SQL Managed Instance, and Azure Synapse for Microsoft Entra cloud-only identity users, or those who use Microsoft Entra hybrid identities.
For more information, see Using multi-factor Microsoft Entra authentication with SQL Database and Azure Synapse (SSMS support for MFA).
Microsoft Entra ID - Service Principal
Use this method to authenticate to the database in SQL Database or SQL Managed Instance with Microsoft Entra service principals (Microsoft Entra applications). For more information, see Microsoft Entra service principal with Azure SQL.
Microsoft Entra ID - Managed Identity
Use this method to authenticate to the database in SQL Database or SQL Managed Instance with Microsoft Entra managed identities. For more information, see Managed identities in Microsoft Entra for Azure SQL.
Microsoft Entra ID - Default
The Default authentication option with Microsoft Entra ID enables authentication that's performed through password-less and non-interactive mechanisms including managed identities.
Use Microsoft Entra identity to connect using Azure portal Query editor for Azure SQL Database
For more information on the Azure portal Query editor for Azure SQL Database, see Quickstart: Use the Azure portal query editor to query Azure SQL Database.
Navigate to your SQL database in the Azure portal. For example, visit your Azure SQL dashboard.
On your SQL database Overview page in the Azure portal, select Query editor from the left menu.
On the sign-in screen under Welcome to SQL Database Query Editor, select Continue as <your user or group ID>.
Use a Microsoft Entra identity to connect from a client application
The following procedures show you how to connect to a SQL Database with a Microsoft Entra identity from a client application. This isn't a comprehensive list of authentication methods when using a Microsoft Entra identity. For more information, see Connect to Azure SQL with Microsoft Entra authentication and SqlClient.
Microsoft Entra integrated authentication
To use integrated Windows authentication, your domain's Active Directory must be federated with Microsoft Entra ID, or should be a managed domain that is configured for seamless single sign-on for pass-through or password hash authentication. For more information, see Microsoft Entra seamless single sign-on.
Your client application (or a service) connecting to the database must be running on a domain-joined machine under a user's domain credentials.
To connect to a database using integrated authentication and a Microsoft Entra identity, the Authentication keyword in the database connection string must be set to Active Directory Integrated
. Replace <database_name>
with your database name. The following C# code sample uses ADO .NET.
string ConnectionString = @"Data Source=<database_name>.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
The connection string keyword Integrated Security=True
isn't supported for connecting to Azure SQL Database. When making an ODBC connection, you need to remove spaces and set authentication to ActiveDirectoryIntegrated
.
Microsoft Entra password authentication
To connect to a database using Microsoft Entra cloud-only identity user accounts, or those who use Microsoft Entra hybrid identities, the Authentication keyword must be set to Active Directory Password
. The connection string must contain User ID/UID and Password/PWD keywords and values. Replace <database_name>
, <email_address>
, and <password>
with the appropriate values. The following C# code sample uses ADO .NET.
string ConnectionString =
@"Data Source=<database_name>.database.windows.net; Authentication=Active Directory Password; Initial Catalog=testdb; UID=<email_address>; PWD=<password>";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Learn more about Microsoft Entra authentication methods using the demo code samples available at Microsoft Entra authentication GitHub Demo.
Microsoft Entra ID access token
This authentication method allows middle-tier services to obtain JSON Web Tokens (JWT) to connect to the database in SQL Database, the SQL Managed Instance, or Azure Synapse by obtaining a token from Microsoft Entra ID. This method enables various application scenarios including service identities, service principals, and applications using certificate-based authentication. You must complete four basic steps to use Microsoft Entra token authentication:
- Register your application with Microsoft Entra ID and get the client ID for your code.
- Create a database user representing the application. (Completed earlier in the section Create contained users mapped to Microsoft Entra identities.)
- Create a certificate on the client computer runs the application.
- Add the certificate as a key for your application.
Sample connection string. Replace <database_name>
with your database name:
string ConnectionString = @"Data Source=<database_name>.database.windows.net; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = "Your JWT token";
conn.Open();
For more information, see SQL Server Security Blog. For information about adding a certificate, see Get started with certificate-based authentication in Microsoft Entra ID.
sqlcmd
The following statements connect using version 13.1 of sqlcmd. Download Microsoft Command Line Utilities 14.0 for SQL Server.
Note
sqlcmd
with the -G
command does not work with system identities, and requires a user principal login.
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -G
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -U bob@contoso.com -P MyAADPassword -G -l 30
Troubleshoot Microsoft Entra authentication
For guidance on troubleshooting issues with Microsoft Entra authentication, see Blog: Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW.