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

Luis Jiménez 6 Reputation points
2022-09-26T11:32:32.44+00:00

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':
244842-captura-de-pantalla-2022-09-26-131856.png

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,752 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Akhilesh Balakrishnan 0 Reputation points
    2023-04-05T06:48:20.99+00:00

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

    0 comments No comments

  2. Pedro Mangino 1 Reputation point
    2023-10-03T14:24:45.2866667+00:00

    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.

    0 comments No comments