0

I have a machine which could connect to an MS SQL Server in the past. I have SSMS installed and the connection was possible when I activated TrustServerCertificate checkbox in SSMS. I could connect and see databases and tables and execute statements etc...

Later I tried to scaffold models with entity framework with the command:

dotnet ef dbcontext scaffold "Server=xxx;Database=yyy;User Id=zzz;Password=abcd;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer --table xxx.yyy

There was a prompt which said i could create or install (I cant remember which it was) a certificate. I accepted the prompt and after that the connection is not possible anymore. Even with SSMS the connection is not possible anymore.

I think entity framework or the machine has created a certificate and now the connection is not possible because the created certificat is not trusted by the sql server.

How do i get the connection working again?

If i try to connect via SSMS i get the message:

Cannot connect to xxx.

------------------------------ ADDITIONAL INFORMATION:

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.) (Microsoft SQL Server, Error: -2146893019)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver--2146893019-database-engine-error

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

If i activate trust server certificate the following message is shown:

Cannot connect to xxx.

------------------------------ ADDITIONAL INFORMATION:

Login failed for user 'zzz'. (Microsoft SQL Server, Error: 18456)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

How can i remove this certificate? Where is it stored on the machine? How can i connect again? I have no acces to the remote SQL Server.

I did not find any certificate in "computer certificates" or "user certificates" on the windows machine which may be indicating that this was the created certificate. There are so many installed certificates on this machine.

Thanks in advance

Improve this question
2
  • 5
    Check the SQL Server error log for additional messages as to the specific reason for the "Login failed for user 'zzz'. (Microsoft SQL Server, Error: 18456)" error and add those to your question.
    – Dan Guzman
    Aug 31 at 11:41
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Aug 31 at 13:49

1 Answer 1

Reset to default
0

for this error:

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.) (Microsoft SQL Server, Error: -2146893019)

You should verify to have installed in your machine the CA certificated that generated the sql server certificate. After installing the CA certificate, you no longer have to check trust server certificate.

This happens because the sql server is configured to manage connection in encrypted way (like web sites with HTTPS).

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.