計(jì)算機(jī)外文資料翻譯--如何解決 sql server 2000 中的連接問(wèn)題

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

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

3、00 supports several methods of communication between the instance of SQL Server and the client applications. If your client application and the instance of SQL Server reside on the same computer, Microsoft Windows interp

4、rocess communication (IPC) components, such as local named pipes or the Shared Memory protocol, are used to communicate. However, when the client application and the instance of SQL Server reside on different computers,

5、a network IPC, such as TCP/IP or named pipes, i</p><p>　　?Communication Components</p><p>　　?Client and Server Net-Libraries</p><p>　　?Managing Clients</p><p>　　Trou

6、bleshoot connectivity issues</p><p>　　Most of the connectivity issues that you may notice in SQL Server 2000 occur because of problems with TCP/IP, Windows authentication, or a combination of TCP/IP and Wind

7、ows authentication. Important Before you start to troubleshoot connectivity issues in SQL Server 2000, make sure that the MSSQLServer service is started on the computer that is running SQL Server.</p><p>　

8、　Verify your DNS settings</p><p>　　The name resolution process in Domain Name System (DNS) is used to resolve the IP address to the name of the instance of SQL Server. If the name resolution process does not

9、 work correctly, the instance of SQL Server is not reachable, and you may receive one or more of the following error messages: </p><p>　　SQL Server does not exist or access denied</p><p>　　Gener

10、al Network Error</p><p>　　Cannot Generate SSPI Context</p><p>　　To verify that the name resolution process is resolving the correct server, you can ping the server by using the server name and t

11、he IP address of the server. To do so, follow these steps: </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>

12、<p>　　3.At the command prompt, run the following command:</p><p>　　ping <Server Name></p><p>　　Note the IP address that is returned.</p><p>　　4.At the command prompt,

