If you host SQL Server on-premises, you know Windows authentication is preferred over SQL Server authentication. Managing passwords is a business most DBAs want to stay out of, and it can create issues if your password management policies aren’t secure.
An access management tool to save passwords securely is required as is the effort to organize them. If you’re an ETL or application developer, you need a secure way to keep these passwords safe in a config file. A process for password sharing policies also needs to be created. Eliminating the use of passwords for SQL Server logins that use SQL authentication is the safest practice.
Authenticating with Managed Identities in Azure
Authenticating by using managed identities is Microsoft’s counterpart to Windows authentication in Azure and provides an alternative to using passwords. Azure resources are assigned an identity in Azure Active Directory, and permissions can be granted to common resources that need access to Azure SQL databases.
Here’s a simple walkthrough using Data Factory. To create database users from managed identities, you first need to connect to the database in SQL Server Management Studio (SSMS) or Azure Data Studio using an Azure Active Directory login. To get you started, find the Azure database server in the Azure portal, and set yourself as the Active Directory Admin:
Then login into the Azure SQL Server using Azure Active Directory authentication with your Azure login, which should be an AAD admin for that server:
From there, run the following script to create the database user by using a managed identity login.
Note: Replace “atx-test-data-factory” with the name of your data factory:
|
CREATE USER [atx-test-data-factory] FROM EXTERNAL PROVIDER –Create DB User EXEC sp_addrolemember ‘db_datareader’, ‘atx-test-data-factory’ –Add DB User to read role EXEC sp_addrolemember ‘db_datawriter’, ‘atx-test-data-factory’ –Add DB User to write role |
You can now create an Azure SQL Database linked service in Azure Data Factory by using the Managed Identity authentication type. No passwords required!
The approach of authenticating by using managed identities should be used whenever possible. For example, you can also authenticate to Azure SQL from resources like Stream Analytics and Virtual Machines. Managed identities can also be used to authenticate to Blob, Table, and other storage services.
Your DBAs are sure to appreciate your efforts! The process can sometimes be a bit tricky, so if you need any help with authentication or have questions, feel free to reach out to ATX
Author, Jon Decowski, ATX Advisory Services