Error from SQL linked server through Gateway

[mbailey]

Hi,

Running MS SQL Server 2012.  I have a Gateway setup to this server. It is working just fine.

I've created a Linked Server on this server to another 2008 SQL Server.  In Management Studio I can run a query against the Linked Server just fine. I created a View on the 2012 Gateway server to a table on the 2008 Linked server.  I can query against that View with no problem. But, in Power BI, when I select this View as the data source I get the error below. It's saying it's an "Authentication" error, but it seems if that was the case then none of the above would work either.  I do know that the 'sa' password for the Linked server is different than the 'sa' password for the Gateway server but would think that was handled be the Linked Server config.

Does anyone have any suggestions?

Thanks,

Mike Bailey

DataSource.Error: Microsoft SQL: The OLE DB provider "SQLNCLI11" for linked server "DB001\GP10" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "DB001\GP10".
Details:
DataSourceKind=SQL
DataSourcePath=csisql01;dmta_app
Message=The OLE DB provider "SQLNCLI11" for linked server "NAUSATDB001\GP10" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "DB001\GP10".
Number=7399
Class=16

[v-yuezhe-msft · Accepted solution]

Hi @mbailey,

Do you connect to the view in Power BI Desktop? If so, I test the scenario as yours, everything works well. I log in SQL Server 2012 using sa account and then configure linked server as follows. Then when I connect to the database containing the view of SQL Server 2012 in Power BI Desktop, I also use sa account.

Could you please describe more details about how you configure the linked server and how to connect to the database in Power  BI Desktop?

Thanks,
Lydia Zhang

[v-yuezhe-msft · Accepted solution]

Hi @mbailey,

Do you connect to the view in Power BI Desktop? If so, I test the scenario as yours, everything works well. I log in SQL Server 2012 using sa account and then configure linked server as follows. Then when I connect to the database containing the view of SQL Server 2012 in Power BI Desktop, I also use sa account.

Could you please describe more details about how you configure the linked server and how to connect to the database in Power  BI Desktop?

Thanks,
Lydia Zhang

[mbailey · Accepted solution]

Hi,

Looks like my configuration is different from yours.  I will try setting up the Linked Server as you have shown and see if it helps.

Thanks,

Mike Bailey

[mbailey]

After changing my settings for the Linked Server it is now working.

Thank you,

Mike Bailey