Unable to connect to domainSQL Server with Windows Auth via data gateway on non-domain server in DMZ

[pauste1]

The issue:

A SQL Server data source in the Power BI service with Windows Authentication cannot connect to the on-premises SQL Server in a domain through an On-Premises Enterprise Data Gateway when the Gateway is hosted on a server in a DMZ that is not connected to the domain.

Actual Behaviour
Attempting to connect from the Power BI service to an on-premises SQL Server Data Source with Windows Authentication displays an error ‘Invalid connection credentials’ when the on-premises data gateway is installed on a stand-alone server in the DMZ not connected to the domain of the data source server.

Gateway log events

Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.FailedToImpersonateUserException: Error logging on user '<pi>rslc\svc_PowerBI</pi>'

5cad-be4a-0268f5939172 71EE0995 [DM.GatewayCore] Error processing request: [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.FailedToImpersonateUserException: Error logging on user '7E8FC3FEDA695A69E0A9CF701AFF10CC95C9523A341ABEE9D42B88FAD377E0E0'.

  TemplateMessage: Error impersonating a user.

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_ImpersonationError --->

Inner exception chain: System.ComponentModel.Win32Exception

'<pi>System.ComponentModel.Win32Exception (0x80004005): The user name or password is incorrect</pi>'

Expected Behaviour

Attempting to connect from the Power BI service to an on-premises SQL Server Data Source with Windows Authentication successfully connects when the on-premises data gateway is installed on a stand-alone server in the DMZ not connected to the domain of the data source server.

According to organisational IT security policy, it is not desirable to configure the on-premises data gateway on a server in the domain.

1. Connecting to the same data source from the Power BI service using Basic Authentication is successful.
   1. This indicates the database server and database of the data source can be found from the gateway even though the gateway is on a server in a DMZ.
2. Connecting to the same data source from an ODBC connection on the server in the DMZ that hosts the gateway is successful.
   1. This indicates the domain credentials can be used successfully to connect from the server in the DMZ to the database server and database data source in the domain.

We have not observed or see any official documentation that prevents installing the Gateway on a server in a DMZ, except that the Gateway should have access to the data source.

Has anyone successfully configured an Enterprise Data Gateway on a server in a DMZ that is not joined to the domain, where the Power BI service can connect to those data sources in the domain with Windows Authentication?

[v-qiuyu-msft]

Hi @pauste1,

When you create a SQL Server data source under data gateway use Windows authentication, make sure the domain account you entered is the same as the account you used in ODBC data source.

By the way, please update the data gateway to the latest version. If issue persists, please run the Fiddler with HTTPS encrypt enabled then repeat the steps to create SQL data source in data gateway. Share .saz file with us.

Best Regards,
Qiuyun Yu

[pauste1]

Hi @v-qiuyu-msft

Yes, the domain account was the same, and the ODBC server name and database were the same.

I updated the data gateway to the latest version and there was no change in behaviour.

I ran Fiddler with HTTPS decrypt enabled and there was no traffic captured. A colleague also ran Fiddler with HTTPS decrypt enabled and he captured no traffic either while testing the data sources from the PowerBI.com Gateway service. A technician from Microsoft was on a Skype screen-sharing session with us and offered no other suggestions regarding Fiddler to capture web traffic, and finished the call stating we should just install the data gateway on a server in the domain.

So our understanding at the moment from the technician at Microsoft, and the documentation online, is that it is an undocumented requirement that the server hosting the Data Gateway must be connected to the domain in order to use Windows Authentication in the data source.

Has anyone else experienced this limitation?

[Koumos]

Hello Pauste1, did you found any solution on the above Problem? I have the same problem with these services!!!

[pauste1 · Accepted solution]

The technician from a support call with Microsoft eventually stated it was required to install the gateway on a domain-connected server to enable Windows Authentication modes. I'm not sure if the Microsoft technician was just opting for an 'easy' answer or it's a genuine requirement that is not documented anywhere that I saw. So we decided it was an 'undocumented requirement' of the Gateway for that architecture and security configuration.