Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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".

share|improve this question
1  
I'm doing a little tour of questions that relate to WID on Windows 2008 to note that Windows 2012 changes the pipe to \\.\pipe\MICROSOFT##WID\tsql\query –  Richard Gadsden Dec 11 '12 at 15:02

1 Answer 1

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.

share|improve this answer
    
Log file says Error: 18456, Severity: 14, State: 11 ("Valid login but server access failure"). –  phenry Oct 9 '09 at 21:21
    
I did some searching, and it sounds like this may be saying that your login account exists but the database user is invalid/nonexistent for your default database. The info I could find is pretty sketchy, though. –  Ed Leighton-Dick Oct 12 '09 at 17:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.