Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp



Complying with IT's Security Requirements for Web Applications

The application is done. It's been tested, documented and is ready for deployment or sale. Finally, you can relax and start working on version 2. Well, not so fast ...

If the application is going to be deployed into or used by an organization that has an Information Technology (IT) department, there are more hurdles to jump. Most IT departments have security requirements for applications. Web applications are no exception. The requirements are often written as checklists. This article addresses how to respond to the request, "Does the application comply with our checklist?" It'll discuss what is in a typical security checklist and how to comply with the most common requirements.

In a typical IT organization, a person or small group of people has principal responsibility for verifying the security of all applications. This has the advantage for the IT organization of limiting the number of people making decisions about what constitutes appropriate security. The disadvantage for the developer is that these decision makers may not be familiar with the application and its requirements. The onus falls on the developer to close the gap by meeting the standards set by IT and by communicating how they met the standards.

Security specialists take their responsibilities seriously. Many take training, attend security conferences, and read articles or books on the subject. Since they're often working with similar information, they tend to produce similar checklists. I've seen several checklists, and I've used the items that appear most frequently for this article. However, every list is different. Usually they've been customized for the organization's technical environment and for special concerns raised by experience within the organization. Be prepared to create a customized response.

Most lists are considered private and/or proprietary by their creators and can't be reproduced verbatim. However, the SANS institute has a set of related policies at that you might want to examine.

Infrastructure Security Checklist Items

Many of the checklist items concern the security of the infrastructure that is used to deploy the Web application. Providing a secure infrastructure for Web applications is a large subject in its own right. This section discusses it briefly. Later we'll jump into application behavior items.

To get started refer to my previous article Protecting Your IIS Server and Web Application at Be thoroughly familiar with Microsoft's security site at

Some of the most important infrastructure security requirements are:

  • Be up to date with all operating system and database security patches.
  • Use NTFS on all partitions.
  • Install 128 bit security.
  • Turn on security event logging for all audit failures and selected success events.
  • Use Microsoft's IISLockd and URLSCAN or equivalents.
  • Install and activate time synchronization.
  • Have a Password Policy that requires strong passwords and regular password changes.
  • Disable the Guest account.
  • Disable Web Publishing.
  • Disable directory browsing.
  • Disable Parent Paths.
  • IIS should run with a specially created account. Don't use an administrator's account.
  • Have a virus checking solution with automated updates.
  • Have an intrusion detection system.
  • Limit use of administrator access.
  • Web and database servers should be physically secure.

A firewall is also a must. If the application is an intranet application, you can prohibit access from the public intranet. While some applications may be open to the public Internet, an extranet application is used only by specific business partners and customers. Consider implementing firewall rules that allow only the confirmed IP address ranges of your business partners or customers into the application. This drastically limits your vulnerability. Only attacks that successfully circumvent the firewall or which come from inside your business partners' networks put you at risk. This policy alone saved one of my servers from NIMBDA back in September 2001.

Many organizations require independent security audits. In fact, you may first hear about the checklist when the auditor comes a calling. You may want to initiate such an audit before that happens. As the Boy Scout motto says: Be Prepared.

Application Behavior Checklist Items

These are typical checklist items that should be considered while developing and administering a Web application.

The application must protect against cross-site scripting.

The essence of a cross-site scripting attack is the insertion of text containing script into input fields that are later displayed, causing the script to be executed by the browser. The inserted script might contain <OBJECT> or <SCRIPT> tags that cause dangerous code to be executed. Because the browser thinks the script comes from the server sending the original page, it is executed in an incorrect security context. A CERT advisory gives a thorough explanation of the vulnerability:

The ASP developer should review two Microsoft Knowledge Base articles:

HOWTO: Prevent Cross-Site Scripting Security Issues (Q252985) at;EN-US;q252985

HOWTO: Review ASP Code for CSSI Vulnerability (Q253119) at;EN-US;q253119

