IIS Logging to SQL Server Fails w/ Blank Username and Password

This article was previously published under Q149398
This article has been archived. It is offered "as is" and will no longer be updated.
We strongly recommend that all users upgrade to Microsoft Internet Information Services (IIS) version 6.0 running on Microsoft Windows Server 2003. IIS 6.0 significantly increases Web infrastructure security. For more information about IIS security-related topics, visit the following Microsoft Web site:
SYMPTOMS
If you are using the IIS Service Properties Logging dialog box, and youselect the Log to SQL/ODBC Database option, you have entered a correct DSNand Table. However, when you enter the username and password, they arereplaced with blanks when the dialog box is opened again.

The SQL Server log will record the following:
Failure condition - logon failed because there is no valid user account.

The server will report the following error:
ODBC reported an error. The Datasource name "dsn" may be incorrect.
Check the server's event log for details.

The Server's event log indicates that SQL Server rejects the transactionbecause it is not defined as a valid user.
CAUSE
This is caused by an improper configuration of the Microsoft SQL ServerLogin Security Mode for use with IIS. If the SQL Server Login SecurityMode is set to Windows NT Integrated option, then SQL Server uses WindowsNT authentication mechanisms for all connections and only trustedconnections are allowed into SQL Server.

The login name and SQL Server password submitted in the login request froma DB-Library or Open Database Connectivity (ODBC) client application arealways ignored by SQL Server. Network users assigned user-level privilegesto SQL Server log in using their network username or the default login ID(if the network username is not found in syslogins). Network usernamesassigned system administrator-level privilege log in as SA. With thisoption, only named pipes or multi-protocol clients are supported. If youhave installed additional Net-Libraries, this option is not available.
WORKAROUND
To resolve this issue set the Login Security Mode to Mixed. In this mode,SQL Server allows both trusted and nontrusted connections.

For trusted (named pipes or multi-protocol) connections, SQL Serverexamines the requested login name as specified by the client DB-Library orODBC application. If this login name matches the user's network username,or if the login name is null or blank spaces, SQL Server first tries theWindows NT Integrated login rules. If this fails, SQL Server uses theStandard rules. If the requested login name is any other value, the usermust supply the correct SQL Server password, and SQL Server handles thelogin using the Standard rules described in the Microsoft SQL Server Helpfile.

NOTE: All login requests from nontrusted connections are handled using thestandard security rules.
Properties

Article ID: 149398 - Last Review: 02/28/2014 08:02:05 - Revision: 3.2

  • Microsoft Internet Information Server 1.0
  • Microsoft Internet Information Server 2.0
  • Microsoft Internet Information Server 3.0
  • Microsoft Internet Information Server 4.0
  • kbnosurvey kbarchive kbprb KB149398
Feedback