Can't create a Fabric connection to standalone on-prem SQL Server through standalone Data Gateway

[aki-siponen]

Hi, 

We are in the process of migrating from ADF to MS Fabric.

The primary data source is an on-prem stand-alone SQL Server. ADF linked service uses an on-prem IR to connect to the database. The on-prem IR Server is also stand-alone. The linked service uses a windows account (username only, no servername) to connect to the database. 

I'm trying to create a connection to the on-prem database in Microsoft Fabric. I have installed on-premises data gateway on the same gateway server that is running on-prem IR. No problems with that.

When I try to create a connection in Fabric, I get an error. The configuration:
Gateway cluster name: my-gateway-cluster

Connection name: my-connection

Connection type: SQL Server

Server: ip-address

Database: my-database

Authentication method: Windows

Username: dbserver\my.name

Password: *****

The error I get is:

Unable to create connection for the following reason: The on-premises data gateway's service account failed to impersonate the user.

Details: xxx: Received error payload from gateway service with ID xxxxx: Error logging on username 'dbserver\my.name'.. Please have this information handy if you choose to create a support ticket. ...

In ADF IR I can give the credentials as "my.name" without domain. This works ok. I can even test the connection in Microsoft Integration Runtime Configuration Manager. 

I'm trying to figure out if there is a way to use Windows credentials in this case or if we should use SQL Server credentials.

The options I've figured out I could try out, but haven't done yet:

- create a user account in gateway and in dbserver, use the same name and password. Use this account to run gateway service.

- see if AD lookup could be used. Configure in Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.CONFIG > ADServerPath. I've never used that, I don't know if this can be used with standalone servers.

- use SQL Server credentials

So, before barging into experimentation, I'd like to ask advice. Any proposals are greatly appreciated.

br.

Aki

[Anonymous · Accepted solution]

Hi @aki-siponen 

Thank you for reaching out in Microsoft Community Forum.

The "failed to impersonate the user" error occurs because Windows authentication requires a domain account, not a local machine account.

1.Please Modify the connection to use a domain account (e.g., DOMAIN\username) instead of the machine account.

2.Make sure the account has the necessary SQL Server access.

3.If using a domain account is not feasible, switch to SQL Server authentication to avoid domain-related issues.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[aki-siponen]

Hi,

I understand this. And I understand that there may be some security considerations behind the decision.

But I'm just a bit disappointed that something that's worked for a number of years does not work anymore. This forced our service provider to change the access to SQL Server to allow SQL Server login.  

[lbendlin]

Not 100% sure but if you use Windows authentication then you are supposed to use a domain account, not a machine account.

[aki-siponen]

I may not have been clear in my initial message. Machine accounts work just fine in ADF & IR, but they do not work in Fabric & DG.