Solving SQL Server Connection Problems

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.


»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles