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.
Let’s recap what we’ve just learned. When creating an Azure SQL database server, you create a server administrator within the database server. This is equivalent to the SA, or server administrator user in SQL Server. You can give an Azure Active Directory identity, be that a user or user group the equivalent permissions with the Set Admin function in the Azure portal.
A server administrator assigned in this way does not appear as a login within the database server. Any Azure Active Directory identity can log in to a database by creating a user within the database with the same name using the “from external provider” keywords. This maps the database user to the AD user or group. If you map an AD user group to a database user, Azure Active Directory users log in with their own credentials.
By association with the user group, they are authenticated. An Azure AD identity doesn’t have to be a user or user group. We saw how to create a managed identity related to an Azure resource, in this case, an app service web app. Using the managed identity, the web app could authenticate without a user Id and password.
Managed identity authentication is analogous to on-premise Windows authentication. In all cases except the server sysadmin, each user needed permissions to access database objects like tables once they had authenticated. Next, I want to look at permissions and access once an identity or user has authenticated with a server.
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.