Both SQL Server authentication (standard security) and
Windows NT authentication (integrated security) are SQL Server authentication
methods that are used to access a SQL Server database from Active Server Pages
This article does not apply to Microsoft Windows 2000 Active
Directory domains. The Microsoft Windows NT authentication model that is
discussed in this article only applies to Windows NT domains.
SQL Server authentication
SQL Server authentication relies on the internal user list
maintained by the SQL Server computer. This list does not include Windows NT
users, and is specific to the SQL Server computer. Users are created and
configured using the SQL Server Enterprise Manager. To use this authentication
method, perform with the following steps:
- If you connect through Open Database Connectivity (ODBC),
in the ODBC Administrator, choose SQL Server authentication when you configure
the data source.
- In the ActiveX Data Objects (ADO) connection string,
include the parameters "UID" and "PWD" when you use ODBC, and "User ID" and
"Password" when you use the SQLOLEDB provider.
Windows NT authentication
The computer running SQL Server enables access to its data
through Windows NT accounts. To enable Windows NT authentication, through the
Internet Information Server (IIS) computer, Basic authentication must be
enabled for the Web application. To do this, perform the following:
- Start the Internet Services Manager.
- Browse to the Web site, right-click the Web site, and then
- Click the Directory Security tab, click Edit under Anonymous access and authentication control, and then
select the Basic authentication (password is sent in clear text) option.
To configure IIS for Windows NT authentication, you cannot use
Windows NT Challenge\Response (NTLM) authentication. You must use one of the
following two IIS authentication methods:
- Enable only Basic authentication for the Web
- If users are allowed anonymous access, verify the
- If the user is configured as the Anonymous user in IIS,
they must also be configured in a Windows NT account on the computer running
- If SQL Server and IIS are not on the same computer,
create the user as either a domain account accessible to both computers or
locally on both the SQL Server computer and the IIS computer with the same
password. If you create the user locally on both computers, the user must be
given the Log on Locally right on the SQL Server computer. If the user is a
domain account, the user must be given "Access this computer from the network"
right on the SQL Server computer.
- If SQL Server and IIS are not on the same computer,
then start the Internet Services Manager (on the Directory Security property
page for the Web application), open the Anonymous User Account dialog box, disable the Enable Automatic Password Synchronization option, and then manually enter the password for the
- The IIS computer requires the user's password to
generate a security token that remains valid on another server. When the Enable Automatic Password Synchronization option is enabled, a token can only be generated for the local
- If IIS and SQL Server are on the same computer, the
name of the SQL Server data source in the connection string and in the ODBC
configuration data source (if applicable) is local.
In addition, perform the following steps to connect to a SQL
Server computer using Windows NT authentication in ASP:
- If you connect through ODBC, in the ODBC Administrator,
choose Windows NT authentication when you configure the data source.
- In the ActiveX Data Objects (ADO) connection string, omit
the parameters "UID" and "PWD" when you use ODBC, and "User ID" and "Password"
when you use the SQLOLEDB provider.
- If you connect using the OLEDB provider for SQL Server
(Provider=SQLOLEDB), the connection string must include "Integrated
- In the SQL Server Enterprise Manager, add all Windows NT
accounts and groups that need access through the integrated security to the
logins, and define them as using Windows NT authentication. To simplify
administration, Microsoft recommends that you add Windows NT groups instead of
individual accounts. Configure permissions to all necessary databases, tables,
and stored procedures when you define the account(s).
The following error messages indicate problems with the SQL
Server configuration for Windows NT authentication:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
SQL Server Driver][SQL Server]Login failed for user '\'.
OLE DB Provider for ODBC Drivers error '80040e4d'
Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
For more information, see the following book:
John Connell Coding Techniques for Microsoft Visual Basic .NET
Microsoft Press, 2001Note
This book is currently out of print.
For more information, see the following Microsoft Training &
additional information about Windows 2000 authentication methods, click the
following article number to view the article in the Microsoft Knowledge Base:
Using Basic Authentication to Generate Kerberos Tokens
Article ID: 247931 - Last Review: June 22, 2014 - Revision: 8.0
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft Active Server Pages 4.0
- Microsoft SQL Server 2000 Standard Edition
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Windows Small Business Server 2003 Premium Edition
- Microsoft Windows Small Business Server 2003 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
|kbsqlsetup kbinfo KB247931|