1

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

1
  • 2
    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 Dec 11, 2012 at 15:02

2 Answers 2

4

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.

1
  • Exactly right. This should be the accepted answer. Dec 14, 2018 at 17:59
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.

2
  • Log file says Error: 18456, Severity: 14, State: 11 ("Valid login but server access failure").
    – phenry
    Oct 9, 2009 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. Oct 12, 2009 at 17:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .