1

This is my first question so please bear with me !

We have an external company whose developers need to login into an instance on our on-premise SQL Server 2016.

They have AD logins, and these are in a distribution group that has access to the relevant instance.

I've created a Self Certificate, installed it to SQL server and encrypted the connection using it, and even installed it on my laptop (which is off our Domain).

To test the situation I use a local laptop login, VPN to our network, and run SSMS using 'runas /netonly /user.....' with the user being a domain user having the same rights as the remote developers.

When I try to connect I get

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.)

even though the certificate is trusted.

If I tick Trust Server Certificate in the SSMS connection options I get "Login failed for user ''"

I'm thinking we need to buy a Certificate - but if anyone has come across a similar situation with a different resolution please let me know !

Thanks.

Improve this question
3
  • 1
    do you get any messages in the error log on the SQL Server? This will let you know if they are even making it to the server.
    – Jonathan Fite
    Oct 22, 2019 at 16:01
  • 1
    Please state exactly how you installed the cert on your laptop. Did you install it to your user personal store, or the local machine's personal store?
    – Tony Hinkle
    Oct 22, 2019 at 17:15
  • you may also need to install the CA cert on your laptop
    – Bob Klimes
    Oct 22, 2019 at 19:03

2 Answers 2

Reset to default
1

I've created a Self Certificate, installed it to SQL server and encrypted the connection using it, and even installed it on my laptop (which is off our Domain).

By "Self Certificate" do you mean a self-signed certificate created on the machine or a certificate created by your private Certificate Authority on your domain (probably domain controller)?

I'm assuming you mean a certificate issued by your internal CA, so you need to make sure you install the public key for your CA into the Trusted Root Certification Authorities store on your laptop so that the certificate identity can be validated by the third party (your internal CA). In a domain-joined machine, this is done automatically, but as your laptop is not domain-joined it may not have the internal CA as a trusted CA.

Also, since you're running SSMS as a domain user, ensure the SQL Server certificate is installed in the local computer certificate store and not the current user store, otherwise the runas user won't see the SQL Server certificate.

Lastly, validate the certificate meets the requirements defined by Microsoft here, and make sure that you've configured SQL Server to force encryption in SQL Server Configuration Manager.

Once you've done this, connect to SQL from either the machine or a workstation that is domain-joined and validate that your connection is encrypted:

SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

This should return true if encrypted or false if unencrypted. Once you've confirmed encryption works from domain-joined machines, test from your non-domain laptop.

Improve this answer
0

Thanks for all your comments - especially HandyD, who got it spot on !

The answer was to create the Certificate on the domain controller, which I was a bit surprised about, not being well-versed in Certification etc.

I didn't realise that the DC would be recognised as an authority.

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.