Trying to recover some disk space by shrinking the size of our WSUS database, or at least figure out why it's so big, I tried to connect to the Windows Internal Database in SQL Server Management Studio (using the \.\pipe\mssql$microsoft##ssee\sql\query database name I've seen referenced in multiple places) and got an error:
Cannot connect to \.\pipe\mssql$microsoft##ssee\sql\query.
ADDITIONAL INFORMATION:
Login failed for user '[ourdomain]/[myusername]'. (Microsoft SQL Server, Error: 18456)
Server Name: \.\pipe\mssql$microsoft##ssee\sql\query
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
My account has domain administrator privileges, so I don't understand why it wouldn't work. The help link it gives me says that "If your error indicates state 1, contact your SQL Server administrator" (who, unfortunately, is me).
I also tried the WsusDBMaintenance script from here, which should apparently just work, and got the same "Login failed for user" message.
Update: I found the corresponding entries in the error log, which say:
2009-10-09 14:13:51.30 Logon Error: 18456, Severity: 14, State: 11. 2009-10-09 14:13:51.30 Logon Login failed for user '[domain]\[user]'. [CLIENT: <named pipe>]
It says here that state 11 = "Valid login but server access failure".
Since this is a Windows 2008 server, most likely, the User Access Control is causing the issue.
When you want to connect the instance, right click on SSMS and Run as Administrator. And give the above name pipe, you should be able to get in.
There are two possibilities here. One is a simple authentication issue; the other has to do with the State code in the error.
First, unlike earlier versions of SQL Server, SQL Server 2008 doesn't create the BUILTIN\Administrators group on installation that used to give all domain admins full rights to the database server. You have to provision each user that has admin rights. If you didn't do that, you'll probably need to login as a user that does have full rights - either the WSUS account (depending on the rights you gave that) or as the SQL Server service account (assuming you used a domain account).
Second, the state code reflected in SQL Server 2008's internal error logs is always 1, regardless of the reason. The corresponding Windows error log message will have a state code that more accurately reflects the reason the login failed. This post does a good job of explaining the possible states.