Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
60 | |
20 | |
18 | |
18 | |
9 |