How to use Azure key vault for storing connection string of SQL Server as secret in Azure Data Factory

0
votes

I'm trying to store connection string of SQL Server in Azure keyvault and use the same secret in linked service of azure datafactory.

I'm trying to specify the name of the Azure Key Vault secret that stores the destined Azure SQL Database linked service's connection string

Server=tcp:<servername>.database.windows.net,3342;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30

But facing below error:

Cannot connect to SQL Database: 'tcp:xxxx.database.windows.net,3342', Database: 'databasename', User: 'username@servername'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user 'username@servername'., SqlErrorNumber=18456,Class=14,State=1, Activity ID: 20aa7503-3b71-4539-a658-a2b5be87278f.

Can anyone please help me out in creating and using a connection string in ADF linked service using AKV.

1
sql-serverazureazure-data-factoryazure-keyvault
Hence, did you set the firewall policy for your connection? make sure the SQL Database firewall allows the integration runtime to access - Tiny Wang
why your port is 3342? Azure SQL database port number is 1433. - Leon Yue

1 Answers

0
votes

It seems to be an issue with SQL server firewall blocking Azure Integration Runtime IP address. Please go to the SQL server in Portal - > Firewalls and virtual networks option under Security and enable Allow Azure Services and resources to access this server.

enter image description here

To know the IP address that needs to be add in the Firewall Rules, just run any SQL query in the database and the error message will occur asking to whitelist the IP. Either click on that error message or add the IP manually in the Rule name under Firewall.

Apart from that you need to add the database connection string in the Key Vault Secret Value and then add the secret name in the ADF SQL database linked service and check the connection(add database password in the connection string before pasting in Secret's Value). Give the read rights (GET and List) to the secret in the access policies.

enter image description here

Create a ADF Linked Service of the Key Vault you have created previously and check the connection.

enter image description here

Using that Key Vault Linked Service, create the SQL Database linked service and check for connection.

enter image description here

Refer to the link for step-by-step implementation to connect the SQL Database in ADF using Azure Key Vault.