Setting up a connection to unencrypted SQL database?

[UnencryptedSQL]

Hello,

I am trying to set up an on premise gateway to connect to an unencrypted SQL database.

It keeps failing even though I have tried with multiple confirmed correct credentials. 

I found this post https://community.fabric.microsoft.com/t5/Service/Error-via-On-Premises-Data-Gateway-to-SQL-server/m...

I just wanted to confirm there is in fact no work around to connect to an unencrypted SQL database? Is there a KB or anthing that states this?

Error message: Unable to create connection for the following reason: Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request.

Thank you,

[v-dineshya · Accepted solution]

Hi @UnencryptedSQL ,

Thank you for reaching out to the Microsoft Community Forum.

Microsoft's On-premises Data Gateway typically requires an encrypted connection (SSL/TLS) when connecting to SQL Server. Unencrypted connections are increasingly blocked due to security concerns.

Please refer below points.

1.By default, Power BI and the On-premises Data Gateway enforce encrypted connections to SQL Server. If SQL Server does not support or allow encrypted connections, the gateway connection will fail, even with correct credentials.

2.While there isn’t a specific KB that flat-out says "unencrypted SQL connections are not allowed," the encryption requirement is embedded in various places:

3.In Microsoft Learn docs, encryption is always assumed or enabled. Microsoft’s error messages and community posts (like the one you linked) repeatedly point to this being a non-configurable requirement for security.

4.There is no supported workaround to disable encryption enforcement via the gateway. Some users have tried modifying connection strings with Encrypt=False, but this is either ignored or overridden by the gateway.

Even if not, it violates Microsoft's security recommendations and may cause unexpected behavior.

Enable encryption on your SQL Server: Use a self-signed certificate if needed (you don't need a CA-issued cert for internal use). Set Force Encryption = Yes on the SQL Server Network Configuration. Ensure the gateway can trust the SQL certificate.

Please refer below Microsoft articles.

Configure SQL Server Database Engine for encryption - SQL Server | Microsoft Learn

Connect to SQL Server with strict encryption - SQL Server | Microsoft Learn

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[rohit1991]

Hi @UnencryptedSQL ,
You're encountering a common issue when trying to connect an on-premises data gateway to an unencrypted SQL database. By default, the Power BI gateway enforces encrypted connections for security reasons, and this can cause failures when attempting to connect to SQL Server instances that do not support or have encryption disabled. Even with correct credentials, the gateway may block the connection if encryption is not negotiated. 

While there is no official Knowledge Base (KB) article that explicitly states unencrypted connections are unsupported, Microsoft's documentation and community forums consistently reinforce the requirement for secure, encrypted communications. Older versions of the gateway may have allowed configuration tweaks to bypass encryption, but these approaches are no longer supported due to increased security enforcement. The best course of action is to enable encryption on your SQL Server or use a valid SSL certificate. Additionally, ensure that your SQL Server is using a compatible authentication mode and that the necessary domain trust and network paths are correctly configured, as these can also trigger similar login errors.