Authentication methods for connections to SQL Server in Active Server Pages

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: