How to Implement AWS Lambda to run SQL against On-Prem MS SQL Server using Windows Authentication

Solution Architecture Diagram

1. Create a VPC in the AWS environment and configure a private subnet within it.

ℹ️ This private subnet will host the AWS Lambda function.

2. Set up a Site-to-Site VPN connection between the on-premises network and the VPC.

ℹ️ This will allow the Lambda function to access the on-premises SQL Server securely.

3. Create an AWS Lambda function that will execute the SQL queries against the on-premises SQL Server.

ℹ️ Ensure that the Lambda function is configured to run within the private subnet of the VPC.

4. Store the SQL Server connection details, such as the server hostname, database name, and authentication credentials, in a secure storage service like AWS Secrets Manager.

ℹ️ Grant the necessary permissions to the Lambda function's execution role to access the secrets.

5. In the Lambda function code, retrieve the SQL Server connection details from the secure storage, establish a connection to the on-premises SQL Server using the VPN, and execute the required SQL queries.

⚡ Create a VPC endpoint for AWS Secrets Manager within your VPC. This will allow the Lambda function running in the private subnet to securely access Secrets Manager without needing to route traffic through the internet or a NAT gateway.

Additional recommendation

• Set up appropriate logging and monitoring mechanisms, such as AWS CloudWatch, to track the execution of the Lambda function, any errors or failures, and the overall performance of the SQL queries.
• Establish a backup and disaster recovery strategy for the data stored in the on-premises SQL Server, either by replicating the data to a different location or using a cloud-based backup solution.
• Regularly review and update the security measures, such as encryption and access controls, to ensure the confidentiality and integrity of the data stored in the on-premises SQL Server.