Introduction

Installing SQL Server has become so routine it is all too often taken for granted; however, accepting the defaults during installation can lead to trouble. Understanding the installation options is necessary to ensure a secure installation. In this article, you will learn about the implications of various installation options so you can make informed decisions.

The setup.iss File

Everything you choose or specify in the SQL Server installation process is logged to the setup.iss file. The location of the file is determined later in the installation process when you specify the installation directories. As the next screen capture shows, the setup.iss file is described as an unattended installation file.

Even if you aren’t going to an unattended installation, SQL Server will create the setup.iss file (SQL Server 7.0/2000). And if you choose Mixed Mode authentication during the installation process, the sa password is written to the setup.iss file. SQL Server will write the sa password to the sqlsp.log file (SQL Server 7.0/2000) or sqlstp.log file (SQL Server 2000) if you apply a SQL Server service pack.

Instead of wasting time discussing whether the password is in clear text, is weakly encrypted, or strongly encrypted, the point to take away is that you might have files containing passwords. This problem is easy to deal with. After installing SQL Server, I change the sa password. You can also use the killpwd utility to find and remove passwords from setup and log files. (For additional information, please read Microsoft Knowledge Base article 263968.)

Setup Type Dialog Box

The Setup Type dialog box by default installs everything to the C drive and does a Typical installation. If you have more than one physical drive, you should override the default locations for Program Files and Data Files. Additionally, you should choose Custom to override the Typical installation defaults. If you have more than one physical disk, use the Browse… buttons to spread your installation over more than the default C drive to obtain better performance.

Select Components Dialog Box

Do you really need all of the features that are installed by default? For example, not all installations of SQL Server need the Upgrade Tools for upgrading from a prior version of SQL Server. If you are installing SQL Server on a laptop, you probably have limited disk space and would do well to avoid installing features that you’ll never use.

Overriding the defaults is more than a matter of just conserving disk space. For example, SQL Server’s Full-Text Search component installs the Microsoft Search service, but for maximum performance, particularly on a laptop, you don’t want unnecessary services running. So, if you aren’t going to do full text searches, uncheck Full-Text Search. If you think you might want to do full text searches in the future, go ahead and install it, then set the Microsoft Search startup type to “manual.” When you need to do a full text search, you can change the startup type to “automatic.”

Books Online, Development Tools, and Code Samples are components that are probably not necessary on a production server. Microsoft’s TechNet article, “Implementation of Server Level Security and Object Level Security” goes even further and recommends that you do not install Performance Counters on a production server. Furthermore, the National Security Agency (NSA) recommends not installing Code Samples on a production server because samples could provide an opportunity for compromising a system. (See the NSA Guide to Secure Configuration and Administration of Microsoft SQL Server 2000 for more information.) As a general rule, if you don’t need something, don’t install it.

Authentication Mode Dialog Box

It is well established that Windows Authentication mode is preferred to Mixed Mode whenever possible. But there is a difference between running SQL Server and installing SQL Server. Even if the SQL Server is configured to run using Windows Authentication, a case can be made for installing SQL Server using Mixed Mode authentication.

If you select Windows Authentication mode (which is the default) when SQL Server is installed, the sa account will have a blank password. That creates a tremendous vulnerability if the SQL Server’s authentication mode can be switched to Mixed. If an unauthorized database user can gain access to HKLM\Software\Microsoft\MSSQLServer\LoginMode in the registry, then the SQL Server can be switched from one authentication mode to the other by changing the value of the LoginMode. And if the sa account password was never set, the unauthorized database user can now connect as sa using a blank password.

This exploitation can easily be prevented by choosing Mixed Mode when installing SQL Server. After installing using Mixed Mode, change the sa password to avoid the previously discussed setup.iss problem, switch back to Windows Authentication, then secure the registry. If an attacker later succeeds in switching the SQL Server to Mixed Authentication, the sa password won’t be blank, which prevents this type of attack.

Services Accounts Dialog Box

