Using Azure Active Directory Authentication
In this tutorial, we will learn and understand about Azure Active Directory (Azure AD) authentication mechanism for connecting to Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics using identities in Azure AD.
Azure AD authentication is for managing the identities of database users and other Microsoft services in one central location. However, central ID management provides a single place for managing database users and simplifies permission management. Benefits include the following:
- Firstly, it provides an alternative to SQL Server authentication.
- Secondly, it helps stop the proliferation of user identities across servers.
- Thirdly, it allows password rotation in a single place.
- Fourthly, customers can manage database permissions using external (Azure AD) groups.
- Then, it can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
- Next, Azure AD authentication uses contained database users to authenticate identities at the database level.
- Further, Azure AD supports connections from SQL Server Management Studio that use Active Directory Universal Authentication, which includes Multi-Factor Authentication. Lastly, Azure AD supports similar connections from SQL Server Data Tools (SSDT) that use Active Directory Interactive Authentication.
For configuring and using Azure Active Directory authentication steps include:
- Firstly, create and populate Azure AD.
- Secondly, create an Azure Active Directory administrator.
- Then, configure your client computers.
- After that, create contained database users in your database mapped to Azure AD identities.
- Lastly, connect to your database by using Azure AD identities.
Trust architecture
- Firstly, it considers only the cloud portion of Azure AD, SQL Database, SQL Manageable Instance, and Azure Synapse is for supporting Azure AD native user passwords.
- Secondly, for supporting Windows single sign-on credentials (or user/password for Windows credential), use Azure Active Directory credentials from a manageable domain configuring for seamless single sign-on for pass-through and password hash authentication.
- And, for supporting Federated authentication (or user/password for Windows credentials), the communication with ADFS block is required.
- Lastly, for a sample federated authentication with ADFS infrastructure check the diagram below. The arrows indicate communication pathways.
Administrator structure
- There are two administrator accounts while using Azure AD authentication.
- Firstly, the original Azure SQL Database administrator, and secondly, the Azure AD administrator.
- The first Azure AD contained database users in a user database that can only be created by an administrator with an Azure AD account.
- An Azure AD user or an Azure AD group can be used as the Azure AD administrator login. When the administrator is a group account, it may be utilized by any group member, allowing the server to have numerous Azure AD administrators.
- Using a group account as an administrator also makes things easier to administer. This is due to the ability to create and remove group members in Azure AD without affecting SQL Database or Azure Synapse users or permissions.
Permissions
For creating new users, you must have ALTER ANY USER permission in the database. However, ALTER ANY USER permission can be granted to any database user. Further, this permission is also taken by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database. And, also by members of the db_owner database role.
For creating a contained database user in Azure SQL Database, SQL Managed Instance, or Azure Synapse, you must connect to the database or instance using an Azure AD identity.
And, for creating the first contained database user, you must connect to the database by using an Azure AD administrator (who is the owner of the database). This is validated in Configure and manages Azure Active Directory authentication with SQL Database or Azure Synapse. Only if the Azure AD admin was setup for Azure SQL Database, SQL Managed Instance, or Azure Synapse is Azure AD authentication available. Existing Azure Active Directory users established earlier inside SQL Server will no longer be able to login to the database using their Azure Active Directory credentials if the Azure Active Directory admin is removed from the server.
Azure AD features and limitations
- Firstly, the following members of Azure AD can be provisioned for Azure SQL Database:
- Native members. This refers to a member-created in Azure AD in the managed domain or in a customer domain.
- Next, the members of an Active Directory domain federated with Azure Active Directory on a managed domain configured for seamless single sign-on with pass-through or password hash authentication.
- Then, Imported members from other Azure AD’s who are native or federated domain members.
- Lastly, Active Directory groups develop as security groups.
- Secondly, Azure AD users that are part of a group that has a db_owner server role cannot use the CREATE DATABASE SCOPED CREDENTIAL syntax against Azure SQL Database and Azure Synapse.
- Thirdly, these system functions return NULL values when executed under Azure AD principals:
- SUSER_ID()
- SUSER_NAME(<admin ID>)
- SUSER_SNAME(<admin SID>)
- SUSER_ID(<admin name>)
- SUSER_SID(<admin name>)
SQL Managed Instance
- Firstly, Azure AD server principals (logins) and user assistance for SQL Managed Instance.
- Secondly, SQL Managed Instance does not support setting Azure AD server principals (logins) mapping to an Azure AD group as a database owner.
- Thirdly, Azure AD server principals (logins) support SQL Agent management and job execution.
- Fourthly, Database backup, and restore operations can be executed by Azure AD server principals (logins).
- Then, auditing of all statements related to Azure AD server principals (logins) and authentication events is supported.
- After that, an administrator connection for Azure AD server principals (logins) which are members of the sysadmin server role is supported.
- Lastly, logon triggers assists for logon events coming from Azure AD server principals (logins).
Connect by using Azure AD identities
Azure Active Directory authentication supports the following methods for connecting to a database using Azure AD identities:
- Firstly, Azure Active Directory Password
- Secondly, Azure Active Directory Integrated
- Then, Azure Active Directory Universal with Multi-Factor Authentication
- Lastly, using Application token authentication
Reference: Microsoft Documentation