SQL Server 2000/2005迁移至2014：无权限下获取应用连接信息方法咨询

Hey Rahul, sorry to hear the SQL Server Profiler caused such a headache on your hosted server—totally get why that approach’s off-limits now. Let’s dive into some lighter, more efficient methods to track down all the apps connecting to your databases, using either SQL queries or low-impact monitoring tools:

These rely on built-in system views/tables, so they’re lightweight and don’t consume extra disk space like Profiler does.

For SQL Server 2005+ (Works on Your Source 2005 and Target 2014) #

Use Dynamic Management Views (DMVs) to pull real-time connection details. Run this query to get client IPs, app names, login accounts, and connection times:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name AS client_machine_name,
    s.program_name AS application_name,
    c.client_net_address AS client_ip_address,
    c.connect_time,
    s.status AS connection_status
FROM 
    sys.dm_exec_sessions s
INNER JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE 
    s.is_user_process = 1; -- Filter out system processes to focus on app connections

The program_name field is especially useful—it’s usually set by the application (e.g., "Microsoft SQL Server Management Studio" or your custom app’s name) to identify itself.

For SQL Server 2000 #

2000 doesn’t have DMVs, so use the legacy sys.sysprocesses table:

SELECT 
    spid AS session_id,
    loginame AS login_name,
    hostname AS client_machine_name,
    program_name AS application_name,
    net_address AS client_mac_address,
    login_time AS connection_time
FROM 
    sys.sysprocesses
WHERE 
    status IN ('runnable', 'sleeping'); -- Capture active and idle user connections

Here, net_address gives you the client’s MAC address, which you can cross-reference with network logs to pinpoint the app server.

If your hosted environment runs SQL Server 2008 or later (your 2005 source is out of luck here, but maybe your provider can enable this temporarily), Extended Events are way more resource-efficient than Profiler. You can create a minimal session to track login events:

-- Create the event session
CREATE EVENT SESSION [AppConnectionTracker] ON SERVER 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_ip, sqlserver.login_name))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\AppConnections.xel') -- Pick a low-storage location
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS);

-- Start the session
ALTER EVENT SESSION [AppConnectionTracker] ON SERVER STATE = START;

After letting it run for a while (to capture all app connections), query the event file to get your data:

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_type,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(100)') AS app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS client_machine,
    event_data.value('(event/action[@name="client_ip"]/value)[1]', 'varchar(50)') AS client_ip,
    event_data.value('(event/action[@name="login_name"]/value)[1]', 'varchar(100)') AS db_login,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS connection_time
FROM 
    (SELECT CAST(event_data AS XML) AS event_data
     FROM sys.fn_xe_file_target_read_file('C:\Temp\AppConnections*.xel', NULL, NULL, NULL)) AS event_results;

Just make sure to clean up the session and event file once you’re done to avoid any storage issues.

If you can get access to run basic command-line tools on the hosted server (or have network visibility), use netstat to track connections to SQL Server’s default port (1433):

netstat -ano | findstr ":1433"

This will show you all active connections to port 1433, including the process ID (PID) of the server-side process. To map the PID to the actual app, run:

tasklist /fi "PID eq [your_pid_here]"

Note: You might need admin rights for this, but it’s far less resource-heavy than Profiler.

Quick Notes #

• Always check with your hosting provider before running any of these—some restrict access to system views or command-line tools.
• For SQL Server 2000, run the sys.sysprocesses query multiple times over a few hours to capture all apps that connect periodically.

内容的提问来源于stack exchange，提问作者Rahul