sql server pre-login troubleshooting

wireshark抓包之后，首先过滤数据库服务器的IP

ip.src==172.22.58.4 or ip.dst==172.22.58.4

找到第一条TCP握手记录之后，右键选中，Follow TCP stream

然后会自动标记筛选出，从握手到断开的所有packet数据包

tcp.stream eq 56

 

Using SQL Server’s SNITrace to Troubleshoot Networking Issues

Login Annotated

The following table contains a high-level annotation of the SQL login and a select @@VERSION from sqlcmd.

 

	No.	Source	Destination	Protocol	Length	Info
Client sends TCP open request to SQL Server (SYN) computer	9489	x.x.x.1	x.x.x.100	TCP	66	56369  >  1433 [SYN] Seq=0 Win=65280 Len=0 MSS=1360 WS=256 SACK_PERM=1
TCP acknowledges request (If port was incorrect this is where server sends RST 10054)	9490	x.x.x.100	x.x.x.1	TCP	66	1433  >  56369 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1396 WS=256 SACK_PERM=1
	9491	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [ACK] Seq=1 Ack=1 Win=262400 Len=0
SQL Client sends TDS pre-login	9492	x.x.x.1	x.x.x.100	TDS	142	TDS7 pre-login message
SQL Server responds to pre-login request	9493	x.x.x.100	x.x.x.1	TDS	102	Response
	9494	x.x.x.1	x.x.x.100	TDS	250	TDS7 pre-login message
	9495	x.x.x.100	x.x.x.1	TCP	1414	1433  >  56369 [ACK] Seq=49 Ack=285 Win=262144 Len=1360 [TCP segment of a reassembled PDU]
	9496	x.x.x.100	x.x.x.1	TCP	1414	1433  >  56369 [ACK] Seq=1409 Ack=285 Win=262144 Len=1360 [TCP segment of a reassembled PDU]
	9497	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [ACK] Seq=285 Ack=2769 Win=262400 Len=0
	9498	x.x.x.100	x.x.x.1	TCP	1414	1433  >  56369 [ACK] Seq=2769 Ack=285 Win=262144 Len=1360 [TCP segment of a reassembled PDU]
Ssl/Tls negotiation packet exchange (SQL Client InitializeSecurityContext -> SQL Server AcceptSecurityContext)	9499	x.x.x.100	x.x.x.1	TDS	70	TDS7 pre-login message (Not last buffer)
	9500	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [ACK] Seq=285 Ack=4145 Win=262400 Len=0
Note: The SQL Server can send a RST for 5 second timeouts in this exchange window.	9501	x.x.x.100	x.x.x.1	TDS	428	TDS7 pre-login message
	9502	x.x.x.1	x.x.x.100	TDS	188	TDS7 pre-login message
	9503	x.x.x.100	x.x.x.1	TDS	113	TDS7 pre-login message
End of Ssl/Tls negotiation process	9504	x.x.x.1	x.x.x.100	TDS	349	TLS exchange
	9505	x.x.x.100	x.x.x.1	TCP	54	1433  >  56369 [ACK] Seq=4578 Ack=714 Win=261632 Len=0
SQL Client sends Login information (SendLogin)	9506	x.x.x.100	x.x.x.1	TDS	800	TLS exchange
	9507	x.x.x.1	x.x.x.100	TDS	163	TLS exchange
Note: The client can send a RST to honor the login timeout during this window	9509	x.x.x.100	x.x.x.1	TDS	125	TLS exchange
	9510	x.x.x.1	x.x.x.100	TDS	147	TLS exchange
	9511	x.x.x.100	x.x.x.1	TDS	120	TLS exchange
SQL Server finishes acknowledgement of successful login after sending back config info (language, …)	9512	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [ACK] Seq=916 Ack=5461 Win=261120 Len=0
Idle keep alive, client not doing anything (sqlcmd at prompt)	9627	x.x.x.1	x.x.x.100	TCP	55	[TCP Keep-Alive] 56369  >  1433 [ACK] Seq=915 Ack=5461 Win=261120 Len=1
	9628	x.x.x.100	x.x.x.1	TCP	66	[TCP Keep-Alive ACK] 1433  >  56369 [ACK] Seq=5461 Ack=916 Win=261632 Len=0 SLE=915 SRE=916
	9629	x.x.x.100	x.x.x.1	TCP	55	[TCP Keep-Alive] 1433  >  56369 [ACK] Seq=5460 Ack=916 Win=261632 Len=1
	9630	x.x.x.1	x.x.x.100	TCP	66	[TCP Keep-Alive ACK] 56369  >  1433 [ACK] Seq=916 Ack=5461 Win=261120 Len=0 SLE=5460 SRE=5461
SQL Client sends query (select @@VERSION)	9741	x.x.x.1	x.x.x.100	TDS	149	TLS exchange
SQL Server responds with version information	9743	x.x.x.100	x.x.x.1	TDS	337	TLS exchange
	9744	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [ACK] Seq=1011 Ack=5744 Win=262400 Len=0
SQL Client sends disconnect request and closes TCP connection	9772	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [FIN, ACK] Seq=1011 Ack=5744 Win=262400 Len=0
	9773	x.x.x.100	x.x.x.1	TCP	54	1433  >  56369 [ACK] Seq=5744 Ack=1012 Win=261376 Len=0
	9774	x.x.x.100	x.x.x.1	TCP	54	1433  >  56369 [FIN, ACK] Seq=5744 Ack=1012 Win=261376 Len=0
Server acknowledges TCP closure	9775	x.x.x.1	x.x.x.100	TCP	54	56369  >  1433 [ACK] Seq=1012 Ack=5745 Win=262400 Len=0

 

SNI Tracing

Understating where the TCP Close (RST) originates provides you with diagnostic guidance.   A general rule for login is if the SQL Server sends the RST it is probably a client issue and if the SQL client provider sends the RST is it probably a SQL Server issue. 

 

Applications experience forcibly closed TLS connection errors when connecting SQL Servers in Windows

Symptoms

When an application tries to open a connection to a SQL Server, one of the following error messages is displayed:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

If you enabled SChannel logging on the Server, you'll receive Event ID 36888 (A Fatal Alert was generated) when the issue occurs.

 

抓包之后的数据可以给这里的工具分析

https://github.com/microsoft/CSS_SQL_Networking_Tools

SQLNA "mytrace-003.pcap" /output "mytrace-003.log" /sql 172.22.58.4,1433

 

How to monitor active connection pool in SQL Server?

This will give number of connections per database, per user:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

 

 

SELECT  des.program_name
      , des.login_name
      , des.host_name
      , COUNT(des.session_id) [Connections]
FROM    sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE   des.is_user_process = 1
        AND des.status != 'running'
GROUP BY des.program_name
      , des.login_name
      , des.host_name
HAVING  COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

 

 

 

 

Max Connection Pool capped at 100

SQL Server allows a maximum of 32767 connections by default. It can be altered using sp_configure. To view your current configuration for this setting, use the following query:

select * from sys.configurations
where name ='user connections'

By default, you should see a maximum of 32767, value_in_use equal to 0 (use the default settings). If this has been altered, you can reconfigure SQL Server to use other values as described in the link.

You should also review how many connections are actually being made, as there might be more activity outside your application (or your application is making more connections than you think). You will want to look at General Statistics -> Logical Connections in either perfmon or query the values in sys.dm_os_performance_counters (cntr_value will show the current point in time value):

select * from sys.dm_os_performance_counters
where counter_name ='User Connections'

作者:Chuck Lu    GitHub