This is part of a series of tips on how bad/rogue admins can get access to the data in your SQL Servers.
Sooner or later a SQL Server is going to need maintenance. This could simply be automatic patching for security vulnerabilities. Now I realize that there are some environments out there where this doesn't happen, but those situations are the exception to the rule. I'm talking what's generally the case, and that is that SQL Servers have to have maintenance performed. After all, here are some things that come to mind:
- Monthly security patches
- Driver and firmware updates
- Installation of new software
- Updates of existing software (not related to any of the previous situations)
- Hardware issues
- Data center issues
This is a list of the most common things I've seen. In any case, they represent periods of time when you know the SQL Server may be stopped and restarted on the server, usually because the server is being stopped and restarted. The catch is, do we monitor how often during that maintenance period SQL Server goes up and down? Do we tie those ups and downs to the operating system being restarted? We know what a bad or rogue system administrator can do.
Copy the Data Files:
If you aren't using encryption for the files at rest, like TDE, then when the SQL Server service is stopped, those files are vulnerable to be copied by an administrator over the whole system. Now if you've got the data encrypted internally, you may still be okay, depending on whether or not the admin can't get access to whatever it is used to encrypt the asymmetric key, symmetric key, or certificate encrypting that data (and the chain thereof). But if there is no encryption, then once the files are at rest because SQL Server is stopped, an admin could copy them off, and then attach them on a personal SQL Server and be up and running.
Start the SQL Server service up in Single User Mode:
If you're running SQL Server 2005 or higher, an admin can start the SQL Server up in single user mode. So if you are using TDE or some other encryption which protects the data at rest, the admin can still get at it if there's no encryption at the data level or if the encyption keys are being controlled by SQL Server. Yes, this is inherently more risky than just copying the files off, because if you're checking the stops and restarts and comparing what needed to be done, you can catch an unexpected start up. For instance, you should see this nice little indicator in the SQL Server logs:
SQL Server started in single-user mode. This an informational message only. No user action is required.
Now if it's normal maintenance, you should never see this. Seeing this in one of the SQL Server logs (and you may have to check each one) is a clue someone has been in when they shouldn't have been. This isn't a hard thing to check for and probably should be added to regular monitoring, to see if someone used the "back door" to get into SQL Server when they don't normally have the rights to do so, but have admin rights over the server itself.