SQL Server 2022: Azure AD Authentication

Comments 0

Share to social media

SQL Server 2022 is finally GA and one of the features I was most expecting is finally available. It’s the allows Azure AD Authentication. Azure AD users can access SQL Server directly, without a second user account.

SQL Server on premises requires Azure ARC to be integrated to Azure. Azure VMs, on the other hand, don’t allow the usage of Azure ARC. Microsoft waited until the last moment to enable the same feature on the SQL Server IAAS Agent Extension.

Azure creates the SQL Server IAAS Agent Extension automatically for us when we create a SQL Server virtual machine. It appears as an additional Virtual Machine object. Using this object, we can control many details of SQL Server configuration inside an azure virtual machine and one of these details is the authentication method.

 

The first requirement for the Azure AD authentication is to set an identify to the virtual machine. On the Virtual Machine, we access the Identity tab and turn on the System Assigned Managed Identity. Azure will create an identity to the VM with the same name as the VM.

 

Define the Identity Permissions

The VM identity needs permission to access active directory for authentication. We can set this permission by assigning the role Directory Readers to the VM account. Follow these steps to set the permissions:

1) Access Azure Active Directory

2) Click the Role and Administrators tab

3) On the search text box, type “Directory” to locate the directory readers role

 

4) Click the Directory Readers role

5) Click the Add assignments button

6) Locate the VM identity and click the add button

 

 

Set the Azure Authentication in SQL Server 2022

After these steps, we can enable the Azure AD Authentication on the SQL Server IaaS agent following these steps:

7) Access the Security Configuration tab

 

8) Click the Enable option

9) On the Managed identity type drop down, select the identity to be used.

 

10) Click the Apply button

Finally, we need to give permission to Azure users to access SQL Server. We need to use SSMS to set the permissions to the Azure users. The statements are simple:

CREATE login [dennes@dennesbufaloinfocom.onmicrosoft.com] FROM EXTERNAL provider
ALTER server role sysadmin ADD member
[dennes@dennesbufaloinfocom.onmicrosoft.com] 

 

Of course, you will adapt the role of your users according to your need. On Azure SQL Databases we can only create users from Azure if we are connected with an Azure account. SQL Server 2022 on an Azure VM doesn’t have this requirement.

Conclusion

SQL Server 2022 is the most cloud connected SQL Server version. Most of the connected features depend on Azure ARC, but Microsoft left for the last minute to enable the features through the SQL Server IaaS agent. One example that this was a last minute feature is how difficult it is to find documentation about this configuration.

 

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com