Forum Discussion
PowerBI On-Prem Gateway Direct Query with TLS
Hello,
We are trying to connect to an on-prem SQL Server database using PowerBI On-Prem Gateway in Direct Query mode and encrypted connection between the Gateway and the SQL Server database. Our setup is a bit special because we have a proxy server between the GW and the SQL Server: PowerBI Cloud --> On-Prem GW --> Proxy --> SQL Server.
The proxy server only supports encrypted connections. What we observe when we try to load data from PowerBI Cloud is that the On-Prem GW tries to connect to the Proxy server using an unencrypted connection (see network capture below of pre-login message). Does anyone here know if encrypted connections are supported in PowerBI On-Prem GW when using Direct Query?
Tabular Data Stream
Type: TDS7 pre-login message (18)
Status: 0x01, End of message
.... ...1 = End of message: True
.... ..0. = Ignore this event: False
.... .0.. = Event notification: False
.... 0... = Reset connection: False
...0 .... = Reset connection keeping transaction state: False
Length: 94
Channel: 0
Packet Number: 1
Window: 0
Pre-Login Message
Option: Version
Option Token: Version (0)
Option offset: 36
Option length: 6
Version: 4.8.4455
Sub-build: 0
Option: Encryption
Option Token: Encryption (1)
Option offset: 42
Option length: 1
Encryption: Encryption is available but off (0)
Option: InstOpt
Option Token: InstOpt (2)
Option offset: 43
Option length: 1
InstOpt:
Option: ThreadID
Option Token: ThreadID (3)
Option offset: 44
Option length: 4
ThreadID: 1152
Option: MARS
Option Token: MARS (4)
Option offset: 48
Option length: 1
MARS: Off (0)
Option: TraceID
Option Token: TraceID (5)
Option offset: 49
Option length: 36
TraceID: 799793f2adf9c64f8d2c3680fa47a82252ad5b7e7f05ac41922e2bb4cb13eeec01000000
Option: FedAuthRequired
Option Token: FedAuthRequired (6)
Option offset: 85
Option length: 1
FedAuthRequired: 1
Option: Terminator
Option Token: Terminator (255)
Thanks!
OK, problem solved. We identified that the proxy was not behaving exactly the same as when connecting without it to SQL server. SQL server will force the client to use encryption and in that case the client will oblige. The proxy only recommended it to the client...
2 Replies
- yoavcohenRegular Visitor
OK, problem solved. We identified that the proxy was not behaving exactly the same as when connecting without it to SQL server. SQL server will force the client to use encryption and in that case the client will oblige. The proxy only recommended it to the client...
- bcdobbsCommunity Champion
No experience with the proxy but worth checking in power bi desktop before publishing that encryption is enabled:
File->Options and Settings -> Data Source Settings in Power BI Desktop, select your SQL data source and tick "Encrypt connections".
from