Sql-server – SSMS login by external developers

certificatepermissionssql serversql-server-2016

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.

Best Answer

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.

Related Question