計(jì)算機(jī)專業(yè)外文翻譯--- 如何解決sql server 2000中的連接問題

1、<p>　　How to troubleshoot connectivity issues in SQL</p><p>　　Server 2000</p><p>　　Chris Tull, Microsoft Help and Support</p><p>　　This article can help you to resolve connecti

2、vity problems with Microsoft SQL Server 2000.</p><p>　　This article contains descriptions of common connectivity problems and the steps that you can</p><p>　　take to help resolve your connectivi

3、ty problems.</p><p>　　SQL Server 2000 supports several methods of communication between the instance of SQL</p><p>　　Server and the client applications. If your client application and the instan

4、ce of SQL Server reside</p><p>　　on the same computer, Microsoft Windows interprocess communication (IPC) components, such</p><p>　　as local named pipes or the Shared Memory protocol, are used t

5、o communicate. However, when</p><p>　　the client application and the instance of SQL Server reside on ditTerent computers. a neiwork IPC,</p><p>　　such as TCP/IP or named pipes, is used to commu

6、nicate.</p><p>　　SQL Server 2000 uses Net-Library, a DLL, to communicate with a particular network protocol. A</p><p>　　matching pair of Net-Libraries must be active on the client computer and t

7、he server computer to</p><p>　　support the network protocol that you want to use. For example, if you want to enable a client</p><p>　　application to communicate with a specific instance of SQL

8、Server across TCP/IP, the client</p><p>　　TCP/IP Sockets Net-Library (Dbnetlib.dll) must be configured to connect to the server on the</p><p>　　client computer. Likewise, the server TCP/IP Socke

9、ts Net-Library (Ssnetlib.dll) must listen on the</p><p>　　server computer. In this scenario, the TCP/IP protocol stack must be installed on both the client</p><p>　　computer and the server compu

10、ter.</p><p>　　After you install SQL Server 2000, you can configure the properties of the client Net-Libraries by</p><p>　　using Client Network Utility. You can configure the properties of the se

11、rver Net-Libraries by</p><p>　　using Server Network Utility (Svrnetcn.exe). The server Net-Libraries are installed during the</p><p>　　installation of the server tools in SQL Servef Setup. Howev

12、er, some of the server Net-Libraries</p><p>　　may not be active. By default, SQL Server 2000 enables and listens on TCP/IP, named pipes, and</p><p>　　Shared Memory. Therefore, for a client to co

13、nnect to a server computer, the client must connect</p><p>　　by using a client Net-Library that matches one of the server Net-Libraries that is currently being</p><p>　　used by the instance of S

14、QL Server.</p><p>　　For additional information about SQL Server communication components and Net-Libraries, see</p><p>　　the following topics in SQL Server Books Online:</p><p>　　.

15、 Communication Components</p><p>　　. Client and Server Net-Libraries</p><p>　　. Managing Clients</p><p>　　Troubleshoot connectivity issues</p><p>　　Most of

16、the connectivity issues that you may notice in SQL Server 2000 0ccur because of</p><p>　　problems with TCP/IP, Windows authentication, or a combination of TCP/IP and Windows</p><p>　　authenticat

17、ion.</p><p><b>　　8</b></p><p>　　Important Before you start to troubleshoot connectivity issues in SQL Server 2000, make sure that</p><p>　　the MSSQLServer service is sta

18、rted on the computer that is running SQL Server.</p><p>　　Verify your DNS settings</p><p>　　The name resolution process in Domain Name System (DNS) is used to resolve the IP address</p>&

19、lt;p>　　to the name of the instance of SQL Server. If the name resolution process does not work correctly,</p><p>　　the instance of SQL Server is not reachable, and you may receive one or more of the follo

20、wing</p><p>　　error messages:</p><p>　　SQL Server does not exist or access denied</p><p>　　General Network Error</p><p>　　Cannot Generate SSPI Context</p><p&

21、gt;　　To verify that the name resolution process is resolving the correct server, you can ping the</p><p>　　server by using the server name and the lP address of the server. To do so, follow these steps:</

