Paul Enfield
Developer Support Engineer, Microsoft Corporation
October 24, 1997
Introduction
What Is a Secure Site?
Windows NT Security
Internet Information Server Security
SQL Server Security
Summary
Since the introduction of Microsoft® Internet Information Server (IIS) 1.0, Microsoft Windows NT® and IIS have and probably always will be bedfellows. Because the two products are so tightly integrated, working with Active Server Pages (ASP) in IIS 3.0 affords us the same level of security that NT itself offers. The benefit of this is the ability to develop Web sites tightly integrated with the Windows NT security model. The side effect, however, is that in order to develop a secure site one must understand the paradigm that IIS and ASP run under with Windows NT. That is what this article is devoted to.
This article provides an overview of Windows NT security in areas relevant to IIS and Microsoft SQL Server™. After gaining a basic understanding of these three products' relationships, you will learn how their mutual features are utilized to implement security with Active Server Pages. These topics will not be explored in any more depth than is necessary for understanding the implementation of security with Active Server Pages.
Note This article discusses security in the context of Windows NT and IIS. Much of this information will not be relevant if applied to a site hosted on a FAT drive.
Security is a broad topic. When implementing a "secure" site, you need to consider two major facets of security.
While completely independent of one another, these two can be used in conjunction to provide any level of security desired.
Controlling who can connect to a Web site and access files is handled through a combination of Windows NT security and IIS. After an overview of the topics surrounding security in IIS and ASP, these topics will be addressed with a discussion of how Active Server Pages can be used with either or both of these.
Making sure that information transmitted is secure is the function of the Secured Socket Layer (SSL) and/or Private Communications Technology (PCT). This is also discussed later in this article.
The important thing to understand about security in relation to Windows NT is that it is pervasive. This means that anything you do in Windows NT is going to involve some type of security check. If you attempt to access a file, a security check will happen; if you try to log on to a workstation, a security check will happen. When using Windows NT as a desktop operating system these security implications are most commonly invisible to us. This is because in most situations you will log in to your own machine as an administrator, and consequently never stumble into any permissions issues. So, if there is any question in your mind as to what this means, it can be answered in one simple statement. An administrator of a given machine is the "god" of that machine.
Note, however, that these security checks extend to any other access of a Windows NT machine as well. For example, if another machine attempts to access a file or resource, such a check will also be performed. This is especially important when you consider the function of Internet Information Server. It allows Web browsers to access files on a Windows NT machine.
If you have ever used Windows NT on a Local Area Network, you have been exposed to more of the security features of NT. All security in Windows NT revolves around user accounts. Depending upon whether you are logged into a LAN or not will determine which user account you will be using. This can get a little confusing, but it is important to differentiate between the two. In a stand-alone machine scenario, all of your privileges to that machine are determined by your Local User Account. For example, this includes all file access, the ability to install programs properly, and the ability to log on at all.
Figure 1. The Domain Controller
In the above diagram you can see that when working on a network, accounts reside in two locations. These accounts are stored in a user account database. The user account database responsible for keeping track of all users on a network is located on a machine called the Domain Controller. Whenever you log on to a Windows NT machine, you will identify yourself as being a user in one of these two databases. As you saw earlier, in a stand-alone machine scenario this is the local machine user database, whereas if you log on to a Windows NT network this is the Domain Controller's user account database. The important point to note here is that the Domain Controller's user database is visible to all machines properly logged on to the Windows NT network, while any particular machine's user database is visible only to that machine. You will see the effect of this later when looking at IIS, SQL Server, and authenticated protocols.
Note This article discusses security in relation to a single domain structure. Multiple domains will require a further understanding of NT user account replication issues.
Figure 2. Local Groups vs. Domain Groups
All Windows NT machines come with a set of predefined groups set up in the user manager. These groups are based around a set of common roles users generally play when working with a computer. Each group has a level of privileges and rights set up for them based around these roles. For example, the Power User group has more rights and privileges than the Users group. Therefore, Local Groups serve two roles. From an administrative standpoint they allow an administrator to organize his or her users into groups, while also granting them privileges and rights at the same time.
Domain Groups differ from Local Groups in one major way. A Domain Group serves only as a means to organize users. The implications of this are very subtle, so consider carefully: this means that making a user an administrator of the domain will not necessarily make them the administrator of all machines on the network. In order for this to happen, each machine must have the domain administrators group in their respective machine's local administrators group. That is, the only users that will be administrators of any given machine are those who are directly or indirectly granted administrator privileges. By adding the domain administrators group to the local administrators group, you are indirectly adding any user that is part of that group to the administrators group of the local machine. This subtlety is why you will commonly add your domain user account to your machine's administrators group as one of the very first things you do when you set up a new Windows NT machine. After all, everyone likes to have complete control over their own machines, right?
Note In corporate environments, it is not uncommon for machines to be set up by IIS departments that might not grant users administrative privileges to a machine they work on.
The basic concept of rights is simple: In order to do something with a resource, you must be granted rights to perform that action. Windows NT rights are fairly easy to understand as well. It is, however, important to understand that rights are not handled the way you might think. While it would be easiest to conceptualize rights as being stored with all of the information about a given user, this is not the case. Instead rights are tracked with each resource you might be attempting to access on a machine. This means that instead of storing information about whether a user can access a particular file with that user's information, such information is stored with the file itself. The same holds true with access rights for the system registry; rights to the system registry are kept with the registry. System policies are kept in a central location, with access rights to them.
The fact that all of this information is independent of the user accounts information highlights one important fact. It means that essentially the main purpose of a user account is to keep track of user credentials. This means that all access rights do is end up pointing back to a user account. For example, all permissions to all files point back to a user account, all policies point back to a user account, and privileges to the system registry point back to a user account.
Authentication is a basic challenge for authority. Or, in other words, authentication is the process of logging into a network machine or having your credentials checked when attempting to do something that requires certain privileges. After your initial logon to a Windows NT workstation or server, all of this happens invisibly to the user. You are first exposed to this when logging on to a Windows NT server or workstation. In a stand-alone machine environment, when initially logging in, your credentials (user name and password) are checked against the local machine user database. After being validated against that user database, all actions performed on the machine are done as that user you logged in as. This process will be discussed in greater depth later in this article.
Logging in to a domain is slightly different. When initially logging on to a machine, you supply your user name, password, and domain name. The machine you are using then attempts to contact the domain controller for the domain specified, and to authenticate you based on the user name and password you provide. After this is done, all rights and privileges will point to this domain user account. Therefore, as you learned earlier, this means that if you wish to perform any type of administrator-level action on a machine, your domain account must be made a member of the administrators group in the local user database.
Windows NT resources are considered objects by NT. Examples of such objects might be a registry key, or a file on the NT File System (NTFS). All Windows NT resources have privileges associated with them. Such privileges are maintained in Access Control Lists (ACLs). ACLs are a list of unique identifiers (SIDs) that represent user accounts. Each SID has associated files kept with it. Setting such privileges in the ACL can vary depending upon which object needs to be set.
When dealing with files that are being hosted on a drive formatted with NTFS, you have the capability of assigning privileges to files on a directory or on a file-by-file basis. This is one of those security checks that goes on behind the scenes for every file you access, but when you are an administrator of your local machine, you rarely realize this. When dealing with a network however, this becomes more apparent.
As mentioned before, file privileges are maintained in ACLs. Each time a file is accessed, this list is referenced to see if the requesting user's SID is in the ACL and determine the access privileges for that user before granting access. To set privileges on a given file, see the section "Using NT File Permissions."
Access to the system registry is also governed by Windows NT security. ACLs are maintained on each registry key in a similar manner to NTFS files. While such privileges are rarely an issue for interactive users, IIS developers should be aware of these, especially in the context of DCOM. DCOM is touched upon briefly later in this document.
Some protocols for communicating between machines on a network require authentication. Two prime examples of protocols that do and do not require authentication are Named Pipes and TCP/IP Sockets. Named Pipes is a good example of an authenticated protocol because it is the default protocol over which Microsoft SQL Server communicates. We will look at the implications of this later when SQL Server is discussed.
As mentioned earlier, rights and policies are stored in one location. Each right or policy is then associated with a list of users or groups that has access to that right or policy. The most obvious of these rights is the Log On Locally right. This right allows users to log on to a machine to use it interactively.
To properly understand how Windows NT Security and IIS interact with one another, it is important to first realize the true nature of IIS. As mentioned earlier, IIS is simply a tool for providing files to browsers requesting them. Since you understand that any interaction with an NT machine necessitates a security check (authentication) to occur, you should realize that each request for a Web page will trigger authentication. Knowing this, consider the following: If Joe Average User browses your site and your NT server has no idea who this user is, how can he get to a file he needs? You know that authentication needs to occur and in this case it will fail. So how can you provide Web content to unrecognized users? The answer to this question is the IIS Anonymous User.
When this option is enabled in IIS, any request for a page from IIS will be done as the Anonymous User: IIS will attempt to access the file in question as if it were the Anonymous User. Once the file is requested by this Anonymous User, NT will check the file ACLs to see if the Anonymous User has privileges to open the file. If the Anonymous User has sufficient privileges, the file is sent.
An Anonymous User is set up by default on a new installation of IIS 3.0. In setting the Anonymous User, IIS must do two things. First, it must create the account on your local machine. During the IIS installation process, setup creates an account by the name of IUSR_Machinename where machinename is the identity of the NT machine. This machinename can be found in Control Panel, Network. The Identification Tab will display your computer name.
Figure 3.
In this case, an Anonymous User by the name of IUSR_PAULEN_HOME would be set up in the local user database with a random password. This account is made a member of the Guests group, and is given the right to "Log On Locally." You can see this account by using the User Manager (USRMGR.EXE) from the Start menu, under Programs, Administrative Tools, User Manager.
Figure 4.
Note that if IIS is set up on a Primary Domain Controller or Backup Domain Controller, this IUSR account will be a domain account that is visible to all machines.
After creating this account, IIS will then set up the WWW service to utilize this account. It does so by setting up the Anonymous User to act as this account whenever pages are requested. To see this, go to Start, Programs, Microsoft Internet Server, and run the Internet Service Manager.
Figure 5.
Once in the IIS Manager, double-click the WWW service on the computer name.
Figure 6.
IIS setup places the same IUSR name here with the appropriate random password used to set up the local user account. With Allow Anonymous enabled, IIS would do the following for each page requested by a Web client:
An important point to note here is that access privileges for any file requested are always checked even though you're using an anonymous account. This means that the use of the anonymous account is by no means any compromise in security. If you wish a file to be safe from the general public, you simply need to restrict access to the file using Windows NT file permissions.
Anonymous access often raises red flags for system administrators, especially when some components may need access to the System32 directory. You should note, however, that the user being authenticated anonymously does not have access to the password used in anonymous authentication. Additionally, such users will have access only to files that have been explicitly shared out through IIS. With this in mind, you can see that there are multiple barriers preventing someone from taking advantage of anonymous access to get to restricted resources. First, they will not be able to access the files because the files will not be visible to them. Second, even if they could get to the files, they cannot complete the authentication because they do not have all of the account information necessary to gain access to the files they seek. Once again, this brings us to the same point: The use of the anonymous account is by no means a compromise in security.
NT Challenge/Response is the preferred method for IIS if the IUSR account cannot access a particular file. Once the IUSR account is denied access to a particular file, IIS will then challenge the Web browser for credentials to access the file. If the browser supports NT Challenge/Response, it will pass user credentials back to the IIS machine. The benefits of this method of authentication are:
NT Challenge/Response requires a live connection between the Web client and the server requiring authentication. This is done through a TCP/IP socket. When challenged for credentials, the IIS machine will indicate to the browser that it requires an open socket. If the socket is lost or closed, NT Challenge/Response will fail. This can be a problem if a firewall or proxy server is not configured to handle this. Because of this, NT Challenge/Response is primarily useful in an intranet setting rather than a real Internet setting.
Currently only Microsoft Internet Explorer supports this method of authentication.
If NT Challenge/Response fails, is not enabled, or the Web browser requests this method, Basic authentication will then be tried if it is enabled. This method will cause the Web browser to display a dialog requesting username and password information to be passed back to the IIS machine. This information is Base64-encoded and transmitted over the wire. If used independently of the Secured Socket Layer (discussed later), user credentials are then visible to anyone who might be monitoring the interactions between the Web browser and Web server.
After the user credentials have been passed over the wire, IIS will use these credentials locally to impersonate that user and access the resources in question. Having the user credentials stored locally can work around some potential problems, as you will see later when we discuss SQL Server.
Securing a Web document is a minor adjustment of the normal HTTP Get/Response process. You should be familiar with the process by which Web documents are retrieved by browsers. In brief:
Securing files alters this process slightly. Instead of responding with the HTTP Response header, the Web server will actually deny access to the document with an HTTP 401 Unauthorized status. Depending upon which authentication method you are using (Basic or NT Challenge/Response) the challenge for authentication changes slightly. This breaks down to:
Note This means that if you use NT Challenge/Response and your client uses a browser other than Internet Explorer, their password will be transmitted over the network, compromising security.
Once you understand this process, you can see that there are two opportunities to force authentication to happen when clients request Web documents. You can either restrict the documents themselves at the file level, or you can force the HTTP 401 message using the Response object.
Controlling who gains access to a given file or page can be handled a couple of ways. Most commonly, this is done by controlling permissions to files using NT security, but it can also be done programmatically.
Generally, Web administrators will lock down permissions to files using the Windows NT ACLs. You can do this on a file-by-file basis, or by changing the permissions on the whole directory and all files contained within that directory.
To change permissions on either of these, use Windows NT Explorer on an NTFS drive.
Figure 7.
Figure 8.
Figure 9.
Figure 10.
Note The asterisk (*) next to \\PAULEN indicates that this is a local user database as opposed to a domain controller's user database.
Sometimes it becomes necessary to programmatically control who will access a particular file. Using the ASP Response object, you can alter the HTTP headers to any given file to deny access to that file, or challenge the user for credentials. To do this you would add something like the following to the ASP script in question:
<%
Response.Clear
Response.Buffer = True
Response.Status = "401 Unauthorized"
Response.AddHeader "WWW-Authenticate","NTLM"
Response.End
%>
This sends an HTTP 401 status to the browser, which will recognize this as a denial of access to the given file. Internet Explorer will check to see what authentication methods are supported, and this example indicates that NTLM (NT Challenge/Response) is supported. Upon recognizing this, Internet Explorer will continue the Challenge/Response process with the IIS server.
IIS utilizes a feature called Impersonation when serving Web pages. IIS serves its Web content by impersonating users. As with the Anonymous User, this is mostly done through the IUSR account. If authenticated via either of the other two methods, IIS will impersonate the user whose credentials were supplied during authentication. This means that any requests you make of an IIS machine will be done as either the IUSR, the account you logged in under in Windows NT or Windows 95, or the account whose name and password you provided during basic authentication.
For such impersonation to occur, the account that will be used must have rights to log on locally. These rights allow a user to interactively log on to the machine in question. When the IUSR account is created during installation, this right is set for the account. Essentially what happens each time a page is accessed anonymously is that the IIS machine impersonates the IUSR account, which in effect has it log on as a virtual user to the NT machine hosting IIS.
NT Authentication is a complex exchange between a client being authenticated, the server providing the resource that needs authentication to be used, and the domain controller validating the action. Due to its design, IIS application developers may run up against this issue. To understand this it is necessary to understand the details of the authentication process. The authentication process breaks down to the following:
Figure 11. The NT authentication process, part one
Figure 12. The NT authentication process, part two
The most important point to notice in this interaction is the fact that the NT Server has neither the user password nor the password hash. Since NT does not transfer passwords over the network, the NT Server must then get a token from the domain controller. Once it has done so it cannot send it to another machine. If it could send this to another machine this would be referred to as delegation. NT does not support this in version 4.0. This leads to some complications with Web development.
Consider the following:
Figure 13. Accessing a resource as an anonymous user
With NT 4.0, under normal circumstances the Web client will be authenticated by the IIS machine as the Anonymous user (IUSR_Machinename). Remember that the IUSR account is on the IIS machine itself, so the authentication is taking place between the IIS machine and the Domain Controller. Now consider what happens if the ASP file does not have privileges set for the IUSR to view it. Under this scenario, the Web client must then be authenticated. Remember that when IIS processes the ASP file it does so as the authenticated user.
Figure 14. Accessing a resource as an authenticated user
In this case, this means that the connection to the MDB will be done as the user PaulEn. Keep in mind that PaulEn's password does not exist on the IIS machine. Now, as IIS attempts to open the MDB file, the NT server hosting the MDB will challenge IIS and ask who wants access to the file. At this point, Windows NT runs through the authentication process described earlier. The problem here occurs at step 7 described earlier when the IIS machine must encrypt the user name and domain name. Because the IIS machine does not have the password, this cannot be done. Additionally, since NT 4.0 does not support delegation, the token used earlier to authenticate the user PaulEn cannot be passed to the MDB-hosting NT server.
While this is a little tricky, it boils down to a simple limitation. This limitation can be stated as: Resources that require authentication in order to be accessed will not be able to access resources on another physical NT machine unless the IIS machine is a domain controller.
Note that the term "resources" is used here. Above, this was seen with an ASP script file, but this extends to other things as well, such as ActiveX™ components.
Also note that these caveats do not apply if the IIS machine is a domain controller. This is because it will have local access to the user accounts and will be able to properly complete the authentication process.
Active Server Pages have the ability to instantiate ActiveX objects (COM/DCOM Components) from script. Commonly, developers will build an ActiveX control such as a business object that accesses resources on another machine. When used within a page that requires authentication to be viewed, you could run into this same problem if care is not taken in the creation of these components.
As you are now aware, resources are accessed under the context of the impersonated user. This extends to components instantiated from within ASP scripts. The ability for these components to reach other resources depends on this user context, which can be affected by the threading model under which the component was created.
DCOM components differ only slightly from an application in how they are used. Because they can be instantiated over the network on remote machines, extra levels of security are introduced. These levels of security are in the form of security permissions placed upon the registry keys needed to instantiate DCOM components. Due to the complexity of this, the tool DCOMCNFG.EXE is provided to facilitate the setting of these permissions.
When setting up your DCOM components, it is important to keep in perspective the user context under which the component will be created. With this in mind, you can appropriately set the permissions using DCOMCNFG such that this user can instantiate the DCOM objects needed.
Components developed for Internet Information Server should be sensitive to the threading model under which they are developed. If you are unfamiliar with threading, more information can be found in the documentation provided with Active Server Pages.
Components are instantiated under a user context determined by their threading model. In brief, single-threaded components will run under the context of the local System account, apartment-model and free-threaded components marked in the registry as "Both" will run under the context of the user being impersonated by IIS.
To determine this, IIS will check the system registry for the threading model of the component. A component can be marked with a key "ThreadingModel," which has valid values of "Apartment" or "Both." An example of such a setting might be:
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{0000022C-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Both"
More detailed information on user contexts with IIS can be found in Microsoft Knowledge Base article Q172925.
Microsoft® Visual Basic 5.0 has the ability to create Apartment model components. To do this, special settings must be made in the project. For an existing ActiveX DLL, to set this you would:
Figure 15.
Once you have established control over who can and cannot view your Web pages, you may need to ensure that any information sent to or from the Web site remains confidential. Under the normal communication channel of HTTP, anyone with the capability of watching the raw traffic crossing the Internet wires will be able to see your data. To address this problem, Netscape introduced Secured Socket Layers (SSL). Microsoft's equivalent technology is Private Communications Technology (PCT) and is compatible with SSL due to the fact that both share the X.509 certificate format. While this is a different technology, most people refer to secure channels as SSL for simplicity's sake.
Active Server Pages provides tools to help integrate your site with SSL. SSL will take care of the details of finding out who a particular Web client is while ASP allows you determine what to do with them once you have determined this. Therefore, once SSL is installed and working properly, all communications between your Web site and the Web client will be encrypted and completely confidential. This of course assumes that your client is indeed connecting via SSL, which requires that they use the HTTPS identifier in their URL, such as: HTTPS://myserver/mypage.htm
SSL imposes a performance hit on serving of Web content due to the additional encryption steps that must be performed. Information that does not need to be transmitted over a secure channel should be left to normal HTTP. An example of this might be any image files (GIFs, JPEGs) used on your site.
Accessing information about the Web client is done through the Request object, which is an ASP intrinsic object. The Request.ClientCertificate method allows you to examine the information about the Web client and determine if access will be granted to pages. This provides an alternate method of "authenticating" a user independent of normal NT security.
The following flowchart provides an overview of what's been discussed up to this point.
Figure 16.
Microsoft SQL Server adds another level of complexity to the implementation of security with IIS. As a partner product in the Microsoft BackOffice package, SQL Server is commonly used in conjunction with IIS. Depending upon what level of integration you want, SQL Server will need to be configured accordingly. The delegation limitations of NT 4.0 affect you here, so let's see how SQL Security relates to NT security.
Microsoft SQL Server supports three different security models. These models allow either complete integration with the NT domain model, no integration with the domain model, or a mix.
To configure, run the Start menu, select Programs, Microsoft SQL Server 6.5, SQL Setup.
Figure 17.
Choose the Set Security Options checkbox to display the following dialog box.
Figure 18.
Standard security is the default option installed with SQL Server. It provides the simplest security model because security exists completely independent of the NT domain model. The level of access a user has to the database and its objects is determined by security settings within SQL Server itself. Authentication consists of comparing the provided user name and password against similar information maintained in the SQL Server database. This is the easiest security model to integrate with IIS.
Integrated security is the opposite end of the scale from standard. It is completely dependent upon Windows NT security as far as who is granted access to the SQL Server database. For example, when a user attempts a connection to the SQL Server, the connection will be made as the logged on user. Integrated security requires that SQL Server be configured to use Named Pipes as its protocol for network communication. Authentication is processed in the same manner as described earlier under the section "Delegation." Integration with IIS can be tricky due to the absence of delegation in NT 4.0. More on this soon.
Mixed security offers a combination of both of these security models. How a user attempts to establish his connection to the SQL Server will determine how the user is authenticated. Basically, if the user makes a trusted ODBC connection, authentication will occur via the Windows NT authentication process. If the user does not, standard security is used, and the provided user name and password are simply compared to the SQL Server user database.
What protocols SQL Server is configured to use will have a large impact on how you will integrate SQL Server with IIS. While SQL Server offers a large variety of protocols, Named Pipes or TCP/IP Sockets are most often used.
As mentioned earlier, Named Pipes is an authenticated protocol. This means that any time a user attempts to open a connection to the SQL Server via Named Pipes, the NT Authentication process will occur. This is important to remember because Named Pipes is also the default protocol when installing SQL Server. Additionally, Named Pipes is required for SQL Server integrated security.
Sockets is a non-authenticated protocol. This means that connections will not be authenticated, and connections and access levels are determined only by the user name and ID passed during the connection.
Integration of SQL Server with IIS is the culmination of all of these security issues into one topic. All factors come into play here, including what security settings IIS is using, how SQL Server is configured, and the locations of each product. Many of these integration challenges come from the inability of NT 4.0 to delegate. With the introduction of Kerberos security in NT 5.0, many of these issues will be put in the past. For now, let's take a look at some common configurations and see how things can function or present challenges.
As mentioned earlier, standard security should provide the least number of challenges when integrated with IIS. An important part of this picture is whether or not an authenticated protocol will be used.
Named Pipes, being an authenticated protocol, forces authentication to occur when a connection is attempted. Because this happens invisibly to us it is an easy point to overlook when configuring or troubleshooting SQL connectivity issues. To understand the impact of this, let's take another look at the sequence of events that occurs when a page utilizing a data connection is browsed.
Figure 19.
In short, running SQL Server and IIS on the same physical NT Server presents no problems in integration of the products.
Hint Use a System DSN and set the server name to local. This prevents IIS from looking out at the network for the SQL Server and will provide better performance. This type of connection is referred to as "local pipes."
Figure 20.
It is important to note where the SQL Server will look for user account information when attempting to validate the Named Pipe connection in step 7. Under normal circumstances, the validation will fail on step 7. This is because by default IIS is set up to use the IUSR account, which is a local user account. This means that when SQL Server attempts to validate the Named Pipe connection, it will not be able to find the user account information and hence, fail.
Further information can be found in Microsoft Knowledge Base article Q159976.
Other protocols ease the integration challenges of IIS and SQL Server considerably. With the lack of an authenticated protocol, access is determined entirely by the user name and password provided during the connection. You run into no problems with passing tokens or delegation.
Using SQL Server with integrated security poses a larger challenge when used with Windows NT 4.0. Once again, this is due to the inability to delegate with NT 4.0. There are some very specific usable scenarios with integrated security. The thing to keep in mind when considering integrated security is its reliance on Named Pipes for authentication.
This integrated security scenario is essentially identical to the one outlined above in standard security with IIS and SQL Server on the same machine. The key point to note here is that since both are on the same machine, no token needs to be passed between machines. Because of this, SQL Server can authenticate the connection and everything will work fine. Pages that utilize ADO to make such a connection should not allow Anonymous access. By restricting access to these ASPs, authentication will occur when a user attempts to open the ASP. This will cause the ASP to be processed under that user context and consequently the connection to the SQL Server will be made as that user.
Note This configuration requires a local pipe connection to work. If a local pipe connection is not used, IIS and SQL Server will behave as if they were on separate machines.
If you want anonymous access to pages utilizing ADO, you must create an Anonymous user account in SQL Server and map the IUSR account to this SQL Server account.
Note By default SQL Server does not allow underscores (_) in its user names, so you will most likely need to set up a new Anonymous user account for use with SQL Server integrated security. Also note that this generally defeats the purpose of using integrated security in the first place. If you have all users authenticated as Anonymous anyway, standard security should be used instead.
The long and the short of SQL Server and integrated security is that it can only be used on the same machine as IIS if you want Web clients to connect to SQL Server securely with their logged-on Windows NT accounts.
While this is intuitively how an administrator would probably design a network under optimal circumstances, there are some caveats with this setup when you are trying to integrate IIS and SQL Server while maintaining integrated security on SQL Server.
You'll recall that under the section "Delegation," the statement was made: Resources that require authentication in order to be accessed will not be able to access resources on another physical NT machine unless the IIS Machine is a domain controller.
When you relate this to working with integrated security, it implies that ASPs that do not allow Anonymous access will not be able to connect to SQL Server on another physical NT machine unless the IIS Machine is a domain controller, or Basic authentication is used instead of NT Challenge/Response.
In other words, you will not be able to have users access Web pages and connect to the SQL Server with their user accounts. Likewise, if you are satisfied with simple Anonymous access to pages needing database access, this will work.
This limitation once again ties back to the inability to delegate. When IIS attempts to make the Named Pipes connection to the SQL Server, it will not be able to encrypt a token because it does not have the user's password necessary as the encryption key (unless it is a domain controller).
Note When using multiple protocols with Microsoft SQL Server, it is necessary to run the client configuration utilities on the SQL Client (IIS machine in most of our scenarios) to configure which protocol will be used. Many administrators will attempt to install TCP/IP Sockets as a workaround to authentication problems, but will forget to change this on the client side. By default, ODBC attempts to use Named Pipes if multiple protocols are installed. Getting to the client configuration utility requires the installation of SQL Server client software.
This paper has looked at the integration of IIS, ASP, and SQL Server with Windows NT Security. The Windows NT authentication process can lead to caveats that influence design considerations of a Web site. With this basic understanding, you should be able to apply these concepts and fully implement a secure site.
Information in this document is subject to change without notice. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents or pending patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. The furnishing of this document does not give you the license to these patents, trademarks, copyrights, or other intellectual property except as expressly provided in any written license agreement from Microsoft.