Alexander Chigrik shares fifteen tips for solving SQL Server connection problems.
1. Make sure that you are using the latest SQL Server service pack available.
Many connection bugs have been fixed; you should always use the latest service pack.
See "How can I check which SQL service pack I am running?" to find out which SQL Server service pack you are running. See "Where can I download the SQL Server service packs?" for download locations.
2. Check that the client and server are running the same network protocol.
For example, if the client tries to connect to SQL Server using IPX/SPX protocol, and the server only has TCP/IP protocol installed, the client will not be able to establish a connection.
3. Verify that the client and server are using the same Net-Library.
The client connected to SQL Server 2000 must use a client Net-Library that matches one of the server Net-Libraries to which the server is currently listening. For example, if the client tries to connect to SQL Server using Multiprotocol Net-Library, the server should currently be listening on Multiprotocol Net-Library too. You can change the client Net-Library by using the SQL Server Client Network Utility in the Microsoft SQL Server program group. To change the server Net-Library, use the Server Network Utility in the Microsoft SQL Server program group.
Note. Keep in mind, any changes made in the Server Network Utility will be saved, but they will not take effect until the SQL Server service is restarted.
4. If you cannot connect using Windows Authentication, ensure that SQL Server is not installed on a Windows 9x box.
Windows Authentication is not supported for SQL Server installed on a Windows 9x box.
5. If you get an error message indicating, "Specified SQL Server not found," check that you specified the SQL Server name correctly and that the SQL Server you are connected to is started.
6. Confirm that the ‘single user’ database option is turned off.
If you get an error message indicating that the database is already open and can only have one user at a time, the ‘single user’ database option is turned on. See your database administrator to set this option to off if several users connect to this database at the same time.
7. Check that the user exists in the database to which you are connected.
If you get an error message indicating that the user is not a valid user in the database, have your database administrator set the appropriate permissions for that user.
8. Check that the ‘DBO use only’ database option is turned off.
If an error message indicates that only the owner can access the database, the ‘DBO use only’ database option is turned on. Have the database administrator set this option to off if others besides the database owner can access the database.
9. If an error message indicates that the login failed for user ‘username,’ check the spelling of the login name and password.
This error message signifies that you’ve tried to connect with the wrong login name or password. See your system administrator if you forgotten your login name or password.
10.If SQL Server returns an error message of "Assertion failed" when attempting to connect to a SQL Server through TCP/IP on a Windows 98 client, then install the Client for Microsoft Networks as one of the networking components on the computer.
The Client for Microsoft Networks does not have to be the Primary Network Logon. This error has been corrected on Windows 98 Second Edition.
11. An error message indicating that the connection is broken will be returned if you run a query against a linked server and all of the following conditions are met:
- ‘Microsoft ODBC Driver for SQL Server’ is used to create the linked server
- The name of one unique index is a part of another unique clustered index
To work around this problem, change the index name, or select the ‘Microsoft OLE DB Provider for SQL Server’ instead of the ‘Microsoft ODBC Driver for SQL Server’ to create the linked server. This is a SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.
12. If users cannot connect to an SQL Server 2000 named SPX instance, when there is also an SQL Server 2000 default instance listening on IPX/SPX, use the SQL Server Network Utility to change the properties of the named SPX instance so that it listens on a port other than 33854.
SQL Server 2000 named SPX instance and SQL Server 2000 default SPX instance both attempt to listen on the same SPX port 33854. To resolve this problem you should change network library or choose a port other than 33854 for the named SPX instance.
13. If you get the error message "Specified SQL Server not found," when you attempt to start SQL Server from Query Analyzer with the option "Start SQL Server if stopped," attempt the connection again at a later time or start SQL Server through Service Manager.
This error arises when SQL Server needs to create or relocate a large .mdf or .ldf file at startup, (for example tempdb.mdf is deleted). In this case, the connection attempt is made before SQL Server is fully started, resulting in the error.
14. If you get error 17824, check the network connections or set the SQL Server ‘priority boost’ option to its default value.
This error indicates that SQL Server encountered connection problems while attempting to write to a client. This error may be caused by network problems, (such as client has been restarted), or it may be caused by deviating a ‘priority boost’ option from the default value.
15. To isolate connectivity problems, disable the Shared Memory Net-Library using the SQL Server 2000 Client Network Utility.
The Shared Memory Net-Library is a Net-Library used only for client/server connections on the same computer, and is the Net-Library used by default for intra-computer communications.