Authentication
Authentication is the process of validating the identity of users trying to access a database. All versions of Azure SQL support two authentication methods: SQL authentication and Active Directory (AD).
SQL authentication involves storing SQL Server–specific login name and password information in the master database, or in the user database for database contained users. As a matter of fact, the administrator account that is defined when creating a SQL Server on Azure VM, Azure SQL MI, Azure SQL Database, or Azure Synapse Analytics dedicated SQL pool is an example of a SQL login. The administrator can also create additional SQL logins that other users or automation services such as the SQL Server Agent or Azure Data Factory can use to interact with the database.
Active Directory authentication involves adding a user or group stored in Windows AD or Azure AD (AAD) as a login or contained user in SQL. This is the preferred method of authentication as it is more secure than SQL authentication and is easier to manage. SQL Server on Azure VMs can use Windows AD logins for authentication if the VNet that contains the SQL Server on Azure VM is joined to a domain that has AD. As of the writing of this book, SQL Server on Azure VMs cannot use AAD users and groups for authentication. Azure SQL Database and Azure SQL MI, on the other hand, can use AAD objects. The following steps outline how to add an AAD user or group as an administrator for an Azure SQL Database logical server.
- Log into portal.azure.com and search for SQL servers in the search bar at the top of the page. Click SQL servers to go to the SQL servers page in the Azure Portal. This page is the home of the logical servers for your Azure SQL Databases.
- Click on the logical server that was created when you built an Azure SQL Database.
- Click Azure Active Directory under Settings in the left-hand side panel. Click Set admin to add an AAD user or group as an administrator on the server. Figure 2.17 illustrates how this page will appear before clicking Set admin.
- Once you have added an account, click Save to save the account as the administrator.
FIGURE 2.17 Adding an AAD Administrator
Non-administrator AAD users and groups can also be added using T-SQL. To add additional AAD users and groups as database users, connect to the logical server using a management tool like SQL Server Management Studio (more on management tools later in this chapter) with a login that has permission to create users in the database. This can include the SQL authentication server administrator or the AAD server administrator. Once you’re logged in, the following command can be used to add a contained user to a database.
CREATE USER [<AAD_User>] FROM EXTERNAL PROVIDER;
There are three methods available for using an AAD login to connect to a database. The correct choice depends on how an organization configures AAD. These methods are as follows:
- Azure Active Directory—Integrated: This method can be used if the user signed into the Windows machine that they are connecting to the database from with an AAD account.
- Azure Active Directory—Password: this method forces the user to enter the AAD login name and password to connect to the database.
- Azure Active Directory—Universal with MFA: This is an interactive method that uses multi-factor authentication (MFA) to provide additional access security for the database.
Authorization
Authorization refers to the level of permissions a user has in the database. Some of these permissions include whether they can read or write data in different tables, execute stored procedures, and add or delete other users. Permissions are typically managed by database roles that include a predefined set of permissions. Database roles include fixed-database roles that are included in SQL Server and Azure SQL and user-defined database roles that are created by a database administrator.