SQL Server Q&A

Symptoms
The following error message is returned when you attempt to configure publishing using a remote distributor if the password required for the publisher to establish an administrative link with the distributor isNULL:

SQL Server Enterprise Manager could not configure ‘<Distributor_name>’ as the Distributor for ‘<Publisher_name>’.
Error 18456: Login failed for user ‘distributor_admin’.
Resolution
Configure the publisher as a trusted publisher to the distributor so that a password is not required to establish the administrative link between the publisher and the distributor. To change the security settings for the publisher, follow these steps:On the Tools menu, select Replication.On the Publisher tab, highlight your publisher in the list, and then click the ellipsis (…) next to that publisher.In the Replication Agents on the Distribution Log into the Publisher section, select the By impersonating the SQL Server Agent account on ‘{ServerName}’ (trusted connection) check box.
-or-Do not use a login that has a NULL password for establishing the administrative link between the publisher and the distributor. The administrative link password can be set in Enterprise Manager. To do this, follow these steps: On the Tools menu, select Replication.On the Distributor tab, under the Administrative link password section, enter your password in the password and confirm password fields.
Note We recommend that you use a strong password in this step.

Symptoms
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.
Resolution
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:

Microsoft OLE DB Provider for ODBC Drivers (0×80040E4D)
[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’.