Course Intro and Overview
Azure Active Directory Authentication
Access within the Database
The course is part of this learning path
Authentication and access control are two crucial factors in securing databases and their servers. One, authentication, controls who can access the data resource, and in what capacity, while the other, access control, specifies what a user can do once they have been authenticated.
Historically, authentication and access have been managed entirely by SQL Server, but Azure has enabled integrated password and multi-factor authentication courtesy of Azure Active Directory, along with built-in SQL DB roles.
This course looks at various ways to integrate Azure SQL with Azure Active Directory and how to best manage user privileges once logged into the database.
If you have any feedback relating to this course, please contact us at firstname.lastname@example.org.
- Get a basic understanding of the history and context of SQL authentication
- Understand how to to use Azure Active Directory to authenticate users with a SQL database
- Learn how to use database roles to customize access
- Understand the principle of least privilege and how to apply it
- Learn how to fine-tune access to database objects
This course is intended for database administrators using Azure, or anyone who wants to understand more about using Azure Active Directory to authenticate a user to access a database.
To get the most out of this course, you should have a basic understanding of databases and the Azure platform.
It’s not practical to have everyone set up as an admin user, so what I’ll look at now is creating an ordinary Azure Active Directory user. I’ll start by creating another database on the same server with the imaginative name AnotherDb. Nothing special here, exactly the same type of database, so I will just fast forward through the creation.
The first thing I’ll do is log in as hallamad, and if you remember, was the admin, but I changed it to lorrylog via the Azure Active Directory SQL admin group. As we would expect when trying to log in with Azure Active Directory password, we cannot.
I’ll now log back in as the SQL admin and go to the new AnotherDb database, and create the hallamad user within that database. This is what’s called a contained user. That means the user only exists within that database and can only access that database on the server.
I’ll use the from external provider keywords to associate the database user with the Azure Active Directory user. We can see the hallamad user under AnotherDb’s users. To access the database, hallamad will need to be a member of the DB_data reader role, and I’ll give the user that role with the sp_add role member stored procedure.
If I try to connect to aadauth using the default database that is not specifying AnotherDb, the connection fails. When selecting AnotherDb is the default database, I can connect with hallamad, and I only have access to the AnotherDb. If I try to create a new table, I’m unable to as I only have read permission.
I’ll go back over to the other query window where I’m still logged in as lorrylog the database admin, and I’ll add hallamad to the DB_owner role. Now when I go back to the hallamad connection, I’m able to create the table.
Now let’s look at a variation on that theme by creating an Active Directory user from scratch. I’ll make a user called HallamSql with the user’s role, and I’ll join them to a group I’ve created called AnotherDbAccessGroup. There is nothing special about this group; it has no special access or roles assigned to it.
This time I will log in to AnotherDb using Active Directory universal with MFA multifactor authentication. The Azure login pops up for me to enter a password for HallamSql. As this is the first time logging in, I will have to create a new password.
It comes as no surprise I’m unable to connect because the Azure SQL Server doesn’t know anything about HallamSql. I’ll log in as lorrylog SQL admin and create a user, AnotherDbAccessGroup from external provider within the AnotherDb database. AnotherDbAccessGroup is the name of the Azure Active Directory group we just created, so this SQL Server user will be associated with that Azure AD Identity.
I’ll assign this user the DB_data reader role. Instead of using the SP_add role member procedure, I’ll use the alter role statement for no reason other than to show you there is more than one way to assign roles.
Now when I log in with HallamSql, I can connect. I didn’t have to enter my password again, as my credentials have already been cached from the first attempt. It was only once I’d been verified with Azure Active Directory that I could not make the connection to the database server.
Once again, HallamSql is a contained user because the login belongs to the user group, which is itself a contained user. If we look underneath the user’s node, we can see the user group name rather than HallamSql. One last thing before we move on is how to remove a user from a role.
So far, we have just been adding users to roles, but what if you want to remove a user’s ability to perform some task. Well, it’s easy enough; instead of alter role add member, we use alter role drop member, which follows the format of other DDL drop commands.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.