Confused about accessing SQL Server

[RossChevalier]

Hello,

I am finding connecting to a SQL Server to be very confusing.

I have an existing set of reports and dashboard published to the PowerBI service.  The data sources are on my local machine (Excel extracts from SQL Server) and any updates are manual.  I would rather get the data directly from the production SQL Server and set a refresh interval to make updating automatic.

We have created gateway connections in the service and also created datasets for the gateway connections and they show as active and live in Manage Gateways but under Settings the datasets show errors as they reference the physical Excel files and I see no way to remove those datasets and add the gateway connected datasets.

I see no way in the PowerBI service to add the gateway connected datasets at all.  If I click Get Data, there is no option for SQL Server, only Files or Azure SQL databases or SSAS.

As I understand things, I need to create a new PowerBI desktop entity that connects to the SQL Server directly.  The SQL Server is on another network that I must VPN into.  When the VPN is connected, and I try to connect to the SQL Server via the PowerBI Desktop Get Data SQL Server method, it tells me that the server cannot be found or that there is a Named Pipes error 40.  I can connect to the SSRS instance on the server and it works fine so I am at a loss as to why I cannot connect from PowerBI desktop.

I understand that for the dataset to be available in the PowerBI service to allow the Gateways to function at all, I must create a database connection locally first, then publish that to the service.  THis seems very convoluted.  Is my understanding correct?  I cannot create a SQL Server connection directly from the PowerBI service even if the gateways and data sources are set up?

Excel and even Access connections are incredibly simple but getting connected to SQL Server seems to require some kind of blood sacrifice and a lot of duplication.

Could someone point me towards a course or tutorial or some clearly understandable documentation on achieving this?

Thank you kindly

[GilbertQ · Accepted solution]

Hi there

Whilst it does appear to be hard, it can certainly be done.

Very often it is all about planning for and testing how you want to change from Excel to a SQL Server database.

Make sure that you already can connect to the SQL Server Source.

What you can do is in your PBIX create a new connection to the SQL Server and load the table in Power Query Editor

Once the table is loaded you can then go into the Advanced Editor and copy and paste the M code into your Excel table. This should then have changed the source from Excel to SQL.

Repeat this for all the tables in Power Query. once you are done, if you click on Data Source Settings you should only see a data source for your SQL Server. If that is working, you can then load your dataset, upload it to the Power BI Service and then connect it via the Gateway.

[v-kelly-msft · Accepted solution]

Hi @RossChevalier ，

Yes,for sql server,it can only be connected in desktop then published to service.

Here is the reference.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[RossChevalier]

Thank you for taking the time and for providing the link Kelly.  I had been using this tutorial but have been unsuccessful in connecting the on premises SQL Server as I am remote and even when connected by VPN, I am unable to connect to the named server.

Thanks

Ross

[GilbertQ · Accepted solution]

Hi there

Whilst it does appear to be hard, it can certainly be done.

Very often it is all about planning for and testing how you want to change from Excel to a SQL Server database.

Make sure that you already can connect to the SQL Server Source.

What you can do is in your PBIX create a new connection to the SQL Server and load the table in Power Query Editor

Once the table is loaded you can then go into the Advanced Editor and copy and paste the M code into your Excel table. This should then have changed the source from Excel to SQL.

Repeat this for all the tables in Power Query. once you are done, if you click on Data Source Settings you should only see a data source for your SQL Server. If that is working, you can then load your dataset, upload it to the Power BI Service and then connect it via the Gateway.

[RossChevalier]

Thank you for your courteous response.  Your answer is much simpler than I had been advised.  Once I get a working connection to the SQL Database from desktop I will be able to follow your process.

Ross