Securing SQL Server for Web Applications

If your SQL Server is exposed to the Internet, hackers are probing it. Probably right now. This article shows how to secure a SQL Server database that’s being used with a Web application. By definition these servers are exposed.

Not sure that there is a problem? We’ll start with the two elements that create the problem: vulnerabilities in Windows, IIS, and SQL Server and the attacks that attempt to exploit them. As Microsoft has increased its focus on security, the number of hot fixes for SQL Server has been on the rise. However, the most exploited vulnerability is still the good old blank password for the SA account.

There Are Vulnerabilities in SQL Server

Like all software SQL Server has security vulnerabilities. Documentation for the vulnerabilities that Microsoft is willing to talk about is located at: www.microsoft.com/technet/security/current.asp?productid=30&servicepackid=0. You’ll find half a dozen or more SQL Server specific security bulletins and information about patching them. It seems a new vulnerability is found almost every week. Start by making sure your SQL Server has all the latest and greatest hot fixes. Later on we’ll talk about the tools that go out to the net to check for updates.

The most widespread attack isn’t covered by a security bulletin. It’s a straightforward login attempt made on the SA account with a blank password. Since some administrators never bother to change the default password, there are ample victims to be infected. Microsoft doesn’t even consider this vulnerability and won’t be issuing a patch. After all, the blank password is “by design”. See Microsoft Knowledge Base article Q313418 at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q313418

A common cause of the blank password is products. For example some versions Visio install MSDE and never change the SA password. The user may not even know that they have MSDE running. To check your network you can download a program that scans for SQL Servers with SA accounts that have blank passwords on your network. It’s from eEye, a security company that spends a lot of time looking for wholes in Microsoft products. Download it at: http://www.eeye.com/html/Research/Tools/sqlworm.html.

Figure 1 shows the tool after it has scanned only one address, 127.0.0.1, the local system. You can use it to scan entire ranges of addresses to find vulnerable SQL Servers you may not know about.

Figure 1

Attack of the Clones!

If you know the enemy and know yourself, you need not fear the result of a hundred battles. If you know yourself but not the enemy, for every victory gained you will also suffer a defeat. If you know neither the enemy nor yourself, you will succumb in every battle.
Sun-Tzu

