Invalid User or Pwd for SQL Server connection string (But they are valid and tested)

John Aherne 141 Reputation points
2023-05-09T15:48:48.74+00:00

Hi All,

In our Data Factory, we are connecting to an on-prem SQL server database via a self-hosted IR using SQL authentication. The connection string is in a Key Vault.

When creating the linked service however, it returns a username or password is incorrect error.

So far, we have tried the following troubleshooting with no fix:

Successfully tested the linked service using AD authentication.

Successfully tested the SQL credentials with SSMS from the IR machine.

Unsuccessful test when entering the connection string details in the linked service (Instead of KV).

Unsuccessful test quoting the password in the connection string.

Unsuccessful test adding other parameters like trustservercertificate.

3 different people have tried setting up the linked service, all of us get the same error.

Any thoughts on what I might be missing?

Thanks!

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,916 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. AnnuKumari-MSFT 31,731 Reputation points Microsoft Employee
    2023-05-10T06:32:06.9666667+00:00

    Hi John Aherne ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your query here. I understand that linked service connection is failing when you are trying to use KeyVault to store the password for onPrem SQL server datastore.

    I tried to repro the scenario and was able to make through it . I would like to highlight couple of things that needs to be taken care of:

    1. Make sure that the managed identity used for authentication in your keyVault linked service has 'Get' secret permission.

    To grant that permission, go to the access policy in keyvault and select 'Get' secret permission for your managed identity

    Select Access policies, selecting Add role assignment

    User's image

    Check this doc for more information: Assign a Key Vault access policy

    1. Make sure to enter correct secret name which has stored the correct SQL password as the secret value.

    keyvault

    Hope it helps. Please accept the answer if it is helpful. Thankyou


  2. John Aherne 141 Reputation points
    2023-05-10T19:54:44.44+00:00

    It turned out to be a firewall issue. The database was on a non-standard port which was getting blocked by internal firewall rules. Once the port was opened, SQL authentication worked.

    Not sure why this only affected SQL authentication and not AD authentication though.