Share via

Unable to connect SQL server to Azure data factory

Sibbinath, Akhil 0 Reputation points
2024-05-20T20:53:22.6433333+00:00

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'DESKTOP-1J7NBOS', Database: 'DataWarehouse', User: 'ak2'. 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 - The certificate chain was issued by an authority that is not trusted.)

The certificate chain was issued by an authority that is not trusted

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,951 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,805 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.6K Reputation points
    2024-05-20T21:03:01.2+00:00

    What the message says. You have requested an encrypted connection(*), but the SQL Server instance does not a certificate that you trust.

    The best solution is make sure that SQL Server uses a proper certificate for the encryption, and that the client has this certificate (or a related root certificate) installed in it trusted-certificate store.

    You can also set the option Trust Server Certificate=yes in the connceton string. However, this makes you susceptible to man-in-the-middle attack. That is, someone pretending to be your SQL Server instance sits between you and SQL Server and corrupts or eacesrops on the traffic.

    (*) In all modern APIs from Microsoft, this is the default setting since a few years back.

  2. MikeyQiao-MSFT 825 Reputation points Microsoft Vendor
    2024-05-21T02:23:36.49+00:00

    Hi，Sibbinath, Akhi

    If you are connecting to the database using SSMS, follow the settings as shown in the screenshot below.

    User's image

    If you are using a connection string, please add the option Trust Server Certificate=yes.

    Best Regards,

    Mikey Qiao

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. ShaikMaheer-MSFT 38,291 Reputation points Microsoft Employee
    2024-05-21T03:24:03.61+00:00

    Hi Sibbinath, Akhil,

    Thank you for posting query in Microsoft Q&A Platform.

    It seems you are using local SQL to connect from ADF. If yes, please consider using selfhosted IR and make sure credentials are correct.

    Hope this helps. If above is not the case, then kindly share more details on your SQL and linked service configurations. That helps to estimate root cause better.

    Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.