22、p><p>　　1. Click Start, and then click Run.</p><p>　　2. In the Run dialog box, type cmd in the Open box, and then click OK.</p><p>　　3. At the command prompt, run the follo

23、wing command:</p><p>　　ping <Server Name></p><p>　　Note the lP address that is returned.</p><p>　　4. At the command prompt, run the following command (where IP address is

24、the lP</p><p>　　address that you noted in step 3):</p><p>　　ping -a <IP address></p><p>　　Verify that the command resolves to the correct server name. If either of the specifi

25、ed</p><p>　　commands are not successful, time out, or do not return the correct values, the DNS lookup is not</p><p>　　working correctly or the problem occurs because of other networking or rout

26、ing issues. To see</p><p>　　your current DNS settings, run the following command at a command prompt:</p><p>　　ipconfig /all</p><p>　　To work around this problem, add an entry for t

27、he server to</p><p>　　the %systemroot%\system32\drivers\etc\hosts file on the clienr computer. You can also work</p><p>　　around the problem by connecting to the server by using the Named Pipes

28、Net-library.</p><p>　　Verify the enabled protocols and aliases</p><p>　　Connectivity problems may occur if the alias on the client computer is set incorrectly. You</p><p>　　can vie

29、w the aliases by using Client Network Utility. To do so, follow these steps:</p><p>　　l. Start Client Network Utility. If the SQL Server client tools are installed on the computer</p><p>　　

30、that is running the client application, follow these steps to start Client Network Utility:</p><p>　　a. Click Start, and then point to Programs.</p><p>　　b. Point to Microsoft SQL Server

31、, and then click Client Network Utility.</p><p>　　If the SQL Server client tools are not installed on the clieni computer, follow these steps ro</p><p>　　start Client Network Utility:</p>

32、<p>　　a. Click Start, and then click Run.</p><p>　　b. In the Run dialog box, type cliconfg in the Open box, and then click OK.</p><p>　　2. In the SQL Server Client Network Uti

33、lity window, click the General tab, and ihen</p><p>　　enable all the protocols that you want to use.</p><p>　　Note You must at least enable the TCP/IP protocol and the named pipes protocol.</

34、p><p><b>　　9</b></p><p>　　3. Click the Alias tab, and then verify the aliases that are configured for the instance of</p><p>　　SQL Server.</p><p>　　4.

35、 Verify the properties of the aliases to make sure that the server name or IP address and</p><p>　　the protocol are configured corfectly.</p><p>　　You can create a new alias to test the connecti

36、vity by using the server name, the lP address, Of</p><p>　　even by using a different protocoL</p><p>　　Note Earlier versions of Microsoft Data Access Components (MDAC) have a different user</

37、p><p>　　interface for Client Network Utility. Therefore, if you do not see the options that are listed in this</p><p>　　article, install a later version of MDAC on the computer that is running the

38、client application.</p><p>　　Verify that the instance of SQL Server i.s listening correctly</p><p>　　To verify that the instance of SQL Server is listening on named pipes, TCP/IP, or another<

39、/p><p>　　protocol that you are using at the client application, open the current SQL Server error log file.</p><p>　　The SQL Server error log file may contain entries that are similar to the follow

40、ing:</p><p>　　2003-11-06 09:49:36.17 server SQL server listening on TCP, Shared Memory, Named Pipes.</p><p>　　2003-1 I -06 09:49:36. 1 7 server SQL server listening on I 92.1 68.1.5: 1433, 1

41、27.0.0. I : 1433.</p><p>　　If you analyze the entries in the SQL Server erfor log file, you can verify that the instance of SQL</p><p>　　Server is listening on the correct IP address and on the

42、correct port. By default, a default instance</p><p>　　of SQL Server listens on the port 1433. You can also use Server Network Utility to verify the</p><p>　　protocol settings for SQL Server and

