Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Error 2146232060 first time connecting to AwsDataMart using Power Query

Hi guys,

Really would appreciate your expertise on this. Tried searching for a solution .. am still searching. 

I have been given a task to integrate a report which was done by a colleague in PBI with the data source coming from SQLs erver. who has already left. I have the service credentials that was used to connect the data source. However after connecting. This is the error i get below?? May i know what is the cause of this??

 

DataSource.Error: Microsoft SQL: 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.)
Details:
    DataSourceKind=SQL
    DataSourcePath=awsDataMart
    Message=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.)
    ErrorCode=-2146232060
    Number=-2146893019
    Class=20

 

BR

Jared

Need Help

2 Replies

  • Divyaraj_Rathod's avatar
    Divyaraj_Rathod
    Icon for Helper II rankHelper II
    2 months ago

     

    Since your colleague who set this up originally has left, it's likely their machine already had that CA's certificate trusted (installed by IT, or trusted implicitly because it was domain-joined to that environment), while yours doesn't.

     

    To fix it, you (or your IT/DBA team) need to get the SQL Server's certificate chain (root and any intermediate CA certificates) installed into the "Trusted Root Certification Authorities" store on whichever machine is making the connection:

    1. Ask the DBA for the certificate (or root CA cert) that AwsDataMart's SQL Server presents, or export it yourself by browsing to the server:port in a browser / using openssl s_client to grab the chain.

    2. Import the root/intermediate .cer file via certmgr.msc (Trusted Root Certification Authorities > Certificates > All Tasks > Import) on your machine, and on the On-premises Data Gateway machine if this dataset is scheduled to refresh through a gateway.

    3. As a temporary diagnostic only (not recommended for production use) some people disable encryption/certificate validation on the connection string to confirm this is indeed the cause, but the proper fix is trusting the correct CA rather than turning off encryption checks permanently.

     

    Once the CA is trusted, the "chain of trust" completes successfully and this error should go away without needing to change credentials at all - the credentials you already have should work fine.