Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Database or other resources by using identities in Azure Active Directory (Azure AD), SQL password Authentication is not a very secured, AD login is more secured and provides password rotation and MFA.
Azure Active Directory authentication uses contained database users to authenticate identities at the database level, i.e. does not have access to master database and access is assigned at individual database level. Azure AD identity can be either an individual user account or a group. I will walk you through how to create container DB access to AD group in this post.
Create Active directory Group in Azure AD
- Create Active directory group for DB access in Azure AD, multiple group can be created to isolate database access or to separate read and Write permission
- Add users to AD group.
Create Contained database users
- Connect to Azure Database using SQL Server Management Studio, to create container users/group you need to login with AD credentials/group that is setup as Admin on Database Server.
- In my case, Admin is Active directory user who is also SQL Admin.
- Run the following command to create contained DB users
CREATE USER Azure_AD_principal_name ROM EXTERNAL PROVIDER
Example: CREATE USER [azsrini-DB-RW] 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
When you create a database user, that user receives the CONNECT permission and can connect to that database as a member of the PUBLIC role. Once you provision an Azure AD-based contained database user, you can grant the user additional permissions, azsrini-DB-RW is granted ‘CONTROL’ permissions
Connect to Database
Active Directory Integrated authentication
To connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword in the database connection string must be set to Active Directory Integrated and the client application should be running on a domain joined machine.
string ConnectionString = @"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated;"
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();Active Directory principal name and password
To connect to a database using AD name an password, the Authentication keyword must be set to Active Directory Password and the connection string must contain User ID/UID and Password/PWD keywords and values
string ConnectionString = @"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Password; UID=azsrini@azsrini.onmicrosoft.com; PWD=passwrod123";Connect using SQL Management Studio
To connect from management studio, go to options, enter the database and then connect using the Active directory username/password
Useful Links:
https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage
Conclusion:
AD authentication is secured way to connect to Database , AD takes care of password rotation and MFA, it also eliminated storing passwords in connecting client applications.
Thank you
Srinivasa Avanigadda
Connect to Azure SQL Managed instance By Using Azure AD Authentication
Tweet
Srinivasa Avanigadda 