13、 run the following command (where IP address is the IP 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

14、. If either of the specified commands are not successful, time out, or do not return the correct values, the DNS lookup is not working correctly or the problem occurs because of other networking or routing issues. To see

15、 your current DNS settings, run the following command at a command prompt:</p><p>　　ipconfig /allTo work around this problem, add an entry for the server to the %systemroot%\system32\drivers\etc\hosts file

16、on the client computer. You can also work around the problem by connecting to the server by using the Named Pipes Net-library.</p><p>　　Verify the enabled protocols and aliases</p><p>　　Connecti

17、vity problems may occur if the alias on the client computer is set incorrectly. You can view the aliases by using Client Network Utility. To do so, follow these steps: </p><p>　　1.Start Client Network Utili

18、ty. If the SQL Server client tools are installed on the computer that is running the client application, follow these steps to start Client Network Utility: </p><p>　　a. Click Start, and then point to Progr

19、ams.</p><p>　　b. Point to Microsoft SQL Server, and then click Client Network Utility.</p><p>　　If the SQL Server client tools are not installed on the client computer, follow these steps to st

20、art Client Network Utility: </p><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 S

21、QL Server Client Network Utility window, click the General tab, and then 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

22、.</p><p>　　3.Click the Alias tab, and then verify the aliases that are configured for the instance of SQL Server. </p><p>　　4.Verify the properties of the aliases to make sure that the server

23、name or IP address and the protocol are configured correctly.</p><p>　　You can create a new alias to test the connectivity by using the server name, the IP address, or even by using a different protocol.Not

24、e Earlier versions of Microsoft Data Access Components (MDAC) have a different user interface for Client Network Utility. Therefore, if you do not see the options that are listed in this article, install a later version

25、of MDAC on the computer that is running the client application.</p><p>　　Verify that the instance of SQL Server is listening correctly</p><p>　　To verify that the instance of SQL Server is liste

26、ning on named pipes, TCP/IP, or another protocol that you are using at the client application, open the current SQL Server error log file. The SQL Server error log file may contain entries that are similar to the followi

27、ng:</p><p>　　2003-11-06 09:49:36.17 server SQL server listening on TCP, Shared Memory, Named Pipes. 2003-11-06 09:49:36.17 server SQL server listening on 192.168.1.5:1433, 127.0.0.1:1433.If you analyze the

28、entries in the SQL Server error log file, you can verify that the instance of SQL Server is listening on the correct IP address and on the correct port. By default, a default instance of SQL Server listens on the port 14

29、33. You can also use Server Network Utility to verify the protocol settings for SQL Se</p><p>　　2001-11-14 15:49:14.12 server SuperSocket Info: Bind failed on TCP port 1433.If you cannot connect to the inst

30、ance of SQL Server by using a TCP/IP connection, try to use the named pipes protocol or the Shared Memory protocol. Run the following command at a command prompt to obtain information about the ports that are in use:<

31、/p><p>　　NETSTAT –an</p><p>　　You can also use the Portqry command-line utility to obtain more information about the ports that are in use.Note For named instances of SQL Server, SQL Server dynami

32、cally determines the port and listens on the determined port. Therefore, when you start the named instance of SQL Server, SQL Server tries to listen on the port that was previously being used. If SQL Server cannot bind t

33、o that port, the named instance may dynamically bind to a different port. In that situation, make sure that the c</p><p>　　Troubleshoot MDAC Issues</p><p>　　Connectivity problems may also occur

34、because of problems with MDAC. For example, a software installation may overwrite some of the MDAC files or change the permissions that you must have to access the MDAC files. You can run the MDAC Component Checker to ve

35、rify the MDAC installation on your computer.Note If you are connecting to a named instance of SQL Server, make sure that you are running MDAC 2.6 or later on your computer. Earlier versions of MDAC do not recognize name

36、d instances of SQL Serv</p><p>　　Troubleshoot firewall issues</p><p>　　If firewall exists between the client computer and the computer that is running SQL Server, make sure that the ports that a

37、re required to communicate through the firewall are open.If you use the TCP/IP protocol to connect to the instance of SQL Server, make sure that you can use the Telnet program to connect to the port where SQL Server is

38、listening. To use the Telnet program, run the following command at a command prompt: </p><p>　　Telnet <IP Address> <Port Number></p><p>　　If the Telnet program is not successful and

39、you receive an error message, resolve the error and then try to connect again.Note Because of issues that were caused by the Slammer virus, the User Datagram Protocol (UDP) port 1434 may be blocked on your firewall.<

40、/p><p>　　Troubleshoot authentication and security issues</p><p>　　Connections to SQL Server may not be successful because of authentication failures. If the authentication fails, you may receive on

41、e of the following error messages:</p><p>　　Login failed for user '<username>'</p><p>　　Login failed for user 'NTAUTHORITY\ANONYMOUS LOGON'</p><p>　　Login fail

42、ed for user 'null'</p><p>　　If you receive an error message because of an authentication failure and the error message does not mention a specific SQL Server login name, troubleshoot the problem with

43、 Windows authentication. You may receive the following error message because of problems with Windows authentication: </p><p>　　Cannot generate SSPI Context</p><p>　　The following problems may c

44、ause authentication and security issues: </p><p>　　?Problems occur with NTLM authentication or with Kerberos authentication.</p><p>　　?The domain controller cannot be contacted because of conn

45、ectivity issues.</p><p>　　?Problems occur with trust relationships across domains.</p><p>　　For more information about possible causes, see the event logs on the computer. To work around connec

46、tivity problems with Windows authentication, you can use SQL Server Authentication to connect to the instance of SQL Server. </p><p>　　If the connection is not successful when you use SQL Server Authenticati

47、on, you receive the following error message: </p><p>　　Login failed for user '<username>' . Not associated with a trusted connectionTo troubleshoot this problem, follow these steps. Warning I

48、f you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor

49、 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 and SQL Server Authentication. To do so, make sure that

50、the following registry keys are on the computer that is running SQL Server. For the default instance of SQL Server: </p><p>　　HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\LoginMode</p>

51、<p>　　For the named instance of SQL Server: </p><p>　　HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\LoginMode</p><p>　　Make sure that the fo

52、llowing registry key values are set:</p><p>　　Authentication typeValue</p><p>　　Windows authentication only1</p><p>　　Mixed mode (SQL Server Authentication and Windows authenticat

53、ion)2</p><p>　　Note If you make any changes to the registry, you must stop and then restart the instance of SQL Server for the changes to take effect.</p><p>　　2.Try to connect to the instance

54、 of SQL Server by using different Windows accounts or SQL Server login accounts. This can help determine if the connection is not successful because of problems with a particular login account. For example, the password

55、of the login account may have been changed.</p><p>　　3.Try to connect to the instance of SQL Server by using different protocols. For example, the connections that use the TCP/IP protocol with Windows authe

56、ntication may not be successful, but connections that use the named pipes protocol with Windows authentication may be successful.</p><p>　　If you are using certificates, you may receive a Secure Sockets Laye

57、r (SSL) security error message when you try to connect to the instance of SQL Server. Troubleshoot stress on TCP/IP sockets</p><p>　　When you use the SQL Server ODBC driver, the Microsoft OLE DB Provider fo

58、r SQL Server, or the System.Data.SqlClient managed provider, you can disable connection pooling by using the appropriate application programming interfaces (APIs). When you disable connection pooling and your application

59、 frequently opens and closes connections, the stress on the underlying SQL Server network library may increase. Sometimes, the Web servers and the JDBC drivers may also try to connect to the instance of SQL </p>&

60、lt;p>　　2003-08-07 20:46:21.11 server Error: 17832, Severity: 20, State: 6 2003-08-07 20:46:21.11 server Connection opened but invalid login packet(s) sent. Connection closed.For additional information, click the fol

61、lowing article numbers to view the articles in the Microsoft Knowledge Base: </p><p>　　154628 (http://support.microsoft.com/kb/154628/) INF: SQL logs 17832 with multiple TCP\IP connection requests </p>

62、<p>　　328476 (http://support.microsoft.com/kb/328476/) TCP/IP settings for SQL Server drivers when pooling is disabled Note You may not notice the stress on TCP/IP sockets if you are running SQL Server 2000 SP3 o

63、r SQL Server 2000 SP3a because a limit on the number of login packets was added. The 17832 error occurs when you use third-party drivers to connect to the instance of SQL Server. To resolve this problem, contact the thir

64、d-party vendor and obtain drivers that have been tested to work with SQL </p><p>　　See if the instance of SQL Server is started in single-user mode</p><p>　　If the instance of SQL Server that yo

65、u are trying to connect to is started in single-user mode, only one connection can be established with SQL Server. If you have software running on your computer that automatically connects to SQL Server, the software can

66、 easily use the only connection. For example, the following software can automatically connect to the instance of SQL Server: </p><p>　　?SQL Server Agent</p><p>　　?Third-party backup software&

67、lt;/p><p>　　?Third-party monitoring software</p><p>　　?Third-party virus software</p><p>　　?Microsoft Internet Information Services (IIS)</p><p>　　?SQL Server Enterpri

68、se Manager </p><p>　　The client application that is trying to connect to the instance of SQL Server receives the following error message: </p><p>　　SQL Server does not exist or Access Denied<

69、/p><p>　　This error generally occurs during SQL Cluster Setup and service pack setup when the setup process starts the instance of SQL Server in single-user mode. The specified applications may automatically co

70、nnect to the instance of SQL Server using the only available connection, and setup is not successful.To determine if the instance of SQL Server has been started in single-user mode, check to see if the SQL Server error

71、log file has an entry that is similar to following: </p><p>　　2003-07-31 11:26:43.79 spid3 Warning ****************** 2003-07-31 11:26:43.80 spid3 SQL Server started in single user mode. Updates allowed to

72、system catalogs.</p><p>　　Verify named pipes connectivity to SQL Server</p><p>　　If you cannot connect to the instance of SQL Server by using named pipes, make sure that the instance of SQL Serv

73、er is configured to accept named pipes connections. </p><p>　　Troubleshoot connections that time out during the recovery process</p><p>　　Every time that you start an instance of SQL Server, SQL

74、 Server recovers each database. During this recovery process, SQL Server rolls back the transactions that are not committed. SQL Server also rolls forward the transactions that are committed and the changes that were not

75、 written to the hard disk when the instance of SQL Server was stopped. When the recovery process is complete, SQL Server logs the following message in the SQL Server error log file: </p><p>　　Recovery Comple

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

77、following: </p><p>　　Timeout Expired</p><p>　　The SQL Server Agent service may not start because it waits for SQL Server to recover the databases. Therefore, when you receive the following messa

78、ge in the SQL Server error log file, the connections will no longer fail with a timeout error: </p><p>　　Recovery Complete</p><p>　　If the recovery process takes a long time, you may have to add

79、itionally troubleshoot the recovery process.</p><p>　　Test different ways to connect to the instance of SQL Server</p><p>　　If you experience connectivity problems when you connect to the instan

80、ce of SQL Server, you can use one or more of the following methods to work around the connectivity problem. </p><p>　　?Test the connectivity to the instance of SQL Server by using both SQL Server Authentica

81、tion and Windows authentication.</p><p>　　?Test the connectivity to the instance of SQL Server from other data sources, such as an ODBC DSN, a .udl file, SQL Query Analyzer, SQL Server Enterprise Manager, t

82、he isql utility, or the osql utility.</p><p>　　?Test the connectivity to the instance of SQL Server by using different protocols. You can specify different protocols by creating a new alias for the instance

83、 of SQL Server using that protocol. You can also specify the protocol in your connection string by adding tcp:, np:, lpc:, or rpc: to the beginning of the name of the instance of SQL Server. For example, if TCP/IP connec

84、tions are not successful, named pipes connections succeed.</p><p>　　?Test the connectivity by using a different login account to help you determine if the problem is associated with a particular login accou

85、nt.</p><p>　　?Try to add an entry that corresponds to the IP address of the computer that is running the instance of SQL Server to the %systemroot%\system32\drivers\etc\hosts file.</p><p>　　?T

86、ry to connect to the instance of SQL Server from the computer that is running SQL Server and from the client.</p><p>　　?If you are connecting from the computer that is running SQL Server, you can specify &q

87、uot;." or "(local)" (without the quotation marks) instead of the server name and then connect.</p><p>　　?Try to connect to the instance of SQL Server by using the IP address instead of the se

88、rver name.</p><p>　　?Try to specify the specific port that the instance of SQL Server is listening on, either by creating an alias or by adding a port number to the connection string (MyServer\MyInstance, 1

89、433, for example).</p><p>　　Capture network monitor traces</p><p>　　If the connectivity problem is not resolved by the steps that are mentioned in "Test different ways to connect to the ins

90、tance of SQL Server 2000" section, use the Network Monitor utility to capture network traces. To obtain more detailed information, you may have to use SQL Profiler traces. You can also use the Network Diagnostics T

91、ool for non-clustered computers that are running SQL Server for network tracing. </p><p>　　如何解決 SQL Server 2000 中的連接問(wèn)題</p><p>　　這一篇文章能幫助你解決微軟 SQL 伺候器 2000 的連接性問(wèn)題。這一篇文章包含你能拿幫助解決你的連接性問(wèn)題的通常連接性問(wèn)題和步驟

92、的描述。</p><p>　　SQL Server的例證和客戶端申請(qǐng)之間的溝通的 SQL Server 2000 支持一些方法。如果你的客戶申請(qǐng)和SQL Server的例證在相同的計(jì)算機(jī)上住, Microsoft Windows 處理之間的溝通 (IPC) 成份（像是當(dāng)?shù)氐拿芑蛘弑环窒淼挠洃浻涗洠┯脕?lái)溝通。 然而, 當(dāng)客戶端申請(qǐng)和SQL Server的例證在不同的計(jì)算機(jī)，網(wǎng)絡(luò)IPC（像是TCP/IP上住的時(shí)候

93、或者命名管道）用來(lái)溝通。</p><p>　　SQL Sever 2000 使用網(wǎng)絡(luò)控件庫(kù)（一個(gè)DLL）與一個(gè)特別的網(wǎng)絡(luò)記錄溝通。 一雙相配網(wǎng)絡(luò)控件庫(kù)一定在客戶計(jì)算機(jī)和服務(wù)器計(jì)算機(jī)上是活躍的支援你想要使用的網(wǎng)絡(luò)記錄。 舉例來(lái)說(shuō)，如果你想要使一個(gè)客戶申請(qǐng)能夠與通過(guò)TCP/IP的一個(gè) SQL Server的特定例證溝通, 客戶TCP/IP套接字網(wǎng)絡(luò)控件庫(kù) (Dbnetlib.dll) 一定配置成在客戶計(jì)算機(jī)上對(duì)伺候器

94、連接。 同樣地, 服務(wù)器TCP/IP套接字網(wǎng)絡(luò)控件庫(kù) (Ssnetlib.dll) 一定在伺候器計(jì)算機(jī)上聽(tīng)。 在這一個(gè)情節(jié)中, TCP/IP協(xié)議堆棧一定是裝置在客戶計(jì)算機(jī)和服務(wù)器計(jì)算機(jī)。</p><p>　　在你安裝SQL Server 2000 之后，你能配置客戶端網(wǎng)絡(luò)的特性使用客戶端網(wǎng)絡(luò)公用程序的網(wǎng)絡(luò)庫(kù)屬性。你能配置服務(wù)器網(wǎng)絡(luò)的特性使用服務(wù)器網(wǎng)絡(luò)公用程序(Svrnetcn.exe)的網(wǎng)絡(luò)控件庫(kù)屬性。在安裝 S

95、QL Server 安裝程序中的服務(wù)器工具期間，服務(wù)器的網(wǎng)絡(luò)庫(kù)也被同時(shí)安裝了。然而，一些服務(wù)器的網(wǎng)絡(luò)庫(kù)可能不是激活的。如此，SQL Server 2000 啟用并偵聽(tīng) TCP/IP、命名管道和共享內(nèi)存。因此，讓一個(gè)客戶端對(duì)一部服務(wù)器計(jì)算機(jī)連接，客戶端一定使用一個(gè)客戶端網(wǎng)絡(luò)庫(kù)，該網(wǎng)絡(luò)庫(kù)匹配與SQL Server實(shí)例正在使用的服務(wù)器網(wǎng)絡(luò)庫(kù)之一。</p><p>　　對(duì)于關(guān)于SQL Server通訊部件和網(wǎng)絡(luò)庫(kù)的其它信

96、息，請(qǐng)參見(jiàn)“SQL Server 在線參考書(shū)”中的下列主題： </p><p><b>　　?通信部件</b></p><p>　　?客戶端和服務(wù)器網(wǎng)絡(luò)庫(kù)</p><p><b>　　?管理客戶端</b></p><p><b>　　解決連接問(wèn)題</b></p>

97、;<p>　　在 SQL Server 2000 中大多數(shù)連接問(wèn)題你可能會(huì)注意到的都是由 TCP/IP 的問(wèn)題或 Windows 身份認(rèn)證的問(wèn)題引起，或者兩者共同組合引起。</p><p>　　重要說(shuō)明：你開(kāi)始解決 SQL Server 2000 的連接問(wèn)題之前，確定MSSQLServer 服務(wù)已在運(yùn)行 SQL Server 的計(jì)算機(jī)上啟動(dòng)。</p><p><b>

98、;　　驗(yàn)證 DNS 設(shè)置</b></p><p>　　域名系統(tǒng) (DNS)的名稱解析過(guò)程被用于解決IP對(duì)SQL Server實(shí)例名稱。如果名稱解析程序不正確地工作，SQL服務(wù)器的實(shí)例不是可到達(dá)的，你可能接受到一條或多條下列錯(cuò)誤信息：</p><p>　　SQL Server does not exist or access denied</p><p>

99、　　General Network Error</p><p>　　Cannot Generate SSPI Context</p><p>　　為了要確認(rèn)名稱解析程序是解析正確的服務(wù)器，你能使用服務(wù)器的服務(wù)器名字和IP地址ping服務(wù)器。為此，請(qǐng)遵從如下步驟操作：</p><p>　　1.單擊“開(kāi)始”，然后單擊“運(yùn)行”。</p><p>

100、　　2.在“運(yùn)行”對(duì)話框中，在“打開(kāi)”框中鍵入 cmd，然后單擊“確定”。</p><p>　　3.在命令提示符下，運(yùn)行下列命令：</p><p>　　ping <Server Name></p><p>　　記錄返回的 IP 地址。</p><p>　　4.在命令提示符下，運(yùn)行下列命令（此處的 IP address 就是你

101、在步驟 3 中記錄的 IP 地址）：</p><p>　　ping –a <IP address></p><p>　　確認(rèn)指令跟正確的服務(wù)器名字解決。如果任一指定指令不成功、超時(shí)或者沒(méi)有返回正確的數(shù)值在兩個(gè)指定的命令，那么DNS查找未能正常工作，或者因?yàn)槠渌木W(wǎng)絡(luò)問(wèn)題引發(fā)問(wèn)題。要查看當(dāng)前DNS設(shè)定，在命令提示符下運(yùn)行下列命令：</p><p>　　ip