Configure and manage Azure AD authentication with Azure SQL
We’ll learn how to set up and administer an Azure Active Directory (Azure AD) instance, as well as how to integrate Azure AD with Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics in this lesson.
Azure AD authentication methods
The following authentication methods are supported by Azure AD authentication:
- Firstly, Azure AD cloud-only identities
- Secondly, Azure AD hybrid identities that support:
- Cloud authentication with two options coupled with seamless single sign-on (SSO)
- Azure AD password hash authentication
- Azure AD pass-through authentication
- Cloud authentication with two options coupled with seamless single sign-on (SSO)
- Lastly, Federated authentication
Associate or add an Azure subscription to Azure Active Directory
- Firstly, associate your Azure subscription to Azure Active Directory by making the directory a trusted directory for the Azure subscription hosting the database.
- Secondly, use the directory switcher in the Azure portal to switch to the subscription associated with domain.
Steps to configure and manage Azure AD authentication
To configure and manage Azure AD authentication with Azure SQL, follow these steps:
- Create an Azure AD tenant and add users: If you don’t already have an Azure AD tenant, create one. Then add users to the tenant who will need access to Azure SQL.
- Create an Azure SQL database: If you don’t already have an Azure SQL database, create one in the Azure portal.
- Configure Azure AD authentication for the Azure SQL server: In the Azure portal, navigate to the Azure SQL server that hosts your database. Select the “Active Directory admin” tab and select “Set admin”. Then choose the Azure AD tenant and user you want to use as the Azure SQL server administrator.
- Configure Azure AD authentication for the Azure SQL database: In the Azure portal, navigate to your Azure SQL database. Select the “Active Directory admin” tab and select “Set admin”. Then choose the Azure AD tenant and user you want to use as the Azure SQL database administrator.
- Grant Azure AD users access to the Azure SQL database: In the Azure portal, navigate to your Azure SQL database. Select the “Firewalls and virtual networks” tab and add the IP addresses or IP address ranges that you want to grant access to the database. Then, under the “Access control (IAM)” tab, add the Azure AD users or groups that you want to grant access to the database.
- Test the Azure AD authentication: To test the Azure AD authentication, try to connect to the Azure SQL database using one of the Azure AD users you granted access to. You can use any tool that supports Azure AD authentication, such as Azure Data Studio or SQL Server Management Studio.
- Manage Azure AD authentication: To manage Azure AD authentication, you can use Azure Active Directory to add or remove users or groups from the Azure SQL database access control list, or to manage the Azure SQL server administrators. You can also use Azure SQL auditing and threat detection to monitor and manage user access to the database.
Azure AD admin with a server in SQL Database
Each server in Azure (which runs SQL Database or Azure Synapse) starts with a single server administrator account that is the administrator of the whole server. As an Azure AD account, create a second administrator account. However, in the server’s master database, this principal is formed as a confined database user. Every user database’s administrator accounts are members of the db_owner role, and they log in as the dbo user.
Furthermore, the Azure Active Directory administrator must be configured for both the primary and secondary servers when using geo-replication with Azure Active Directory. Azure Active Directory logins and users receive a Cannot connect to server error if a server does not have an Azure Active Directory administrator.
Provision Azure AD admin (SQL Managed Instance)
To complete tasks such as user authentication through security group membership or the creation of new users, your SQL Managed Instance requires permissions to read Azure AD. You must provide the SQL Managed Instance access to read Azure AD for this to operate. You may either use the Azure interface or PowerShell to accomplish this.
Azure portal
To grant your SQL Managed Instance Azure AD read permission using the Azure portal, log in as Global/Company administrator in Azure AD and follow these steps:
- Firstly, in the Azure portal, in the upper-right corner, select your connection from a drop-down list of possible Active Directories.
- Secondly, choose the correct Active Directory as the default Azure AD.
- Thirdly, navigate to the SQL Managed Instance you want to use for Azure AD integration.
- Then, select the banner on top of the Active Directory admin page and grant permission to the current user.
- After the operation succeeds, the following notification will show up in the top-right corner:
- Now you can choose your Azure AD admin for your SQL Managed Instance. For that, on the Active Directory admin page, select Set admin command.
- Then, on the Azure AD admin page, search for a user, select the user or group to be an administrator, and then select Select.
- Lastly, at the top of the Active Directory admin page, select Save.
- The process of changing the administrator may take several minutes. Then the new administrator appears in the Active Directory admin box.
Provision Azure AD admin (SQL Database)
The Azure portal and PowerShell can provision an Azure Active Directory administrator for your server in the following two methods.
Azure portal
- Firstly, in the Azure portal, in the upper-right corner, select your connection to drop down a list of possible Active Directories. There, choose the correct Active Directory as the default Azure AD. This step links the subscription-associated Active Directory with server making sure that the same subscription is used for both Azure AD and the server.
- Secondly, search for and select SQL server.
- Thirdly, on the SQL Server page, select Active Directory admin.
- Then, in the Active Directory admin page, select Set admin.
- After that, in the Add admin page, search for a user, select the user or group to be an administrator, and then select Select.
- Lastly, at the top of the Active Directory admin page, select SAVE.
- The process of changing the administrator may take several minutes. Then the new administrator appears in the Active Directory admin box.
Create contained users mapped to Azure AD identities
Using confined database users isn’t necessary because SQL Managed Instance supports Azure AD server principals (logins). You may construct logins from Azure AD users, groups, or apps using Azure AD server principles (logins). This implies you may use the Azure AD server login to log in to your SQL Managed Instance.
Using Azure Active Directory authentication with SQL Database and Azure Synapse, on the other hand, necessitates the usage of confined database users with Azure AD identities. A contained database user does not have a login in the master database but instead maps to an Azure AD identity that is linked to the database. An Azure AD identity might be a single user account or a group of users.
Further, to create an Azure AD-based contained database user, connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
SQL
CREATE USER FROM EXTERNAL PROVIDER;
Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group.
Examples: To create a contained database user representing an Azure AD federated or managed domain user:
SQL
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:
SQL
CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
However, 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. The user’s sole rights are those assigned to the PUBLIC role and those granted to any Azure AD groups to which they belong.
Using an Azure AD identity to connect using SSMS or SSDT
The instructions below demonstrate how to connect to SQL Database using SQL Server Management Studio or SQL Server Database Tools with an Azure AD identity.
Active Directory integrated authentication
If you’re connected into Windows with your Azure Active Directory credentials from a federated domain or a managed domain with seamless single sign-on for pass-through and password hash authentication, use this technique.
- Firstly, Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box. Then, in the Authentication box, select Azure Active Directory – Integrated. No password is needed or can be entered because your existing credentials will be presented for the connection.
- Then, 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.
Active Directory password authentication
When connecting to an Azure AD principal name via the Azure AD managed domain, use this technique. It may also be used for federated accounts without domain access, such as while working remotely.
- Firstly, Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box. Then, in the Authentication box, select Azure Active Directory – Password.
- Secondly, in the User name box, type your Azure Active Directory user name in the format [email protected]. User names must be an account from Azure Active Directory or an account from a managed or federated domain with Azure Active Directory.
- Then, in the Password box, type your user password for the Azure Active Directory account or managed/federated domain account.
- Lastly, 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.
Using an Azure AD identity to connect from a client application
The instructions below demonstrate how to connect a client application to a SQL Database using an Azure AD identity.
Active Directory integrated authentication
Your domain’s Active Directory must be federated with Azure Active Directory, or it must be a managed domain setup for seamless single sign-on for pass-through or password hash authentication to use integrated Windows authentication.
The Authentication keyword in the database connection string must be set to Active Directory Integrated to connect to a database utilising integrated authentication and an Azure AD identity. The following C# code sample uses ADO .NET.
C#
string ConnectionString = @”Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;”;
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Active Directory password authentication
The Authentication keyword must be set to Active Directory Password when connecting to a database using Azure AD cloud-only identity user accounts or Azure AD hybrid identities. User ID/UID and Password/PWD keywords and values must be present in the connection string. ADO.NET is in the following C# code sample.
C#
string ConnectionString =
@”Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Password; Initial Catalog=testdb; [email protected]; PWD=MyPassWord!”;
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
DP-300 Exam Practice Questions
Question: You have an Azure SQL database that you want to secure with Azure AD authentication. Which of the following steps should you perform first?
A) Add users to the Azure SQL database
B) Create an Azure AD tenant and add users
C) Configure Azure SQL auditing and threat detection
D) Grant Azure AD users access to the Azure SQL database
Answer: b) Create an Azure AD tenant and add users
Explanation: Before you can configure Azure AD authentication for an Azure SQL database, you need to have an Azure AD tenant and add users to it who will need access to the database.
Question: You want to configure Azure AD authentication for an Azure SQL database. Which of the following steps should you perform next after creating an Azure AD tenant and adding users?
A) Grant Azure AD users access to the Azure SQL database
B) Create an Azure SQL database
C) Configure Azure AD authentication for the Azure SQL server
D) Configure Azure AD authentication for the Azure SQL database
Answer: d) Configure Azure AD authentication for the Azure SQL database
Explanation: After creating an Azure AD tenant and adding users, the next step in configuring Azure AD authentication for an Azure SQL database is to configure it for the database itself.
Question: You have configured Azure AD authentication for an Azure SQL database. Which of the following steps should you perform to grant access to specific Azure AD users or groups?
A) Add the users or groups to the Azure SQL server admin list
B) Add the users or groups to the Azure SQL database access control list
C) Add the users or groups to the Azure AD tenant
D) Grant the users or groups the “Contributor” role for the Azure SQL resource
Answer: b) Add the users or groups to the Azure SQL database access control list
Explanation: To grant access to specific Azure AD users or groups, you need to add them to the Azure SQL database access control list. This can be done under the “Access control (IAM)” tab for the Azure SQL database in the Azure portal.
Question: You want to manage Azure AD authentication for an Azure SQL database by using Azure Active Directory. Which of the following actions can you perform with Azure Active Directory?
A) Monitor user access to the Azure SQL database
B) Add or remove users or groups from the Azure SQL database access control list
C) Manage the Azure SQL server administrators
D) All of the above
Answer: d) All of the above
Explanation: Azure Active Directory can be used to manage Azure AD authentication for an Azure SQL database, including monitoring user access, adding or removing users or groups from the access control list, and managing the Azure SQL server administrators.
Question: You want to test Azure AD authentication for an Azure SQL database. Which of the following tools can you use to connect to the database?
A) SQL Server Management Studio
B) Azure Data Studio
C) Both a) and b)
D) Neither a) nor b)
Answer: c) Both a) and b)
Explanation: Both SQL Server Management Studio and Azure Data Studio support Azure AD authentication, so either tool can be used to test Azure AD authentication for an Azure SQL database.
Reference: Microsoft Documentation