43、to change the properties in SQL Server, including the</p><p>　　protocols that can connect to SQL Server and the ports that can be used. For more information</p><p>　　about using Server Network U

44、tility, see the "SQL Server Network Utility" topic in SQL Server</p><p>　　Books Online.</p><p>　　Sometimes, SQL Server 2000 may not bind to port 1433 0r any other specified porc. This

45、problem</p><p>　　may occur if the port is being used by another application or if you are trying to connect by using</p><p>　　an lP address that is not correct. Therefore, the TCP/IP connections

46、 to SQL Server may not be</p><p>　　successful and you may receive the following error message in the SQL Server error log file:</p><p>　　2001-11-14 15:49:14.12 server SuperSocket Info: Bind fail

47、ed on TCP port 1433.</p><p>　　If you cannot connect to the instance of SQL Server by using a TCP/IP connection, try to use the</p><p>　　named pipes protocol or the Shared Memory protocol. Run th

48、e following command at a command</p><p>　　prompt to obtain information about the ports that are in use:</p><p>　　NETSTAT -an</p><p>　　You can also use the Portqry command-line utili

49、ty to obtain more information about the ports</p><p>　　that are in use.</p><p>　　Note For named instances of SQL Server, SQL Server dynamically determines the port and listens</p><p&g

50、t;　　on the determined port. Therefore, when you start the named instance of SQL Server, SQL Server</p><p>　　tries to listen on the port that was pfeviously being used. If SQL Server cannot bind to that port,

51、</p><p>　　the named instance may dynamically bind to a different port. In thai situalion, make sure ihat the</p><p>　　client application is also set to determine the port dynamically. Alternativ

52、ely, you can also specify</p><p>　　a static port for the named instance to bind to and to listen on by using Client Network Utility.</p><p>　　Troubleshoot MDAC Issues</p><p>　　Conne

53、ctivity problems may also occur because of problems with MDAC. For example, a</p><p>　　software installation may overwrite some of the MDAC files or change the permissions that you</p><p>　　must

54、 have to access the MDAC files. You can run the MDAC Component Checker to verify the</p><p><b>　　10</b></p><p>　　MDAC installacion on your computer.</p><p>　　Note If you

55、 are connecting to a named instance of SQL Server, make sure that you are running</p><p>　　MDAC 2.6 0r later on your computef. Earlier versions of MDAC do not recognize named</p><p>　　instances

56、of SQL Server. Therefore, connections to named instances may not be successful.</p><p>　　You can use the Odbcping.exe utility to verify connections through the SQL Server ODBC driver</p><p>　　Yo

57、u can also test connectivity to the instance of SQL Server by using a .udl file.</p><p>　　Troubleshoot firewall issues</p><p>　　If firewall exists between the client computer and the computer th

58、at is running SQL Server,</p><p>　　make sure that the ports that are required to communicate through the firewall are open.</p><p>　　If you use the TCP/IP protocol to connect to the instance of

59、SQL Server, make sure that you can</p><p>　　use the Telnet program to connect to the port where SQL Server is listening. To use the Telnet</p><p>　　program, run the following command at a comman

60、d prompt:</p><p>　　Telnet <IP Address> <Port Number></p><p>　　If the Telnet program is not successful and you receive an error message, resolve the error and</p><p>　　th

61、en try to connect again.</p><p>　　Note Because of issues that were caused by the Slammer virus, the User Datagram Protocol (UDP)</p><p>　　port I434 may be blocked on your firewall.</p>&l

62、t;p>　　Troubleshoot authenticacion and security issues</p><p>　　Connections to SQL Server may not be successful because of authentication failures. If the</p><p>　　authentication fails, you ma

63、y receive one of the following error messages:</p><p>　　Login failed for user'<username>'</p><p>　　Login failed for user 'NTAUTHORITY\ANONYMOUS LOGON'</p><p>　

64、　Login failed for user 'null'</p><p>　　If you receive an error message because of an authentication failure and the error message</p><p>　　does not mention a specific SQL Server login na

