Microsoft SQL Server login using Active Directory Credentials

Question

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.

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.

Solution 1

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.

Solution 2

The easiest way is just to simply run SQL Server as another user. To do this follow these steps:

• Navigate to the folder containing a shortcut for SQL Server, i.e.:
  • Press the Windows key
  • Type "SQL Server"
  • When the application pops up under the "Best Match" section, click "Open file location"
• Shift + Right-Click the shortcut
• Select "Run as different user"
• At the prompt, type in your AD credentials
• Done!

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.

Comments

[Gilles Lesire]

If the runas works, is there a way to avoid the command prompt? Not all our users are tech savy.

[Katherine Villyard]

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.

[noam]

Or use Sysinternals ShellRunAs