How do I obtain the public IP address of an external server or a client that is connected to an ApsaraDB RDS for SQL Server instance? - 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.

Scenario 1

Description

The public IP address of the external server or client dynamically changes, and the IP address that you obtain by using a local IP address query tool may be incorrect. As a result, when you connect the external server or client to your RDS instance, the system reports connection errors even after you add the obtained public IP address to the IP address whitelist of the RDS instance. You can access the RDS instance only after you obtain the originating IP address of the external server or client.

Usage notes

If the public IP address of the external server or client dynamically changes in a production environment, we recommend that you connect the external server or client to your RDS instance over an internal network or add an appropriate CIDR block to the IP address whitelist of the RDS instance. This prevents disconnections due to changes of the public IP address.

Procedure

Add the CIDR block 0.0.0.0/0 to the whitelist of your RDS instance. For more information, see Configure an IP address whitelist.
Important
The CIDR block 0.0.0.0/0 indicates that all IP addresses are allowed to access the RDS instance. If you use this CIDR block to test connectivity, we recommend that you change the configuration after the test is complete.
Use a client to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

Execute the following statements to query the IP address of the client:

SELECT  CONNECTIONPROPERTY('PROTOCOL_TYPE') AS PROTOCOL_TYPE,
        CONNECTIONPROPERTY('CLIENT_NET_ADDRESS') AS CLIENT_NET_ADDRESS

The following output indicates that the query is successful.

Delete the CIDR block 0.0.0.0/0 that you added in Step 1. Then, add the obtained IP address to the IP address whitelist.

Scenario 2

Description

You want to obtain all IP addresses that are connected to the RDS instance, or you want to locate security issues such as link leakage.

Procedure

Add the CIDR block 0.0.0.0/0 to the whitelist of your RDS instance. For more information, see Configure an IP address whitelist.
Important
The CIDR block 0.0.0.0/0 indicates that all IP addresses are allowed to access the RDS instance. If you use this CIDR block to test connectivity, we recommend that you change the configuration after the test is complete.
Use a client to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

Execute the following statements to query all IP addresses that are connected to the RDS instance:

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 following output indicates that the query is successful.

Delete the CIDR block 0.0.0.0/0 added in Step 1 or the CIDR block of your company from the IP address whitelist of the RDS instance.

View the parameter settings of a connection

After you obtain all IP addresses that are connected to the RDS instance, you can execute the following statement to query the parameter settings of a specific connection:

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

The following output indicates that the query is successful.