65、me,troubleshoot the problem with Windows</p><p>　　authentication. You may receive the following error message because of problems with Windows</p><p>　　authentication:</p><p>　　Cann

66、ot generate SSPI Context</p><p>　　The following problems may cause authentication and security issues:</p><p>　　. Problems occur with NTLM authentication or with Kerberos authentication.<

67、;/p><p>　　. The domain controller cannot be contacted because of connectivity issues.</p><p>　　. Problems occur with trust relationships across domains.</p><p>　　For more in

68、formation about possible causes, see the event logs on the computer. To work</p><p>　　around connectivity problems with Windows authentication, you can use SQL Server</p><p>　　Authentication to

69、connect to the instance of SQL Server.</p><p>　　If the connection is not successful when you use SQL Server Authentication, you receive the</p><p>　　following error message:</p><p>

70、　　Login failed for user'<username>' . Not associated with a trusted connection</p><p>　　To troubleshoot this problem, follow these steps.</p><p>　　Warning If you use Registry Edito

71、r incorrectly, you may cause serious problems that may require</p><p>　　you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems</p><p>　　that result from u

72、sing Registry Editor incorrectly. Use Registry Editor at your own risk.</p><p>　　1. Make sure that the instance of SQL Server is configured to use Windows authentication</p><p>　　and SQL Ser

73、ver Authentication. To do so, make sure that the following registry keys are on the</p><p>　　computer that is running SQL Server. For the default instance of SQL Server:</p><p>　　HKEY_LOCAL_MACH

74、INE\Software\Microsoft\MSSQLServer\MSSQLServer\LoginMode</p><p>　　For the named instance of SQL Server:</p><p>　　HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<lnstance</p>

75、<p>　　Name>\MSSQLServer\LoginMode</p><p>　　Make sure that the following registry key values are set:</p><p>　　Authentication type Value</p><p>　　Windows authentication onl

76、y 1</p><p>　　Mixed mode (SQL Server Authentication and Windows authentication) 2</p><p>　　Note If you make any changes to the registry, you must stop and then restart the instance of</p&g

77、t;<p>　　SQL Server for the changes to take effect.</p><p>　　2. Try to connect to the instance of SQL Server by using different Windows accounts or</p><p>　　SQL Server login accounts.

78、This can help determine if the connection is not successful because of</p><p>　　problems with a particular login account. For example, the password of the login account may</p><p>　　have been ch

79、anged.</p><p>　　3. Try to connect to the instance of SQL Server by using different protocols. For example,</p><p>　　the connections that use the TCP/IP protocol with Windows authentication m

80、ay not be successful,</p><p>　　but connections that use the named pipes protocol with Windows authentication may be</p><p>　　successful.</p><p>　　If you are using certificates, you

81、may receive a Secure Sockets Layer (SSL) security error</p><p>　　message when you try to connect to the instance of SQL Server.</p><p>　　Troubleshoot stfess on TCP/IP sockets</p><p>

82、;　　When you use the SQL Server ODBC driver, the Microsoft OLE DB Provider for SQL Server,</p><p>　　or the System.Data.SqlClient managed provider, you can disable connection pooling by using the</p>&l

83、t;p>　　appropriate application programming interfaces (APIs). When you disable connection pooling and</p><p>　　your application frequently opens and closes connections, the stress on the underlying SQL Ser

84、ver</p><p>　　network libfary may increase. Sometimes, the Web servers and the JDBC drivers may also try to</p><p>　　connect to the instance of SQL Server. Therefore, the increase in connection r

85、equests to SQL</p><p>　　Server may be more than SQL Server can handle. This may stress the TCP/IP sockets, and you</p><p>　　may receive the following error message in the SQL Server error log fi

86、le:</p><p>　　2003-08-07 20:46:21.1 I server Error: 17832, Severity: 20. State: 6</p><p>　　2003-08-07 20:46:21.1 I server Connection opened but invalid login packet(s) sent. Connection</p>