To prevent cross-site scripting be aware of at least these issues:

  • Be up to date on all Windows and IIS security updates.
  • Filter user input for special characters such as < > " ' % ( ) & + -
  • Filter output that has been based on user input for special characters. Watch out for data from:
    • Request.Form Collection
    • Request.QueryString Collection
    • Request Object
    • The database
    • Cookies
    • Session and Application variables

To enable filtering, specify character encoding on all Web pages within a META tag in the HTTP header. For example:

<META http-equiv="Content-Type" Content="text/html; charset=ISO-8859-1">

The application may not use persistent cookies.

Persistent cookies are files, sent by a Web application, that stay on a computer after the browser leaves a site. They are often used to store enough information about the user so that the Web application can bypass the login screen, or so it can be customized to the user's preferences.

Non-persistent cookies reside in memory of the client computer only as long as the browser is open. IIS relies on a non-persistent cookie to identify the ASP session. Without one, IIS is not able to maintain any session state such as session variables.

If your site uses persistent cookies, don't request that IIS store them in the IIS log. While this is a great debugging tool, I've seen sites that store the user ID and password in a cookie to eliminate the need to login to the application. The log includes the cookies and therefore the user ID and password of every user. If the log were ever compromised, all the user Ids and passwords would be revealed.

Use SSL for all sensitive pages traveling on the public Internet

SSL encrypts the contents of TCP/IP communications so that it cannot be read by anyone who has access to listen on Internet traffic. SSL or another encryption solution, such as a VPN, is essential when sending sensitive information (i.e. credit card numbers or social security numbers). While the chance of interception is low, there is a common perception that interception is possible. Your site won't have credibility with users if sensitive information isn't encrypted.

The downside to SSL is performance. The encryption and decryption overhead can be 10 to 100 times the CPU overhead of an unencrypted page. If the server has a high percentage of SSL traffic, you might want to consider an SSL hardware accelerator.

Users will be required to log in every time they use the application.

This applies to applications where a login is required. It implies that cookie-based logins are not allowed. This can be inconvenient for the user, but logging a user back in based on a cookie is risky. As we saw above, persistent cookies are also not always permitted.

A further measure to protect passwords is to disable IE's Autocomplete feature for password fields. This is done by adding the AUTOCOMPLETE="OFF" attribute to either the <FORM> or <INPUT> tags. For example:

<input type="password" name="pwd" size=16 maxlength=16 AUTOCOMPLETE="OFF">

Log out users as soon as they leave the site.

Say that a user is viewing a page on your site. They then type the address of another site in the browser's address bar. Finally, they use the back button to go back to the previous page on your site and press the submit button on a form. This requirement asks that the user login again.

