CODEWITHSUNDEEP
c#sql-serveramazon-web-serviceslambda
theoneandonly2
theoneandonly2

Reputation: 831

Has anyone successfully run a SQL Server connector within a C# AWS Lambda function, or AWS Linux EC2 instance?

I've been working all night trying to get a SQL Server connection from either a Lambda function or an AWS Linux EC2 instance with no luck.

I started by getting .NET Core running in Windows and getting a successful write/query working there using System.Data.SqlClient. Then I attempted to move it into an AWS Lambda function. It doesn't appear that there are any issues with libraries, but rather I always get this error when trying to open the database connection:

Unhandled Exception: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

I then decided to get the same code working in Ubuntu Linux (16.04) and was able to successfully write/query data to a SQL Server database there. Next I attempted the exact same code running in a AWS Linux AMI (since these are what AWS Lambda runs on), and received the exact same error code above. Then I attempted to give it a shot using Entity Framework and got the exact same error.

Has anyone been successful doing this?? If so, I'd love to hear more about your specific approach. Or, also, does anyone know if this isn't possible?

I'll paste the code snippet below, even though I don't believe this is the problem (same code works in Ubuntu but not in AWS Linux). Note: this is a mock database just to test. It seems to be a driver problem, but hard to be sure.

using System;
using System.IO;
using System.Data.SqlClient;

namespace CSharpLambdaFunction
{
    public class Program
    {
        public static void Main() {
            LambdaHandler.myHandler();
        }
    }

    public class LambdaHandler
    {
        public static void myHandler()
        {
            SqlConnection myConnection = new SqlConnection("user id=usernamehere;" +
                                        "password=passwordhere;server=serveraddresshere;" +
                                        "database=test; " +
                                        "connection timeout=30");
            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            TimeSpan t = DateTime.UtcNow - new DateTime(1970, 1, 1);
            int secondsSinceEpoch = (int)t.TotalSeconds;

            SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.Table_1 (test1, test2) " +
                                        "Values ('testing', " + secondsSinceEpoch + ")", myConnection);
            myCommand.ExecuteNonQuery();

            try
            {
                SqlDataReader myReader = null;
                myCommand = new SqlCommand("select * from dbo.Table_1",
                                                            myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    Console.WriteLine(myReader["test1"].ToString());
                    Console.WriteLine(myReader["test2"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

Upvotes: 1

Views: 3626

Answers (1)

theoneandonly2
theoneandonly2

Reputation: 831

I found the issue to be that the EC2 SQL Server instance didn't have the correct inbound rules! The code above is functional. Just for example purposes, see below for the corresponding project.json:

{
  "version": "1.0.0-*",
  "dependencies": {},
  "buildOptions": {
    "warningsAsErrors": true,
    "emitEntryPoint": true
  },
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.1"
        },
        "System.Data.SqlClient": {
          "version": "4.1.0-*"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

Upvotes: 2

Related Questions