Accessing an RDS SQL Server from a .NET 6 application in Lambda

Want to learn more about AWS Lambda and .NET? Check out my A Cloud Guru course on ASP.NET Web API and Lambda.

Download full source code.

Accessing an Amazon RDS SQL Server from a Lambda function (I will sometimes refer to this by the word “function”) doesn’t work out of the box, the function needs access to the Virtual Private Cloud (VPC) the SQL Server is in. The process for doing this is not hard, but there are a few steps.

By the end of this post, you will have a .NET 6 Web API application, deployed to a Lambda function, querying a SQL Server inside your VPC that is not publicly accessible. The SQL Server will be seeded with data, and the function will be accessible from anywhere in the world.

A little background

By default, resources created inside a VPC are inaccessible from the internet but can be accessed from inside the VPC. So, if you had an EC2 instance (running Windows/Linux) and a SQL Server in the same VPC there would be no problem deploying a Web API application to the EC2 instance and querying the SQL Server from there. You could then open up a port in the security group to allow access to the Web API application from the internet. The database itself would remain inaccessible from the internet.

But if you put the Web API application in a Lambda function, you will not be able to access the SQL Server, because the two are not in the same VPC. Lambda functions run on a separate VPC that you do not own or control. For the function to communicate with the SQL Server, a connection must be made from the function to the VPC.

The steps are -

1. Get the tools
2. Create a SQL Server database that is not publicly accessible.
3. Create required roles and permissions to allow the Lambda function to connect to the VPC where the SQL Server is located.
4. Create a simple Web API application that uses the database.
5. Deploy the Web API application to a Lambda function.
6. Connect the function to the VPC where the SQL Server is located.

1. Getting the tools

Install the latest tooling, this lets you deploy and run Lambda functions.

dotnet tool install -g Amazon.Lambda.Tools

Install the latest templates to get .NET 6 support.

dotnet new --install Amazon.Lambda.Templates

Get the latest version of the AWS CLI, from here.

2. Create the database

Different regions have varying SQL Server versions available. Run the following command to see the versions available in your region:

aws rds describe-db-engine-versions --engine sqlserver-ex --query 'DBEngineVersions[*].EngineVersion'

Choose the version you want to use and include it in the command below -

aws rds create-db-instance --db-instance-identifier my-sql-server --db-instance-class db.t3.small --engine sqlserver-ex --master-username admin --master-user-password SOME_COMLPEX_PASSWORD123 --allocated-storage 20 --license-model license-included --engine-version VERSION --no-publicly-accessible

This will create a small SQL Server Express database that will not be publicly accessible, you cannot access it from your home computer, only from something inside your VCP. Or, as you will see from a Lambda function that has not been connected to your VPC. The output of the above command will show you the security group and subnets the database will be on (important for later).

Leave this alone, it will take a while.

3. Roles and permissions

All Lambda functions run under a role, for this scenario very specific permissions are needed to create the VPC connections.

Open the “serverless.template” file, find the section on Policies and replace with this

"Policies": [
  "AWSLambda_FullAccess",
  "AWSLambdaVPCAccessExecutionRole"
]

If you are interested in finding out more, check this document - https://docs.aws.amazon.com/lambda/latest/dg/configuration-vpc.html.

4. The application

You are going to use a simple Web API application with Entity Framework to connect to the SQL Server. A single controller will query the database and return data to the client.

Create the application

Create the Web API application with -

dotnet new serverless.AspNetCoreWebAPI --name AspNetCoreWebApiRds

Add a couple of packages to the project -

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package AutoFixture

Update the connection string

Including the connection string in the source code is NOT a good idea, I will show you how to use Secrets Manager for this in a subsequent post.

Change to the AspNetCoreWebAPI/src/AspNetCoreWebAPI directory.

Hopefully, the server has spun up, but this can take some time. Get its address -

aws rds describe-db-instances --db-instance-identifier my-sql-server --query 'DBInstances[0].Endpoint.Address' --output text

Open the appsettings.json file and add a connection string, putting in the address you just found -

{
  "ConnectionStrings": {
    "SalesDb": "Server=ADDRESS;Database=SalesDb;User Id=admin;Password=SOME_COMLPEX_PASSWORD123;MultipleActiveResultSets=true"
  }
}

Again, remember you should not store the connection string, especially the username and password in your real applications.

The code changes for Startup.cs, the seeder, context, data models, and products controller are all in the attached zip file, so I will not include them here.

5. Deploy the Lambda function

Before you can deploy your application to the Lambda function, you need an S3 bucket for the CloudFormation stack that will be created.

aws s3api create-bucket --bucket cloudformation-templates-2022

If you are not using the us-east-1 region, add --create-bucket-configuration LocationConstraint=REGION to the end of the command.

Note, you must use a unique name for the bucket, you can’t use the one shown here.

Deploy using -

dotnet lambda deploy-serverless --stack-name AspNetCoreWebApiRds --s3-bucket cloudformation-templates-2022

Wait a little while as the resources are created. When it’s done you will see a URL for your deployed application.

Stack finished updating with status: CREATE_COMPLETE

Output Name                    Value
------------------------------ --------------------------------------------------
ApiURL                         https://xxxxxxxx.execute-api.us-east-1.amazonaws.com/Prod/

Try opening the ApiURL from your browser, you will get an error like -

{"message": "Endpoint request timed out"}

This is because the Lambda function does not have a connection to the VPC that the database is on, and the request timed out!

Getting more details about the Lambda function

When you use the dotnet lambda deploy-serverless... command, the full name of the Lambda function has not been generated yet, but it starts with “AspNetCoreWebApiRds”.

Run the below to get the full name -

aws lambda list-functions --query 'Functions[?starts_with(FunctionName, `AspNetCoreWebApiRds`) == `true`].FunctionName' --output text

It will look something like “AspNetCoreWebApiRds-AspNetCoreFunction-xxxxxxxxxxx”.

Some security notes

Because we are not specifying any security groups for the SQL Server, it will go into the default security group, which allows all traffic between everything in the group. There is no need to open any ports for the SQL Server.

6. Connecting the Lambda function to the VPC

To get the security group of the SQL Server, run -

aws rds describe-db-instances --db-instance-identifier my-sql-server --query 'DBInstances[0].VpcSecurityGroups[0].VpcSecurityGroupId' --output text

To get the subnets the SQL Server is on, run -

aws rds describe-db-instances --db-instance-identifier my-sql-server --query 'DBInstances[0].DBSubnetGroup.Subnets[].SubnetIdentifier'

All you need is one of the SubnetIdentifiers.

Use one of the SubnetIdentifiers and the VpcSecurityGroupId in the next command -

aws lambda update-function-configuration --function-name AspNetCoreWebApiRds-AspNetCoreFunction-xxxxxxxx --vpc-config SubnetIds=subnet-xxxxxx,SecurityGroupIds=sg-xxxxxxxx

After a few minutes, this will complete.

An Elastic Network Interface (ENI) connecting your function to the VPC where the SQL Server is has been created.

Now you can make a request to the endpoint and successfully connect to the SQL Server.

https://xxxxxxxx.execute-api.us-east-1.amazonaws.com/Prod/ and https://xxxxxxxx.execute-api.us-east-1.amazonaws.com/Prod/products.

Conclusion

You should now have a Lambda function that can connect to the SQL Server and return data, but the database remains inaccessible to the outside world.

In a subsequent post, I will show how to add the database username and password to Secrets Manager and access them from within the application - no more connection strings in the source code!

Download full source code.