SQL Windows Authentication can’t login to SSMS locally

Recently, a client was experiencing a really weird issue where they could connect to a recently built SQL server using SQL Server Management Studio loaded on remote workstations, however, they could not login to the console of the server or via RDP and successfully login.

Server Specs

The SQL server was running a rather unusual build and install as it was a Windows 2008 R2 x64 server with SP1 running SQL 2005.  Also of note, the users in this particular environment exist in a Windows NT 4 domain (yes incredible, but they are still out there) and the machine accounts exist in an Active Directory domain (Windows 2003 DCs running in Windows 2000 mode).  So we didn’t rule this out to be part of the issue until later.  Eventually the resolution was clear.

Resolution:

However, as it turns out the resolution was much simpler than expected.  The actual problem turned out to have nothing to do with the NT 4 domain or the SQL server build which was a sigh of relief.

The culprit in our case turned out to be User Account Control (UAC).  With UAC enabled and active on the SQL2K8 R2 box, Windows Authentication will fail if the application is not launched with administrative privileges even if the user account is part of the BUILTINadministrators group which in our case were properly added to the SQL box.

Evidently, there is a known issue with UAC and passing group membership from the user logged in to SSMS, so it doesn’t know that you are authenticated to access the server even if you are an adminstrator.  How do we resolve the issue?

There are basically three resolutions to this issue:

• Disable UAC completely
• Rightclick and runas administrator
• Enable the run this program as administrator on the SSMS shortcut

Disable UAC:

• launch msconfig

 

 

Runas administrator:

Alternatively, you can run the SSMS shortcut as administrator each time you run the program:

Enable run this program as administrator

Once this option is enabled on the SSMS shortcut, you won’t have to right click each time and run as administrator, it will simply prompt you to run it as administrator from any account that tries to run (as long as the setting is changed for all users.

Final Thoughts:

While UAC adds another layer to the security “onion”, it can certainly cause issues in certain situations as we have described above.  However, depending on your environment and your needs, you can tweak and handle UAC as need be to best suit your circumstances.

About The Author

Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com, and a 7-time VMware vExpert, with over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, He has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family. Also, he goes through the effort of testing and troubleshooting issues, so you don't have to.

See author's posts