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.
Firstly I want to show you setting the Active Directory Admin on an Azure SQL Server. I’m going to start by creating a new Azure SQL database. I’ll select an existing resource group and create a new server. As part of creating the server, we’ll create an SQL login and user — the equivalent of the SA or system admin user when you install an SQL server instance on-premise.
My server admin login will be called AzureUser. I’ll change my compute and storage to standard S0, and I’ll have no public access, and I go with the standard additional settings. No tags, and now I’ll create the database. Looking at the newly created database, I’ll go into firewall settings and allow other Azure services to access this resource, and I will set up a firewall rule to allow me to connect from my local PC and save it.
Now that the ADAuthenticate database is configured let’s set up the Active Directory admin on the server. Under settings, click on Active Directory admin and the Set Admin button. The add admin list is credentials from your Azure Active Directory. I’ll select the hallamad user and click the save button.
Now I can open up SQL server management studio and log into the aadauth server using my Active Directory identity. If I click on security and then logins, we can see that hallamad is not a login within the server. The only login is the AzureUser login that we set up when we created the database server.
It would be preferable to set up a SQL Server admin group and assign users to that, rather than a specific user. What we can do is go back to active Directory and create a new group, a security group called SQL admin, and then assign an Active Directory user to that group. I’ll assign the lorrylog user to that group and make myself the owner.
Going back to our aadauth server, we can change the admin from hallamad to the SQL admin group we just created. Once I saved that, I can go back to management studio and disconnect from the server and reconnect using the lorrylog Azure A.D. user login with a password. Because lorrylog is part of the SQLAdmin group, I’m authenticated.
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.