Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.

I tried logging in using single user mode as Remus described but I get the following error:

Login failed for user 'SERVER\Administrator'.
Reason: The account is disabled.
(Microsoft SQL Server, Error: 18470)

share|improve this question
v-consult.be/2011/05/26/… – astander May 17 at 12:48
See e.g. this question (The same steps should work for non-Express editions) – Damien_The_Unbeliever May 17 at 12:48
@astander this doesn't work since the current windows administrator can't access the SQL Server. Need to add Buil-in Administrator group back to sql admins list – Akhil K Nambiar May 17 at 12:51
3  
@aaron Thanks. Learn something new everyday. Question, I did some test and if NT AUTHORITY\SYSTEM SQL login is also disabled/deleted, is there still any way around? – Travis Gan May 17 at 15:24
show 3 more commentsadd comment (requires an account with 50 reputation)

migrated from stackoverflow.com May 17 at 13:13

3 Answers

up vote 4 down vote

Follow the steps on Connect to SQL Server When System Administrators Are Locked Out. Start server with -m, connect as local admin, enable [sa], retsart server normally. Read the link.

share|improve this answer
Chk my update part. It doesn't work – Akhil K Nambiar May 17 at 13:15
@AkhilKNambiar: It is the only solution to this problem. Remus is right (with some caveat: enable SA + reset its password). – Marian May 17 at 13:28
add comment (requires an account with 50 reputation)
up vote 3 down vote

Based on feedback in the comments on this answer, the situation is this:

  • There is an explicitly-created BUILTIN\Administrators group login in SQL Server that has been denied CONNECT to the database engine.

  • There is no other sysadmin-level login available, including the NT AUTHORITY\SYSTEM login that gets created by default.

SQL Server does not allow a user to disable the BUILTIN\Administrators login, but it can still be denied CONNECT. I consider this a bug, as it's obvious that the inability to disable the login outright was hard-coded into the engine to avoid scenarios like this.

I'm unable to get in after applying that permission.

You will have to either find a way to edit the contents of the master database to get rid of the DENY permission (totally unsupported, and at your own peril), reinstall the database engine from the installation media, or I suppose you could attempt to brute-force the sa password.

I don't believe you can simply rebuild master as that requires connecting and authenticating against the database engine, which isn't available here.

Okay, I can repro this. I was wrong in the comments. In fact, I just locked myself out of a local instance!

There is a SERVER\Administrator login in SQL Server that's explicitly disabled.

If there is a NT AUTHORITY\SYSTEM (local system) login in SQL Server, you can use one of the methods described on my blog here to get in without needing to down the service.

If there is no login for that account, here's what you need to do:

  1. Create a new local administrator user account
  2. Restart the database engine service in single-user mode using -m
  3. Impersonate the account created in step 1 to start either sqlcmd or Management Studio
  4. Fix things up as appropriate
  5. Restart the database engine service in multi-user mode (remove -m)
  6. Delete the local admin account created in step 1
share|improve this answer
Logged in as newly created admin but unable to access sql account since BuilIn Users/Administrators doesn't have access to MSSQL Server. – Akhil K Nambiar May 17 at 14:00
Lovely. Okay, I'll have to work on that. Did you try one of the methods on my blog? If someone has gone to the extent of locking out BUILTIN\Administrators (bad idea, by the way), the system account login is probably gone, too, but it's worth a shot. – Jon Seigel May 17 at 14:23
ya. can't connect login in anyway... Login failed for user 'SERVER\Administrator'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470) – Akhil K Nambiar May 17 at 14:52
This is interesting. For testing, I added a BUILTIN\Administrators login to my instance, which succeeded. I tried to disable it, but it wouldn't let me. I was able to DENY CONNECT, however, and this gives me the same error message. Now... to try and fix it. – Jon Seigel May 17 at 15:06
add comment (requires an account with 50 reputation)
up vote 1 down vote

I'm going to second the service account suggestion made by Justicator. If that doesn't work...

This is a long shot, but are you able to do something like this?

http://sqlblog.com/blogs/argenis_fernandez/archive/2012/01/12/leveraging-service-sids-to-logon-to-sql-server-2012-instances-with-sysadmin-privileges.aspx

(It would depend on NT Authority\System, which may be blocked by the BUILTIN\Administrators explicit deny.)

share|improve this answer
And when that does not work either, stop the service and start copying your .mdf and .ldf files to a new instance, while developing grudge and hatred to all DBAs in the world... :) – Justicator Jun 3 at 7:06
add comment (requires an account with 50 reputation)

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.