On the positive side, by default, SQL Server 2000 setup does not use Local System as a service account. The problem with running SQL Server as Local System is that it provides more privilege than is necessary. Although Chapter 10 of the SQL Server Resource Kit has some excellent material and is worth reading, it was written a few years ago and doesn’t always reflect the most current security recommendations. Specifically, it does not advise against running as Local System. More recent documents such as the previously mentioned NSA Guide to Secure Configuration and Administration of Microsoft SQL Server 2000 and Microsoft TechNet’s “Implementation of Server Level Security and Object Level Security” advise against running SQL Server services as Local System. If the database server is compromised and it is running as Local System, then xp_cmdshell can be used to execute operating system commands as Local System with potentially disastrous consequences.

Don’t be fooled by the Services Accounts dialog box. You aren’t limited to only running the services as Local System or a Domain User; you can also run the services using an ordinary local computer account. To do this, enter the name of the local computer in the Domain textbox. You’ll want to use an account with the minimum privileges necessary, so don’t use a domain or local computer account that is in the Administrators group. If you need access to network resources, use a domain user account; otherwise use a local computer account.

It’s best to create a new user (either domain or local computer) account just for running the SQL Server services. To provide even greater security, use Group Policy or Local Security Policy to prevent the SQL Server service account from being able to logon to the computer running SQL Server. Find the policy for Deny logon locally, right-click and select Properties.

Use the Add User or Group button to add the newly created SQL Server service account to the list of accounts that cannot log on locally.

If the SQL Server service account should ever be compromised but its user can’t log on locally, then the harm is limited to the database itself instead of the entire server.

Network Libraries Dialog Box

There are known exploitable features of Named Pipes as described in Microsoft Knowledge Base article “815495.” Named Pipes can be hijacked to let an unauthorized user in or cause a SQL Server Denial of Service attack, but as article “815495” explains, there is a Cumulative Security Patch to fix these problems. Keeping in mind the principle of secure by default, if you don’t need Named Pipes, uncheck the Named Pipes checkbox during installation, because a hacker can’t exploit what’s not there in the first place.

The TCP/IP port number defaults to 1433 during a default (as opposed to named instance) SQL Server installation. It is fairly well known by now that ports 1433 and 1434 are targets of SQL Server attacks and need to be protected. But what is sometimes overlooked is that named instances of SQL Server will show a port number of 0 during the installation process.

What a port number of zero means is that SQL Server will assign a port number during the installation. After the installation is complete, you’ll have to use the SQL Server Network Utility to find out what port number was assigned.

Be sure to coordinate with your network administrators so they’ll know what additional ports your named instances are using. They’ll probably want you to talk to them before doing the installation so that they can tell you what port number to specify instead of letting SQL Server make the assignment.

Choose Licensing Mode Dialog Box

Licensing defaults to Per Seat for 0 devices. Obviously something has to be changed. If you are licensed per processor, you’ll need to choose it.

Hidden Defaults

Once you’ve installed SQL Server, there are additional steps to undertake. The model database, which is in effect a collection of database defaults, should be changed to be more secure. Remember, any databases you create will be patterned after the model database, so it’s a very good idea to make your starting model database more secure. The SANS Institute in its SQL Server Top 20 List suggests removing the Guest account from the model database. If you have a production SQL Server, drop the Northwind and pubs databases.

Finishing Up

No SQL Server installation is complete until the latest service pack and any necessary hotfixes are applied. After applying those updates, run the latest version of the Microsoft Security Baseline Analyzer, and then follow up with the latest version of SQL Server 2000 Best Practices Analyzer.

Conclusion

If installing SQL Server has become routine for you, it might be wise to take a step back and think about the security implications of what you’re doing. Don’t just stop at reconsidering the installation process; reconsider everything. Network administrators, Windows administrators, DBAs, and developers all need to get together and come up with a unified plan to implement the most secure installations possible.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, Oracle, and the .NET framework.