Wednesday, November 3, 2010

SQL 2008 -- Can't connect with management studio from the local machine

Brief Description:  On a SQL 2008 R2 machine running on Windows 2008 R2, when attempting to launch Management Studio logged in as a domain account with local admin privileges, SQL server refuses connection with access denied.

More Details:  
If the default admin of the SQL server is the local administrators group, members of the local admin group will be able to access with remote copies of Management Studio, but can't connect locally when directly launching Management Studio

Solution:
Right click management studio and "run as administrator".  Maybe this should be intuitive, but it wasn't for me.  Since Management Studio is an administrative tool by default, it seems slightly off to me that I'd have to tell it I want to be an administrator.

I'm still getting used to the whole concept of Microsoft deciding that "su" is the new thing.  I'm not sure it's the thing I would have taken from *nix if I was going shopping for things windows should totally have...I'd maybe have gone with a native way to mount an ISO, but that's just me.

Disclaimer:
Connecting as a user account to do any real work isn't so much a best practice since so many things end up as owned by the account that created them (mirroring endpoints being owned by user accounts was an unpleasant surprise to me), but sometimes it's practical for troubleshooting, or work that actions that should be audited by username liking shutting down a service.

No comments:

Post a Comment