|
 |
About Us | |
|
SSWUG Articles | |
 |
Articles |
 |
Administering | |
|
Comparison | |
|
General | |
|
Know How | |
|
Replication | |
|
Tuning | |
|
Undocumented | |
|
UDF | |
|
SQL 6.5 |
|
FAQ | |
|
Scripts | |
|
Tips | |
|
Test Exams | |
|
Advertise | |
|
Download | |
|
History | |
|
Search | |
|
Traffic | |
|
Related Links | |
|
||
|
|
||
How to troubleshoot connection problemsAlexander Chigrik Should you have connection problems, review this troubleshooting checklist to find potential solutions. 1. Check that you use the latest SQL Server service pack. Because many connection's bugs were fixed in SQL Server service packs, you should use the latest service pack. To check what SQL Server service pack are you running, see this link: How can I check what SQL service pack am I running? See also this link: Where can I download the SQL Server service packs? 2. Check that the client and the server run the same network protocol. For example, if the client tries to connect to SQL Server using IPX/SPX protocol, and the server has only the TCP/IP protocol installed, the client will not be able to establish a connection. 3. Check that the client and the server use 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 the server is currently listening on. For example, if the client tries to connect to SQL Server using Multiprotocol Net-Library, the server should currently listen on Multiprotocol Net-Library too. You can change the client Net-Library by using SQL Server Client Network Utility in the Microsoft SQL Server program group. To change the server Net-Library, you can use Server Network Utility in the Microsoft SQL Server program group. Note. Keep in mind, that any changes you have made in 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, check that SQL Server is not installed on Windows 9x box. The Windows Authentication is not supported for SQL Server installing on Windows 9x box. 5. If you get the error message indicates that "Specified SQL Server not found" check that you specify SQL Server name correctly and SQL Server you are connected to is started. 6. Check that the 'single user' database option is turned off. If you get the error message indicates that 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 database option to off if several users should connect to this database at the same time. 7. Check that the user exists in the database you are connected to. If you get the error message indicates that the user is not a valid user in the database, see your database administrator to give the user appropriate permissions. 8. Check that the 'DBO use only' database option is turned off. If you get the error message indicates that only the owner can access the database, the 'DBO use only' database option is turned on. See your database administrator to set this database option to off if not only the database owner can access the database. 9. If you get the error message indicates that login failed for user 'username', you should check spelling of the login name and password. This error message indicates that you tried to connect with the wrong login name or password. See your system administrator if you forgot your login name or password. 10. If you get the error message indicates that "Assertion failed" when you attempt to connect to a SQL Server through TCP/IP on a Windows 98 client, 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 not been reproduced on Windows 98 Second Edition. 11. You can get the error message indicates that connection broken when you run query against linked server and you use the 'Microsoft ODBC Driver for SQL Server' to create the linked server and the name of one unique index is a part of another unique clustered index. To work around this problem, you can 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 SQL Server 7.0 bug; SQL Server 2000 does not contain such problems. 12. If users cannot connect to a SQL Server 2000 named SPX instance, when there is also a SQL Server 2000 default instance listening on IPX/SPX, use the SQL Server Network Utility to change the properties of the named SPX instance to listen 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. So, 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 indicates that "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 some time later 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 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 may be caused by deviating 'priority boost' option from the default value. 15. To isolate connectivity problems, you can 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 this Net-Library used by default for intra-computer communications. |