I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
Recently a friend by the name of Chris Bell (blog | twitter) wrote about an easy way to disrupt SQL Server. That disruption comes in the form of the SHUTDOWN TSQL command. You can read what Chris wrote from his article here.
Granted, you do need to have elevated permissions such as sysadmin, serveradmin or securityadmin. I include securityadmin even though Chris did not because a securityadmin can create an account and grant that account sysadmin permissions. And in Chris’ article he only discusses the threat to the SQL Server process. When I read the article, I wondered to myself if the threat stopped there. I also wondered to what extent could I cause disruption. Yes, the gremlin in me did start to come out.
When I say I was curious what level of disruption could be caused, I really wanted to know if I could reboot the server from within SQL Server or even if I could simply shut down the entire server. Well, you can certainly bounce the server from within a TSQL script – if you have adequate permissions (or know how to elevate your permissions).
The first step is rather simple: check to see if xp_cmdshell is enabled. If it is not, then enable it.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO
Now that xp_cmdshell is enabled, let’s bounce the server.
EXECUTE xp_cmdshell 'shutdown /r /m \\SQL14CTP2 /t 60 /c "Reconfiguring SQL Server" /f /d p:4:1' GO
Now this code will not work on your server as-is. I have coded it to be my sandbox server. That said, if the server name matches you should get a prompt informing you that SQL Server will shut down. And that shutdown will happen after 60 seconds.
If you happen to see a message reporting “Access Denied”, that is also easy to circumvent—well, at least up to SQL Server 2016. Prior to SQL Server 2016, I could utilize xp_cmdshell to also add the service account (let’s say it is NT Service\MSSQLSERVER) through the use of net localgroup /add. However, in SQL Server 2016, you will continue to get an access denied message (even if you try to use a proxy account). Here is an example of that exploit as well (prior to 2016):
xp_cmdshell 'net localgroup Administrators nt service\mssqlserver /add'
Let’s say you have done your duty and changed the service account off of the default local service to a domain account or a local account (not nt service), but you decided to add that account to the local Administrators group. You have actually opened yourself up to plenty of other problems. Even in SQL 2016, if the service account for SQL Server is in the Local Admins group, then the shutdown SQL Server example shared here will work and force a shutdown.
So, in the end, please be mindful of the service account in use. And be mindful of the level of permissions that service account has been granted at the OS level. If you are not careful, you could have some rogue developer or intruder shutting you down before you know it.