Azure AD or Microsoft Entra ID Authentication in SQL Server 2022
Azure AD or Microsoft Entra ID authentication in SQL Server 2022
Azure AD Authentication methods
The new functionality allowing users to connect to SQL Server 2022 using the following Azure AD authentication methods:
Azure Active Directory Password
Azure Active Directory Integrated
Azure Active Directory Universal with Multi-Factor Authentication
Azure Active Directory access token
The new authentication mode using Azure AD is based on the central authentication repository provided for Azure cloud. This functionality is already supported for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
Microsoft renamed Azure Active Directory (Azure AD) to Microsoft Entra ID to communicate the multicloud, multiplatform functionality of the products, alleviate confusion with Windows Server Active Directory
For SQL Server to communicate with Azure, both SQL Server and the Windows or Linux host it runs on must be registered with Azure Arc. To enable SQL Server's communication with Azure, you need to install the Azure Arc Agent and Azure extension for SQL Server.
Enable Azure AD or Microsoft Entra ID authentication in SQL Server 2022
The diagram below presents two stages required for a SQL Server 2022 instance to support Azure AD authentication:
Azure AD setup
Azure AD user authentication or Microsoft Entra ID authentication
Steps for Azure AD setup based on the diagram above:
Using Azure Arc in the Azure portal, register a host server (an on-premises Windows or Linux server) with a SQL Server 2022 instance installed on this server. This registration creates an Azure Arc agent on the host server, and you will have a new SQL Server – Azure Arc resource.
In your SQL Server – Azure Arc resource, go to the Azure Active Directory setting to set up an Azure AD administrator for the SQL Server instance.
This setup triggers the Azure AD administrator data transfer to Azure Arc agent.
The Azure Arc agent transfers this information to the SQL Server instance.
Steps for Azure AD or Microsoft Entra ID user authentication based on the below diagrams
For example, SQL Server Management Studio (SSMS) version 18.0 or higher—to connect to the SQL Server instance with the Azure AD admin credentials set up for the SQL Server instance .
In the Options>> tab, check the boxes for Encrypt Connection and Trust server certificate.
If you are using the Azure AD admin, the database connection to <default> (master database) or any other user database is allowed. For other Azure AD users, a connection to a specific user database may be required as they will need permission to connect to that database.
After being authenticated as an Azure AD admin, database operations can be executed.
Once the Azure AD admin is connected to the SQL Server instance, the account can create other Azure AD logins and users, and grant them necessary database permissions
SQL Server 2022 also supports linked servers that can be configured using Azure AD with two authentication mechanisms, by providing credentials for Password or Access token.
SQL Server Management Studio (SSMS) version 20.0 or higher—to connect to the SQL Server instance, You will see Microsoft Entra ID authentication methods,
Benefits of using Azure AD or Microsoft Entra ID authentication
Enabling Azure AD or authentication opens access to the Azure cloud identity system. Azure AD is used by many cloud services and unifies all local authentication mechanisms used by Microsoft products providing one central identity repository and authentication management system available to different platforms, including Azure SQL and SQL Server on-premises. The variety of available authentication methods including single sign-on (SSO) and multifactor authentication (MFA), provides strong security support in the authentication area for different services used internally by Microsoft and by external customers. Azure AD authentication is the recommended authentication method for Azure SQL and SQL Server.