How to connect to azure sql from local visual studio environment without storing connection string?

Question

net core and azure sql. I am trying to connect to azure sql from my local visual studio. If I am deploying app in app service I can make use of managed identities. Currently I am app in local environment. I do not want to store any password in local ystem like appsettings.json. I am curious to know is there any way where I can connect to azure sql without storing password in local visual studio. Can someone help me if there is any other way? Thank you for help.

Answer 1

You can make use of Azure AD based authentication for SQL Databases. Assuming you are signed-in into Visual Studio using your Azure AD credentials, you can assign yourself a database role in the SQL Database using a command like:

CREATE USER [<Azure_AD_principal_name>] FROM EXTERNAL PROVIDER;
ALTER ROLE [<databse-role-name e.g. dbmanager>] ADD MEMBER [AzureAD_object] 

Once you do that, you should be able to connect to your SQL Database using a connection string like the following:

Server=tcp:<db-server-name>.database.windows.net,1433;Authentication=Active Directory Default;Database=<db-name>;TrustServerCertificate=true;

Please see these links for more details:

• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell
• https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-logins-tutorial?view=azuresql