Azure Key Valut connection to Azure SQL

Question

I have successfully setup a Linked Service in Azure Data Factory that uses a Key Vault for the connection string which includes the user/pwd and connects to the Azure SQL DB as desired. However, I can only do this when I use the "admin" account. The string below works.

Server=tcp:database1.database.windows.net,1433;Initial Catalog=DB;Persist Security Info=False;User ID=Admin;Password=Pa$$w0rd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

I created a new login/user and granted the necessary permissions. I know because I can connect using the new login via remote SSMS or by adding the credentials directly in the linked service in Azure. (e.g. hard coding the user/pwd in the connection string in the linked service)

Unfortunately, when I switch to using the key vault connection string, I get the generic SQLErrorNumber 18456 for the newly created user. I know the credentials are correct, I know I can connect via the Key Vault (when using the elevated admin account), I just cannot use the Key Vault connection string when using the new user.

Server=tcp:database1.database.windows.net,1433;Initial Catalog=DB;Persist Security Info=False;User ID=Username;Password=Pa$$w0rd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Answer 1

If using Azure SQL with Data Factory look at using Managed Service Identity That way you'd add the name of the Data Factory directly to the SQL DB and no need for a username and password. Plus can assign permissions directly on the user.

Only downside is if DataFactory get's wiped out and redeployed the user will need to be dropped and recreated since it is using a thumbprint to recognize the Identity, this isn't the case with all resources and MSI auth.

For your specific case check to make sure the SQL server is allowing Azure Services and resource to Access the sever by going to "Firewalls and virtual networks" and make sure it is turned on: