SQLServerCentral Article

Review: Microsoft Baseline Security Analyzer


Review: Microsoft Baseline Security Analyzer


I recently received a Security Bulletin in my e-mail from Microsoft. This is a great service and if you have not subscribed, you should. If you go to the Microsoft Security Notification Service, you can enter your e-mail to receive these updates. They will definitely help you to be aware of new patches that you might want to test.

But I digress. I received a notification and was wondering if I needed to apply this patch. At my current job I am fairly new and still getting a handle on the environment. However, even if I was more familiar, I have direct responsibility to administer 16 production servers, as well as a few development servers and 4 or 5 more that I have to patch. These are various versions, a few v6.5, some 7.0 and mostly 2000. However there is not a good list that shows each version, SP level, etc., at least not one that is up to date and that I can trust.

We have a pretty extensive SMS group here, but they don't have the ability to tell which hot fixes are on my SQL Servers. They get lots of information and tracing all this down isn't the easiest thing to do. My first step was to look at scripting some checks for versions, service packs, hot fixes, etc. And I still might do this, but I decided to check and see if there is a tool out there.

The Baseline Security Analyzer

What I found after a couple quick searches was the The Microsoft Baseline Security Analyzer. From this link, you can download the latest version as well as learn how to operate it. A technical white paper is also available.

This tool does exactly what I was looking for (or so I thought). Get the hot fixes and patches that have been or not been applied. Since I makes an XML query to a Microsoft database, you must be online to run it, but it should (theoretically) be up to date.

Running the product

It installs fairly easily and runs as a GUI with lots of web looking screens inside. It basically allows you to scan a computer, a domain, or a range of IPs. It drops all the reports it generates in your %user profile%\SecurityScans folder. For most of you on 2000 or XP, this will be in Documents and Settings\UserName. In keeping with the latest MS fads, these are XML files that contain the information generated by the scan. Note that you must have an XML parser (with I.E. 5.01 or greater), be running 2000 or XP, and have administrative privileges on the computers being scanned. The scanned computers must also be running the Server service.

When you first select this product, you select whether you want to scan a computer or multiple computers. I was hoping I could specify a list of servers to scan, but that's not an option. So for me, I had to run each scan individually for the SQL Servers.

The tool generates a file for each computer scanned and you can change the naming pattern to suit yourself. A fairly flexible arrangement, though it would be nice to be able to specify the locations for the reports.

You can, however, determine which scans should be run. The options are:

  • Check for Windows Vulnerabilities
  • Check for weak passwords
  • Check for IIS Vulnerabilities
  • Check for SQL Server Vulnerabilities
  • Check for hot fixes

You can select any combination of these to run. Each process is described at the Scan Options page. It's a pretty minimal description and you really need to run the tool to get more information about the scans.

So I scanned my machine. It took 5 seconds to complete the scan on my machine and then I received a report that showed the computer name, IP address, date, version of the hot fix database and an assessment. I was a High risk :(. A shot of the report is shown below, with some information in the XML file changed.

The report then breaks into different areas, corresponding to the different scans requested. I was mainly interested in the SQL Server section, so I'll concentrate on the items checked. I've included a table of the items scanned based on scans of 5 SQL Servers.

SQL Account Password TestThis checks for the following:

  • Password is blank
  • Password is the same as the user account name
  • Password is the same as the machine name
  • Password uses the word "password"
  • Password uses the word "sa"
  • Password uses the word "admin" or "administrator"
SQL Server Hot fixesThis checks from the MS database for the service packs and hot fixes that have been released for the version of SQL. NOTE: Only v7.0 and v2000 are checked. Not so great since I have a few v6.5 servers.
Exposed sa PasswordThis is a check based on the MS00-035 Security Bulletin. The sa password may be stored in some log or setup files on your server (v7.0) if you use mixed security. All our servers are supposed to use Windows Auth only, but it's something I need to check out.
Restricting the CmdExec RightsThis check looks to see that the CmdExec right is restricted to sysadmins only. This is a SQLAgent check and something that you should have locked down.
Sysadmin RoleThis check is to see how many people are given the sysadmin role. A failure occurs if more than 2 people show up. Not sure why 2 is the number chosen, but it is.
Folder PermissionsA check on certain SQL Server folders on the server. This looks at the ACLs on these servers to verify only the SQL Service accounts and local admins can access:

  • Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn
  • Program Files\Microsoft SQL Server\MSSQL$InstanceName\Data
  • Program Files\Microsoft SQL Server\MSSQL\Binn
  • Program Files\Microsoft SQL Server\MSSQL\Data
BUILTIN\Administrators are SysAdminI personally think this is a good idea, but it raises a yellow flag in this tool if the local admins are sysadmins in SQL Server.
Service AccountsA check on the SQL Server service accounts (MSSQLServer and SQLAgent) to see if they are running as Local System or Administrators. The recommendation is a domain user, not as any administrator.
Security ModeA check on the mode (mixed or Windows) for SQL Server authentication. Mixed mode throws a yellow flag.
Domain Controller TestThis is a check to see if SQL is running on a domain controller. This is a no-no for security reasons, but it easily allows me to check to see if this is occurring.
Registry PermissionsThis check the registry keys under HKLM\Microsoft\ for SQL Server, which are MSSQLServer and Microsoft SQL Server. It ensures that everyone can only READ on these keys.
Guest AccountChecks to see if the guest account is enabled for any database (it shouldn't be).

Each item also receives a score based on what the tool reports. This is an easy to see graphic of one of the following:

  • Red Exclamation - Alert for some type of failure.
  • Red X - This particular vulnerability exists.
  • Yellow X - This particular vulnerability cannot be confirmed or denied or this is a lower risk item.
  • Green check - No vulnerability

When I scanned my v6.5 server, I received a SQL Server is not installed message. I don't have any other versions, so this is the extent of my testing. I doubt there's much 4.2 out there or 6.0, so I'm not sure how relevant this is.


This is a good start for a tool. I like being able to check on the SQL Servers and get a Service pack and hot fix report. The other items are like, but I don't like some items, like not running SQL as a local admin. It's caused me issues and allows me to have the server perform local work. Not sure I think Mixed mode is a yellow flag if everything else is patched.

I also like this tools ability to run as a command line, which makes the fact I cannot specify certain servers easier to deal with. I can easily write a .CMD file to scan all my SQL Servers at once. I recommend this tool as a good starting point, though it doesn't easily give me a SQL Server report for my servers. It's likely I'll build something onto the results from this file to let me know the SQL Version and Service pack along with the hot fix information in this report.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also

rate this article.

Steve Jones

©dkRanch.net July 2002

Return to Steve Jones Home



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating