You are currently offline, waiting for your internet to reconnect

Authentication methods for connections to SQL Server in Active Server Pages

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Summary
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 (ASP).

Note 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.
More information

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:
  1. Start the Internet Services Manager.
  2. Browse to the Web site, right-click the Web site, and then click Properties.
  3. 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 application.
  • If users are allowed anonymous access, verify the following:
    • 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 SQL Server.
    • 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 account.
    • 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 computer.
    • 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:
  1. If you connect through ODBC, in the ODBC Administrator, choose Windows NT authentication when you configure the data source.
  2. 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.
  3. If you connect using the OLEDB provider for SQL Server (Provider=SQLOLEDB), the connection string must include "Integrated Security=SSPI."
  4. 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)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '\'.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
References
For more information, see the following book:
John Connell Coding Techniques for Microsoft Visual Basic .NET Microsoft Press, 2001
Note This book is currently out of print.
For more information, see the following Microsoft Training & Certification courses:
Microsoft Corporation 2389 Programming with ADO.NET
For additional information about Windows 2000 authentication methods, click the following article number to view the article in the Microsoft Knowledge Base:
287537 Using Basic Authentication to Generate Kerberos Tokens
Properties

Article ID: 247931 - Last Review: 06/22/2014 11:11:00 - 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
Feedback
r varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" sListForLargeScreens track by $index" class="col-sm-6 col-xs-24 ng-scope"> El Salvador - Español
Panamá - Español
Uruguay - Español
대한민국 - 한국어
España - Español
Paraguay - Español
Venezuela - Español
mp;t=">I=4050&did=1&t=">&did=1&t=">ng = 1; var Route = "76500"; var Ctrl = ""; document.write(" appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >>