We recently moved our SQL Server from Windows Server 2003 to Windows Server 2008. When I tried to connect through SSMS on the server I got a rather odd error:
Now, I'm a SQL Administrator and am able to connect to the server from my local PC (Windows XP). I check the SQL Event log and see the following:
. After some Googling I find an article referencing UAC. It suggests right clicking on SSMS and running as administrator. I do this, accept the annoying message, and yowza, the system works.
Now, this is all well and good, but I really don't want to have to right click and run as administrator each time. Plus, as infrequently as I actually log on to the server to use SSMS, I'm sure I'll forget and end up googling this exact same error again. So, I poked around a little bit and found out that you can force the .exe to always run as administrator
- SSMS Properties
- Compatability Tab
- Show Settings for all users
Notice how this is now for the ssms.exe file, not for the shortcut anymore
- Check "Run this program as administrator"
Now, whenever you run SSMS on the server, you will get the annoying message and it will work. I'm a little surprised that Microsoft didn't include that in the install package for SQL Server 2008, but at least there's a pretty easy fix.