Access an ApsaraDB RDS for SQL Server database though a VPC for enhanced security - Function Compute

This topic provides guidance for establishing access to an ApsaraDB RDS for SQL Server database from Function Compute through a virtual private cloud (VPC). Specifically, you can configure VPC-related settings in a function and a whitelist in the database to access the ApsaraDB RDS for SQL Server database and perform related operations. In this topic, Serverless Devs is used to deploy a function to access an ApsaraDB RDS for SQL Server database in the Python 3 runtime.

Before you start

Create an ApsaraDB RDS for SQL Server instance
Important
- Make sure that the database instance that you create is in the same region as the function that needs to access the database instance.
- We recommend that you create the database instance in a zone that Function Compute supports. For more information, see Zones where Function Compute is available.
  If your database instance is not in a zone that is supported by Function Compute, you can create a vSwitch in your VPC in the same zone as Function Compute and use this vSwitch ID in the VPC configurations of the function. vSwitches in the same VPC can communicate with each other over the private network. Therefore, Function Compute can use the vSwitch to access resources in VPCs that reside in other zones. For more information, see What do I do if the vSwitch is in unsupported zone error is reported?
Create a table named users.
In the sample code of index.py in this topic, all data in the users database table is queried. Therefore, you must create the table in advance.

Procedure

Install Serverless Devs and Docker and configure the AccessKey information.
For more information, see Install Serverless Devs and dependencies and Configure Serverless Devs.
Run the following command to initialize your project.
```
sudo s init
```
In the CLI, specify Alibaba Cloud as the vendor, specify the quick start mode, and select a built-in Python runtime. Configure the project name and the region where the project is deployed. In this example, the start-fc-sql-server-python project is deployed in the China (Hangzhou) region.
Run the following command to go to the project directory.
```
cd start-fc-sql-server-python
```

Modify the directory file based on your own business requirements.

Edit the s.yaml file. Example:

edition: 3.0.0
name: hello-world-app
access: "default"

vars: # The global variables.
  region: "cn-hangzhou"

resources:
  hello_world:
    component: fc3 # The name of the component. Serverless Devs is similar to a game console and does not have specific business capabilities without components. A component is similar to a game card. You can insert different game cards into the game console to play different games. Similarly, you can use different components to implement different business capabilities.
    props:
      region: ${vars.region}              # For information about how to use variables, visit https://docs.serverless-devs.com/serverless-devs/yaml#%E5%8F%98%E9%87%8F%E8%B5%8B%E5%80%BC.
      functionName: "start-python-xt8f"
      description: 'hello world by serverless devs'
      runtime: "python3.9"
      code: ./code
      handler: index.handler
      memorySize: 128
      timeout: 30
      vpcConfig:
        vpcId: vpc-bp11y195luy47h8cm****  # The ID of the VPC in which the database instance resides.
        securityGroupId: sg-bp1el3hto8hhkdup****  # The ID of the security group.
        vSwitchIds:
          - vsw-bp116uemmj7fniubi****  # Make sure that the CIDR block of the vSwitch is added to the whitelist of the database instance.
      environmentVariables:
        MSSQL_SERVER: rm-bp1gmz41ws982****.sqlserver.rds.aliyuncs.com  # The database endpoint.
        MSSQL_PORT: "1433"  # The port of the database.
        MSSQL_USER: sqlserver****  # The name of the database user.
        MSSQL_PASSWORD: z****  # The password of the database user.
        MSSQL_DATABASE: z****  # The name of the database.

Important

Make sure that the CIDR block of the vSwitch that you configure for the function is added to the whitelist of the database instance. For more information, see Configure an IP address whitelist for the database.

Edit the index.py code file. The following code snippet provides an example. The code logic is to read all data in the users table.

# -*- coding: utf-8 -*-
import logging
import pymssql   # You need to install the pymssql library first.
import os

logger = logging.getLogger()


# Create a database connection.
def getConnection():
    try:
        conn = pymssql.connect(
            host=os.environ['MSSQL_SERVER'],
            port=int(os.environ['MSSQL_PORT']),
            user=os.environ['MSSQL_USER'],
            password=os.environ['MSSQL_PASSWORD'],
            database=os.environ['MSSQL_DATABASE'],
            charset='utf8')
        return conn
    except Exception as e:
        logger.error(e)
        logger.error(
            "ERROR: Unexpected error: Could not connect to SQL Server instance.")
        raise Exception(str(e))


def handler(event, context):
    conn = getConnection()
    try:
        with conn.cursor() as cursor:
            # Query all data in the users table.
            cursor.execute("SELECT * FROM users")
            result = cursor.fetchone()
            logger.info(result)
            return result
    finally:
        # Close the database connection.
        conn.close()

Run the following command to build the project.
```
sudo s build --use-docker
```
Run the following command to deploy the project.
```
sudo s deploy -y
```

Run the following command to invoke the function.

sudo s invoke -e "{}"

The following code snippet shows the expected output. The following response indicates that data is successfully read from the users table in the database.

========= FC invoke Logs begin =========
FunctionCompute python3 runtime inited.
FC Invoke Start RequestId: 1-65d404e6-15b9013a-67628c1019d2
2024-02-20T01:48:22.181Z 1-65d404e6-15b9013a-67628c1019d2 [INFO] None
FC Invoke End RequestId: 1-65d404e6-15b9013a-67628c1019d2

Duration: 90.16 ms, Billed Duration: 91 ms, Memory Size: 128 MB, Max Memory Used: 16.54 MB
========= FC invoke Logs end =========

Invoke instanceId: c-65d404de-15f440b6-75331892ecca
Code Checksum: 18155960032536767495
Qualifier: LATEST
RequestId: 1-65d404e6-15b9013a-67628c1019d2

Invoke Result:
null
[hello_world] completed (0.32s)

Configure an IP address whitelist for the database

Important

Use an IP address whitelist to authorize functions to access the database. Do not use the security group mode. Otherwise, functions may occasionally fail to connect to the database, which affects the businesses.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Whitelist and SecGroup.
On the Whitelist Settings tab, you can view the mode of the IP address whitelist.
Note
Existing RDS instances may run in enhanced whitelist mode. All new RDS instances run in standard whitelist mode.
On the Whitelist Settings tab that appears, find the default whitelist group and click Modify.
In the Edit Whitelist dialog box, configure IP Addresses and click OK.
Important You must specify an IP address that belongs to the CIDR block of the vSwitch that you add when you configure the network for Function Compute.

More information

For more information about accessing an ApsaraDB RDS for SQL Server database, see Python access sql server database in Function Compute.
For more information about how to view the information about the configured vSwiches in Function Compute and add CIDR blocks of Function Compute vSwiches to the whitelist of a ApsaraDB RDS for MySQL database, see Configure network settings and Configure an IP address whitelist.
You can troubleshoot a database connection failure by referring to How to troubleshoot database access failures?