How to Identify Applications Connecting to SQL Server Before Patching

Introduction

When preparing to patch a SQL Server environment, one of the most important questions is often one of the hardest to answer:

Which applications, servers, users and services actually depend on this SQL Server?

In well-documented environments, the answer may already exist in a CMDB, application register or architecture diagram.

In many real-world environments, however, application dependencies have accumulated over years, ownership has changed, documentation is incomplete, and nobody has a completely reliable view of what connects to each SQL Server instance.

This creates a significant risk during Windows Server and SQL Server patching.

If an unknown application depends on a database being patched, the technical patch may complete successfully while the business still experiences an outage because an application was not tested, restarted or validated.

One practical method for improving application dependency discovery is to capture active SQL Server connections at regular intervals and build a historical record.

In this article, we show how to:

• identify applications connecting to SQL Server;

• capture client hosts, application names, users and IP addresses;

• store connection information in a SQL Server table;

• schedule the collection automatically using SQL Server Agent;

• analyse the results to build an application dependency inventory;

• understand the limitations of connection sampling;

• improve the process for production patching.

Why Application Dependency Discovery Matters Before SQL Server Patching

A SQL Server patching exercise typically affects more than the SQL Server itself.

A typical dependency chain may look like:

Business Application → Application Server → SQL Server → Database

There may also be additional dependencies such as:

Reporting Server → SQL Server

ETL Server → SQL Server

Integration Platform → SQL Server

Scheduled Batch Job → SQL Server

Power BI Gateway → SQL Server

Linked SQL Server → SQL Server

Third-Party Vendor Application → SQL Server

Before patching, the infrastructure or database team ideally needs to know:

• which applications use the SQL Server;

• which databases they use;

• which servers the applications run from;

• which service accounts or users connect;

• which application owners need to be notified;

• what needs to be tested after patching;

• whether services need to be restarted;

• whether the application supports temporary SQL Server unavailability;

• whether HA or failover mechanisms need to be tested.

Without this information, patching becomes significantly harder to coordinate.

Using SQL Server DMVs to Discover Application Connections

SQL Server exposes active session and connection information through Dynamic Management Views.

Two particularly useful views are:

• sys.dm_exec_sessions

• sys.dm_exec_connections

Microsoft documents sys.dm_exec_sessions as returning one row for each authenticated SQL Server session. It includes information such as login time, client program, hostname and login identity.

sys.dm_exec_connections provides connection-level information including the client network address and can be joined to sys.dm_exec_sessions using the session ID.

Together, these DMVs can provide a useful snapshot of the applications currently using a SQL Server.

Basic SQL Server Connection Discovery Query

A useful starting query is:

SELECT 
    GETDATE() AS CaptureTime, 
    @@SERVERNAME AS SQLServer, 
    DB_NAME(s.database_id) AS DatabaseName, 
    s.login_name, 
    s.host_name, 
    s.program_name, 
    c.client_net_address AS ClientIPAddress, 
    s.login_time, 
    s.session_id 
FROM sys.dm_exec_sessions s 
LEFT JOIN sys.dm_exec_connections c 
    ON s.session_id = c.session_id 
WHERE s.is_user_process = 1 
ORDER BY s.login_time DESC;

This provides information similar to:

This information can provide valuable clues about application dependencies.

Why a Single Snapshot Is Not Enough

Running the query once gives only a view of connections that exist at that particular moment.

This creates a major limitation.

Consider an application that connects to SQL Server at:

02:05 AM

runs a batch for:

45 seconds

and then disconnects.

If the DBA checks active connections at 10:00 AM, that application will not appear.

The same problem applies to:

• overnight processing;

• scheduled interfaces;

• payroll runs;

• weekly reports;

• ETL jobs;

• scheduled integrations;

• month-end processes;

• batch applications;

• vendor applications that connect periodically.

For this reason, connection data should ideally be collected repeatedly over an observation period.

Capturing SQL Server Connections Every 30 Minutes

