Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Tutorial: Enable Azure Active Directory simply authentication with Azure SQL
Article
10 minutes to read
Thank you lot.
APPLIES TO: Azure SQL Database Azure SQL Managed Instance
This article guides y'all through enabling the Azure AD-simply hallmark feature within Azure SQL Database and Azure SQL Managed Example. If you lot are looking to provision a SQL Database or SQL Managed Instance with Azure Advertizement-only authentication enabled, see Create server with Azure AD-only hallmark enabled in Azure SQL.
In this tutorial, you learn how to:
Assign office to enable Azure Ad-only hallmark
Enable Azure AD-only authentication using the Azure portal, Azure CLI, or PowerShell
Bank check whether Azure Advertisement-just hallmark is enabled
Exam connecting to Azure SQL
Disable Azure AD-only hallmark using the Azure portal, Azure CLI, or PowerShell
Prerequisites
An Azure AD instance. For more information, see Configure and manage Azure AD authentication with Azure SQL.
A SQL Database or SQL Managed Instance with a database, and logins or users. Come across Quickstart: Create an Azure SQL Database single database if you oasis't already created an Azure SQL Database, or Quickstart: Create an Azure SQL Managed Instance.
In order to enable or disable Azure AD-just authentication, selected congenital-in roles are required for the Azure Advertisement users executing these operations in this tutorial. We're going to assign the SQL Security Manager role to the user in this tutorial.
For more than data on how to assign a office to an Azure Advertizing account, run into Assign administrator and not-administrator roles to users with Azure Active Directory
For more information on the required permission to enable or disable Azure AD-merely authentication, see the Permissions section of Azure AD-only authentication article.
In our example, nosotros'll assign the SQL Security Manager function to the user UserSqlSecurityManager@contoso.onmicrosoft.com. Using privileged user that can assign Azure Advert roles, sign into the Azure portal.
Go to your SQL server resource, and select Access control (IAM) in the menu. Select the Add together button and then Add together role consignment in the drop-downwards menu.
In the Add role assignment pane, select the Office SQL Security Director, and select the user that you want to accept the ability to enable or disable Azure AD-simply authentication.
Click Save.
Portal
The Azure CLI
PowerShell
Enable in SQL Database using Azure portal
To enable Azure AD-only authentication auth in the Azure portal, see the steps below.
Using the user with the SQL Security Manager role, get to the Azure portal.
Go to your SQL server resource, and select Azure Agile Directory under the Settings menu.
If you haven't added an Azure Active Directory admin, you'll need to set this earlier yous can enable Azure Advertizement-only authentication.
Select the Support only Azure Active Directory authentication for this server checkbox.
The Enable Azure Advertizing authentication only popup volition show. Click Yes to enable the feature and Save the setting.
Enable in SQL Managed Case using Azure portal
To enable Azure Ad-only authentication auth in the Azure portal, see the steps below.
Using the user with the SQL Security Manager part, go to the Azure portal.
Get to your SQL managed example resources, and select Agile Directory admin under the Settings card.
If you haven't added an Azure Active Directory admin, yous'll need to prepare this before you can enable Azure AD-only hallmark.
Select the Support but Azure Agile Directory hallmark for this managed instance checkbox.
The Enable Azure Advertising authentication simply popup volition show. Click Yep to enable the characteristic and Save the setting.
Enable in SQL Database using Azure CLI
To enable Azure AD-only authentication in Azure SQL Database using Azure CLI, see the commands below. Install the latest version of Azure CLI. Yous must have Azure CLI version 2.fourteen.ii or higher. For more information on these commands, see az sql server ad-just-auth.
For more information on managing Azure AD-only authentication using APIs, see Managing Azure Advertizing-merely authentication using APIs.
Note
The Azure Advert admin must be set for the server before enabling Azure Ad-but hallmark. Otherwise, the Azure CLI control will neglect.
For permissions and actions required of the user performing these commands to enable Azure Ad-just authentication, see the Azure AD-only hallmark article.
Sign into Azure using the business relationship with the SQL Security Manager function.
az login
Run the following control, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resources that holds the SQL server.
az sql server ad-just-auth enable --resource-grouping <myresource> --proper name <myserver>
Enable in SQL Managed Instance using Azure CLI
To enable Azure Advertisement-simply authentication in Azure SQL Managed Instance using Azure CLI, see the commands beneath. Install the latest version of Azure CLI.
Sign into Azure using the account with the SQL Security Managing director role.
az login
Run the following control, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.
az sql mi advert-just-auth enable --resources-group <myresource> --name <myserver>
Enable in SQL Database using PowerShell
To enable Azure AD-only authentication in Azure SQL Database using PowerShell, come across the commands below. Az.Sql 2.10.0 module or college is required to execute these commands. For more information on these commands, encounter Enable-AzSqlInstanceActiveDirectoryOnlyAuthentication.
For more information on managing Azure AD-but authentication using APIs, meet Managing Azure AD-only hallmark using APIs
Note
The Azure Advert admin must exist set for the server before enabling Azure AD-merely authentication. Otherwise, the PowerShell command will fail.
For permissions and deportment required of the user performing these commands to enable Azure AD-only hallmark, see the Azure Advertizement-only hallmark article. If the user has insufficient permissions, you will get the following error:
Enable-AzSqlServerActiveDirectoryOnlyAuthentication : The client 'UserSqlServerContributor@contoso.onmicrosoft.com' with object id '<guid>' does non have potency to perform activeness 'Microsoft.Sql/servers/azureADOnlyAuthentications/write' over scope '/subscriptions/<guid>...'
Sign into Azure using the account with the SQL Security Director role.
Connect-AzAccount
Run the post-obit command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.
To enable Azure Advert-simply authentication in Azure SQL Managed Case using PowerShell, see the commands below. Az.Sql 2.10.0 module or college is required to execute these commands.
For more information on managing Azure AD-just hallmark using APIs, see Managing Azure AD-just hallmark using APIs.
Sign into Azure using the account with the SQL Security Managing director office.
Connect-AzAccount
Run the following command, replacing <myinstance> with your SQL Managed Instance proper noun, and <myresource> with your Azure Resource that holds the SQL managed case.
Check whether Azure AD-only hallmark is enabled for your server or case.
Portal
The Azure CLI
PowerShell
Check status in SQL Database
Go to your SQL server resource in the Azure portal. Select Azure Active Directory under the Settings carte du jour.
Check status in SQL Managed Instance
Go to your SQL managed instance resource in the Azure portal. Select Active Directory admin under the Settings menu.
These commands can be used to cheque whether Azure Ad-simply authentication is enabled for your logical server for Azure SQL Database, or SQL Managed Instance. Members of the SQL Server Correspondent and SQL Managed Instance Correspondent roles can utilise these commands to bank check the status of Azure AD-only authentication, but can't enable or disable the feature.
Bank check status in SQL Database
Sign into Azure using the account with the SQL Security Director role. For more information on managing Azure AD-only authentication using APIs, meet Managing Azure AD-only authentication using APIs
az login
Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resource that holds the SQL server.
az sql server ad-merely-auth get --resources-group <myresource> --name <myserver>
These commands tin be used to check whether Azure Advertising-only hallmark is enabled for your logical server for Azure SQL Database, or SQL Managed Case. Members of the SQL Server Correspondent and SQL Managed Example Correspondent roles can use these commands to check the status of Azure Advertizement-but authentication, but can't enable or disable the feature.
The status will return True if the characteristic is enabled, and Imitation if disabled.
Check status in SQL Database
Sign into Azure using the business relationship with the SQL Security Manager role. For more information on managing Azure AD-only authentication using APIs, run into Managing Azure AD-only authentication using APIs
Connect-AzAccount
Run the post-obit command, replacing <myserver> with your SQL server proper name, and <myresource> with your Azure Resource that holds the SQL server.
Sign into Azure using the account with the SQL Security Manager role.
Connect-AzAccount
Run the post-obit command, replacing <myinstance> with your SQL Managed Case name, and <myresource> with your Azure Resource that holds the SQL managed instance.
Afterwards enabling Azure Advertizing-only authentication, exam with SQL Server Direction Studio (SSMS) to connect to your SQL Database or SQL Managed Instance. Apply SQL hallmark for the connection.
You should see a login failed message like to the following output:
Cannot connect to <myserver>.database.windows.internet. Additional data: Login failed for user 'username'. Reason: Azure Active Directory only hallmark is enabled. Please contact your system administrator. (Microsoft SQL Server, Error: 18456)
By disabling the Azure AD-but authentication feature, you lot allow both SQL authentication and Azure Advertizement hallmark for Azure SQL.
Portal
The Azure CLI
PowerShell
Disable in SQL Database using Azure portal
Using the user with the SQL Security Director role, become to the Azure portal.
Become to your SQL server resource, and select Azure Active Directory nether the Settings menu.
To disable the Azure Advert-just authentication characteristic, uncheck the Support only Azure Active Directory authentication for this server checkbox and Save the setting.
Disable in SQL Managed Instance using Azure portal
Using the user with the SQL Security Manager role, go to the Azure portal.
Go to your SQL managed case resource, and select Agile Directory admin under the Settings menu.
To disable the Azure Ad-just authentication characteristic, uncheck the Back up only Azure Active Directory authentication for this managed instance checkbox and Save the setting.
Disable in SQL Database using Azure CLI
To disable Azure Advertizing-only authentication in Azure SQL Database using Azure CLI, see the commands below.
Sign into Azure using the business relationship with the SQL Security Managing director role.
az login
Run the following command, replacing <myserver> with your SQL server name, and <myresource> with your Azure Resources that holds the SQL server.
az sql server ad-only-auth disable --resource-group <myresource> --name <myserver>
Later on disabling Azure Advertisement-only authentication, y'all should see the following output when you check the status:
To disable Azure Advert-only authentication in Azure SQL Managed Instance using Azure CLI, see the commands below.
Sign into Azure using the business relationship with the SQL Security Manager function.
az login
Run the following command, replacing <myserver> with your SQL server proper noun, and <myresource> with your Azure Resources that holds the SQL server.
az sql mi advertizement-only-auth disable --resource-group <myresource> --name <myserver>
Afterwards disabling Azure Ad-only hallmark, yous should see the following output when you check the status:
To disable Azure AD-only authentication in Azure SQL Managed Instance using PowerShell, meet the commands below.
Sign into Azure using the account with the SQL Security Manager role.
Connect-AzAccount
Run the following control, replacing <myinstance> with your SQL Managed Instance name, and <myresource> with your Azure Resources that holds the managed case.
Afterwards disabling Azure AD-merely authentication, test connecting using a SQL hallmark login. Yous should now be able to connect to your server or example.
Next steps
Azure Advertising-only hallmark with Azure SQL
Create server with Azure AD-only authentication enabled in Azure SQL
Using Azure Policy to enforce Azure Active Directory only hallmark with Azure SQL
Azure SQL Database 
