This blog post explains how to get into SQL Server if you have lost your sa password or you have no way of getting into your SQL Server instance.

This blog post is dedicated to the people who came along to my training course in Wellington 22nd February 2019…..

Issue:

During the course on “DevOps for the Database” – my 2nd instance on the training VMs was not setup for the training user. I did not have the sa password on me at the time and so I had to break into the instance…..

Resolution:

There are two methods you can do this:

• the GUI
• command line

Using the GUI:

First thing – stop the SQL Server Agent using configuration manager:

Now we want to stop the SQL Server instance and add the -m flag to it’s startup parameters:

We now start the service up and SQL Server will be in single user mode. You can now choose which methods to connect to the SQL Server instance.

Using the CommandLine to bring up SQL Server in Single User Mode:

These days it is way more preferable to write scripts for everything – and it’s a bit quicker to run the following commands.

For the example below the instance name is PROD.

First – if you’re not sure where SQL Server is installed you can look at the properties of the instance via the services applet and see where the path to the executable is.

For the example above the executable that we want to put into an ADMIN command prompt is:

"C:\Program Files\Microsoft SQL Server\MSSQL13.PROD\MSSQL\Binn\sqlservr.exe" -sPROD

You can now stop the services:

You can now paste in the executable path from the services applet and add a /m to the end

Logging in:

Regardless of which method you used to start SQL Server up in Single User mode you can now bring up another command prompt in Administrator mode and use SQLCMD to connect to the single user instance:

You have two methods of creating users – SQL Authentication or Windows Authentication.

Creating a SQL Authenticated User:

1.Connect to the master database in SQLCMD window:

USE MASTER
GO

2. Create a login called temp login (or whatever you want to call it)

CREATE LOGIN TempLogin WITH PASSWORD = '***********'
GO

(replace ******** with your password)

3. Add your user to SYSAdmin role:

ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin
GO

You can now connect to your instance with SQL Authentication and your username/password from above and add your required user that could not login to your SQL Server instance.

Or change the sa password if required…

Creating a Windows Authenticated User:

1.Connect to the master database in SQLCMD window:

USE MASTER
GO

2. Create a login for your windows domain (i.e. my domain is called MyDOMAIN)

CREATE LOGIN [MyDOMAIN\Hamish] FROM WINDOWS; 
GO

3. Add your user to SYSAdmin role:

ALTER SERVER ROLE sysadmin ADD MEMBER [MyDOMAIN\Hamish];
GO

You can now connect to your instance with Windows Authentication and  and add your required user that could not login to your SQL Server instance.

Or change the sa password if required…

When you are happy that you can connect – remove your temporary login and if you used the GUI – stop the SQL Server instance and remove the -m flag from startup parameters and start up SQL Server.

This is a very quick way to essentially break into your SQL Server instance and create an admin account – obviously quite dangerous if you have RDP access to the server… so be careful who can access the server that hosts your SQL Server instances!!

Yip.