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 support@cloudacademy.com.
Learning Objectives
- 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
Intended Audience
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.
Prerequisites
To get the most out of this course, you should have a basic understanding of databases and the Azure platform.
A managed identity is a way for Azure resources to authenticate with each other using tokens, so this is within the Azure ecosystem. An example of this is an app service web app authenticating with the backend Azure SQL server database.
In this demo, I’ll create a web app, then a managed identity, and then use that managed identity to connect with an Azure SQL database to retrieve records. First off, I’ll create a basic web app through the portal. Nothing special here, I’ll use the same resource group that I’ve been using so far, and it will be a .Net Core with applications running under Windows.
All the other settings will just be the defaults, and I’ll hit the create button. Now that the web app exists, I need to create, or rather assign an identity to it. I’m going to do that through the Azure CLI through the Bash terminal with the “az web app identity assign command”. I need to specify the resource group and then the name of the identity, which is the same as the name of the web app.
Next, I’m going to create an Azure Active Directory group called AnotherDbAppAccess and assign it to a variable called groupid. I’ll then get my newly created identity and assign it to another variable called msiobjectid.
Finally, I will join my identity to the AnotherDbAppAccess group with the “az ad group member add” command. Using my group variable, I’ll list the members of the group. Let’s just make a note of the app ID and object ID values.
As before, I’ll need to give this Azure A.D. identity permissions to connect with the database, so I’ll create a contained user called dbauthwebapp and give it read and write permissions on AnotherDb. And as before, if we open up users under security, we can see the newly created user.
Switching back to the portal and going into Active Directory and then into groups, we can see the AnotherDbAppAccess group. This group has one member which is not a user but an other, and if we have a look at the members, we can see that it is, in fact, dbauthwebapp.
Drilling down into this identity, we can also see that the app ID and object ID values other same as those in the CLI. Looking up at the top left, dbauthwebapp is identified as an enterprise application, so we can also find it within enterprise applications.
I guess the next question is, does this actually work? What I’ll do is insert three records into the new table in AnotherDb and then create a boilerplate web app called weather forecast in visual studio. Instead of returning randomly generated weather forecasts, I’ve got to connect to the database using the AzureUser and password, so that’s an SQL server user. It does a select * from NewTable and returns the 3 records as JSON.
What I’ll do now is go back to the web app configuration and modify the connection string so that the password is incorrect. I’ll say that configuration and refresh the web app page. Now it returns nothing. In the background, there is obviously a database connection error taking place, but we don’t get to see that. I’ll now remove the connection string with the “az the web app config connection string delete” command, and going back to the portal and refreshing, we can see that that connection string has, in fact, disappeared.
But now, when I refresh the web app, it does connect to the database and retrieves the three records because it’s now using the managed identity to authenticate. Remember when I was setting up the Azure SQL server’s firewall setting, I said yes to “Allow Azure services and resources to access this server”. This is how the dbauthwebapp Enterprise application is able to access the server and database.
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.