INFO: Accessing SQL Server with Integrated Security from ASP (176377)
The information in this article applies to:
- Microsoft Active Server Pages
- Microsoft Internet Information Server 3.0
- Microsoft Internet Information Server 4.0
- Microsoft Internet Information Server 5.0
This article was previously published under Q176377 We strongly recommend that all users upgrade to Microsoft Internet Information Services (IIS) version 6.0 running on Microsoft Windows Server 2003. IIS 6.0 significantly increases Web infrastructure security. For more information about IIS security-related topics, visit the following Microsoft Web site: SUMMARY
When accessing SQL Server with integrated security from Active Server Pages
(ASP) there are some limitations that you should be aware of when designing
your Web site. This article gives a high-level overview of these
limitations and describes possible workarounds.
NOTE: This limitation was fixed in Windows 2000 but for Kerberos security only.
MORE INFORMATION
Microsoft SQL Server Integrated Security requires NTLM authentication in
order to map user accounts to SQL Server accounts. This process requires
that a token be created during the authentication process. This token
requires a the user password to create a private encryption key. Because of
this, the token can only be created on a domain controller or the logged on
user's machine. Also note that Windows NT 4.0 does not allow the forwarding
of such tokens.
With these points in mind you can see that after a Web browser is
authenticated by Internet Information Server (IIS), an authenticated
connection to the SQL Server is not possible. At this point when IIS
attempts to connect to SQL Server via NTLM, IIS does not have the necessary
information to complete the NT authentication process.
There are a couple possible workarounds to this limitation:
Host IIS and SQL Server on the Same Machine
By eliminating the need for IIS to create an authenticated connection to
SQL Server, you can work around this problem. To do this you must use a
data source name (DSN) that does not look out to the network for the SQL
Server and instead looks directly to the local machine. This can be done by
using the "(local)" setting in a System DSN.
Use Basic Authentication Instead of NTLM in IIS
By using Basic authentication, the password is BASE64 encoded and sent to
IIS during the authentication process. With the password, IIS can now
complete the NTLM authentication process when connection to SQL Server.
NOTE: This method is not secure. BASE64 encoded passwords can be decrypted
by anyone able to sniff network packets over the Internet or intranet.
Map the Anonymous User Account from IIS to a SQL Server Guest Account
This method assumes that all users will have the same level of privileges
to the SQL Server resources. This method is most often the LEAST acceptable
option.
REFERENCESFor additional information, click the article numbers below
to view the articles in the Microsoft Knowledge Base:
176379 HOWTO: IIS and SQL Server on Separate Machines with Trusted Connection
176380 HOW TO: Use ASP with a SQL Trusted Connection with Guest Account
325022 INFO: MSDE Security and Authentication
For the latest Knowledge Base artices and other support information on
Visual InterDev and Active Server Pages, see the following page on the
Microsoft Technical Support site:
Modification Type: | Major | Last Reviewed: | 5/2/2006 |
---|
Keywords: | kbDatabase kbinfo kbOSWin2000fix kbSecurity kbWebServer KB176377 |
---|
|