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.
In terms of connecting to an Azure database, Azure Active Directory adds several options to long-established SQL Server and Windows Integration methods. Just to recap, SQL Server or Mixed Mode is authenticating with a login that has been set up within the Azure SQL server or the SQL or Managed Instance, or a user contained within a database on the server instance.
Windows Integration mode is authenticating with a verified Windows domain user. That domain can be a single Windows computer of an Active Directory. The authentication can be through an SQL client application like SQL Server Management Studio or via an application using a connection string.
In the SQL Server authentication example, the connection is being made to a database hosted on Azure, so nothing has changed from connecting to an on-premise SQL server, no matter if you’re connecting to a PaaS database, a managed instance, or an instance running on a VM.
To achieve the Azure AD Integration, there are 3 additional methods for authenticating. Universal with multi factor authentication implements a 2 step authentication, meaning the user is sent a confirmation request through another channel like email, an SMS message, or an authentication app.
Password is similar to SQL Server password from a user’s perspective, in that they are entering a password, but their login name will be their Azure Active Directory login. Integrated is pretty much the same as Windows integrated, in that the user has already authenticated with Azure Active Directory, so their credentials have been verified.
Let’s now see how to implement Azure Active Directory authentication with an Azure SQL 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.