cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcapurro
Regular Visitor

Power BI Enterprise Gateway in DMZ has issues connecting to internal SQL server

Hello Power BI Community,

 

I'm a little stumped here... on my DMZ server, I can use SQL Server Management studio to connect to an internal database.  I try to use those same parameters on a registered Power BI Gateway from the Power BI admin panel (web interface), in data source settings, and I get errors.

 

Wireshark output of where I think Power BI Gateway is having an issue:

 

223595    80460.639327    192.168.1.202    10.1.1.216    SMB2    228    Create Request File: MSSQL$DATASTORE\sql\query

223596    80460.639806    10.1.1.216    192.168.1.202    SMB2    131    Create Response, Error: STATUS_ACCESS_DENIED

 

Wireshark output of the same exchange, but with SQL Server Management studio:

 

232965    81623.491986    192.168.1.202    10.1.1.216    SMB2    228    Create Request File: MSSQL$DATASTORE\sql\query

232966    81623.492368    10.1.1.216    192.168.1.202    SMB2    210    Create Response File: MSSQL$DATASTORE\sql\query

232967    81623.494741    192.168.1.202    10.1.1.216    SMB2    273    Write Request Len:103 Off:0 File: MSSQL$DATASTORE\sql\query

232968    81623.495121    10.1.1.216    192.168.1.202    SMB2    138    Write Response

232969    81623.496042    192.168.1.202    10.1.1.216    SMB2    171    Read Request Len:4096 Off:0 File: MSSQL$DATASTORE\sql\query

232970    81623.496354    10.1.1.216    192.168.1.202    SMB2    186    Read Response

232971    81623.497491    192.168.1.202    10.1.1.216    SMB2    146    Close Request File: MSSQL$DATASTORE\sql\query

232972    81623.497809    10.1.1.216    192.168.1.202    SMB2    182    Close Response

 

Error message I get from Power BI web interface:

 

Unable to connect: We encountered an error while trying to connect to smcusertech2.smcu.org\DATASTORE. Details: "Cannot connect to the server."Hide details

Activity ID:0eca4898-101b-4bbd-941d-50af8029216f
Request ID:b16c2404-e5d1-dcdd-9ea5-ed4882220b4c
Cluster URI:https://wabi-us-north-central-redirect.analysis.windows.net
Status code:400
Error Code:DM_GWPipeline_Gateway_ServerUnreachable
Time:Thu Sep 15 2016 11:42:10 GMT-0700 (Pacific Standard Time)
Version:13.0.1605.478
Underlying error code:-2146232060
Underlying error message: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)
DM_ErrorDetailNameCode_UnderlyingHResult:-2146232060
DM_ErrorDetailNameCode_UnderlyingNativeErrorCode:5

 

 

The gateway is running as NT SERVICE\PBIEgwService (by default), and SQL Server Management Studio is running as local administrator.  The DMZ server is not joined to the domain.

 

From the same server in the DMZ, SQL Server Management studio connects, but Power BI Gateway doesn't.  Any ideas?  I had to use Wireshark initially to see what ports needed to be opened, as our DMZ is not able to access the internal network unless we allow it to.  I believe I have ruled out network issues by demonstrating that I can connect from the DMZ server's SQL Server Management studio.  What am I missing here?  Is it because the local gateway isn't running as a user with administrative permissions?

 

Thanks,

 

David Capurro

1 ACCEPTED SOLUTION

I'm happy to report that this issue is now resolved.  Apparently, I needed to switch from TCP Dynamic ports to a specific TCP port for this instance on our DB server.  I found this site to be very helpful:  https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-erro...

 

As soon as I changed it to the default port 1433, I was able to connect with Power BI Gateway.

View solution in original post

3 REPLIES 3
v-caliao-msft
Microsoft
Microsoft

Hi @dcapurro,

 

I have tested it on my local environment, I can connect to that internal database in Power BI gateway without any issues.

 

In your scenario, which authentication type are you using to connect to that database. Since that internal database server haven't join into your domain. You need to use a SQL Server authentication to connect it. So in your scenario, create a SQL Server login and try it again.
Capture.PNGCapture1.PNGCapture2.PNG

 

Besides, here is a document about troubleshooting on-premises data gateway for you reference.
https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/

 

Regards,

Charlie Liao

I should have mentioned that I am already using an internal SQL account, not a domain account.  I even tried the "sa" account just to see if it was a permissions issue, and "sa" wasn't able to connect with Power BI Gateway either.

 

 

I'm happy to report that this issue is now resolved.  Apparently, I needed to switch from TCP Dynamic ports to a specific TCP port for this instance on our DB server.  I found this site to be very helpful:  https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-erro...

 

As soon as I changed it to the default port 1433, I was able to connect with Power BI Gateway.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors