Breaking into SQL Server

I recently had a situation where I needed to access a SQL Server that I didn’t have permissions to. This was a bit inconvenient and always in such a situation, the SQL Server was experiencing difficulties that needed a quick resolution.

An Application had been installed by someone with Domain Administrator privileges. The Application Installed itself an instance of SQL Server Express Edition, created a ‘sa’ Account and kept the Account details to itself. This become a problem because no-one else had administrative access to the SQL Server.

In early versions of SQL Server, Domain Administrators automatically received “system administration” access to the SQL Server. This was not ideal since it meant that a wide group of users had full access to change the SQL Server settings, not to mention access potentially sensitive data held within. This loophole was fixed in SQL Server 2008 and all Accounts need to be explicitly granted access to SQL Server in order to log in.

Now, I needed to log into the SQL Server Instance and found that I had no access. I checked the Account that the Service was running under – it was NT AUTHORITY\LOCALSERVICE

First thoughts were to create a new Domain account and set SQL Server to run the Instance under this Account.

I then tried logging in using the newly created Domain account that was running the SQL Server Service, but this did not have access either.

The answer was to run SQL Server in Single-User mode. In this mode, members of the local Administrators group on that Server receive ‘sa’ permissions automatically. To do this properly, I added the following to the Startup Parameters “-m SQLCMD” to the SQL Server Instance.

After adding the Parameter, SQL Server needs to be re-started.
You will only be able to connect using SQLCMD – i.e. SSMS cannot be used until the parameter has been removed.

Next, open a CMD window and navigate to the appropriate BINN directory for the SQL Instance that you want to log into.
I used “SQLCMD -S. -E” to log onto the local SQL Instance with my Windows Account.
SQLCMD provides command line access to SQL Server. From here, I was able to create a Login for myself and grant myself the sysadmin permissions – i.e.

CREATE LOGIN [Domain\UserName] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [Domain\UserName]
EXIT

After providing access to my regular Account, I went back removed the single-user Startup parameter and re-started SQL Server. It was possible to login once more.

Update

In the event that the Server only has Windows Authentication enabled, and if you don’t want to connect using Active Directory (for example, the Server is not in an AD domain), then you need a few more steps. After putting SQL Server into “single user” mode…

Open SQLCMD and run the following:

ALTER LOGIN sa ENABLE;

GO

ALTER LOGIN sa WITH PASSWORD = ‘<new password>’;

GO

Change the Server to Mixed Authentication

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2; GO

Exit SQLCMD

Restart SQL Server

Log back in using “sa”

SQLCMD -S. -u sa

Type the password for the newly created ‘sa’ user

So long as you can log in, exit SQLCMD again

Remove the ‘-m SQLCMD’ swithch from SQL Server

Restart SQL Server

You should now be able to log in using ‘sa’ in SQL Authentication. Now, continue with remaining security setup before disabling the ‘sa’ login again (for security purposes).

My learnings from this experience were:

1. Try to keep more of an active watch on new SQL Server installs that may have been made within Application installs. This can be performed using network scanning software that sniffs out SQL Server installations – Idera do a free tool here: https://www.idera.com/productssolutions/freetools/sql-instance-check

2. SQL Instances using Express Edition need plenty of attention too

3. It’s really not so hard for someone to access your SQL Server, alter something and then remove their access again afterwards. Consider using a Security Logging tool or keeping an eye out for strange Server Stop / Starts and to record accords that have experienced