1. When
to use which protocol?
·
Shared memory works only for both
server and client on the same machine. Although it cannot be used as in most
databases, it can be used for diagnosing for problems if they are related to
network or SQL Server itself.
·
Named pipe runs on the top of TCP
or other network protocols and it must pass Windows authentication to visit the
shared IPC$ on the server. If both client and SQL Server are in the same LAN,
and it can pass Windows authentication, named pipe can be used.
·
If client and server in a WAN,
and hard to pass Windows authentication, it is better to use TCP/IP. In
general, TCP/IP is recommended.
2. SQL
Server Browser
a. Background:
Prior
to SQL Server 2000, only one instance of SQL Server could be installed on a
computer. SQL Server listened for incoming requests on port 1433, assigned to
SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one
instance of SQL Server can use a port, so when SQL Server 2000 introduced
support for multiple instances of SQL Server, SQL Server Resolution Protocol
(SSRP) was developed to listen on UDP port 1434. This listener service
responded to client requests with the names of the installed instances, and the
ports or named pipes used by the instance. To resolve limitations of the SSRP
system, SQL Server 2005 introduced the SQL Server Browser service as a
replacement for SSRP.
b. How SQL Server Browser is better than SSRP? – use minimum
Account Privileges
SQL
Server Browser listens on a UDP port and accepts unauthenticated requests by
using SQL Server Resolution Protocol (SSRP). SQL Server Browser should be run
in the security context of a low privileged user to minimize exposure to a
malicious attack. The logon account can be changed by using the SQL Server
Configuration Manager. The minimum user rights for SQL Server Browser are the
following:
· Deny
access to this computer from the network
· Deny
logon locally
· Deny
Log on as a batch job
· Deny
Log On Through Terminal Services
· Log
on as a service
· Read
and write the SQL Server registry keys related to network communication (ports
and pipes)
c. Purpose:
The
SQL Server Browser program runs as a Windows service. SQL Server Browser
listens for incoming requests for Microsoft SQL Server resources and
provides information about SQL Server instances installed on the computer. SQL
Server Browser contributes to the following actions:
·
Browsing a list of available servers
·
Connecting to the correct server instance
·
Connecting to dedicated administrator
connection (DAC) endpoints
d. How SQL Server Browser Works?
When
an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled
for SQL Server, the server is assigned a TCP/IP port. If the named pipes
protocol is enabled, SQL Server listens on a specific named pipe. This port, or
"pipe," is used by that specific instance to exchange data with
client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default
instance, but those can be changed later by the server administrator using SQL
Server Configuration Manager. Because only one instance of SQL Server can use a
port or pipe, different port numbers and pipe names are assigned for named
instances, including SQL Server Express. By default, when enabled, both named
instances and SQL Server Express are configured to use dynamic ports, that is,
an available port is assigned when SQL Server starts. If you want, a specific
port can be assigned to an instance of SQL Server. When connecting, clients can
specify a specific port; but if the port is dynamically assigned, the port
number can change anytime SQL Server is restarted, so the correct port number
is unknown to the client.
Upon
startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser
reads the registry, identifies all instances of SQL Server on the computer, and
notes the ports and named pipes that they use. When a server has two or more
network cards, SQL Server Browser returns the first enabled port it encounters
for SQL Server. SQL Server Browser support ipv6 and ipv4.
When
SQL Server clients request SQL Server resources, the client network library
sends a UDP message to the server using port 1434. SQL Server Browser responds
with the TCP/IP port or named pipe of the requested instance. The network
library on the client application then completes the connection by sending a
request to the server using the port or named pipe of the desired instance.
e. What if SQL Server is not running?
If
the SQL Server Browser service is not running, you are still able to connect to
SQL Server if you provide the correct port number or named pipe. For instance,
you can connect to the default instance of SQL Server with TCP/IP if it is
running on port 1433.
However,
if the SQL Server Browser service is not running, the following connections do
not work:
·
Any
component that tries to connect to a named instance without fully specifying
all the parameters (such as the TCP/IP port or named pipe).
· Any component that generates or
passes server\instance information that could later be used by other components
to reconnect.
· Connecting to a named instance
without providing the port number or pipe.
· DAC to a named instance or the
default instance if not using TCP/IP port 1433.
· The OLAP redirector service.
·
Enumerating
servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.
If
you are using SQL Server in a client-server scenario (for example, when your
application is accessing SQL Server across a network), if you stop or disable
the SQL Server Browser service, you must assign a specific port number to each
instance and write your client application code to always use that port number.
This approach has the following problems:
·
You
must update and maintain client application code to ensure it is connecting to
the proper port.
·
The
port you choose for each instance may be used by another service or application
on the server, causing the instance of SQL Server to be unavailable.
f. Connection Problems related to SLQ Server Browser?
·
If you see “SQL Server doesn’t exist or
access denied”, the UDP 1434 may be prohibited. You need to open the port on
the firewall and gateway. You may also try a different port or piped name
to see if SQL Server can be connected.
·
SQL Server Browser itself rarely causes
problems.
· You
need to make sure that the service account for SQL Server Browser has the
permission to Read and write the SQL Server registry keys related to
network communication (ports and pipes)
(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib)
3. Which
Driver Library should a client use?
a. MDAC
i.
APIs for non .NET applications such as old
ODBC and OLEDB
ii.
Cannot use new features since SQL Server
2005, thus, for back compatibility only.
b. SQL
Server Native Client
i.
APIs for .NET applications introduced in SQL
Server 2005, supporting new features
ii.
Different versions of the native client can
co-exist
c. JDBC
– for Java applications
4. What
is the order of a client choosing the connection protocol if multiple protocols
are available?
a. Specified
in the connection string
i.
Option 1: Server = [protocol:]
Server[,port], where protocol can be tcp (TCP/IP), lpc (shared memory), or np (named
pipes)
Example
1: Server=np:myServer\myInstance
Example
2: if you specify the connection server is np:server_name in SSMS, then SSMS will only use this
named pipe for the connection.
ii.
Option 2: Network = dbmssocn
(for TCP/IP) or dbnmpntw (for Named Pipe)
Only
one of the methods can be used. If the connection failed, an error is reported.
b. Alias
– If the connection failed, an error is reported.
c. The
“LastConnect” value in the registry – if not
successful, will try the methods below
d. If
no protocol is specified in method 1, server name does not appear in the alias
or ‘LastConnect”, then the client chooses the
protocol based on the precedence of protocols, SQL Server Browser will acquire
the port or piped name dynamically.
5. Troubleshooting
the connection problem with named pipe?
a. How
named pipe works?
i.
Named pipe is based on the shared ICP$ on
the server.
ii.
SQL Server first create a named pipe on the
server and monitor it, then the client connects to this pipe.
iii.
Named pipe is named using UNC: \\server\Pipe\path_name such as \\.\pipe\sql\query for the default
instance or \\.Pipe\MSSQL$instancename\sql\query for a named instance.
iv.
How to know if SQL Server monitor the named
pipe? – check the errorlog
b. Troubleshooting
named pipe connection problems
i.
Use SQL Server Client Connection Utility to
check the setup of the named pipe and make sure SQL Server monitors it
ii.
Make sure named pipe is enabled, and make
sure the connected one or the alias is the one SQL Server is monitoring
iii.
Make sure network connection is good such as
you can PING the IP for SQL Server or the name of the SQL Server
iv.
Make sure the client can pass through the
Windows authentication for SQL Server, If there is an
error, it probably is insufficient permission on the server.
Net
view \\servername
Net
use \\servername \IPC$
v.
Make sure the login has the proper
permission on SQL Server.
c. Examples
of troubleshooting
i.
[named pipes] SQL Server does not exist or
access denied
1. Cause:
the client has not found the named pipe.
2. Solution:
(1) make sure network connection is through (PING), (2) SQL Server is started,
(3) the setup of named pipe matches on the server and client.
ii.
Login failed for user ‘NULL’ or Login
failed for user anonymous
1. Cause:
the named pipe does not have permission on the server.
2. Solution:
(1) make sure the piped name has the right on IPC$, you can use Net use \\servername \IPC$ to check.
iii.
Login failed for user ‘xxx’
1. Cause:
insufficient permission
2. Solution:
give the user sufficient permission
d. Tips
i.
Use the following script to check the
protocol used – LPC=local system
Select Net_library, hostname, program_name, nt_domain, nt_username,loginame
FROM master..sysprocesses
WHERE spid>50
ii.
Use ODBCAD32.exe to test ODBC sources
6. Troubleshooting
the connection problem with TCP/IP?
a. Two
key elements in TCP
i.
IP address – dynamically assigned
ii.
Port number: any unused one, >5000 or
<1024 is recommended
b. How
to know which ports are used by SQL Server?
Netstat
–an in DOS
c. Steps
to troubleshoot
i.
Make sure SQL Server is listening on TCP/IP
– errorlog
ii.
Make sure the TCP/IP port the server is
listening is the same as that set up on the client machine or the alias
iii.
Examine the network connectivity by PING the
IP address and Name of SQL Server
1. Make
sure you can PING SQL Server IP address
a. How
to obtain the IP address for SQL Server?
If you're running SQL Server 2005 or above you can use:
Select
client_net_address,local_net_address
from sys.dm_exec_connections
where session_id=@@SPId;
/*
client_net_address
local_net_address
169.254.161.61
169.254.161.61
*/
Limitation: You only get IP addresses if you're connective via TCP/IP. If you're local and using Shared Memory then those attributes don't
exist. If you turn off Shared Memory (or any
protocols except for TCP/IP) via Server Configuration Manager you will always
get IP address for any connection using this technique.
b. How
to PING?
PING
169.254.161.61
c. The
client and the server should be on the same network. You can run ipconfig /all
on both the client and the server to check. Or run the above script in SSMS.
2. Make
sure you can PING SQL Server name
a. PING
XU_XPS
b. If
there is a problem, try to add the IP/name pair to the hosts file under
c:\windows\system32\drivers\etc
3. Use
Telnet to check the port that SQL Server is listening on. (Telnet was disable
in Windows 8.1 by default, you need to turn it on through Windows features)
Telnet
169.254.161.61 1433
d. TIPS
i.
How to know which program is using port 1433
or 1434?
1. Download
and install ortQry.exe
2. Run
the following query:
Portqry-n
localhost –p TCP –e 1433
Portqry-n
localhost –p UDP –e 1434
ii.
Which protocol is used?
Select Net_library, hostname, program_name, nt_domain, nt_username,loginame
FROM master..sysprocesses
WHERE spid>50
iii.
How to log into SQL Server with a specific
port?
Use
TCP: ServerName, 1433 instead of ServerName
on the SSMS Connection UI
e. Troubleshooting
General Network Errors
i.
SQL Server service has been found, but
something is wrong somewhere in the process.
ii.
Check the error message to diagnose where
the error occurred.
iii.
Many possible reasons:
1. Too
much workload on the server
2. No
connection pool available on a busy server
3. Network
traffic problems between the server and some clients
4. Windows
system for its own protection, may refuse some large/busy network requests to
SQL Server. Some errors are on the server, some on the clients.
5. Inappropriate
system setting
a. Security
harden such as modification of TCP parameters in (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters)
b. Some
SQL Server setting may cause the GNEs such as sp_configure
‘priority boost’, [0-7] and sp_configure ‘lightweight
pooling’, [0-1] - 1
causes SQL Server to switch to fiber mode scheduling. The default value for
this option is 0.
6. AV and Firewall – positive false
7. Network routers or firmware causes the GNEs
iv.
Recommendations
1. Communicate with Network Administrators
2. Check SQL Server and client health
a. Check windows error log
b.
CPU usage cannot be 100% for a
long time
c.
No problems on cache and RAM
d.
No serious error in errorlog such as AV, RAM, disk, or 17883/17884
e.
SQL Server cannot have blocks
over 100.
f. SQL Server should not have many runnable and running processes
Select *
FROM master..sysprocesses
WHERE spid>50
3. Best practices
a. Set EnableRSS in HKey
to 0
b.
Set DisableTaskOff
to 1
c.
Disable TCP Chimney
d.
Set TCPA in HKey
to 0
e.
Restart the machine
f.
Do not change priority boost and
lightweight pooling
g.
Do not change the default values
under theHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
h.
Do not use teaming network card
i.
Make sure the automatic closing
function for the idle status of the network equipment is disabled.
j.
Temporarily disable AV and
Firewall
k.
Make the server and client
machine physically closer if possible
l. Change TCP/IP to a named pipe or vice versa
7. Authentication and Delegation
a. Typical errors:
i.
Illegal login – Error 18456
|
ERROR STATE |
ERROR DESCRIPTION |
|
State 2 and State 5 |
Invalid userid |
|
State 6 |
Attempt to use a
Windows login name with SQL Authentication |
|
State 7 |
Login disabled and
password mismatch |
|
State 8 |
Password mismatch |
|
State 9 |
Invalid password |
|
State 11 and State 12
|
Valid login but
server access failure |
|
State 13 |
SQL Server service
paused |
|
State 18 |
Change password
required |
ii.
Authentication error – rarely on
SQL Server login, mostly on Windows Login
b. SQL Server Authentication
i.
SQL Server does the validation
and rarely has problems
ii.
SQL Server 2005/Windows Server
2003+ make the password management almost as complex as the Windows system.
c. Windows authentication
i.
Two techniques: NTLM and Kerberos
ii.
Which one to use is not
determined by the client, neither the server. Windows
determines which technique to use based on the value returned from the
authentication API.
iii.
NTLM is a simple one and does on
hop authentication between a client and a server.
iv.
Double hop or multi hop
authentication needs Kerberos authentication, which is much complicated than
NTLM
d. Delegation
i.
When authenticating many machines,
delegation may be needed in the middle machine.
ii.
The setup of delegation is more than
Kerberos.
