{"id":95075,"date":"2022-11-21T17:00:03","date_gmt":"2022-11-21T17:00:03","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95075"},"modified":"2022-11-21T22:19:07","modified_gmt":"2022-11-21T22:19:07","slug":"sql-server-2022-azure-ad-authentication","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-2022-azure-ad-authentication\/","title":{"rendered":"SQL Server 2022: Azure AD Authentication"},"content":{"rendered":"

SQL Server 2022<\/strong> 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.<\/p>\n

SQL Server on premises requires Azure ARC<\/strong> 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<\/strong>.<\/p>\n

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.<\/p>\n

<\/p>\n

 <\/p>\n

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.<\/p>\n

<\/p>\n

 <\/p>\n

Define the Identity Permissions<\/h2>\n

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:<\/p>\n

1) Access Azure Active Directory<\/p>\n

2) Click the Role and Administrators tab<\/p>\n

3) On the search text box, type “Directory” to locate the directory readers role<\/p>\n

<\/p>\n

 <\/p>\n

4) Click the Directory Readers role<\/p>\n

5) Click the Add assignments button<\/p>\n

<\/p>\n

6) Locate the VM identity and click the add button<\/p>\n

<\/p>\n

 <\/p>\n

<\/p>\n

 <\/p>\n

Set the Azure Authentication in SQL Server 2022<\/h2>\n

After these steps, we can enable the Azure AD Authentication on the SQL Server IaaS agent following these steps:<\/p>\n

7) Access the Security Configuration<\/strong> tab<\/p>\n

<\/p>\n

 <\/p>\n

8) Click the Enable<\/strong> option<\/p>\n

9) On the Managed identity type drop down<\/em>, select the identity to be used.<\/p>\n

<\/p>\n

 <\/p>\n

10) Click the Apply<\/strong> button<\/p>\n

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:<\/p>\n

CREATE<\/span>\u00a0login<\/span>\u00a0[dennes@dennesbufaloinfocom.onmicrosoft.com]<\/span>\u00a0FROM<\/span>\u00a0EXTERNAL<\/span>\u00a0provider<\/span>
\nALTER<\/span>\u00a0server<\/span>\u00a0role<\/span>\u00a0sysadmin<\/span>\u00a0ADD<\/span>\u00a0member<\/span>
\n[dennes@dennesbufaloinfocom.onmicrosoft.com]<\/span>\u00a0 <\/span><\/div>\n

 <\/p>\n

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.<\/p>\n

Conclusion<\/h2>\n

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.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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,……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5364,145788,147170],"coauthors":[6810],"class_list":["post-95075","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure","tag-azure-active-directory","tag-sql-server-2022"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95075","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95075"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95075\/revisions"}],"predecessor-version":[{"id":95084,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95075\/revisions\/95084"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95075"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95075"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95075"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95075"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}