One of the biggest drawbacks to SQL Database adoption has been the need to create separate SQL authentication users in each database. However, as of August 4, 2016, Azure Active Directory authentication has become generally available.
Let me explain a few components:
The easiest part of this process is configuring the database. All the work before that, of setting up Azure AD, then integrating with your Windows AD, is the hard part - and beyond the scope of this blog post. Azure AD Connect is the current tool of choice to integrate. Want more information on getting Azure AD set up or integrating with Windows AD? Our Cloud & Hybrid Datacenter team can help!
Once that’s configured, it’s easy to set up AD authentication to your database.
Log into the portal and click on SQL Servers. Click on your server, then Overview. In the Overview blade, under Active Directory Admin, click Not configured. On the AD Admin blade, click Set Admin.
Search for an AD user. When you find the user, click to choose, then click Select at the bottom of the blade.
Make sure that on the AD Admin blade, you click “… More” and click Save.
(This can also be done via PowerShell – you can see the cmdlets here.)
Now, as an AD Admin, I can connect to the server using SQL Server Management Studio. (Make sure you have the latest release of SSMS!)
When connecting, I choose “Active Directory Integrated Authentication” because I am logged into this computer using the domain credentials. The “Active Directory Password Authentication” option could be used if I wanted to connect using my domain credentials, but was on an un-federated domain or computer, and I knew the username and password for the account.
The AD admin can now add other AD accounts. Connect to the database you wish to create the account in and issue the command:
CREATE USER [firstname.lastname@example.org] FROM EXTERNAL PROVIDER;
This creates a contained user in that database. After this, if I want to give the user permissions to a specific object, or make them a member of a database-level role, I grant permissions. For example:
sp_addrolemember db_datareader, [email@example.com];
With an AD admin in place, and a script to create the users and assign roles, it is much easier to manage your SQL Database users. Much like an on-premises SQL Server, AD authentication reduces the need to manage passwords – especially when you have multiple environments for a database.