Create contained users mapped to Azure AD identities
In this we will learn about creating contained users mapped to Azure AD Identities. You should be aware that SQL Managed Instance supports Azure AD server principals (logins), so you don’t have to employ confined database users. You may construct logins from Azure AD users, groups, or apps using Azure AD server principals (logins). This implies that instead of employing a confined database user, you may connect in to your SQL Managed Instance using the Azure AD server login.
Using Azure Active Directory authentication with SQL Database and Azure Synapse, on the other hand, necessitates the usage of confined database users with Azure AD identities. A contained database user does not have a login in the master database, but instead maps to an Azure AD identity that is linked to the database. An Azure AD identity might be a single user account or a group of users.
Next, to create an Azure AD-based contained database user, connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
SQL
CREATE USER FROM EXTERNAL PROVIDER;
Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group.
Examples: To represent an Azure AD federated or managed domain user in a contained database user, follow these steps:
SQL
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:
SQL
CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
To create a contained database user representing an application that connects using an Azure AD token:
SQL
CREATE USER [appName] FROM EXTERNAL PROVIDER;
- When you establish a database user, however, the user is given CONNECT permission and may connect to that database as a PUBLIC role member.
- The user’s sole rights are those assigned to the PUBLIC role and those granted to any Azure AD groups to which they belong.
- You may provide extra rights to an Azure AD-based confined database user, in the same manner, you grant permission to any other type of user once the user has been provisioned.
- Typically, database roles are given rights, and users are added to them.
Connect to the database using SSMS or SSDT
Connect to the master database using the Azure AD administrator account to ensure the Azure AD administrator is correctly configured. Then, connect to the database with an Azure AD identity that has database access to provision an Azure AD-based confined database user.
Using an Azure AD identity to connect using SSMS or SSDT
The instructions below demonstrate how to connect to SQL Database using SQL Server Management Studio or SQL Server Database Tools with an Azure AD identity.
Active Directory integrated authentication
If you’re connected into Windows with your Azure Active Directory credentials from a federated domain or a managed domain with seamless single sign-on for pass-through and password hash authentication, use this technique.
- Firstly, Start Management Studio or Data Tools and in the Connect to Server dialog box, in the Authentication box, select Azure Active Directory – Integrated. Here, no password is needed or can be entered because your existing credentials will be presented for the connection.
- Secondly, select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to.
Active Directory password authentication
When connecting to an Azure AD principal name via the Azure AD managed domain, use this technique. It can, however, be used for federated accounts without domain access, such as while working remotely. Additionally, with Azure AD cloud-only identity users or those who utilise Azure AD hybrid identities, you may use this approach to authenticate to the database in SQL Database or SQL Managed Instance. A Windows user can specify their domain account and password in this scenario. They can also authenticate to SQL Database, SQL Managed Instance, or Azure Synapse databases.
- Firstly, Start Management Studio or Data Tools and in the Connect to Server dialog box, in the Authentication box, select Azure Active Directory – Password.
- Secondly, in the User name box, type your Azure Active Directory user name in the format [email protected]. Here, user names must be an account from Azure Active Directory or an account from a managed or federated domain with Azure Active Directory.
- Thirdly, in the Password box, type your user password for the Azure Active Directory account or managed/federated domain account.
- Lastly, select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to.
Active Directory interactive authentication
This approach may be used for interactive authentication with or without Multi-Factor Authentication (MFA), and the password can be asked interactively. Moreover. For Azure AD cloud-only identity users or those who utilise Azure AD hybrid identities, this approach may be used to authenticate to the database in SQL Database, SQL Managed Instance, and Azure Synapse.
Using an Azure AD identity to connect from a client application
The following procedures shows how to connect to a SQL Database with an Azure AD identity from a client application.
Active Directory integrated authentication
Your domain’s Active Directory must be federated with Azure Active Directory in order to use integrated Windows authentication. Alternatively, it should be a managed domain with seamless single sign-on for pass-through or password hash authentication setup.
The Authentication keyword in the database connection string must be set to Active Directory Integrated to connect to a database utilizing integrated authentication and an Azure AD identity. ADO.NET is used in the following C# code sample.
C#
string ConnectionString = @”Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;”;
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Active Directory password authentication
The Authentication keyword must be set to Active Directory Password to connect to a database utilizing Azure AD cloud-only identity user accounts. However, the connection string must include the keywords and values User ID/UID and Password/PWD. ADO.NET is used in the following C# code sample.
C#
string ConnectionString =
@”Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Password; Initial Catalog=testdb; [email protected]; PWD=MyPassWord!”;
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Azure AD token
By receiving a token from Azure AD, middle-tier services can receive JSON Web Tokens (JWT) in order to access to the database in SQL Database, SQL Managed Instance, or Azure Synapse. Furthermore, this technique supports a variety of application situations, including service identities, service principals, and certificate-based authentication applications. To utilize Azure AD token authentication, you must fulfill four essential steps:
- Firstly, register your application with Azure Active Directory and get the client ID for your code.
- Secondly, create a database user representing the application.
- Thirdly, create a certificate on the client computer runs the application.
- Lastly, add the certificate as a key for your application.
Sample connection string:
C#
string ConnectionString =@”Data Source=n9lxnyuzhv.database.windows.net; Initial Catalog=testdb;”
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = “Your JWT token”
conn.Open();
Reference: Microsoft Documentation