MSSQL Connections

Databases

SQL Server

MSSQL Diagnostics & Tuning

Check

Check Connection Protocol

Troubleshooting

Auditing

Bibliography

Check

exec sp_who

exec sp_who2

SELECT DB_NAME(dbid) AS DBName,

COUNT(dbid) AS NumberOfConnections,

loginame AS LoginName

FROM sys.sysprocesses

WHERE dbid > 0

GROUP BY dbid,

loginame;

DECLARE @dbname SYSNAME =NULLSELECT sdes.session_id , sdes.login_time , sdes.last_request_start_time , sdes.last_request_end_time , sdes.is_user_process , sdes.host_name , sdes.program_name , sdes.login_name , sdes.status , sdec.num_reads , sdec.num_writes , sdec.last_read , sdec.last_write , sdes.reads , sdes.logical_reads , sdes.writes , DatabaseName = COALESCE( db_name(sdes.database_id), N'') , sdest.ObjName , sdes.client_interface_name , sdes.nt_domain ,sdes.nt_user_name , sdec.client_net_address , sdec.local_net_address , sdest.Query , KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR) FROM sys.dm_tran_locks t INNER JOIN sys.dm_exec_sessions sdes ON t.request_session_id = sdes.session_id LEFT OUTER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id OUTER APPLY ( SELECT DB_NAME(dbid) AS DatabaseName , OBJECT_NAME(objectid) AS ObjName , COALESCE(( SELECT TEXT AS [processing-instruction(definition)] FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) FOR XML PATH('') ,TYPE ), '') AS Query FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle) ) sdest WHERE t.resource_type = 'database' AND t.resource_database_id = CASE WHEN @dbname IS NULL THEN t.resource_database_id ELSE DB_ID(@dbname) END AND t.request_type = 'LOCK' AND t.request_status = 'GRANT'Cribbed from: Marcello Miorelli (https://stackoverflow.com/questions/1248423/how-do-i-see-active-sql-server-connections)

Check Connection Protocol

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

Troubleshooting

Testing a connection without installing any SQL tools...

Create an empty text file and give it a .udl extension
Double-click the file and the window shown on the right should appear.
Enter the login information you want to test

If you use "Windows NT Integrated security" it will attempt to login to the target SQL Server as the use you are currently logged in as.

Select a database
Click "Test Connection"

Auditing

TODO

Bibliography

https://sqlstudies.com/2020/04/08/has-this-login-been-used-recently/ https://eitanblumin.com/2020/03/09/finding-details-missing-sql-server-failed-logins-audit/ https://docs.microsoft.com/en-gb/archive/blogs/steverac/test-remote-sql-connectivity-easilyhttps://dba.stackexchange.com/questions/53279/how-do-i-troubleshoot-login-failed-errors-in-sql-serverhttps://learn.microsoft.com/en-us/sql/tools/configuration-manager/creating-a-valid-connection-string-using-shared-memory-protocolhttps://learn.microsoft.com/en-us/sql/tools/configuration-manager/creating-a-valid-connection-string-using-tcp-iphttps://stackoverflow.com/questions/1248423/how-do-i-see-active-sql-server-connectionshttps://serverfault.com/questions/229731/is-there-a-connection-history-for-sql-server