Detecting this situation based strictly with browser based script and the information available to the server isn't possible. The server doesn't know where the user has been. The only way to detect this situation fully is with a proxy server type security solution such as Netegrity SiteMinder ( Proxy server solutions intercept every Web request from the browser and can filter them and track the addresses the browser has visited.

A limited form of site restriction checking can be done by looking at the Request.ServerVariables("HTTP_REFERER"). If the user tries to enter any page, except the login page, from a URL that's not in your site, the request should be rejected. This solution doesn't prevent the user from leaving your site, coming back to one of your pages, and continuing with their session.

Disconnect sessions when inactive for X minutes

Here are two strategies for timing out users. One based on the server, the other implemented with browser script. On the server, using the IIS Manager, set the ASP session limit to the number of minutes desired. By default the timeout is set to 20 minutes. In the application, store login information in a session variable and check it on every page hit. If it's not in the appropriate session variables, the session has timed out and the user should be redirected to the login page. In addition, although it's reported to be less than 100% reliable, you could code an action to take in response to a timeout in the Session_OnEnd event in global.asa.

The client side strategy uses a bit of JavaScript. Insert this script at the top of every page generated by the application:

<script Language="JavaScript">
window.setTimeout("window.navigate('Logout.asp')", 900000);

'Logout.ASP' is the page that logs the user out of the application. 9000000 is the number of milliseconds to allow the user to sit on the page. In this case, 15 minutes.

Application code will prevent simultaneous logins.

Assuming the application requires a login in the first place, this requirement asks that each user only be allowed to be logged in to one session at a time. This has long been a requirement in many mainframe and client/server applications.

This is easy in an IIS/ASP environment. Global.asa has two events Session_OnStart and Session_OnEnd that can be used to track current logins. Session_OnEnd is reported to be less than 100% reliable so you might want to implement a solution in the database that invalidates any existing sessions once a new session is started.

Application code will not contain developer comments.

Any layer of security might fail. Sometimes an attacker can gain access to the source files from a Web site. Developer comments can be an aid to the hacker, particularly if they contain such gems as userids and passwords used during development or testing. This requirement applies only to script files, such as an ASP page. It doesn't apply to code in a compiled COM object.

In the past, IIS was particularly vulnerable to having ASP scripts read. Many hackers learned that they could view the server-side script of an ASP page by adding the string "::$DATA" to the end of a page request. Read the relevant Microsoft security bulletin at:

To satisfy this requirement, remove the comments from ASP pages, HTML and JavaScript. It can be done by hand, but it would be pretty easy to write a program to remove comments from each type of file.

Don't store database connection information in global.asa

The database connection information has the server name, database name, SQL Server Login, and the password for the login. As with other text files, global.asa could be compromised, thus revealing database information. Some other location for this information should be used. The workable choices boil down to storing it in a file or in the registry.

Storing the connection information in a file and then reading it with either the File System Object or the XML Parser is somewhat more secure than global.asa. Another file based option is using a UDL file. It holds all the connection details. Your ADO connection string becomes "FILE Name=C:\Path_That_IUSR_<machinename>_Can_Get_To\MyDataLink.UDL". The IIS service account, IUSR_<machinename>, must have access to read the file.

Storing connection information in an encrypted form in the registry is the most secure choice. This involves an application to write the encrypted information to the registry and a COM component that retrieves and decrypts it at runtime.

For IIS 5, if there is a COM+ component in use, there is an additional registry based option. COM+ allows each component to have a Constructor String set in the Component Services Manager. Components can retrieve this string at runtime and use it to create their ADO Connection string. While it's not encrypted, this allows the site administrator to control database access and change it at any time.

A database audit log should record all modifications to data.

A database audit log provides historical information about how every record has been changed, who initiated the change, and when the change occurred. Although there are other solutions, the two approaches that I've implemented are:

  1. Code database triggers for capturing and logging all Insert, Update and Delete operations.
  2. Code the audit logging into the application.

Coding the audit logging into the application is a lot of work. It might be done at the business layer, but it's easiest to implement if the application has a database access layer. This approach works with databases that don't have triggers. That is the only situation in which I would use it. Every insert, update and delete operation should be coded to include the appropriate level of audit information.

I prefer the trigger method. It has several advantages:

  • It can be implemented without modifying the application code.
  • It is easiest to maintain.
  • It can be turned on and off easily by enabling or disabling the triggers.

It also has disadvantages; principally:


  • It is still a lot of work, although less work than adding logging to the application code. There are a lot of tables to be created and scripts to be written, and then maintained.
  • It has a negative impact on performance. At least one database insert is required to record every row inserted, updated or deleted.

While one could create the tables and write the triggers by hand, an automated solution makes life easier and reduces errors. There are several products and one free script ( that create the tables and write the triggers. I use a program of my own creation based on SQL DMO.

Recording every change to every record can multiply your database size several fold. To reduce the amount of audit data, apply a reasonableness test to limit which tables are audited. For example, if data is historical, such as audit trails that are part of the application, auditing database changes is redundant and should be skipped. Sometimes data is analytical, providing summary information. It may not be necessary to audit this data. You'll also need a solution to purge or archive the audit log.

Application code must use stored procedures to access the database.

Limiting database access to stored procedures has advantages for both security and performance. It's worth doing for the performance advantages. However, if the stored procedures only approach isn't used from the start of development, it is difficult to retrofit.

The stored procedure approach is more secure than using ADO Recordsets or SQL statements because once implemented, all database access permissions to all user tables can be removed from all users except the database owner (dbo). Users get execute permission on the stored procedures but no rights to read or modify the tables directly. Only the dbo and administrators are able to fire up Query Analyzer or Crystal Reports and work with the data. By the way, this requirement also implies that if Crystal Reports or a similar tool is used on the Web site, it must use stored procedures to retrieve data.

To implement a stored procedure approach, create four procedures for each table, one each for: Select, Insert, Update and Delete. You may also want to create wrapper classes in your application's database access layer that interface with the procedures. There are many products that will write the procedures for you. I use a program of my own creation to write both the database procedures and the wrappers. I started out with the code provided in an article by David Rabb that you can find at: Visual Studio .Net has an interesting wizard for creating a wrapper class called a typed dataset.

Here's a sample procedure that inserts data into the authors table from the pubs sample database:

CREATE PROCEDURE dp_authors_ins
	@au_id	varchar(11),
	@au_lname	varchar(40),
	@au_fname	varchar(20),
	@phone	char(12) = NULL  OUTPUT ,
	@address	varchar(40) = NULL ,
	@city	varchar(20) = NULL ,
	@state	char(2) = NULL ,
	@zip	char(5) = NULL ,
	@contract	bit
IF @phone IS Null SET @phone = ('UNKNOWN')

		au_id, au_lname, au_fname, phone,
		address,  city, state, zip, contract)
VALUES (@au_id, 	@au_lname, @au_fname, @phone, @address,        @city, @state, @zip, @contract)

 SELECT  @phone = phone 
	FROM authors 
	WHERE au_id = @au_id


Reading and modifying data is different with the stored procedure approach. Instead of working with ADO recordsets or creating SQL statements to be executed by the server, all access to the database goes through an ADO command object. The ADO command object executes the stored procedure. The command object's parameter collection provides for passing parameters both ways. A very valuable reference for working with the ADO command object and stored procedures is William Vaugh's book ADO Examples and Best Practices (Apress 2000). Check out Bill's web site at:

Use a secure password for the SQL Server administrator, SA

This one would go without saying if there weren't so many SQL Servers running with a blank password. SA and other accounts should use a strong password. There is already a virus that searches out SQL Servers with blank passwords. For more information about it see the article at: (login required). The Internet Storm Center has recorded frequent port scans on port 1433, the SQL Server default TCP/IP port. The details are at:

Responding to the Inquiry

All the rules of successful communication apply when preparing a response to IT's question, "Does the application comply with our checklist?" It's best to keep the response simple, positive and unapologetic. When a Yes will do, answer Yes. When you don't understand the issue raised by an item, respond with a request for clarification.

When the application doesn't comply with an item on the list, you have to decide how to respond. If you want to implement the item anyway, you may want to respond with a promise to deliver the feature in an "upcoming release". Just beware that the response might be, "OK. but we'll wait until it's done."

If you're not planning on implementing a checklist item, you should respond with an explanation about why the application doesn't comply. For example, to the requirement "Log the user off as soon as they leave the site" I recently responded that "Linking the user to specific pages of related sites X and Y are features of the application important to its usability." That was sufficient. A strait forward "No" answer is likely to be met with a strait forward "No Go" response.

The response should be customized to the situation. Examine the technical environment. Checklists are often applied broadly to all applications: Web, client/server and mainframe. Respond only where necessary and relate your response to the specifics of their environment.

The response should also be customized to the risks associated with the application. Situations where there is financial exposure or private information call for more secure environments than sites with only public information.


These days, security has to be a major consideration when developing a Web application. The gatekeepers in IT are doing their best to protect corporate assets. The savvy developer will be aware of security requirements before starting development and be in a position to respond positively to security concerns.


Personal Blog

New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage


Nov 7, '12
Loser: DB

Full Schedule