I was shocked when I learned of the extent of the probes flying around the Net. It’s worthwhile to understand what’s going on out there. Sites such as Internet Storm Center (http://isc.incidents.org) track the frequency of worms, Denial-Of-Service attacks (DOS) and other hacks. Right now they’re reporting on high incidence of port 1433 scans looking for SQL Servers with blank passwords for the SA account. We’re talking about thousands of computers making 750,000 reported probes a day. Most probes go unreported, so there are really millions of actual probes.

Another places to look for information on Web attacks is www.securityfocus.com (see the article http://online.securityfocus.com/archive/75/272790). For information about operating system, IIS and SQL Server bugs that may or may not affect security, I rely on www.ntbugtraq.com. For virus information I often look to http://securityresponse.symantec.com/. Your virus detection vendor probably has their own site.

Many of the above sites have e-mail notification services. Relying on them is easier than checking the sites daily. Be sure you’re getting Microsoft’s security bulletin notifications, which you’ll find at: http://www.microsoft.com/technet/security/notify.asp.

Goals

Now that we’ve established that there is a problem, let’s step back a minute. What are we really trying to do when we say, “Secure our SQL Server?” My list is:

• Protect the server from attack.
• Detect attacks that cannot be prevented so that the holes can be closed later.
• Fulfill any due diligence responsibility that I have as an administrator and that my clients may have to their stockholders, clients and business partners. In other words, CYA.

Protecting the SQL Server from attack will include:

• Setting Windows and up the network
• Configuring accounts used by Windows Services such as IIS and SQL Server
• Configuring SQL Server

Detecting attacks is primarily a matter of logging and checking the logs. We’ll go over this in detail soon.

Do You Need a Written Security Policy?

Some sources suggest that you should document very specific goals for security. Specifically:

• What resources are you protecting? For example: ABC and XYZ SQL Servers, DEF Web server.
• Why are they being protected? For example: “It contains private information about individuals.”

I like written policies, but create them with care. Although I’m not a lawyer and don’t give legal advice, I’m pretty sure that once you adopt a written policy, you’d better follow it. If you don’t, someone, someday may decide that you hadn’t exercised your “due diligence” in protecting the resources that were under your care, and your written policy is evidence against you.

Securing Windows

Before you can have a secure SQL Server, you must have a secure Windows server and network. I’ve covered these in previous articles that you might want to take a look at. They are: Protecting Your IIS Server and Web Application and Complying with IT’s Security Requirements for Web Applications.

Setting Up the Network

There are two topologies that you might consider when setting up your network. Figure 2 shows the first where IIS and SQL Server are on one server. It’s the simpler configuration and can be employed inside a DMZ.

Figure 2

Splitting IIS and SQL Server onto two servers has its advantages. For starters, SQL Server can be inside your internal network behind a second firewall. Being on the inner network can be important when the SQL Server is used by more than just the Web application. Figure 3 shows the split network setup. Two Firewalls are employed creating what is commonly referred to as a DeMilitarized Zone or DMZ. Web traffic, HTTP on Port 80 or 1443, is allowed only as far as the Web server. The Web server communicates with the SQL Server over TCP on Port 1433.

Figure 3

To protect the communications between the IIS server and the SQL Server, you can use a protocol other than TCP/IP or a port other than 1433 and turn on protocol encryption. SQL Server 2000 supports encryption using all protocols. Using something other than the default settings will prevent anyone that is sniffing on network traffic from understanding what is transmitted. Of course, as with other security measures, there is a price to pay in performance. Encryption and decryption slow network traffic.

Running components in COM+ complicates the picture even further. They can be run on one of the existing servers or on a third server. You’ll place the COM+ server inside the inner firewall of your DMZ and set up communications between it and the machine with IIS. I’ve always placed my components on the machine with IIS, which limits the complexity.

Windows Services Accounts

Even though there is no login screen, every program that runs as a service must log into Windows as a particular user with a matching password. This applies to IIS, COM+ components, SQL Server and SQL Server Agent. Choosing which account each process runs as is important to securing your system, managing performance, and avoiding driving yourself crazy when programs stop working. There are a lot of possible combinations, so we’ll touch on the most important choices here.

For starters, each account involved must be either a domain account or a local account. Checking with a Domain Controller authenticates domain accounts. Local accounts are checked in the SAM database on the local machine. In some ways using a domain account is more secure. Be aware that each time an account is authenticated a network round trip is made to the domain administrator and back.

Privileges requested from another computer require the use of either a domain account or local accounts with identical user id and password. For example, if a process such as IIS or COM+ wants to communicate with SQL Server using the Named Pipes protocol, a Domain account must be used. That’s because Named Pipes is implemented by connecting via the IPC$ network share, a protected resource.

For IIS or a COM+ component to connect to SQL Server, it must either supply a SQL Server login ID and password, or use Integrated Security. SQL Server logins are created in SQL Server and are administered either with script or Enterprise manager. Integrated Security, sometimes referred to as Windows Authentication, means: logging in using a Windows Domain account. So using Integrated Security implies that IIS or the COM+ component must be running as a Windows Domain account.

In Intranet scenarios, users have their own Windows accounts and these accounts can be relied on by selecting “NT Challenge Response” authentication in IIS. When this is used, IIS impersonates the user’s Windows account when accessing SQL Server. The users’ Windows account or group must be given login rights in SQL Server. Being able to manage logins on a group basis greatly simplifies the process of maintaining security because as new users are given Windows logins, they automatically gain the SQL Server login rights corresponding to the groups they belong to.

Overall, in an Intranet, the secure choice is to use Integrated Security and Integrated Security only. In SQL Server 2000 this is referred to as “Windows Only” security mode. The security mode is set up only with Enterprise Manager or SQL-DMO. There is no T-SQL Script equivalent to change security mode. The Authentication section near the top of Figure 4 shows the choice of “Windows only” security.

Figure 4

In scenarios where large numbers of users are accessing your Web site from the Internet, assigning Windows accounts to them isn’t very practical, and most sites use anonymous access. This lets a Web user connect without a login. So whose login is used by IIS? IIS is a process that runs under a Windows Service account. The default name for the account is IUSR_<machine-name>, where <machine-name> is replaced by the Windows computer name. The default account can be set for IIS as a whole, but it also can be different for each virtual directory.

IUSR_<machine-name> is a local account. If your IIS is running on the same computer as SQL Server, you can create a SQL Server Login for it, grant access to the databases that the Web application uses, and you’re in business.

If IIS is running on a different computer from SQL Server, setup is more complex. The first solution is to use a domain account. Make a separate account just for this purpose and be sure it is not a domain administrator. Let the IIS virtual directory login as the new account. The alternative solution is to have matching local accounts on both the IIS machine and the SQL Server. The accounts must have both the same user ID and password. If you’re using a domain account to run IIS, it’s no longer necessary to put a userid and password on your ADO connection string. Instead, specify “Trusted_Connection=Yes”. This forces the use of Windows authentication.

There are many other ways to configure the Windows Service accounts when working with the combination of IIS, COM+ and SQL Server. Far too many to cover here. To help make some sense of the options, Microsoft makes available a tool called the IIS Permissions What If Tool. It’s a small application built into a Web page. You can download it by starting with Microsoft Knowledge Base article Q229694. More information about setting up connections between IIS and SQL Server is available in Knowledge Base articles Q176379, Q174811.

So far in this section we’ve covered the Windows Service Account used by IIS. But what about SQL Server? It turns out to be less important. Whether SQL Server runs as the local system account or as a domain account, the choice doesn’t effect how IIS connects. The preferred solution is to use a domain account. Be sure it’s not a domain administrator and grant the account access only to the resources that it needs such as the directories that store the SQL Server programs and data files. Using a domain account has the advantage that SQL Server will be able to access network resources, if you give it the proper permissions. This can be useful for tasks such as backing up over the network and sending SQL mail.

Keeping Up with Hot Fixes and Protecting the Server

Earlier we discussed the availability of HotFixes and the importance with keeping up with them. Microsoft and its partner Shavlik Technologies have been working on making this job easier. The Microsoft Network HotFix checker has been doing this job for over a year. Now Microsoft has distributed a new tool based on the same technology, The Microsoft Baseline Security Analyzer (MBSA) which you can download at: http://microsoft.com/technet/treeview/default.asp?url=/technet/security/tools/Tools/MBSAhome.asp. MBSA covers Windows, IIS and SQL Server. For SQL Server it makes a series of checks shown in this list:

The most important check is to look for missing SQL Server hotfixes. Adding new checks to the list above will require that a new version of MSBA be issued, but the search for new hotfixes is done by downloading a XML file of the available fixes so it is right up to the minute.

Figure 5

The result of running MSBA against a system that hadn’t been protected are shown in Figure 6. Each check gets scored as Critical, Non-critical or Passed. For each test, Links are available to an explanation of the test, to the details of the analysis, and to the procedure for fixing the problem. Clicking on the Result Details link for the “Guest Account” check brings you to the next screen seen in Figure 6.

Figure 6

One word on the “SQL Account Password Test”. It’s a check for SA passwords in log files created during SQL Server setup. Security bulletin MS00-035 covers this vulnerability and can be found at: http://www.microsoft.com/technet/security/bulletin/ms00-035.asp. It points to the program killpwd, which you should download and run.

MBSA runs only on Windows 2000 and Windows XP but can check SQL Servers running on Windows NT as well. If you’re running Windows NT, you’ll have to stick with Hfnetchk.

Logging So You Won’t Get Burned Again

Maintaining logs won’t prevent attacks. However, they’re essential to detecting attacks, recovering, and cutting them off.

There are a series of logs that can be employed in defense of a SQL Server that supports a Web application. They are:

• Web Log in IIS. IIS’s log records every HTTP request received. It’ll show the IP address that originated the request, the type of request, the contents of the request and any cookies. Set this using Internet Information Services Manager. Select the Web site and then properties. Log settings start on the “Web Site” tab. I always set it to roll the log file over daily.
  

  Notice a couple of things:

  • The “New Log Time Period” setting instructs IIS when to stop writing to the old file and start a new log. By using the daily setting the date of the request can be eliminated from the log and the file size kept reasonable.
  • By choosing “Use local time for file naming and rollover” IIS won’t use Greenwich Mean Time, which is its standard.
  • The location of the log file is moved out of the Windows system tree to a larger disk. These files tend to add up over time and you don’t want to fill your system disk.

• URLScan Log. You are using URLScan, aren’t you? It’s an ISAPI filter for IIS. Microsoft gives it away for free, and it’s now a standard part of LockDownIIS. URLScan blocks a series of malformed or dangerous Web requests. These are usually attempts to run CMD.EXE or DEFAULT.IDA by a Nimbda infected system. There are still plenty of infected systems out here. The log shows what it blocked. Here’s a sample URLSCAN log:

  [Tue, Oct 09 2001 – 12:05:32] Client at XXX.XXX.XXX.XXX: Sent verb ‘OPTIONS’, which is not specifically allowed. Request will be rejected.
  [Wed, Oct 10 2001 – 11:08:53] Client at XXX.XXX.XXX.XXX: URL contains extension ‘.exe’, which is disallowed. Request will be rejected. Raw URL=’/download/win32/en/ie5setup.exe’
  [Thu, Oct 11 2001 – 09:53:58] Client at XXX.XXX.XXX.XXX: URL contains extension ‘.exe’, which is disallowed. Request will be rejected. Raw URL=’/scripts/root.exe’
  [Thu, Oct 11 2001 – 09:53:58] Client at XXX.XXX.XXX.XXX: URL contains extension ‘.exe’, which is disallowed. Request will be rejected. Raw URL=’/MSADC/root.exe’
  

• Windows Security Log. This is a key log for debugging the problems that occur when configuring security so be sure to know how it works. It’s the Security log visible in the Event Viewer. Until you change the security policy on the machine, the Security Event Log will be empty. Figure 7 shows almost all options turned on. This is an extreme but very useful when configuring the system or trying to diagnose why it stopped working. By tracing where the successes and failures occur, it’s possible to learn which authentication steps succeeded and which ones failed and thus pinpoint your problem. However, this entire tracing has a cost in performance. Once everything is working, you may want to turn off logging of successes for Account logon, Logon, Process Tracking, and System Events. Always leave tracking on for failures.
  
  Figure 7

  By the way, if you’re system is split on to multiple machines, they each have their own Windows Security Log. Be sure you synchronize the system clocks so the times recorded for each event are synchronized, otherwise it’s very difficult to get the most out of this log

• SQL Server Log. SQL Server logs all sorts of information in a common log file. The log files can be viewed in the Management\SQL Server Logs entry of Enterprise Manager. The setting that governs which logon events are logged is selected in Enterprise Manager. Select the server name, then right click and open properties. Figure 8 shows the choice of audit level. Once again, auditing all events is the best choice while you’re setting everything up but it has a performance cost. For production, I may start out auditing All events, but once it’s working I change the setting back to audit failures only.
  
  Figure 8

  Figure 9 shows the full entry in the SQL Server log from a failed login. In this case, the SQL Server was set to “Windows Only” security and an attempt to log in with a SQL Server account was denied.

  
  Figure 9

• Application Log. This is the log that your application builds. It’s up to you and the application developers what to log and where to keep the results.

Once you’ve turned on all these log files, be sure that you archive them someplace where they can be retrieved easily. Web hackers often try and delete log files to cover their trails but a good regime of backing up the logs to a safe location will catch most hacks. Once the logs are safe, make sure they’re easy to get to. I’ve used the Windows and IIS logs to trace back what happened to one hacked system. The logs showed that the hack had started about a month before. That didn’t save the system. It was trashed, but I got a better idea about what had happened and what to do next time.

Summary

Exposure to the Internet makes planning the security of SQL Server doubly important. There are vulnerabilities in the operating system and its components. Hackers eager to exploit them. However, I don’t think that means we should throw the baby out with the bath water. There’s no such thing as “totally safe,” but with careful planning and attention to security, SQL Server can safely participate in a Web application.

References

SQL Server Security
http://www.microsoft.com/technet/prodtechnol/sql/maintain/security/default.asp

SQL Server 2000 Operations Guide, Chapter 3–Security Administration
http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops3.asp

SQL Server 2000 Security White Paper
http://www.microsoft.com/technet/prodtechnol/sql/maintain/security/sql2ksec.asp

SQL Server 2000 Resource Kit, Chapter 10–Implementing Security
http://www.microsoft.com/technet/prodtechnol/sql/reskit/sql2000/part3/c1061.asp

Microsoft SQL Server 2000 Security
http://www.microsoft.com/technet/prodtechnol/sql/deploy/confeat/05ppcsqa.asp

About the Author

Andrew Novick develops business applications as an independent consultant using ASP, VB and SQL Server. He’s a frequent contributor to the local VB Pro user group. 2002 marks his 31st year of computer programming, starting in High School with a PDP-8 and moving on a degree in Computer Science, an MBA and then programming mainframes, minicomputers and for the last 16 years PCs. When not programming he enjoys coaching Little League baseball, woodworking, mowing the lawn, and the occasional movie with his wife. He can be reached at anovick@world.std.com.

»


See All Articles by Columnist Andrew Novick