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

Question

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

John Aherne 151

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!

2 answers

Your answer

Answer 1

AnnuKumari-MSFT 34,561 Microsoft Employee Moderator

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:

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

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

John Aherne 151 Reputation points

2023-05-10T13:25:43.9033333+00:00

The key vault works. It's the authentication to the on prem SQL that is the problem.

I can successfully test the linked service if I use AD authentication with my own user.

It does not work when using a SQL user - Says the username or password is incorrect. Except that same username and password works just fine from SSMS
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2023-05-10T19:10:19.5933333+00:00
@John Aherne : Something is not right here for sure. Can I request you to

Create a linked service and try to see if you can connect to the On- premise SQL using just the UserID and password ( by pass KV for now ) . If this works, then there is something wrong happening while reading the KV data. It can also happen that the secrets stored in KV are stale .

For some reason if you are not able to do #1 , please create a SQL user On- premise SQL and try to see if the if you can connect to the server using the new Userid .

If #3 is successful, maybe there is some character in the password which triggering this. .

Let me know how it goes.

Thanks
Himanshu
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2023-05-10T19:10:51.8266667+00:00
@John Aherne : Something is not right here for sure. Can I request you to

Create a linked service and try to see if you can connect to the On- premise SQL using just the UserID and password ( by pass KV for now ) . If this works, then there is something wrong happening while reading the KV data. It can also happen that the secrets stored in KV are stale .

For some reason if you are not able to do #1 , please create a SQL user On- premise SQL and try to see if the if you can connect to the server using the new Userid .

If #3 is successful, maybe there is some character in the password which triggering this. .

Let me know how it goes .

Thanks
Himanshu

Answer 2

John Aherne 151

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.

PRADEEPCHEEKATLA 90,736 Reputation points Moderator

2023-05-12T11:03:05.77+00:00

@John Aherne - Glad to know that your issue has been resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread.

Share via

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

2 answers

Your answer