1 / 46

Troubleshooting Microsoft SQL Server 2000 Connectivity Farooq Mahmud Suresh Channamraju Support Professionals Develope

Troubleshooting Microsoft SQL Server 2000 Connectivity Farooq Mahmud Suresh Channamraju Support Professionals Developer Support Microsoft Corporation. Overview. Client-server communication basics Net-Libraries TCP/IP communications Troubleshooting connectivity References.

damali
Download Presentation

Troubleshooting Microsoft SQL Server 2000 Connectivity Farooq Mahmud Suresh Channamraju Support Professionals Develope

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Troubleshooting Microsoft SQL Server 2000 ConnectivityFarooq Mahmud Suresh Channamraju Support ProfessionalsDeveloper SupportMicrosoft Corporation

  2. Overview • Client-server communication basics • Net-Libraries • TCP/IP communications • Troubleshooting connectivity • References

  3. Client-Server Communication Basics • Communication components • Client-server communication overview

  4. Components • Provider • Driver • IPC • Net-Library

  5. Client-Server Communication Overview

  6. Net-Libraries • Net-Libraries defined • Net-Libraries in-depth • Configuring Net-Libraries • Aliases

  7. What Is a Net-Library? • Net-Libraries shield data providers from the details of communicating with different IPC components • Net-Libraries are present on the client side and server side

  8. Net-Libraries In-depth • Client-side Net-Library (Dbnetlib.dll) • Server-side Net-Library (Ssnetlib.dll) • TCP/IP and NWLink IPX/SPX work directly with the network • Other protocols go through a Net-Library router (intermediate DLL) • Named pipes: Dbnmpntw.dll, Ssnmpn70.dll • VIA Giganet SAN: Dbmsgnet.dll, Dbmsgnet.dll • Multiprotocol: Dbmsrpcn.dll, Ssmsrpc.dll

  9. Net-Libraries In-depth (2)

  10. Configuring Net-Libraries • Microsoft® SQL Server™ network utility • Microsoft SQL Server client network utility

  11. Server Network Utility • Used to configure server-side Net-Libraries • SQL Server can listen on multiple Net-Libraries • By default, located in: C:\Program Files\Microsoft SQL Server\ 80\Tools\Binn\svrnetcn.exe • Books Online topic: “SQL Server Network Utility”

  12. Server Network Utility (2)

  13. Client Network Utility • Used to configure client-side Net-Libraries • To open, click Start, point to Run, and type Cliconfg.exe • Interface varies depending on the MDAC version • SQL Server client tools are not required to run the Client Network Utility

  14. Client Network Utility(2)

  15. Aliases • Forces a client to connect to a SQL Server through a specific Net-Library or server name • Overrides the Net-Library configured in the General tab of the Client Network Utility • Useful in troubleshooting connectivity issues

  16. Aliases (2)

  17. TCP/IP Communications • TCP/IP communication basics • Connecting to the default instance of SQL Server 2000 • Connecting to the named instance of SQL Server 2000

  18. TCP/IP Communication Basics • Client and server communicate using ports (TCP or UDP) • The server listens on an IP address and port for the incoming request • The client connects to the IP address and port that the server is listening on • After it is connected, exchange of information can start

  19. Connecting to Default Instance of SQL Server 2000 • By default, SQL Server listens on TCP port 1433 for connection requests • This port is configured using the SQL Server Network Utility • The SQL Server error log can be used to verify the port and Net-Libraries that SQL Server is listening on 2002-08-20 01:55:20.75 server SQL server listening on TCP, Shared Memory, Named Pipes.2002-08-20 01:55:20.75 server SQL server listening on 10.10.10.10:1433, 127.0.0.1:1433.

  20. Connecting to Named Instance of SQL Server 2000 • By default, a named instance will listen on a dynamically assigned port on startup • Can be configured to listen on a static port using the Server Network Utility • More information about dynamic port assignment in KB article 286303

  21. Connecting to a Named Instance of SQL Server 2000(2)

  22. Connecting to Named Instance of SQL Server 2000 (3) • MDAC 2.6 clients and later versions can connect to a named instance listening on a dynamic port • Do the following to allow pre MDAC 2.6 clients to connect to a named instance • Configure named instance to listen on a static port • Create a TCP/IP alias on the client • Specify the server name and the port that the named instance is listening on in the alias properties

  23. Troubleshooting Connectivity • Narrowing down the issue • Troubleshooting connectivity checklist • Troubleshooting tools • Network Monitor • Common connectivity errors • Firewall considerations

  24. Narrowing Down the Issue • Understanding the scope of the issue helps • Some questions to ask: • What is the exact error message? • Is it an issue with local or remote connections? • Are all or some clients having connection issues? • Is the issue specific to a certain Net-Library or authentication method? • After the issue is narrowed down, use the available resources to resolve the issue

  25. Troubleshooting Connectivity Checklist • Is the SQL Server service running? • Is the error reproducible at will or is it an intermittent problem? • What Net-Libraries are configured on the client and server? • Are you able to connect using Query Analyzer, osql, isql, an ODBC DSN, or a UDL file? • Did the problems coincide with an upgrade of MDAC components on the client or SQL Server? • What is the SQL Server version and service pack? • What is the MDAC version and service pack? • What type of network is between the client and the server? • Are there any firewalls between the client and SQL Server? • Is the network reliable or is there a history of outages and slow transmission speeds? • Can you connect using the IP address, but not the name?

  26. Troubleshooting Tools • Basic troubleshooting tools • Ping, Netstat, Tracert, Portqry, Odbcping • HOSTS and LMHOSTS files • Microsoft Windows® event logs • SQL Server Client Tools • ODBC Data Sources (ODBC DSNs) and Data Link files (UDL files) • SQL Server error logs • In-depth troubleshooting tools • SQL Profiler • Network Monitor • Books Online and Microsoft Knowledge Base

  27. Basic Troubleshooting Tools • Ping • Can ping by IP address or name • Can help determine if basic communication between the client and server can be made • Can help determine name resolution issues (ping -a) • Netstat • Use with -an to list ports being used on a SQL Server • Can help troubleshoot port binding issues • Portqry • Similar to netstat -an, but it can sometimes tell you what process is using the port • 310099, “Description of the Portqry.exe Command-Line Utility” • Tracert • Determine the path that a network packet takes to communicate between a SQL Server and client • HOSTS and LMHOSTS files • Add entries to determine if there are name resolution issues

  28. Basic Troubleshooting Tools (2) • SQL Server error logs can tell you: • The Net-Libraries, IP addresses, and ports that SQL Server is listening on • The port bind failures • Other connectivity errors • Windows event logs

  29. Basic Troubleshooting Tools (3) • SQL Server Client Tools • Query Analyzer • Osql.exe • Isql.exe • Error message can be different depending on the tool used • Odbcping.exe • Can be used to verify connectivity to SQL Server through ODBC Driver • 138541, “HOW TO: Odbcping.exe to Verify ODBC Connectivity to SQL Server”

  30. Basic Troubleshooting Tools (4) • ODBC data sources (ODBC DSNs) • Test ODBC connectivity over different Net-Libraries • Configured through the ODBC Data Sources tool in Control Panel • Data link files (UDL files) • Test OLE DB connectivity over different Net-Libraries • Create a file with a .udl extension, and double-click to configure • These tools are useful when SQL Server Client Tools are not installed or available

  31. In-depth Troubleshooting Tools • Network Monitor • View packets being sent on a network • For more advanced troubleshooting • Used in conjunction with the basic tools • SQL Profiler • Traces activity on a SQL Server • Determine if a connection can be made to the SQL Server

  32. Network Monitor • Successful connect • Successful disconnect • Network Monitor tips

  33. Successful Connect • Client makes a three-way handshake connection when connecting over TCP/IP TCP: ....S., len: 0, seq: 12075287-12075287, ack: 0, win:64240, src: 2236 dst: 3679 TCP .A..S., len: 0, seq:2080018304-2080018304, ack: 12075288, win:17520, src: 3679 dst: 2236 TCP: .A...., len: 0, seq: 12075288-12075288, ack:2080018305, win:64240, src: 2236 dst: 3679 • Client communicates on port 2236, and sends packet to port 3679 on the SQL Server • SQL Server responds with a SYN packet • Client responds with another ACK packet to complete the three-way handshake

  34. Successful Disconnect • Client sends a FIN packet to the server to disconnect TCP: .A...F, len: 0, seq: 12137487-12137487, ack:2080092977, win:63425, src: 2237 dst: 3679 TCP: .A...., len: 0, seq:2080092977-2080092977, ack: 12137488, win:16604, src: 3679 dst: 2237 TCP .A...F, len: 0, seq:2080092977-2080092977, ack: 12137488, win:16604, src: 3679 dst: 2237 TCP: .A...., len: 0, seq: 12137488-12137488, ack:2080092978, win:63425, src: 2237 dst: 3679

  35. Network Monitor Tips • Capture end-to-end traces • Capture traces between the client and SQL Server • Synchronize time between the machines • Router and firewall considerations • Set the capture buffer size to a higher value when tracing for extended periods of time • Traces are best analyzed by a network administrator

  36. Common Connectivity Errors • Initial troubleshooting steps • “SQL Server does not exist, or access denied” • “Cannot generate SSPI context” • “General network error”

  37. Initial Troubleshooting Steps • Connect with Windows and SQL Server Authentication if possible • Use aliases to test connectivity over different Net-Libraries (locally and remotely) • Connect using an ODBC DSN or data link file • Search for the error in the Microsoft Knowledge Base and SQL Server Books Online

  38. “SQL Server does not exist, or access denied” • SQL Server cannot be contacted • Specified SQL Server may not be started • Specified SQL Server may not be listening on the protocol or port that the client is using to connect • Possible name resolution issues • This error does not indicate a log on failure

  39. “Cannot generate SSPI context” • Background • Troubleshooting “Cannot generate SSPI context”

  40. Background • SSPI stands for Security Support Provider Interface • Allows user tokens to be transmitted securely between client and server • All Net-Libraries except named pipes use SSPI • Service Principle Name (SPN)

  41. Troubleshooting “Cannot generate SSPI context” • Trusted connections fail, but not SQL Server authenticated connections • Connections over named pipes succeed • See if there are name resolution issues • Make sure SPN is configured for the SQL Server service startup account • List, add, and remove SPNs on a server using the Setspn tool

  42. “General Network Error” • Typically indicates a problem at the network layer • Typically, an additional error is returned to the client, such as: SQLState = 01000, NativeError = 10054 Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()) • Try copying a large file between the client and server to test network • Get a Network Monitor trace

  43. Firewall Considerations • For TCP/IP connectivity through a firewall, open the correct ports • Connecting to a default instance of SQL Server • Open TCP Port 1433 from client to SQL Server • Open ports above 1024 from SQL Server to client (TCP/IP requirement) • Connecting to a named instance • Instance must listen on a static TCP/IP port • Open that port going from client to SQL Server • Open ports above 1024 from SQL Server to client

  44. Firewall Considerations (2)

  45. References • Network Monitor references • 294818, “Frequently Asked Questions About Network Monitor” • 148942, “How to Capture Network Traffic with Network Monitor” • Support WebCasts • Port Scanning Using PortQry • Connectivity • 328306, “INF: Potential Causes of the "SQL Server Does Not Exist or Access Denied” • 169292, “The Basics of Reading TCP/IP Traces” • 287932, “INF: TCP Ports Needed for Communication to SQL Server Through a Firewall” • SQL Server Books Online • http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

  46. Thank you for joining today’s Microsoft Support WebCast. For information about all upcoming Support WebCasts, and access to the archived content (streaming media files, PowerPoint® slides, and transcripts), visit: http://support.microsoft.com/webcasts/ Your feedback is sincerely appreciated. Please send any comments or suggestions about the Support WebCasts to supweb@microsoft.com.

More Related