Browse DevX
Sign up for e-mail newsletters from DevX


Securing Your SQL Server : Page 3

A growing consciousness of security has brought Microsoft SQL Server database security into focus as never before. Planning for database security should begin early in the development process and there are important vulnerabilities that you need to prevent in your application's SQL Server.

Security Patches
The Microsoft SQL Server group has been busy these past months producing cumulative patches for both SQL Server 7.0 and 2000. Presumably all the patches will be rolled up in future service packs but it's reasonable to expect that there will be subsequent patches after those service packs are released. Let's drill down into what the SQL Server 2000 security patches provide, how you can install them, and how you can test your installation. (See Sidebar 2: A New Departure)

The Microsoft SQL Server group has been busy producing cumulative patches for SQL Server 7.0 and 2000.
Each security patch contains a readme.txt file that instructs you to install the patch on the correct service pack (SQL Server 2000 SP2 has a version level of 8.00.534; SP3 is due out in late 2002.) After you apply the security patch, your SQL Server's version number will be incremented. This incremented build number implies that in addition to getting the security fixes, you are also receiving an executable containing other miscellaneous patches, the ones you would normally have to contact Microsoft PSS to receive.

At the time this article was written the security patches require you to install them manually, unlike service packs. By the end of 2002, it is likely the SQL Server group will have automated the security patch system so that it's more like a service pack install. Until the security patch system is automated you'll have to spend time automating the application of the patch. You may decide that the vulnerabilities fixed in a given patch do not affect your application and choose not to install the patch.

The full instructions for applying a given patch are in the readme.txt. If you have to apply the security patches manually, create a simple command file to apply them. There's just enough file copying involved from different file folder locations to make one-at-a-time copying error-prone. Make sure you create a command file to back up the current binaries, copy the new ones in, and restore the old from the copy. One helpful suggestion is that you include the security patch number in the command filename so that you can clone them for the next patch after that. Be careful about file locations; it's common on database servers to place data and log files for production databases on their own volumes. Test each security patch the same way you would a service pack.

One step may be confusing. The command-line executable, servpriv.exe, restricts registry permissions (obliquely described in the Microsoft Knowledge Base article Q322853 "FIX: SQL Server Grants Unnecessary Permissions or an Encryption Function Contains Unchecked Buffers.") When you run this program you pass the name of the SQL Server instance as a parameter. For the default instance on your machine, it's just

  servpriv.exe MSSQLSERVER
For a named SQL Server 2000 instance you just use the name of the instance without a machine prefix. Normally on a server called MyServer, with an instance named Intance1, you specify MyServer\Instance1 whenever you connect to it or refer to it in Transact-SQL. But for this executable, you would actually type

  servpriv.exe Instance1
Since the registry keys affected by servpriv.exe are not documented, it's not clear how you would back out of its changes.

By the time you read this, Microsoft should be coordinating the Microsoft Baseline Security Analyzer (MBSA) with the security patch files and you can then use this utility to inspect a SQL Server and determine whether the latest patch has been applied. You can download it from Microsoft TechNet. It uses an XML file to periodically update the proper settings. MBSA can inspect SQL Server, Windows, and IIS security on your system, and it reports on SQL Server security issues not addressed by the patches. You'll learn more about the Microsoft Baseline Security Analyzer later in this article.

Note that there's a risk to applying the security patches. Because these fixes are coming out fairly rapidly they may not have been as well tested as a full service pack. It's possible that they might introduce some regression or new bug. Another risk is that manually applying a patch can be error prone. Your best defense is to use command files that you test first.

Thanks for your registration, follow us on our social networks to keep up-to-date