Article ID: 247931
Article Last Modified on 12/3/2007
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft ActiveX Data Objects 2.0
- Microsoft ActiveX Data Objects 2.01
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.5
- 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
This article was previously published under Q247931
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:
- Start the Internet Services Manager.
- Browse to the Web site, right-click the Web site, and then click Properties.
- 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:
- 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 Security=SSPI."
- 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:
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
Microsoft Corporation 2073 Programming a Microsoft SQL Server 2000 Database
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
Keywords: kbinfo KB247931
