Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
evanliang
Employee
Employee

Not able use data source configured on on-premises data gateway

I installed on-premises data gateway on one box, and addedQL server DB as one data source, all connections are green. And gateway can be found on PowerBI service page, up and running.

 

Wherever data source can't be connected from PowerBI desktop or PowerBI service page, here are steps

 

Get Data -> SQL Server Database -> Input Server Name and DataBase with exactly configured in data gateway -> DirectQuery -> Input user name and password exactly same configured in data gateway -> Unable to Connect with error below

 

Details: "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

1 ACCEPTED SOLUTION

Thanks much you guys replies. Eventually I figured out the reason. Originally I though Data Gateway could be used by Power BI Desktop app, but actually it's not the truth. Because Power BI wiki for Get Data from SQL is a little not clear to me on what machanisim is inside.

 

Backend SQL service is up and running properly. The machine(dev box) where is running Power BI Desktop app needs to be able to connect the SQL service directly using the particualr address pointing to backend SQL service. And gateway needs to configure exactly same address and DB name configured in Power BI Desktop app. And Power BI service internally does magic thing to match data source setting and get data from gateway service.

 

Based on my findings. here are knowledge points what I have learned

 

1. Gateway Service is only avaiable to Power BI service and no configurtion is required between both

2. Power BI Service does mapping the data source with exactly server address used in Power BI Desktop app

3. Power BI Desktop app on where machine needs to be able to access SQL service directly

4. Gateway Service on where machine needs to be able to access SQL service directly

 

Thanks

Evan

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

Hi @evanliang

Based on your description, you successfully add SQL Server data source under your gateway but fail to connect to SQL Server from Power BI Desktop. Are the power BI Desktop and SQL Server installed in two separate machines? If so, please also check the following things besides checking SQL Server Service status.

1. Make sure you are able to ping the server where SQL Server is installed.
2. Ensure that you enable TCP/IP protocol in SQL Server Configuration Manager(SSCM).
3. Make sure that the SQL Server Browser service is running.
4. Configure the Windows Firewall for the SQL Server port and SQL Browser service if firewall is on.

Reference:
https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-erro...

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks much you guys replies. Eventually I figured out the reason. Originally I though Data Gateway could be used by Power BI Desktop app, but actually it's not the truth. Because Power BI wiki for Get Data from SQL is a little not clear to me on what machanisim is inside.

 

Backend SQL service is up and running properly. The machine(dev box) where is running Power BI Desktop app needs to be able to connect the SQL service directly using the particualr address pointing to backend SQL service. And gateway needs to configure exactly same address and DB name configured in Power BI Desktop app. And Power BI service internally does magic thing to match data source setting and get data from gateway service.

 

Based on my findings. here are knowledge points what I have learned

 

1. Gateway Service is only avaiable to Power BI service and no configurtion is required between both

2. Power BI Service does mapping the data source with exactly server address used in Power BI Desktop app

3. Power BI Desktop app on where machine needs to be able to access SQL service directly

4. Gateway Service on where machine needs to be able to access SQL service directly

 

Thanks

Evan

BhaveshPatel
Community Champion
Community Champion

Can you please make sure that the SQL Server service is running. You can go to SQL Server Configuration Manager and Check that the SSMS service is running.

 

This is the most probable reason that there is a network specific error.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.