On premise data gateway: SQL server authentication error

[mishrg]

I am getting the following error when I am creating a data source to connect to a sql server with a service account. But I am able to connect it with SQL server managment studio with same credentials. Below is the error:

Unable to connect: We encountered an error while trying to connect to la-y-db-hris-1. Details: "Invalid connection credentials."Hide details

Activity ID:	deddfac3-5cab-6d5b-8179-3e9b1c16f182
Request ID:	c8dfbae4-b205-62ed-8c9d-69c54b3d3096
Cluster URI:	https://wabi-west-us-redirect.analysis.windows.net
Status code:	400
Error Code:	DM_GWPipeline_Gateway_InvalidConnectionCredentials
Time:	Fri Apr 21 2017 14:48:20 GMT-0700 (Pacific Daylight Time)
Version:	13.0.1700.1873

Underlying error code:	-2146232060
Underlying error message:	Login failed for user 'svcdevcompsys'.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2146232060
DM_ErrorDetailNameCode_UnderlyingNativeErrorCode:	18456

[v-qiuyu-msft]

Hi @mishrg,

1. Is the data gateway installed on the same machine as the SSMS exists?

2. Try to update the data gateway to the latest version.

3. Please share the image about the page of Manage Gateways where the error throws and credential section.

Best Regards,
Qiuyun Yu 

[mishrg]

Answers for questions below:

1. Is the data gateway installed on the same machine as the SSMS exists? -- Yes, both are on the same machine.

2. Try to update the data gateway to the latest version. -- I tried but of no help.

3. Please share the imae about the page of Manage Gateways where the error throws and credential section. -- Image below. I have tried both with and without domain name in the user name. Also, have tried both basic and windows authentication.

[v-qiuyu-msft]

Hi @mishrg,

It seems that the svcdevcompsys is a Windows account. When you create a SQL Server data source under data gateway, please set the Authentication as Windows instead of Basic. Then please enter the user name in the format of <Domain>\<user>, make sure the password is correct and not expired yet.

Best Regards,
Qiuyun Yu

[mishrg]

@v-qiuyu-msft I had tried that option and it gave the same error. svcdevcompsys password has not expired, same password is working with the SQL server management studio.

[v-qiuyu-msft]

Hi @mishrg,

Regarding this issue, I would suggest you create a support ticket to let Microsoft engineers look into your local environment.

Best Regards,
Qiuyun Yu

[Umram]

Hey,

Were you able to resolve this issue. I'm facing a similar problem with gateway authentication. 

[mishrg]

@v-qiuyu-msftDid you get chance to see the issue.

[mishrg]

Adding to the point above, I am using windows authentication in SQL server management studio to login and here I have tried both windows and basic with no success.