Setting database permissions based on Active Directory Credentials and Groups has been a tried and true technique for authentication on premises for years. With the addition of AAD to SQL Azure we can now use this technique in the cloud. However, I could not find a good step by step on how to use AAD Groups in this scenario. So, I put this post together, hopefully it will help you.
The first step is to confirm what AAD is “trusted” by the Azure subscription you want to deploy the database into. Just navigate to the settings tab, here you will see your subscriptions listed, on the right you will see the directory that it is tied to. NOTE: if you need to change this see the details in the official documentation above.
With that housekeeping out of the way, let’s set up some accounts to test this with. I will create 2, a user account and an admin account. Just navigate to your directory, select add user and go through the wizard. In this screen shot you can see the two users I created. NOTE: After I create new accounts in AAD I always log in once to trigger a password reset. I do this in a new in private browser window, by going to the Azure Portal. After logging in it should say “No Subscriptions Found” this is expected.
Now that we have some accounts, lets go ahead and create two groups one for admins and another for users, and put each account in its group. This can be done on the groups tab in AAD, just click add group at the bottom, and go through the wizard. After you add the group don’t forget to put the proper users in the group. Here are some screen shots from my portal after I completed the process for the Admin group.
Ok, one more piece of setup, lets create a SQL Azure DB, during this process you will have to assign it a non-AAD admin account. I just used the Adventure Works sample for this demo so it would have some data.
Ok, now we get to the fun part. The first thing we want to do is assign the admin group as admin on our entire SQL Database server, we can do this easily in the Azure Portal. From the database blade (screen shot above) click on the server name in the upper right, to bring up the server blade, here you can select Active Directory admin from the options list, and then set admin. This will pop up a blade for you to search for the users or groups you want to assign. As you can see in the screen print I did a search for SQL and I can see the 2 users we created and the two groups we created. I am going to select SQL Admin and then press the select button.
Now you must save your changes. The save button is hidden under the more ellipsis.
Great now our admins can log into our database server with SSMS. NOTE: I said server, the admin group permission is in the master db. Also, look I am logging in as the user, but we assigned the permissions to the group. Now when users are added/removed from the group, permissions will be reflected automatically in SQL Azure.
For our users, we don’t want them in master, just in the database. So, let’s give our users just access to the database. We can do this with a bit of TSQL while logged in as the admin. NOTE: I set the database in the drop down before executing the TSQL. Also I am giving the entire group access not just my user, for the same reasons we did it at the server level.
And lets give our users the db_owner SQL Role.
Now login with the user account the same way we did before, however you must set the database under “connection properties” in the options dialog, or you will get this error.
And now our users can get in to just this database and do what they need to. All with the security of Azure Active Directory and the convince of using AAD Groups for maintenance.