12

Our Microsoft SQL Servers are running on Windows Servers which are part of an Active Directory domain.

For easy user management, our SQL authorization is set up by using the Active Directory User Groups as explained in this post.

Now this works fine as long as everyone is working inside the domain. People login to their computer using their AD credentials and can connect to the SQL server by using the "Windows Authentication".

Problem is that our users will also be working on other client computers which are not part of the Active Directory domain (and adding them to the domain is not an option).

I was hoping they could simply keep using their AD credentials to login to the server by using the AD authentication as mentioned in the SQL Server login screen.

SQL Server Login Screen

However this does not seem to be working.

Logging in with Acive Directory Password Authentication gives certificate issues. Error: "The certificate chain was issued by an authority that is not trusted."

Cannot connect to x.x.x.x.

===================================

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.) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476 (page does not exist)

Oddly enough, if I go to the "options" settings at the login window and check the box "trust server certificate". I suddenly am NOT able to connect to the server and I simply get the error.

Cannot connect to x.x.x.x.

===================================

Login failed for user ''. (.Net SqlClient Data Provider)

Is the setup I want to achieve possible? And if so, how can I achieve it? :)

Recap: SQL Servers are running in AD domain, users have AD credentials but also need to be able to login from client computers NOT part of the AD domain with their AD credentials.

  • Open C:\WINDOWS\system32\cliconfg.exe and check to see if force protocol encryption is enabled. If it is disable it – Drifter104 Aug 24 '17 at 9:12
  • No it's not enabled. – Gilles Lesire Aug 24 '17 at 11:04
  • 1
    Interesting problem. This seems valid: runas /netonly /user:domain\username "c:\path\ssms.exe" mssqltips.com/sqlservertip/3250/… – Michal Sokolowski Aug 25 '17 at 15:49
9
+50

The error message is that the certificates were issued by an untrusted authority. That would be your AD domain, most likely.

A couple of workaround that you might want to try:

  1. This dba.stackexchange.com post suggests clicking the options button on your connection window above and adding TrustServerCertificate=True under Additional Connection Parameters. This is basically telling SSMS that yes, you know, just do it.
  2. If that doesn't work, this dba.stackexchange.com post suggests using runas in order to trick Windows. For example, runas /netonly /user:domain\username "C:\path_to\ssms.exe" I've never done this with SSMS but it's worth a try, since the OP seems to be in a similar situation to you (and I trust the guy answering).

Anyway, hopefully one of those workarounds will do the trick for you.

  • 1
    The first solution didn't work, it was already adressed in my question. The second solution seems to work, altough when logged in it still says my windows name next to the connection, have to do some tests if it is actually the domain account and if it works properly as hoped. Fingers crossed. – Gilles Lesire Aug 29 '17 at 7:41
  • If the runas works, is there a way to avoid the command prompt? Not all our users are tech savy. – Gilles Lesire Aug 29 '17 at 9:23
  • 2
    Maybe you could create a custom shortcut? I actually just tested this myself by adding runas /netonly /user:domain\username to the beginning string of a copied shortcut. It worked fine, although it also popped up a command prompt for them to enter their credentials. – Katherine Villyard Aug 29 '17 at 17:38
  • Or use Sysinternals ShellRunAs – noam Aug 22 '18 at 1:33
1

The easiest way is just to right-click on "Microsoft SQL Server Management Studio" - "more" select "Run as a different user". It worked for me. I thought that if I select "Active Directory - Password" from the login screen that would do the same job but... unfortunately it didn't.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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