topic Connect Linked SQL Server to Power BI Desktop in Developer

Connect Linked SQL Server to Power BI Desktop

Humanoidhuman — Mon, 08 Jul 2024 19:40:33 GMT

Background and Problem:

I am trying to create a Power BI report using data pulled from MSSQL server to which I have access. I am able to successfully connect to the primary SQL server using the regular connection steps. However, the dbo which has the data I need for this particular report lives on a linked server which is connected to my primary MSSQL server (I can access it on MSSQL management studio under the path >>'Server Objects' > 'Linked Servers' > 'My Linked server').

Anticipated Solution:
I am hoping to establish a connection to the linked server so I can access the dbo which has the data that i need for my report.

TIA!

Re: Connect Linked SQL Server to Power BI Desktop

lbendlin — Tue, 09 Jul 2024 00:56:23 GMT

In your connection definition try running a SQL query against the linked server.

Re: Connect Linked SQL Server to Power BI Desktop

Humanoidhuman — Tue, 09 Jul 2024 14:01:31 GMT

I tried running the a SQL script under advanced options while establishing connection to the server (picture below), but it asks for a database. For this I tried entering the linked server name as well but it doesn't establish connection saying authentication failed. I double checked my credentials and permissions all the server they seem to be all there.

I am also attaching the script I used below for reference

SELECT * FROM linkedserver.Runtime.dbo.History WHERE TagName = 'SysTimeSec' AND DateTime >= '2023-09-12 12:59:00' AND DateTime <= '2024-01-12 13:00:00'

Please let me know what I am doing wrong. TIA!

Re: Connect Linked SQL Server to Power BI Desktop

lbendlin — Tue, 09 Jul 2024 15:00:36 GMT

Use the initial server and database for the connection. The database is NOT optional, that dialog title is faulty.

Then run the query as you listed it.

Re: Connect Linked SQL Server to Power BI Desktop

Humanoidhuman — Wed, 10 Jul 2024 15:15:53 GMT

Thank you for that. However, as you can see below, the linked servers do not fall under the any database. For this purpose I am unable to discern what to enter as a database when linking to the SQL server

Re: Connect Linked SQL Server to Power BI Desktop

lbendlin — Wed, 10 Jul 2024 15:55:20 GMT

Pick any database on the original server that you have access to.

Re: Connect Linked SQL Server to Power BI Desktop

Humanoidhuman — Wed, 10 Jul 2024 16:19:59 GMT

Thank you so very much. That worked!