February 6th, 2019
Connecting to an RDS Database with Lambda
By Anthony Roberson

This article will detail deploying a Lambda function written in .NET Core C# that connects to an RDS database, selects some data, and returns the output in JSON format. A .NET Core Lambda deployment package is a zip file of your function’s compiled assembly along with all of its assembly dependencies. The package will contain several files (proj.deps.json and proj.runtimeconfig.json) which configure the .NET Core runtime. Your Lambda function will fail to run if these files are missing; however we do not need to worry about creating these ourselves. In this example, we will be using the .NET CLI to help build the deployment package, which will also create these files for us once we publish the function.

Please note that AWS provides a Toolkit to be used with Visual Studio if you are using Windows, but we will not be using that for this example (though it is highly recommended).

Working with the dotnet CLI:

This article assumes you have installed the .NET Core CLI. If you haven’t, do so here. The CLI comes with blank templates that you can use to quickly develop projects and applications. Run the following command to get a list of options, as well as a list of templates to choose from:
dotnet new -all

Which will return the following output:

 

For this example, we will use the Lambda Empty Function template to create a Lambda function which connects to an RDS database, selects some data, and returns the output.

You must also install the Amazon.Lambda.Tools .NET Core Global Tool in order to properly create and deploy the function. To install the .NET Core Global Tool run the following command:

dotnet tool install -g Amazon.Lambda.Tools

Before we create the function, make sure to log in to the console and create the IAM service role. Click here to learn how to create your IAM Roles. Once you’ve done this, run the following command in your terminal, replacing the function name, profile, and region with your own information:

dotnet new lambda.EmptyFunction –name MyFunction –profile default –region region

This will create the necessary files and directories to get started on your code. If you open the aws-lambda-tools-defaults.json, you will see the profile information passed to the CLI. Next, in your project folder, open the Function.cs file which will contain your actual Lambda handler function. This includes the default Amazon.Lambda.Core library and a default LambdaSerializer attribute, which are necessary to run the code.

Function code:

In order to run the code in this example, you must already have an RDS database created. The function code will connect to the database with a SQL Connection string using the database server, name, username, and password. Once the connection is open, the function will execute a SQL data reader, loop through the results, add each item to a string list, generate an output, and return the value to the user. In the example below, our database “books” contains some book entries with the title, author, and some other relevant information.

Replace the Lambda Empty Function code with the following code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Amazon.Lambda.Core;
 
[assembly: LambdaSerializer(typeof(Amazon.Lambda.Serialization.Json.JsonSerializer))]
 
namespace LambdaToRDS
{
	public class Function
	{
    	/// 
    	/// A simple function that connects to an RDS database, selects some data, and returns the output.
    	/// 
    	/// 
    	/// 
    	/// 
    	public string FunctionHandler(object input, ILambdaContext context)
    	{
        	// DB Connection Variables
        	string server = Environment.GetEnvironmentVariable("DB_ENDPOINT");
        	string database = Environment.GetEnvironmentVariable("DATABASE");
        	string username = Environment.GetEnvironmentVariable("USER");
        	string pwd = Environment.GetEnvironmentVariable("PASSWORD");
        	string ConnectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}",server,database,username,pwd);
 
        	int count = 3;
        	string books = "Books:";
 
        	List myDbItems = new List();
 
        	using (var Conn = new SqlConnection(ConnectionString))
        	{
            	using (var Cmd = new SqlCommand($"SELECT top {count} * from books", Conn))
            	{
                	// Open SQL Connection
                	Conn.Open();
 
                	// Execute SQL Command
     	           SqlDataReader rdr = Cmd.ExecuteReader();
 
                	// Loop through the results and add to list
                	while (rdr.Read())
                	{
                        myDbItems.Add(rdr[1].ToString());
                	}
                	// Generate Output
                	for (int i = 0; i < myDbItems.Count; i++) {
                    	books += $" {myDbItems[i]}";
                    	// Add a comma if it's not the last item
                    	if (i != myDbItems.Count - 1) {
                        	books += ",";
                    	}
                	}
            	}
        	}
        	// Return the value
        	return books;
    	}
}
}

Once installed you can deploy your function with the following command:

dotnet lambda deploy-function MyFunction

Lambda will then create the necessary files mentioned above in your project and deploy the function. Once your function is deployed, log in to the AWS management console and navigate to your function in the Lambda console. Scroll down to the Environment Variables section, and enter your database connection credentials as a key value pair as such:

 

This step is so that we don’t hardcode our plaintext credentials in the code, as this is not a best practice from a security standpoint. In order to encrypt the password in the console, you must check the “Enable helpers for encryption in transit” box as shown above, select a KMS key (KMS key creation is not covered in this demo), then select “Encrypt.” After choosing your key from the dropdown, select the “Code” box, which will give you a code snippet. Here is the snippet for .NET C# to add to your function code:


using System;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Amazon.KeyManagementService;
using Amazon.KeyManagementService.Model;

namespace AWSLambda
{
	public class Function
	{
    	private static string Key1Value;
    	// Read values once, in the constructor
    	public Function()
    	{
        	// Decrypt code should run once and variables stored outside of the
        	// function handler so that these are decrypted once per container
        	Key1Value = DecodeEnvVar("PASSWORD").Result;
    	}
    	private static async Task DecodeEnvVar(string envVarName)
    	{
        	// Retrieve env var text
        	var encryptedBase64Text = Environment.GetEnvironmentVariable(envVarName);
        	// Convert base64-encoded text to bytes
        	var encryptedBytes = Convert.FromBase64String(encryptedBase64Text);
        	// Construct client
        	using (var client = new AmazonKeyManagementServiceClient())
        	{
            	// Construct request
            	var decryptRequest = new DecryptRequest
            	{
                	CiphertextBlob = new MemoryStream(encryptedBytes),
            	};
            	// Call KMS to decrypt data
            	var response = await client.DecryptAsync(decryptRequest);
            	using (var plaintextStream = response.Plaintext)
            	{
                	// Get decrypted bytes
                	var plaintextBytes = plaintextStream.ToArray();
                	// Convert decrypted bytes to ASCII text
                	var plaintext = Encoding.UTF8.GetString(plaintextBytes);
                	return plaintext;
            	}
        	}
    	}
    	public void FunctionHandler()
    	{
        	Console.WriteLine("Encrypted environment variable Key1 = " + Key1Value);
    	}
	}
}

Another way to accomplish this would be to use AWS Systems Manager or Parameter Store to handle the encryption/decryption. Visit this AWS blogpost to see how to use this method.

Once everything is complete you can test the function by configuring a test event in the Lambda console.

Conclusion:

This article should include everything you need in order to connect to your RDS database with a Lambda if your application is written in .NET Core C#. As stated above, it’s a good idea to use the AWS ToolKit for Visual Studio; however if you are not able to utilize these tools, this article can be a quick reference guide to deploy a function using the dotnet and AWS CLI.



Sources:
 

  1. https://docs.aws.amazon.com/lambda/latest/dg/lambda-dotnet-coreclr-deployment-package.html
  2. https://docs.aws.amazon.com/toolkit-for-visual-studio/latest/user-guide/lambda-cli-publish.html
  3. https://aws.amazon.com/blogs/compute/sharing-secrets-with-aws-lambda-using-aws-systems-manager-parameter-store/

If you have questions about how to deploy a Lamda function or anything AWS-related, reach out to us at info@1Strategy.com!