SQL Server Security Audit (Part 3) – Operating System Level Audit
Microsoft Baseline Security Analyzer
Microsoft Baseline Security Analyzer (MBSA) is a freely downloadable tool from the Microsoft web site. You can run it against your target SQL Server machine to get a detailed report on security vulnerabilities. MBSA can be configured to check for security updates for Windows Server 2003 or 2008. The updates can be checked either from the Microsoft web site or from a WSUS server. It can also check IIS or SQL Server for vulnerabilities. The automated audits are saved as reports that you can refer back to later. The tool also lets you check a range of machines in one single session.
Provided you have administrator privileges in the machine, it may be worthwhile to run the MBSA tool against the SQL Servers installed.
You may get a report like the following:
Probably many of your findings in the security audit so far will also be picked up the MBSA. Nevertheless it is a good idea to run the application to see if you have missed anything.
Check with your system administrators to see if the SQL Server machine has the latest anti-virus software installed and enabled and if the virus definitions are automatically updated. This may sound obvious since most organisations will have anti-virus software running on their servers, but this is for completeness’ sake.
You would not probably want to include your development and test servers in the security audit described here since there will be little value in doing so. Ideally, at the very beginning, the audit should include one or few critical SQL Servers in the enterprise. Depending on time, budget and scope, you may not want to include everything in your audit and everything discussed here may not apply to your environment either. But once you have the process ready through scripts, manual checks and documentation – it can easily be repeated.
Another point to remember is that you will probably perform the audit for management presentation. In such cases you would want to keep it as free of technicalities as possible. This often means you will have to prepare a second “bird’s eye-view report” based on your initial findings. You should be ready to back this high level audit report with all the screenshots and query outputs wherever necessary.
Also, once you have the initial audit ready – with all the Excel spreadsheets, Word documents, screenshots and reports – you need to start working with developers, system administrators, team leaders and business stakeholders to find out the reasons behind any anomalies that you identified.
So for example, if your audit shows the accounting team user Joe Blogg has sysadmin privilege in your SQL Server and after talking with the stakeholders you establish that he should not have this access, present the fact in an understandable format. Instead of showing the account MYCOMAPNYJBlogg listed under a column titled “sysadmins”, make sure your final report only highlights the fact that some of the accounting department personnel have system administrator privilege in the database server and this is against the business rules.