MSSQL Connections

Databases

SQL Server

MSSQL Diagnostics & Tuning

Check

Check Current Connection

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;

SELECT

c.session_id,

s.login_name,

c.connect_time,

c.client_net_address,

c.local_net_address,

c.local_tcp_port,

c.net_transport,

c.encrypt_option,

c.auth_scheme

FROM sys.dm_exec_connections AS c

JOIN sys.dm_exec_sessions AS s

ON c.session_id = s.session_id

ORDER BY c.connect_time DESC;

MSSQL Authentication

sys.dm-exec-connections

sys.dm-exec-sessions

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)

sys.dm-exec-connections

sys.dm-exec-sessions

sys.dm-exec-sql-text

sys.dm-tran-locks

Check Current Connection

SELECT c.session_id,

@@SERVERNAME,

SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsOnly,

s.login_name,

c.connect_time,

c.client_net_address,

c.local_net_address,

c.local_tcp_port,

c.net_transport,

c.encrypt_option,

c.auth_scheme

FROM sys.dm_exec_connections AS c

JOIN sys.dm_exec_sessions AS s

ON c.session_id = s.session_id

WHERE c.session_id = @@SPID;

MSSQL Authentication

sys.dm-exec-connections

sys.dm-exec-sessions

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
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-connections-transact-sqlhttps://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-sessions-transact-sqlhttps://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-sql-text-transact-sqlhttps://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-tran-locks-transact-sql