This topic describes how to locate a real public IP address when the public IP address is not fixed.

Case 1

Solution

The public IP address of your computer dynamically changes, so the IP address you obtain by using a local IP address query tool may be incorrect. Errors are also reported when the RDS is connected. You can access the RDS instance only after you obtain the correct IP address of your client.

Considerations

If the public IP address of your computer dynamically changes and the established connection is used for a production environment, we recommend that you use an intranet connection instead. An appropriate public CIDR block is configured in the whitelist to guarantee the availability of the CIDR block when the IP address changes.

Obtain the IP address of the client

  1. Add the IP address 0.0.0.0/0 to the whitelist of the RDS instance. For more information, see Configure a whitelist for an ApsaraDB RDS for SQL Server instance.
    Note The IP address 0.0.0.0/0 indicates that all IP addresses are allowed to access the RDS instance.
  2. Use a client connect to the apsaradb RDS for SQL Server database.
  3. Run the following command to query the IP address of the client:
    SELECT CONNECTIONPROPERTY('PROTOCOL_TYPE') AS PROTOCOL_TYPE,
            CONNECTIONPROPERTY('CLIENT_NET_ADDRESS') AS CLIENT_NET_ADDRESS
    					
  4. Delete the IP address 0.0.0.0/0 you added to the IP address whitelist in step 1, and add the obtained real outbound IP address to the IP address whitelist.

Case 2

Solution

You can obtain all IP addresses that are connected to the RDS instance if you want to obtain the IP addresses of all databases connected to the RDS instance or locate some security risk such as link leakage.

Obtain the IP addresses of all connections to the database.

  1. Add the IP address 0.0.0.0/0 to the whitelist of the RDS instance. For more information, see Configure a whitelist for an ApsaraDB RDS for SQL Server instance.
  2. Connect to the apsaradb RDS for SQL Server database from a client.
  3. Run the following command to view 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'
    					
  4. Delete the IP address 0.0.0.0/0 or the CIDR block containing your company's IP address segment that you added to the whitelist in 1.

View the parameter settings of a connection

After you obtain all IP addresses that are connected to the RDS instance, you can run the following command to view the parameter settings of a connection:

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