87、;<p><b>　　closed.</b></p><p>　　For additional information, click the following ariicle numbers to view the articles in the</p><p>　　Microsoft Knowledge Base:</p><p&

88、gt;　　154628 INF: SQL logs 17832 with multiple TCP\IP connection requests</p><p>　　328476 TCP/IP settings for SQL Server drivers when pooling is disabled</p><p>　　Note You may not notice the stre

89、ss on TCP/IP sockets if you are running SQL Server 2000 SP3 0r</p><p>　　SQL Server 2000 SP3a because a limit on the number of login packets was added. The 17832</p><p>　　error occurs when you u

90、se third-party drivers to connect to the instance of SQL Server. To resolve</p><p>　　this problem, contact the third-party vendor and obtain drivers that have been tested to work with</p><p>　　S

91、QL Server 2000 SP3 and SQL Server 2000 SP3a.</p><p><b>　　12</b></p><p>　　See if the instance of SQL Server is started in single-user mode</p><p>　　If the instance of SQL

92、 Server that you are trying to connect to is started in single-user mode,</p><p>　　only one connection can be established with SQL Server. If you have software running on your</p><p>　　computer

93、that automatically connects to SQL Server, the software can easily use the only</p><p>　　connection. For example, the following software can automatically connect to the instance of SQL</p><p>&

94、lt;b>　　Server:</b></p><p>　　SQL Server Agent</p><p>　　Third-party backup software</p><p>　　. Third-party monitoring software</p><p>　　. Third-party virus

95、 software</p><p>　　. Microsoft Internet Information Services (IIS)</p><p>　　. SQL Server Enierprise Manager</p><p>　　The client application that is trying to connect to th

96、e instance of SQL Server receives the</p><p>　　following error message:</p><p>　　SQL Server does not exist or Access Denied</p><p>　　This error generally occurs during SQL Cluster S

97、etup and service pack setup when the setup</p><p>　　process starts the instance of SQL Server in single-user mode. The specified applications may</p><p>　　automatically connect to the instance o

98、f SQL Server using the only available connection, and</p><p>　　setup is not successful.</p><p>　　To determine if the instance of SQL Server has been started in single-user mode, check to see if&

99、lt;/p><p>　　the SQL Server error log file has an entry that is similar to following:</p><p>　　2003-07-31 11:26:43.79 spid3 Warning ******************</p><p>　　2003-07-31 11:26:43.80 sp

100、id3 SQL Server started in single user mode. Updates allowed to system</p><p><b>　　catalogs.</b></p><p>　　Verify named pipes connectivity to SQL Server</p><p>　　If you ca

101、nnot connect to the instance of SQL Server by using named pipes, make sure that the</p><p>　　instance of SQL Server is configured to accept named pipes connections.</p><p>　　Troubleshoot connect

102、ions that time out during the recovery process</p><p>　　Every time that you start an instance of SQL Server, SQL Server recovers each database.</p><p>　　During this recovery process, SQL Server

103、rolls back the transactions that are not committed. SQL</p><p>　　Server also rolls forward the transactions that are committed and the changes that were not written</p><p>　　to the hard disk whe

104、n the instance of SQL Server was stopped. When the recovery process is</p><p>　　complete, SQL Server logs the following message in the SQL Server error log file:</p><p>　　Recovery Complete</p

105、><p>　　During the recovery process, SQL Server may not accept connections. Clients that try to</p><p>　　connect to the instance of SQL Server during that time may receive an error message that is&l

106、t;/p><p>　　similar to the following:</p><p>　　Timeout Expired</p><p>　　The SQL Server Agent service may not start because it waits for SQL Server to recover the</p><p>　　d

107、atabases. Therefore, when you receive the following message in the SQL Server error log file,</p><p>　　the connections will no longer fail with a timeout error:</p><p>　　Recovery Complete</p&

108、gt;<p><b>　　13</b></p><p>　　If the recovery process takes a long time, you may have to additionally troubleshoot the</p><p>　　recovery process.</p><p>　　Test diff