All Products
Search
Document Center

ApsaraDB RDS: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?

Last Updated:Sep 28, 2023

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

  1. 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.

  2. Use a client to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

  3. 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.

    image.png
  4. 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

  1. 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.

  2. Use a client to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

  3. 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.

    image.png
  4. 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.

image.png