Vivian Andringa 11 Jul, 2022
Share 
With GitHub Advanced Security, you can scan your code for secrets and vulnerabilities in your codebase and git history by using Secret Scanning or even go one step further and use push protection to avoid secrets being pushed in your repository. This is an excellent feature when we have secrets in our applications, but it is not enough when we talk about passwords in connections strings, you also need to rotate those passwords, protect them, make sure you are using the correct one for each different stage (Dev, QA, Prod, etc.). A solution to this problem is to use Managed Service Identity (MSI) instead of usernames and passwords to connect to different Azure services, including SQL Azure.
In this post I will show you how to connect to SQL Azure without secrets by using Azure Active Directory. This applies to users, managed identities, and service principles.
Enabling Azure Active Directory authentication on SQL Azure
First, we need to make sure Azure Active Directory authentication is enabled on SQL Azure.
Adding users to SQL Azure and assigning permissions
To do this, we need to connect to the SQL Azure server using SSMS and run the following query that creates a user from an Azure Active Directory account and assigns the db_datareader role to it. You can learn more about the SQL roles here.
CREATE USER [<user@AAD-tenant-domain>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<user@AAD-tenant-domain>];Example:
Note: You can do this at the server level, or at the database level. If you do it at the server level, the user will have access to all the databases in the server. If you do it at the database level, the user will only have access to that database.
As mentioned, you can also use managed identities and service principals. Here is an example of how it looks like when using a managed identity:
Here is an example of the query you need to run to create the user from a managed identity and assign it a role, in this case db_datareader:
CREATE USER [name-of-the-object] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [name-of-the-object];Example:
Connecting to SQL Azure using Azure Active Directory
Now that we have Azure Active Directory authentication enabled on SQL Azure, and we have added users and assigned permissions, we can connect to SQL Azure using Azure Active Directory.
The connection string looks like this:
Server=tcp;<your-server>.database.windows.net,1433;Initial Catalog=<your-database>;Authentication=Active Directory Managed Identity;Conclusion
Your connections to SQL Azure are now more secure and without using secrets by using Azure Active Directory.
