2

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 I am facing the 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.

How can I create and use a connection string in ADF linked service using AKV?

Improve this question
2
  • Hence, did you set the firewall policy for your connection? make sure the SQL Database firewall allows the integration runtime to access Commented Jun 28, 2021 at 1:16
  • why your port is 3342? Azure SQL database port number is 1433. Commented Jun 28, 2021 at 2:01

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.