PowerBI On-Prem Gateway Direct Query with TLS

[yoavcohen]

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!

[yoavcohen · Accepted solution]

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...

[bcdobbs]

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 

https://community.powerbi.com/t5/Desktop/unable-to-connect-to-the-data-source-using-an-encrypted/m-p...