A place for spare thoughts

09/06/2011

Troubleshooting: SQL Server error 18456 state 11

Filed under: sqlserver — Ivan Danilov @ 14:47

After my team installed SQL Server 2008 R2 on our project server for testing I experienced annoying problem: I can’t login to it until my domain account was explicitly added to SQL Server. And that’s despite the fact I was member of local Administrators group.

It was very strange, so I decided to investigate. To check that domain is not related to the issue I created local admin account and logged to system from that account. But the result was the same: error 18456. Error message is intentionally uninformative to prevent disclosing of sensitive information. So I took a look at the logs where I found additional ‘state 11’ piece of info. As google told me helpfully state 11 means ‘Valid login but server access failure’ (you can see other states here or here).

Turned out the reason was extremely simple: User Account Control. When I run SQL Server Management Studio without elevated rights UAC effectively ‘removed’ Administrators membership from my account and thus SQL Server couldn’t grant me access, because it is only this membership was my ticket to pass in.

So, the moral of this story: either always run SQL Server Management Studio with elevated rights or turn off the UAC 🙂

Advertisements

3 Comments »

  1. I have exactly same problem but SQL Server 2008 R2 Developer edition is installed on my desk top on Windows 7. I was able to connect to Analysis, Integration, and Report Services. No access to Database Engine. I googled many posts, some suggested create a SA account, some suggested run a command line script.
    Is there a simpler way to add Administrator auth back to my log in?

    Thanks

    Comment by Irvine — 20/09/2011 @ 22:15

  2. BTW, I tried starting management studio using ‘run as administrator’.

    Comment by Irvine — 20/09/2011 @ 22:17

  3. Run as administrator should be helpful. Did it solve your problem? If so – you can set the option to run SSMS as administrator always: go to shortcut file properties, ‘Shortcut’ tab, click ‘Advanced…’ and check ‘Run as administrator’.

    Comment by sparethought — 20/09/2011 @ 22:24


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: