Re enable Windows Authetication in SQL Server

Question

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)

Answer 1

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

Answer 2

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.

Answer 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

Answer 4

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?

• Leveraging Service SIDs to Logon to SQL Server 2012+ Instances with Sysadmin Privileges

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