6

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)

Improve this question
5
  • 4
    mssqltips.com/sqlservertip/2682/…
    – Aaron Bertrand
    Commented May 17, 2013 at 13:54
  • @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
    Commented May 17, 2013 at 15:24
  • @Travis not with PSEXEC I don't think; you'll need to use a different workaround.
    – Aaron Bertrand
    Commented May 17, 2013 at 15:25
  • @aaron OK, at least we know the MDF/LDF files copy solution to make sure nobody get screwed.
    – Travis
    Commented May 17, 2013 at 15:29
  • @AkhilKNambiar There is one more option that I can think of. After querying sys.server_principals joined with sys.server_role_members I realized that there are 2 more sysadmins in my server: the accounts used by the services MSSQLSERVER and SQLSERVERAGENT. You can use services.msc or the configuration manager to find what user name your services are using, and then find a way to impersonate them.
    – Roi Gavish
    Commented May 19, 2013 at 16:57

4 Answers 4

Reset to default
5

Follow the steps in:

Connect to SQL Server When System Administrators Are Locked Out.

Briefly:

  1. Start server with the -m switch
  2. Connect as a local administrator
  3. Enable the sa account (and reset its password if necessary)
  4. Restart server normally
Improve this answer
0
4

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.

Improve this answer
4
  • 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
    Commented May 17, 2013 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
    Commented May 17, 2013 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
    Commented May 17, 2013 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
    Commented May 17, 2013 at 15:06
3

When all windows user accounts with sysadmin role or sa account are disabled in SQL server then how to access the instance?

Below are the steps to solve this problem:

  1. Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled

  2. Start SQL Server instance in single user mode with following steps

    2.1 Open command prompt in admin mode

    2.2 Run command ( I am running SQL Server 2012 instance) 

    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s 
SQL2012 –m

  3. Open another command prompt in admin mode

  4. Connect to the SQL Server instance using command

    C:>sqlcmd –S(local)\instancename –E

  5. Create login 

    C:> CREATE LOGIN [loginname] FROM WINDOWS;
C:>go

  6. Add login to sysadmin role

    C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]

  7. Now disconnect from SQL Server instance

  8. Stop SQL Server instance by pressing cntl+C and ‘Y’

  9. Start SQL Server instance in multi-user mode and now you will be able to connect using newly created login

Improve this answer
1

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?

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

Improve this answer
1
  • 1
    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... :)
    – Roi Gavish
    Commented Jun 3, 2013 at 7:06

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