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

1
Troubleshooting Microsoft SQL Server 2000
Connectivity Farooq 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 015520.75 server SQL server
  listening on TCP, Shared Memory, Named
  Pipes.2002-08-20 015520.75 server SQL
  server listening on 10.10.10.101433,
  127.0.0.11433.

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, win64240, src 2236 dst 3679
• TCP .A..S., len 0, seq2080018304-2080018304,
  ack 12075288, win17520, src 3679 dst 2236
• TCP .A...., len 0, seq 12075288-12075288,
  ack2080018305, win64240, 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,
  ack2080092977, win63425, src 2237 dst 3679
• TCP .A...., len 0, seq2080092977-2080092977,
  ack 12137488, win16604, src 3679 dst 2237
• TCP .A...F, len 0, seq2080092977-2080092977,
  ack 12137488, win16604, src 3679 dst 2237
• TCP .A...., len 0, seq 12137488-12137488,
  ack2080092978, win63425, 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
  MicrosoftODBC SQL Server DriverDBNETLIBConne
  ctionWrite (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/2
  000/books.asp

46

• Thank you for joining todays 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_at_microsoft.com.