Problem connecting to SQL Server from Linked Service. Is it posible to have an unencrypted connection to a DB from Azure?

Question

I'm trying to create a Linked Service to connect to an SqlServer Database. But I'm getting the following error message:

Cannot connect to SQL Database: 'Server name', Database: 'Database Name', User: 'user'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.), SqlErrorNumber=10054,Class=20,State=0,
An existing connection was forcibly closed by the remote host
Activity ID: activityId

I think the problem might be that the database does not accept encrypted connection. When connecting from SQL Server Management Studio I get the following error:

Cannot connect to 'Server name'.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - La cadena de certificación fue emitida por una entidad en la que no se confía.) (Microsoft SQL Server, Error: -2146893019)
For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver--2146893019-database-engine-error
La cadena de certificación fue emitida por una entidad en la que no se confía

Which means untrusted certificate. Just as a workaround until I get to solve the problem I can connect unchecking the 'Encrypt connection' option like this in SQL Server management Studio or checking the 'Trust server certificate':

But from the linked service I cannot get to connect to the server. I have tried to modify the connection string using: "encrypt=true;trustServerCertificate=true". Also using just "trustServerCertificate=true" and using just "encrypt=false". But I keep getting the same error message. Do you know what could be happening? Or maybe Azure doesn't allow these types of connections?

Thanks

Answer 1

We also had similar problem; changed the backend to accept encrypted connection.

Answer 2

To fix this you just need to provide additional Connection properties:

Under the "Additional Connection properties" section on your Azure SQL Database Linked service, add the following property name and value:

Property Name: TrustServerCertificate
Property Value: true

This configuration should solve your issue.