Query the public IP address of a client connected to SQL Server - ApsaraDB RDS

When the public IP address of an external server or client dynamically changes, local IP address query tools may return an incorrect result. This document shows how to query the true originating IP from the RDS instance side so you can add it to the IP address whitelist.

Scenario 1: Find the IP address of a single connecting client

Use this approach when connection errors persist after you add an IP address to the whitelist, and you suspect the client's public IP address has changed.

Usage notes

If the client's public IP address changes frequently in a production environment, connect over an internal network or add a stable CIDR block to the IP address whitelist instead. This avoids repeated disconnections caused by IP changes.

Find the client IP address

Add the CIDR block 0.0.0.0/0 to the IP address whitelist of your RDS instance. For more information, see Configure an IP address whitelist.
Important
0.0.0.0/0 allows all IP addresses to access the RDS instance. Remove this entry as soon as you finish querying.
Connect a client to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.
Run the following query to get the originating IP address of the current connection:
```
SELECT  CONNECTIONPROPERTY('PROTOCOL_TYPE') AS PROTOCOL_TYPE,
        CONNECTIONPROPERTY('CLIENT_NET_ADDRESS') AS CLIENT_NET_ADDRESS
```
A successful query returns output similar to the following:
Remove the CIDR block 0.0.0.0/0 added in step 1. Add the IP address returned by CLIENT_NET_ADDRESS to the whitelist.

Scenario 2: List all IP addresses connected to the RDS instance

Use this approach to audit all active connections or investigate security issues such as link leakage.

List all connected IP addresses

Add the CIDR block 0.0.0.0/0 to the IP address whitelist of your RDS instance. For more information, see Configure an IP address whitelist.
Important
0.0.0.0/0 allows all IP addresses to access the RDS instance. Remove this entry as soon as you finish querying.
Connect a client to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

Run the following query to list all active connections:

SELECT
    SP.SPID,
    SP.LOGINAME,
    SP.LOGIN_TIME,
    SP.HOSTNAME,
    SP.PROGRAM_NAME,
    DC.CLIENT_TCP_PORT,
    DC.CLIENT_NET_ADDRESS
FROM SYS.SYSPROCESSES AS SP
INNER JOIN SYS.DM_EXEC_CONNECTIONS AS DC
    ON SP.SPID = DC.SESSION_ID
WHERE SP.SPID > 50              -- Excludes system processes (SPID <= 50)
  AND DC.AUTH_SCHEME = 'SQL'    -- Returns only SQL Server-authenticated connections

A successful query returns output similar to the following:

Remove the CIDR block 0.0.0.0/0 or your company's CIDR block from the IP address whitelist.

View the parameter settings of a connection

After identifying a connection's SPID in scenario 2, run the following query to inspect its session parameters:

SELECT * FROM SYS.DM_EXEC_SESSIONS WHERE SESSION_ID = <The obtained SPID>

Replace <The obtained SPID> with the SPID value returned in step 3 above.

A successful query returns output similar to the following: