Cannot create SQL Server datasource connection for on-premises Gateway

[duncfair]

I have a new Gateway and was able to migrate\re-create all Gateway connections except for one SQL Server datasource.

1.) the connections for that SQL Server work fine on the old Gateway.

2.) Microsoft support first contacted me 5 days ago but has not been able to resolve the issue 

Here is the error I get:

Any ideas, anyone?

Thanks!

Duncan

[v-sgandrathi]

Hi @duncfair,
Thank you for being a part of the Microsoft Fabric Community.

Try these troubleshooting steps that may help you resolve the issue:
Please ensure the new gateway is fully updated.

Review the Data Source Settings in both the old and new gateway configurations.

Disable "Force Encryption" in SQL Server to allow connections without mandatory encryption, preventing   potential SSL/TLS mismatches with the new gateway.

The new gateway may be operating under a different service account compared to the old one. Once verify that the service account has the necessary permissions on the SQL Server.

Consider adjusting the encryption settings (TrustServerCertificate=True) in the new gateway connection.

Ensure the credentials used for the SQL Server connection are accurate and have the required permissions to      access the database. Use the same credentials that are successful in SSMS.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Regards,
Sahasra.

[duncfair]

There is no SQL Server setting like this:

Disable "Force Encryption" in SQL Server to allow connections without mandatory encryption, preventing   potential SSL/TLS mismatches with the new gateway.

[v-sgandrathi]

Hi @duncfair,

Thanks for using Microsoft Fabric Community Forum.

To disable Force Encryption in SQL Server, open SQL Server Configuration Manager, navigate to SQL Server Network Configuration 

Click on Protocols for [YourInstanceName], and open Properties.
In the Flags tab, set Force Encryption to No and click OK.
Finally, restart the SQL Server service for the changes to take effect.

This step may help you reslovoing the issue.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thankyou.

[duncfair]

Thanks! I was just looking in SSMS.

Anyway, that setting was already set to 'no'.

[v-sgandrathi · Accepted solution]

Hi @duncfair,

If the troubleshooting steps have not resolved the issue, the most appropriate approach is to seek assistance from Microsoft Support.

If you have already raised a support ticket, please wait for further updates from the team as they continue investigating and working on a resolution.

Thanks for using Microsoft Fabric Community Forum.

[duncfair]

I have had a ticket with Microsoft open for almost 2 weeks...☹️

[v-sgandrathi]

Hi @duncfair,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[duncfair]

This was resolved by a Microsoft tech I had worked with in the past

[duncfair]

Also, in addition to the connection working fine with the old Gateway, I am able to use the credentials in SSMS to connect to and query the data.