Course Intro and Overview
Azure Active Directory Authentication
Access within the Database
Summary
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 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.
Azure Active Directory authentication to a database and access within a database are mirror images. They both share the same structure of users and roles, and they both have predefined roles. In both cases, users can take on the abilities or privileges of the role or roles they belong to.
Using Azure Active Directory credentials to gain access to an Azure or SQL database streamlines user management, reducing the number of places where credentials must be maintained. Azure Active Directory offers integrated, password, and multifactor authentication with database servers.
Within Azure active directory, users can be assigned permissions via role-based access control to perform tasks on databases or servers. Except for the admin role, once users have authenticated, they are mapped to users or roles within the database.
The role user structure is replicated within the database but allows a far greater level of granularity and control, but this is primarily down to the vast number of objects users can be permitted or denied access to. It is possible to grant or deny a user a particular type of access or privilege on any database object; you can even specify whether the user or role can delegate access permissions to other users or roles.
The very high level of authentication and access customization that is possible leaves no valid excuse for not applying the principle of least privilege access. When users are joined to one of the predefined database roles, their access is limited to what that role will allow, but there is one exception apart from system admin or DB owner that have full control anyway, and that is the DB security admin role.
The DB security admin role is for managing custom role membership and related permissions, so it is conceivable that members of this role can give themselves elevated permissions where it is not intended. The combination of authentication and access provides dual mechanisms for protecting data and database integrity.
In summary, both Azure Active Directory and SQL Server databases work on the principle of permissions assigned to roles. With Azure A.D., it is the role-based access control mechanism, and with Azure and SQL Server, it is the granting and denying of various actions or abilities to roles that users belong to.
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.