The intellectual property(IP) or other valuable information may be stored on SQL Server in your ISV product. Its value is more than credit card records (credit cards can be cancelled if lost, IP can’t). It may be the equivalent of a state secret for your firm. Microsoft provides two versions of its free Best Practices Analyzer Tools to check your SQL Server. One version is for SQL Server 2005 and the other version is for SQL Server 2008.
Let us face the reality that you support a dozen different products and really do not have time to become a SQL Server expert – the Best Practices Analyzer saves you from having to learn a long list of items to review and how to check them. It provides information on a truly need-to-know basis so you can do your job well without needing to consume a lot of time.
BPA does a whole system analysis of your SQL Server installation—including Microsoft Windows and SQL Server configuration and security settings—according to industry standards and best practices. BPA is very simple to use. It should be executed after the database is first created and then on a quarterly basis. Make sure you run BPA from your desktop machine and not on the SQL Server machine. You can use your desktop installation to do analyses of multiple SQL Servers.
The BPA report may show an exception similar to that shown above; some exceptions are by design for the product. I will be following this post with additional short posts for each product, listing the expected exceptions. If I have not yet posted for your product, please email me with your results and I will provide personal feedback.
Either correct all other violations or document the reason for the violation. Running BPA at least every quarter is recommended to detect any changes that may have occurred.
The SQL Server 2008 R2 BPA was released in summer 2010. SQL Server 2005 BPA functionality evolved into the Policy Management Framework in SQL Server 2008 SSMS to allow custom rules. SQL Server 2008 R2 BPA actually supports both SQL Server 2008 and SQL Server 2008 R2. It uses Microsoft Baseline Configuration Analyzer (MBCA) 2.0 and Powershell 2.0, which means that it is not a single download and install.
To illustrate this process and show some things that may complicate the process
Well, not entirely correct – the last step produced an error. Checking the application log I saw Microsoft SQL Server 2008 R2 BPA -- Error 1722. Fortunately, the BPA team created an auxiliary whitepaper cited above that identified the problem. Although I had installed Powershell 2.0, it was not configured. I executed the recommended command (verifying that it was being executed with Administrator privileges).
I tried installing once more. This time, I noticed that the second dialog told me about the above – but being human, I had ignored it as yet another EULA.
I encountered one more issue connected to my running SQL Server on an isolated network without a Domain Server (my recommended approach to reduce surface exposure of your SQL Server). This issue and its solution is also documented in the BPA whitepaper. I had to add a parameter to the command line.
This resulted in a successful install. If you encounter additional issues, check the whitepaper – they did a good job covering all of the likely complications.
1. After downloading and installing the MBCA, you will find it listed under Programs. Click the Microsoft Baseline Configuration Analyzer 2.0 link to launch it.
You should export the report and include it in your reports to management as an attachment to illustrate that you are doing due-diligence.
The BPA report may show an exception similar to that shown above; some exceptions are by design for the product. I will be following this post with additional short posts for each product listing the expected exceptions. If I have not yet posted for your product, please email me with your results and I will provide personal feedback.
Either correct all other violations or document the reason for the violation. Running BPA at least every quarter is recommended to detect any changes that may have occurred. I recommend running SQL Server 2008R2 BPA on at least a monthly schedule.
Installing BPA can be a bit of an exercise but the benefits are huge. There are time savings from not having to do the checks manually and improved quality assurance by the thoroughness of coverage.
A simple illustration of an item affecting performance was found above, and is shown below:
In my case, I immediately knew how this happened. The machine originally had an early Windows OS on it and I did not redo the drives when I upgraded to Windows Server 2008. An easy human error to do, and one that BPA catches .
Running it on a weekly basis often will reduce your exposure risk. Often I have seen a critical problem fixed by doing ‘random’ changes that are not undone later. Your system may have been secure on the 1st of the month, but a support person may have accidentally made it vulnerable on the 7th. BPA will catch many of these human-ware errors.