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.
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.
- Disable UAC completely
- Rightclick and runas administrator
- Enable the run this program as administrator on the SSMS shortcut
- launch msconfig
Alternatively, you can run the SSMS shortcut as administrator each time you run the program: