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.
While many applications still use a separate database authentication model, and to be fair, often that’s for historical reasons, Azure Active Directory allows you to centralize user authentication and management by providing authentication as a service.
The question is not so much why would you use Azure AD, but why wouldn’t you? From an end user’s point of view, it’s a case of authenticating once and not have to remember multiple usernames and passwords. From an administration perspective, there is only one credential per user and one place to administer access.
If you’re unfamiliar with Azure Active Directory, the central concept in the context of database authentication is using Role-Based Access Control or RBAC. In a nutshell, the ability to perform specific tasks is given to roles, and then those roles can be assigned to users. A role defines the type of access or permission, like read, write, or administer a user can have over an Azure resource. The easiest way to think of a role is a group of one or more permissions with a name.
At the top of the RBAC hierarchy is the subscription owner role, which has full control over all resources in the subscription, right down to reader roles on resources that allow a user assigned that role only to view a resource.
While you can create your own roles if you have the appropriate permissions and Active Directory Premium, Azure has some built-in roles that are specific to Azure SQL. These roles allow you to create and manage servers, databases, and control access to the database resources. SQL DB Contributor will enable you to create and deploy Azure SQL databases, SQL Managed Instance Contributor allows you to deploy managed instances.
SQL Security Manager role won’t give you access to a server or database but lets you manage the associated security policies. Role-based access control operations can be performed through the portal or via the azure command-line interface – CLI.
While Azure AD and RBAC control access to servers and databases, once authenticated, it is Azure SQL server and database security mechanisms that control access to resources within the 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.