One simple approach is to schedule the DMV query through SQL Server Agent.

For example:

Every 30 minutes → collect active sessions → insert into historical table

Over several days, this builds an increasingly useful picture of the SQL Server's application ecosystem.

Microsoft describes SQL Server Agent as the service used to execute scheduled administrative jobs and recurring tasks. Jobs can contain T-SQL steps and can be run according to defined schedules.

Step 1 – Create a Connection History Table

The following table can be created in the master database.

USE [master];
GO

IF OBJECT_ID('dbo.SQLConnectionHistory', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.SQLConnectionHistory
    (
        CaptureID        bigint IDENTITY(1,1) NOT NULL
            CONSTRAINT PK_SQLConnectionHistory PRIMARY KEY,

        CaptureTime      datetime2(0) NOT NULL,
        SQLServer        sysname NULL,
        DatabaseName     sysname NULL,
        LoginName        nvarchar(256) NULL,
        HostName         nvarchar(256) NULL,
        ProgramName      nvarchar(512) NULL,
        ClientIPAddress  varchar(48) NULL,
        LoginTime        datetime NULL,
        SessionID        int NULL
    );
END;
GO

This table provides a persistent history rather than relying on transient DMV information.

Step 2 – Add Useful Indexes

If the monitoring runs for several weeks, the table can accumulate a significant number of records.

Indexes can make reporting easier.

CREATE INDEX IX_SQLConnectionHistory_CaptureTime
ON master.dbo.SQLConnectionHistory (CaptureTime);
GO

CREATE INDEX IX_SQLConnectionHistory_Database_Host
ON master.dbo.SQLConnectionHistory
(
    DatabaseName,
    HostName,
    LoginName
);
GO

The exact indexes should be adjusted depending on how the table will be queried.

Step 3 – Insert the Current Connection Snapshot

Before configuring SQL Server Agent, test the collection manually.

USE [master];
GO

INSERT INTO dbo.SQLConnectionHistory
(
    CaptureTime,
    SQLServer,
    DatabaseName,
    LoginName,
    HostName,
    ProgramName,
    ClientIPAddress,
    LoginTime,
    SessionID
)
SELECT
    GETDATE(),
    @@SERVERNAME,
    DB_NAME(s.database_id),
    s.login_name,
    s.host_name,
    s.program_name,
    c.client_net_address,
    s.login_time,
    s.session_id
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
WHERE s.is_user_process = 1;
GO

Verify that information has been collected:

SELECT TOP (100)
    *
FROM master.dbo.SQLConnectionHistory
ORDER BY CaptureTime DESC;

Step 4 – Create the SQL Server Agent Job

Create a dedicated Agent job:

USE [msdb];
GO

EXEC dbo.sp_add_job
    @job_name = N'Capture SQL Application Connections',
    @enabled = 1,
    @description = N'Captures SQL Server user connections every 30 minutes for application dependency discovery.',
    @category_name = N'Database Maintenance';
GO

Microsoft supports creating SQL Server Agent jobs using SQL Server Management Studio, T-SQL or SQL Server Management Objects.

Step 5 – Add the Capture Job Step

Create the T-SQL job step:

USE [msdb];
GO

EXEC dbo.sp_add_jobstep
    @job_name = N'Capture SQL Application Connections',
    @step_name = N'Capture Active Connections',
    @subsystem = N'TSQL',
    @database_name = N'master',
    @command = N'
INSERT INTO dbo.SQLConnectionHistory
(
    CaptureTime,
    SQLServer,
    DatabaseName,
    LoginName,
    HostName,
    ProgramName,
    ClientIPAddress,
    LoginTime,
    SessionID
)
SELECT
    GETDATE(),
    @@SERVERNAME,
    DB_NAME(s.database_id),
    s.login_name,
    s.host_name,
    s.program_name,
    c.client_net_address,
    s.login_time,
    s.session_id
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
WHERE s.is_user_process = 1;
',
    @retry_attempts = 3,
    @retry_interval = 1;
GO

SQL Server Agent supports T-SQL job steps for performing automated administrative operations.

Step 6 – Schedule the Job Every 30 Minutes

Create a schedule:

USE [msdb];
GO

EXEC dbo.sp_add_schedule
    @schedule_name = N'Every 30 Minutes - Connection Capture',
    @enabled = 1,
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 4,
    @freq_subday_interval = 30,
    @active_start_time = 000000;
GO

Attach it to the job:

EXEC dbo.sp_attach_schedule
    @job_name = N'Capture SQL Application Connections',
    @schedule_name = N'Every 30 Minutes - Connection Capture';
GO

Add the job to the local SQL Server:

EXEC dbo.sp_add_jobserver
    @job_name = N'Capture SQL Application Connections',
    @server_name = N'(LOCAL)';
GO

SQL Server Agent schedules define when a job executes and can be configured using SSMS, T-SQL or SMO.

Step 7 – Test the SQL Server Agent Job

Run the job manually:

USE [msdb];
GO

EXEC dbo.sp_start_job
    @job_name = N'Capture SQL Application Connections';
GO

Then confirm that rows have been captured:

SELECT TOP (100)
    CaptureID,
    CaptureTime,
    SQLServer,
    DatabaseName,
    LoginName,
    HostName,
    ProgramName,
    ClientIPAddress,
    LoginTime,
    SessionID
FROM master.dbo.SQLConnectionHistory
ORDER BY CaptureID DESC;

Converting the Raw Data into an Application Dependency Report

After the job has been running for several days, thousands of individual observations may exist.

The objective is not simply to produce a large table.

The objective is to transform the data into a useful dependency inventory.

Use:

SELECT
    SQLServer,
    DatabaseName,
    HostName,
    ClientIPAddress,
    ProgramName,
    LoginName,
    MIN(CaptureTime) AS FirstSeen,
    MAX(CaptureTime) AS LastSeen,
    COUNT(*) AS NumberOfObservations,
    MIN(LoginTime) AS EarliestLoginTime,
    MAX(LoginTime) AS LatestLoginTime
FROM master.dbo.SQLConnectionHistory
GROUP BY
    SQLServer,
    DatabaseName,
    HostName,
    ClientIPAddress,
    ProgramName,
    LoginName
ORDER BY
    DatabaseName,
    HostName,
    ProgramName,
    LoginName;

This can reveal patterns such as:

This information can then be correlated with application owners.

Understanding the program_name Column

program_name is extremely useful, but it should not be treated as authoritative.

Useful values might include:

• Microsoft SQL Server Management Studio;

• Power BI;

• SAP BusinessObjects;

• Tableau;

• application-specific product names.

However, many applications return generic names such as:

• .NET SqlClient Data Provider;

• Microsoft JDBC Driver for SQL Server;

• ODBC;

• sqlcmd;

• generic Java clients.

In these cases, the combination of:

HostName + ClientIPAddress + LoginName + DatabaseName

is usually much more useful than the application name alone.

For example:

Database: FinanceDB
Host: APPFIN01
IP: 10.21.5.33
Program: .NET SqlClient Data Provider
Login: DOMAIN\svc_finance

Although the application name is generic, the hostname and service account strongly indicate where the application resides.

Finding Generic or Unknown Applications

The following query can help identify connections requiring further investigation:

SELECT
    SQLServer,
    DatabaseName,
    HostName,
    ClientIPAddress,
    ProgramName,
    LoginName,
    MIN(CaptureTime) AS FirstSeen,
    MAX(CaptureTime) AS LastSeen,
    COUNT(*) AS Observations
FROM master.dbo.SQLConnectionHistory
WHERE
       ProgramName IS NULL
    OR ProgramName = ''
    OR ProgramName LIKE '%SqlClient%'
    OR ProgramName LIKE '%JDBC%'
    OR ProgramName LIKE '%ODBC%'
GROUP BY
    SQLServer,
    DatabaseName,
    HostName,
    ClientIPAddress,
    ProgramName,
    LoginName
ORDER BY Observations DESC;

These entries can then be investigated with the server and application teams.

Using Service Accounts to Identify Applications

Service accounts are frequently one of the best clues available.

For example:

DOMAIN\svc_crm
DOMAIN\svc_payroll
DOMAIN\svc_finance
DOMAIN\svc_datawarehouse
DOMAIN\svc_powerbi

may immediately identify the associated system.

Service account information should therefore be correlated with:

• Active Directory;

• server ownership;

• Windows services;

• IIS application pools;

• scheduled tasks;

• application configuration;

• CMDB records.

Important Limitation: 30-Minute Sampling Can Miss Connections

A scheduled snapshot is useful, but it is important to understand its limitation.

Suppose an application connects at:

10:05

executes its workload for two minutes, and disconnects at:

10:07

while the SQL Agent job executes at:

10:00
10:30

That application will never appear in the captured data.

Therefore, a 30-minute sampling process should be considered:

application dependency discovery assistance

rather than:

absolute proof of every application that has ever connected to the server.

For highly critical environments, Extended Events or other continuous auditing techniques can be used to complement the snapshot approach.

How Long Should You Run the Collection?

For most environments:

Minimum

7 days

This should capture normal weekday and weekend behaviour.

Better

14 days

This provides greater confidence and is useful for weekly batch processes.

Ideal for Complex Environments

30 days

This may capture:

• month-end processing;

• monthly reports;

• periodic interfaces;

• infrequently used applications;

• scheduled financial workloads.

Even 30 days, however, cannot guarantee discovery of every application.

Some systems may connect only quarterly or annually.

Additional SQL Server Dependency Checks

Connection monitoring should be only one component of the discovery process.

For a more complete patching dependency assessment, DBAs should also review the following.

SQL Server Agent Jobs

Check:

SELECT
    j.name AS JobName,
    s.step_id,
    s.step_name,
    s.subsystem,
    s.database_name,
    s.command,
    SUSER_SNAME(j.owner_sid) AS JobOwner
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps s
    ON j.job_id = s.job_id
ORDER BY j.name, s.step_id;

Agent jobs can reveal:

• batch processing;

• ETL;

• data exports;

• integration processes;

• reporting jobs;

• vendor applications;

• PowerShell scripts;

• SSIS packages.

Linked Servers

Check for SQL Server-to-SQL Server or SQL Server-to-external-system dependencies.

SELECT
    name,
    product,
    provider,
    data_source,
    is_linked
FROM sys.servers
WHERE is_linked = 1;

A database server being patched may itself be depended upon by another database server.

SSIS and ETL Workloads

If SQL Server Integration Services is used, review:

• SSISDB;

• SSIS packages;

• package connection managers;

• SQL Agent SSIS job steps;

• file-share dependencies;

• APIs;

• Oracle connections;

• external databases.

These workloads may run only occasionally and may not appear during a short observation period.

Reporting Platforms

Investigate dependencies such as:

• Power BI gateways;

• SSRS;

• Tableau;

• Qlik;

• SAP BusinessObjects;

• custom reporting platforms.

Reporting workloads may also occur only during business hours or scheduled reporting windows.

Building the Final SQL Server Application Dependency Register

The technical information should eventually become a structured application register.

A useful format is:

Additional fields may include:

• technical owner;

• vendor contact;

• outage tolerance;

• restart requirement;

• HA configuration;

• test procedure;

• validation contact;

• maintenance window;

• rollback requirement.

A Better SQL Server Patching Workflow

Once the application dependencies are understood, the patching workflow becomes significantly safer.

Before patching

Identify:

• SQL Server instances;

• databases;

• application dependencies;

• application owners;

• business owners;

• support teams;

• HA/DR configuration;

• backup status;

• patch prerequisites;

• testing procedures.

During patching

Monitor:

• SQL Server services;

• Windows services;

• Always On availability groups;

• failover cluster status;

• SQL Server Agent;

• database status;

• application connections.

After patching

Validate:

• SQL Server version/build;

• database availability;

• AG synchronisation;

• SQL Agent jobs;

• application connectivity;

• scheduled integrations;

• reporting;

• business application functionality.

Should You Store Monitoring Data in master?

For a short-term discovery exercise, storing a small monitoring table in master can be practical.

For long-term monitoring, however, a dedicated DBA database is normally preferable.

For example:

DBAUtility
DBAMonitoring
DBAAdmin
SQLAdministration

Advantages include:

• easier backup and retention management;

• separation from system databases;

• easier migration;

• more controlled security;

• easier monitoring of table growth;

• better organisation of DBA utilities.

For a temporary one-to-two-week exercise, the master approach may still be acceptable if organisational standards permit it.

Security and Permissions

Access to server-level DMVs should be appropriately controlled.

For newer SQL Server versions, permissions for some server DMVs may require server-level performance-state permissions rather than simply database access. Microsoft documents the required permissions for sys.dm_exec_connections and related views by SQL Server version.

SQL Server Agent management also requires appropriate msdb SQL Agent roles or sysadmin privileges depending on the operation being performed.

Always apply least privilege and follow organisational change-control procedures before deploying monitoring jobs into production.

What This Technique Cannot Tell You

Connection monitoring can identify:

• who is connecting;

• from where;

• using which login;

• using which client program;

• which database they are connected to.

It cannot automatically tell you:

• the business owner;

• whether the application is still officially supported;

• how critical the application is;

• whether the application needs restart after SQL patching;

• acceptable outage duration;

• required testing procedures;

• vendor dependencies;

• hidden business processes.

These still require investigation with application, infrastructure and business teams.

Recommended Approach

For SQL Server environments with incomplete documentation, we recommend combining several discovery techniques:

1. Capture current SQL Server sessions regularly.

2. Run the capture for at least one to two weeks.

3. Review SQL Agent jobs.

4. Review linked servers.

5. Review SSIS and ETL dependencies.

6. Review service accounts.

7. Review reporting platforms.

8. Investigate unknown hostnames and generic application names.

9. Correlate findings with infrastructure and application teams.

10. Create a formal application dependency register before patching.

For business-critical environments, consider complementing snapshot monitoring with Extended Events or another continuous connection capture mechanism to identify short-lived sessions.

Final Thoughts

Patching SQL Server safely is not simply about applying a cumulative update or Windows security patch.

The more difficult question is often:

What depends on this database server?

Where historical documentation is missing, SQL Server's own session and connection metadata can provide an excellent starting point.

By periodically capturing:

SQL Server → Database → Host → IP → Application → Login

DBAs can progressively reverse engineer the application environment and provide infrastructure teams with the information required for safer maintenance planning.

The process is not a replacement for proper configuration management, but it can be an extremely effective way to rebuild missing application dependency documentation.

More importantly, the information collected during a patching exercise should not be discarded afterwards.

The resulting dependency register can become an ongoing operational asset for:

• patch management;

• incident response;

• SQL Server upgrades;

• cloud migrations;

• HA/DR planning;

• security assessments;

• server decommissioning;

• capacity planning;

• disaster recovery testing.

Need Help Identifying SQL Server Dependencies Before Patching?

Incomplete SQL Server documentation can significantly increase the risk of patching, upgrades and infrastructure changes.

Onsys Technologies provides SQL Server DBA and managed database services across Australia and the wider APAC region, including:

• SQL Server health checks;

• patching and upgrade planning;

• application dependency discovery;

• SQL Server performance tuning;

• Always On Availability Groups;

• HA and DR architecture;

• SQL Server migrations;

• Azure SQL migration;

• database monitoring;

• 24×7 DBA support.

If your organisation is planning SQL Server or Windows Server patching but does not have a reliable application dependency inventory, Onsys can assist with analysing the environment, identifying dependencies and preparing a structured patching and validation plan.

Contact the Onsys team through onsys.com.au to discuss your SQL Server environment.