The words that many a person dreads hearing – Security Audit. Whether you’re the one having to perform the audit or just having to deal with the aftermath of one, it is never fun. Over the next few blog posts, we’re going to look at a few common points that are included in security audits, what they mean, and why they’re important to review. Now, some things that are included in your security audit many not be listed or explained here. Does that mean they shouldn’t be? Definitely not. I am just listing some of the things I’ve done for a security audit at past clients and what they showed. Also, if you have any questions or comments, please do not hesitate to comment below.
The first thing that I like to look at is the server itself. I first like to look at the Windows Services that start the actual SQL Server services to see if anything is being turned on manually by someone or is just flat turned off. To do this, I run the following command:
CONCAT('Startup Type set to ', startup_type_desc, ' for Service ', servicename)
WHERE startup_type_desc ‘Automatic’
For my personal machine, it shows the following:
Startup Type set to Disabled for Service SQL Server Agent (MSSQLSERVER)
Startup Type set to Manual for Service SQL Server (MSSQLSERVER)
Now, the first one is okay for my personal machine. But, if it was a Production machine, I would need to have some strong justification for it being disabled. For those that are not familiar with the SQL Server Agent, one of the many uses for it is to run a set of scheduled jobs to manipulate data in some way (We won’t go into detail here, but suffice it to say it’s very important for it to be active if you actually want to do anything with your data.)
The second one is also okay on my personal machine. But, like before, if this was a Production machine, I would definitely need some strong justification for this to be set to manual. This means that for your SQL Server to actually be running, some poor soul will have to manually turn on the service after the Operating System is running full swing, rather than just letting it come up at the same time as the Operating System.
Well, we have now come to the end of the first of many parts of things reviewed during the security audit. Hopefully, next time, I can get through more than one section. But if not, be assured that we’ll have many more parts to come.