Query client public IP addresses on SQL Server - ApsaraDB RDS

This topic describes how to obtain the originating public IP address of an external server or a client that is connected to an ApsaraDB RDS for SQL Server instance when the public IP address dynamically changes. The IP address recorded by the database is the public egress IP — not the IP shown by a local lookup tool. As a result, adding the locally queried IP to the whitelist still results in connection failures.

This topic covers two scenarios:

Identify your client's egress IP and add it to the whitelist
List all IP addresses currently connected to the instance

Usage notes

If your client or external server has a dynamically changing public IP address in production, connect over an internal network or add an appropriate CIDR block to the IP address whitelist. This prevents disconnections caused by IP address changes.

Scenario 1: Identify your client's egress IP

Use this approach when you cannot connect to the RDS instance because the IP address you added to the whitelist does not match the actual egress IP seen by the instance.

Procedure

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 instance. Remove this entry as soon as the test is complete.
Connect to the RDS instance using a client. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.
Run the following query to retrieve the protocol type (PROTOCOL_TYPE) and the client's public IP address as seen by the instance (CLIENT_NET_ADDRESS):
```
SELECT  CONNECTIONPROPERTY('PROTOCOL_TYPE') AS PROTOCOL_TYPE,
        CONNECTIONPROPERTY('CLIENT_NET_ADDRESS') AS CLIENT_NET_ADDRESS
```
The CLIENT_NET_ADDRESS column contains the egress IP address to add to the whitelist. The following output indicates that the query is successful.
Remove the 0.0.0.0/0 CIDR block added in step 1, then add the IP address returned by CLIENT_NET_ADDRESS to the whitelist.

Scenario 2: List all connected IP addresses

Use this approach to audit all active connections to the RDS instance or to investigate connection leaks.

Procedure

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 instance. Remove this entry as soon as the test is complete.
Connect to the RDS instance using a client. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.
Run the following query to list all active SQL-authenticated connections. The query returns the session ID (SPID), login name, login time, hostname, client application name, TCP port, and client IP address:
```
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
AND DC.AUTH_SCHEME='SQL'
```
The filter SP.SPID > 50 excludes system processes. DC.AUTH_SCHEME='SQL' limits results to SQL-authenticated connections. The following output indicates that the query is successful.
Remove the 0.0.0.0/0 CIDR block (or your company's CIDR block) from the IP address whitelist.

View connection parameter settings

After running the query in scenario 2, use the SPID value to inspect the full parameter settings of a specific connection:

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

Replace <The obtained SPID> with the SPID value from the scenario 2 query results.

The following output indicates that the query is successful.