IDC Problems

Troubleshooting Microsoft SQL Server

If an Internet Information Server application cannot connect to SQL Server, it might indicate a problem with the network or SQL Server. You can test Microsoft Internet Information Server connectivity with SQL Server by using tools provided with Microsoft SQL Server. Table 8.3 describes tools included with the Microsoft SQL Server product that can help isolate network problems.

Table 8.3 SQL Server Troubleshooting Tools

SQL Server tool		Purpose		Location
Makepipe.exe		Tests the integrity of the network named pipe services.		SQL Server
Readpipe.exe		Tests the integrity of the network named pipe services.		SQL Server clients
Odbcping.exe		Troubleshoots connectivity to SQL Server through Microsoft ODBC SQL Server drivers.		SQL Server
Isqlw.exe	Queries SQL Server, analyzes the execution plan of a query, and views statistics about the executed query.		SQL Server and clients

For more information about Microsoft SQL Server and these tools, see the Microsoft SQL Server Books Online.

SYBASE SQL Server Does Not Work with Internet Information Server

If you try to connect to a SYBASE® database through an .idc file by using the ODBC drivers included in Internet Information Server, you might see this message:

Error performing query state=01000, error 1225 Microsoft ODBC SQL Server
Driver DBNMPNTW Connection open Create file
State = 08001 error = 1225 Microsoft ODBC SQL Server Driver unable to
connect to data source.

The current Microsoft SQL Server ODBC driver is not certified for use with SYBASE SQL Server.

To correct this problem, use SYBASE SQL Server ODBC drivers. Contact SYBASE Corporation for ODBC drivers certified for use with SYBASE SQL Server. Many third-party ODBC vendors also provide ODBC drivers certified for use with SYBASE SQL Server.

SQL Server Returns 'Not Defined as a Valid User'

You might see the following message when accessing an Internet Database Connector (IDC) script:

Error Performing Query
*[State=3700][Error=18450][Microsoft][ODBC SQL Driver][SQL Server]
Login failed- User: _ Reason: Not defined as a valid user of a trusted
SQL Server Connection.

This message can appear under the following conditions:

You are not running SQL Server and Internet Information Server on the same computer.
You are using Windows NT challenge/response authentication on Internet Information Server.
You are using integrated or mixed security with Microsoft SQL Server.
You are not providing the user name or password values in your .idc file.

When the client runs the IDC script, Internet Information Server must determine whether the data source is local or remote. If the data source is defined as Local System, the query is passed to SQL Server on the same computer. If the data source is remote, the computer running Internet Information Server must make a network connection to SQL Server. When Internet Information Server makes the network connection, it does not pass the credentials of the remote user. It passes a blank user name and password instead.

SQL Server does not validate a user without a user name, and returns the message to Internet Information Server. Internet Information Server then passes the results of the query to the client Web browser.

There are two possible workarounds:

Install SQL Server and Internet Information Server on the same computer. Then set up your ODBC system Data Source Name (DSN) to be Local Server instead of a remote computer name.
Use Basic authentication instead of Windows NT challenge/response authentication.

Pipe/Connection Busy or Time-out Expired Errors Appear when Using SQL Server

If Internet Information Server connects to Microsoft SQL Server through the Internet Database Connector, multiple error messages can appear as databases are added to SQL Server.

The following messages might be displayed when using the Internet Database Connector to make SQL requests to Microsoft SQL Server, if it has a growing number of databases:

*[State=01000][Error=231][Microsoft][ODBC SQL Server Driver]Pipe busy.
*[State=08001][Error=3][Microsoft][ODBC SQL Server Driver]Connection is busy.
*[State=S1T00][Error=0][Microsoft][ODBC SQL Server Driver]Time-out expired.

These problems can result if the SQL Server configuration parameter Open Databases is set too low. SQL Server 6.0 sets this value to 20 by default. If you add databases to SQL Server until you exceed the configured value, it does not generate an "out of open databases" message in the SQL Server log. Instead, the messages listed above appear when you query the database from Internet Information Server.

To solve this problem, increase the value of the SQL Server configuration parameter Open Databases. For more information on setting this parameter, consult the Microsoft SQL Server Administrator's Companion.