MSDN Documentation

Azure AD Authentication for Azure SQL Database

This tutorial guides you through setting up and using Azure Active Directory (Azure AD) authentication for your Azure SQL Database. Azure AD authentication offers a centralized identity management solution, enabling you to manage database access using familiar Azure AD identities and groups.

Introduction to Azure AD Authentication

Azure AD authentication for Azure SQL Database allows you to connect to your database using identities defined in Azure AD, rather than traditional SQL Server logins. This provides several benefits:

• Centralized Identity Management: Manage users, groups, and service principals in one place.
• Enhanced Security: Leverage Azure AD's security features, including multi-factor authentication (MFA).
• Simplified Access: Users can use their existing Azure AD credentials to access multiple Azure resources.
• Role-Based Access Control (RBAC): Assign permissions to Azure AD groups for easier management.

Prerequisites

Before you begin, ensure you have the following:

• An Azure subscription.
• An Azure SQL Database.
• An Azure Active Directory tenant.
• The necessary permissions to manage Azure AD and Azure SQL Database.

Step 1: Configure Azure AD Admin for Azure SQL Server

To enable Azure AD authentication, you must designate an Azure AD user, group, or service principal as the Azure AD administrator for your Azure SQL Server. This administrator account will have full control over the SQL Server instance.

1.2 Configure Azure AD Admin

In the server's settings, find and click on the Active Directory admin option. Click Set admin.

Step 2: Create an Azure AD User or Group in the Database

Once the Azure AD admin is set, you can create users or groups within your Azure SQL Database that map to Azure AD identities. These identities can then be granted specific permissions.

2.1 Connect to Azure SQL Database

Connect to your Azure SQL Database using SQL Server Management Studio (SSMS) or Azure Data Studio. You can connect using the Azure AD admin account or a SQL login if one is configured.

2.2 Create Database User from Azure AD Identity

Execute the following Transact-SQL (T-SQL) statement to create a database user from an Azure AD user or group:

CREATE USER [AzureADUserOrGroupName] FROM EXTERNAL PROVIDER;

For example, to create a user for an Azure AD user named 'john.doe@example.com':

CREATE USER [john.doe@example.com] FROM EXTERNAL PROVIDER;

Or, to create a user for an Azure AD group named 'DBAdmins':

CREATE USER [DBAdmins] FROM EXTERNAL PROVIDER;

2.3 Grant Permissions

After creating the user, you can grant them database roles or specific permissions. For instance, to add the user to the db_datareader role:

ALTER ROLE db_datareader ADD MEMBER [AzureADUserOrGroupName];

Or to grant specific permissions:

GRANT SELECT ON SCHEMA::dbo TO [AzureADUserOrGroupName];

Step 3: Connect to Azure SQL Database using Azure AD Authentication

Now, users can connect to the database using their Azure AD credentials.

3.1 Using SQL Server Management Studio (SSMS)

When connecting in SSMS:

1. In the Connect to Server dialog, enter your server name.
2. For Authentication, select Azure Active Directory - Universal with MFA or Azure Active Directory - Password (if MFA is not enforced for the user).
3. Enter the Azure AD login name (e.g., john.doe@example.com).
4. If using Universal with MFA, a browser window will open for you to authenticate with your Azure AD credentials and complete the MFA challenge.

3.2 Using Azure Data Studio

In Azure Data Studio, when setting up a new connection:

1. Enter your server name.
2. For Authentication type, select Azure Active Directory.
3. Enter your User name (Azure AD login).
4. Click Connect. You will be prompted to authenticate via your Azure AD credentials.

Best Practices and Advanced Scenarios

• Use Azure AD Groups: Managing permissions via Azure AD groups is highly recommended for scalability and ease of management.
• Principle of Least Privilege: Grant only the necessary permissions to users and groups.
• Service Principals: For applications connecting to Azure SQL Database, use Azure AD service principals for authentication.
• Auditing: Configure Azure SQL Database auditing to track access and operations performed by Azure AD users.

Conclusion

Azure AD authentication significantly enhances the security and manageability of your Azure SQL Database. By following these steps, you can leverage your existing Azure AD identities for seamless and secure database access.