cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yve214
Helper II
Helper II

A connection was successfully established with the server, but then an error occurred during the log

PLease I need help! 
 
I have looked through online to solve this problem to no avail. I cant refresh or connect to my datasource. Its giving me this error below:
 
"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= xxxx
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
"
 
- I tried the whole connecting from ssms and tclicking the "Options" button at the lower right in the dialog, checked the box next to "Trust server certificate" then click Connect.
- read another source about using MFA to sign in and didnt work. 
 
I dont know how to fix this. I installed and reinstalled SSMS 18.12 like so many times and nothing. please help. I can connect quite alright in ssms, but not in power bi.
1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @yve214 ,

Cause:

When establishing encrypted connections to SQL Server, Secure Channel (Schannel) creates the list of trusted certificate authorities by searching the Trusted Root Certification Authorities store on the local computer. During the TLS handshake, the server sends its public key certificate to the client. The issuer of a public key certificate is known as a Certificate Authority (CA). The client has to ensure that the certificate authority is one that the client trusts. This is achieved by knowing the public key of trusted CAs in advance. When Schannel detects a certificate that was issued by an untrusted certification authority, such as in the above two cases, you get the error message listed in the Symptoms section.

Solution:

If you intentionally use either a certificate from a non-trusted authority or a self-signed certificate to encrypt connections to SQL Server, you can use one of the following options:

For Scenario 1: Add the certificate authority to the Trusted Root Certification Authorities store on the client computer initiating encrypted connection. To do this, complete the Export the server certificate and Install the root certificate authority (CA) on the client machine procedures listed below in that sequence.

Export the server certificate

The example uses a file named caCert.cer as a certificate file. You must obtain this certificate file from the server. The following steps explain how to export the server certificate to a file:

  1. Click Start and then Run, and type MMC. (MMC is an acronym for the Microsoft Management Console.)

  2. In MMC, open the Certificates.

  3. Expand Personal and then Certificates.

  4. Right-click the server certificate, and then select All Tasks->Export.

  5. Click Next to move past the welcome dialog box of the Certificate Export Wizard.

  6. Confirm that No, do not export the private key is selected, and then click Next.

  7. Make sure that either DER encoded binary X.509 (.CER) or Base-64 encoded X.509 (.CER) is selected, and then click Next.

  8. Enter an export file name.

  9. Click Next, and then click Finish to export the certificate.

Install the root certificate authority (CA) on the client machine

  1. Start the Certificates snap-in for MMC on the client computer and then add the Certificates snap-in.

  2. In the Certificates snap-in dialog box, choose Computer account, and then choose Next.

  3. In the Select Computer pane, choose Local computer: (the computer this console is running on), and then choose Finish.

  4. Choose OK to close the Add or Remove Snap-ins dialog box.

  5. In the left pane of MMC, expand the Certificates (Local Computer) node.

  6. Expand the Trusted Root Certification Authorities node, right-click the Certificates subfolder, select All Tasks, and then choose Import.

  7. In the Certificate Import Wizard, on the Welcome page, choose Next.

  8. On the File to Import page, choose Browse.

  9. Browse to the location of the caCert.cer certificate file, select the file, and then choose Open.

  10. On the File to Import page, choose Next.

  11. On the Certificate Store page, accept the default selection, and then choose Next.

  12. On the Completing the Certificate Import Wizard page, choose Finish.

For scenarios 1 and 2: Set Trust Server Certificate setting to true in your client application.

 

 

To learn more details ,refer to the official documentation:

The certificate received from the remote server was issued by an untrusted certificate authority err...

 

And similar question refer:

https://stackoverflow.com/questions/17615260/the-certificate-chain-was-issued-by-an-authority-that-i... 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

1 REPLY 1
v-luwang-msft
Community Support
Community Support

Hi @yve214 ,

Cause:

When establishing encrypted connections to SQL Server, Secure Channel (Schannel) creates the list of trusted certificate authorities by searching the Trusted Root Certification Authorities store on the local computer. During the TLS handshake, the server sends its public key certificate to the client. The issuer of a public key certificate is known as a Certificate Authority (CA). The client has to ensure that the certificate authority is one that the client trusts. This is achieved by knowing the public key of trusted CAs in advance. When Schannel detects a certificate that was issued by an untrusted certification authority, such as in the above two cases, you get the error message listed in the Symptoms section.

Solution:

If you intentionally use either a certificate from a non-trusted authority or a self-signed certificate to encrypt connections to SQL Server, you can use one of the following options:

For Scenario 1: Add the certificate authority to the Trusted Root Certification Authorities store on the client computer initiating encrypted connection. To do this, complete the Export the server certificate and Install the root certificate authority (CA) on the client machine procedures listed below in that sequence.

Export the server certificate

The example uses a file named caCert.cer as a certificate file. You must obtain this certificate file from the server. The following steps explain how to export the server certificate to a file:

  1. Click Start and then Run, and type MMC. (MMC is an acronym for the Microsoft Management Console.)

  2. In MMC, open the Certificates.

  3. Expand Personal and then Certificates.

  4. Right-click the server certificate, and then select All Tasks->Export.

  5. Click Next to move past the welcome dialog box of the Certificate Export Wizard.

  6. Confirm that No, do not export the private key is selected, and then click Next.

  7. Make sure that either DER encoded binary X.509 (.CER) or Base-64 encoded X.509 (.CER) is selected, and then click Next.

  8. Enter an export file name.

  9. Click Next, and then click Finish to export the certificate.

Install the root certificate authority (CA) on the client machine

  1. Start the Certificates snap-in for MMC on the client computer and then add the Certificates snap-in.

  2. In the Certificates snap-in dialog box, choose Computer account, and then choose Next.

  3. In the Select Computer pane, choose Local computer: (the computer this console is running on), and then choose Finish.

  4. Choose OK to close the Add or Remove Snap-ins dialog box.

  5. In the left pane of MMC, expand the Certificates (Local Computer) node.

  6. Expand the Trusted Root Certification Authorities node, right-click the Certificates subfolder, select All Tasks, and then choose Import.

  7. In the Certificate Import Wizard, on the Welcome page, choose Next.

  8. On the File to Import page, choose Browse.

  9. Browse to the location of the caCert.cer certificate file, select the file, and then choose Open.

  10. On the File to Import page, choose Next.

  11. On the Certificate Store page, accept the default selection, and then choose Next.

  12. On the Completing the Certificate Import Wizard page, choose Finish.

For scenarios 1 and 2: Set Trust Server Certificate setting to true in your client application.

 

 

To learn more details ,refer to the official documentation:

The certificate received from the remote server was issued by an untrusted certificate authority err...

 

And similar question refer:

https://stackoverflow.com/questions/17615260/the-certificate-chain-was-issued-by-an-authority-that-i... 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!