Recently I wrote a blog post about SQL Server security, and today I would like to write about taking over SQL Server when all administrators are locked out.
So, how easy it is to take over SQL Server if you are not an administrator?
Quite easy, actually.
All you need is to be a member of the local Administrators group on the server.
From there on, the procedure is fairly simple:
- stop the SQL Server service, make sure to disable the Agent and any other services / applications which may connect to SQL Server
- then start the SQL Server service in a single user mode, using -m or -f
- then just add yourself as an admin to the SQL Server instance
This is quite easy to do.
There is even a Microsoft script on MSDN which automates the procedure of adding yourself as an admin to SQL Server. The script is really great – it takes a few parameters, stops and starts the SQL Server service for you, sets you as an admin and then starts the service as usual.
The script to add the current user to the SQL Server ‘sysadmin’ role can be found here.
The script, however, is not so straight-forward when it comes to clustered environments. The script does not work on clustered servers, and if you want to take over one, you would have to do the work manually.
Here is some explanation:
The difference between a stand-alone SQL Server and a clustered one is that the Services which run SQL Server are managed by the cluster and not by the server itself. In other words, it is the clustering software that takes care of starting the services every time the owner of the services changes.
So, how do we take over a SQL Server cluster when the System Administrators are locked out?
Here is how:
Let’s assume that we have a clustered SQL Server with two nodes called A and B. The SQL Server is started and running on node B at the moment.
- go to Server A and open the cluadmin console
- on Server A also open the SQL Server Configuration Manager console
- set the SQL Server services, including the SQL Agent to disabled (when they are clustered they are set to start Manually)
- then from the cluadmin console fail over the SQL Server service to node A, it will fail of course
- then proceed as normal – start the SQL Server service manually on Server A in single user mode and make your account a sysadmin in SQL Server
- then set all services on Server A as they were – to start manually
- then go to the cluadmin console and fail over the SQL Server to node B, make sure it starts
- then test to fail over to node A and make sure that the SQL Server service starts
And this is it. You are a sysadmin.
Again, a few words on security: do NOT make anyone part of the local Administrators group on your servers. It is really easy to take over a SQL Server instance.