Recover SA Password - PSTools - Not working

Question

I'm on a new company, and the first thing I saw is that they don't even know how many instances they have. they don't have passwords and the old DBA had no Idea what he was doing.

long story short, I could manage to create my own sysadmin account in almost every server ( I will move all databases to only 1 or 2 instances ) but there are some that I just can't.

In this Link by mssqltips there is a tool called PsExec. Following the tutorial I could run the program and connect to SQL Server with NT AUTHORITY\SYSTEM. But it's not working. NT AUTHORITY\SYSTEM is Public and I can't add my account with sysadmin. Does this program runs only with SQL 2008?

There's no way to stop servers because our databases aew running for all over Brazil.

Countless posts here, but almost everyone needs to restart SQL Server.

How do you reset the sa password?

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist ( This one says about the program, but as I said, it's not working for me (he is using 2008 )).

In this video he's using the -M method. Does someone already tested it on SQL Server 2014? I would like to know and, I will try.

I can connect with the NT AUTHORITY\SYSTEM user but when I try to create a sysadmin login or change sa password, it says I have no rights to do that. And then, looking at the nt rights, it's public. I could change the sa login with the -m trick on my machine, but I would like something that does not require to shutdown the SQL.

Answer 1

Here is a technet article that has the detailed steps.

In a nutshell you need to start SQL server in single user mode and connect using sqlcmd to add your account. This should get you what you need as long as you have admin rights at the OS level.

One note is that if the sqlagent is configured to start up automatically you should disable that before you start SQL server in single user mode so the connection isn't taken by the sqlagent.

Answer 2

As far as I am aware, there is not way to do this without some downtime for the SQL Server. If there are no known sysadmin users on the server, you will have to take it down for at least a few minutes.

(I believe that the psexec method mentioned in the question body worked on older versions of SQL Server, but as the OP discovered, at least as of SQL Server 2014, even NT AUTHORITY\SYSTEM is not a sysadmin.)

If the -m method you mention in the body - which does require restarting SQL server - will work for you, the technet article linked by @Aaron may be exactly what you need. However, you will run into trouble if Mixed Mode Authentication is not enabled - if it isn't, SQL Sever will refuse to authenticate the SQL login you just created.

The following method will grant a Windows user sysadmin privileges on the SQL Server, which will allow you to connect with Mixed Mode Authentication turned off. It worked for me on SQL Server 2014 just now. Like the method mentioned on technet, it does require local administrator access to the Windows server.

1. Launch SQL Server Configuration Manager

   You can launch the MSC file directly if, as in my case, searching the Start Menu wasn't finding it:

   • SQL Server 2016: C:\Windows\SysWOW64\SQLServerManager13.msc
   • SQL Server 2014: C:\Windows\SysWOW64\SQLServerManager12.msc
   • SQL Server 2012: C:\Windows\SysWOW64\SQLServerManager11.msc
   • SQL Server 2008: C:\Windows\SysWOW64\SQLServerManager10.msc

2. Click on "SQL Server Services", and then stop the "SQL Server (MSSQLSERVER)" service.

   Note that "SQL Server (MSSQLSERVER)" is the name of the default instance. If you are attempting to recover a named instance, that instance name will be in the parentheses.

   Note also that you will likely need to stop the reporting and integration services (for each instance) as well, because we are about to put SQL Server into single user mode, and if those services successfully reconnect to the server before you do, you'll get an error message like Login failed for user ‘[user]’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461).

3. Place the SQL Server into single-user mode

   1. Right-click on the "SQL Server (MSSQLSERVER)" service and select "Properties"
   2. Select the "Startup Parameters" tab and add a -m flag, which instructs the server to start up in single-user mode
   3. Click OK

4. Start the SQL Server (but not the reporting and integration services)

5. Run sqlcmd, and then type the following commands to add a Windows account as a sysadmin on the SQL Server instance:

   USE master
   GO
   CREATE LOGIN [domainname\username] FROM WINDOWS
   GO
   sp_addsrvrolemember [domainname\username],'sysadmin'
   GO
   

   Note that if you wish to add a local user as a sysadmin, rather than a domain user, use the computer name instead of domainname above.

6. Exit from sqlcmd, stop the SQL Server again, remove the -m flag from the startup parameters, and start SQL Server

7. Connect using SSMS