-1

I am creating an Alexa skill to get some information from my local database on request. I have SQLServer 2008 running and I can use the pymssql library to pull data when I run my code locally. However after deploying it with AWS, I see the following error :

File "pymssql.pyx", line 644, in pymssql.connect (pymssql.c:10892)
InterfaceError: Connection to the database failed for an unknown reason.

Here is a screen capture of my aws package, which I created with the help of Amazon EC2 :

Screenshot of aws package and code

The relevant code to connect to the SQL DB is as below :

def getDB_response():
    session_attributes = {}
    card_title = "farzi"
    server = 'LT-CMU352CCM5'
    user = 'ss'
    passwd = 'abc@1234'
    DB = 'Market_DB'
    port = '1433'

    conn = pymssql.connect(server, port,user, passwd, DB)
    cursor = conn.cursor()
    cursor.execute('select @@servername')
    row = cursor.fetchone()

Does anyone know if there is anything else needed for my aws function to talk to my local DB?

s_om
  • 661
  • 2
  • 9
  • 24
  • 1
    Where's your server located? I assume you're using RDS? Did you put Lambda function in security group that allows it to connect to the database? – Caldazar Jun 21 '18 at 07:12
  • the server is actually my laptop. I am not using the amazon RDS, the SQL server instance is running on my computer. Pardon my ignorance but could you please tell me how to go about adding the lambda function in the correct security group? – s_om Jun 21 '18 at 07:14

1 Answers1

2

Since you're using your own laptop as a server, you can't use server name like that. "LT-CMU352CCM5" is recognizable from your local network (that's thy connection works when you run function locally), but not from the internet. Database has to be visible from the internet. You'd have to provide your public IP, and to set your router to forward the traffic from the internet (for port 1433) to your laptop (look at port forwarding for your specific router model). Also, you have to set your firewall to allow incoming traffic, and SQL server as well (turn on the TCP/IP and Named Pipes Protocols in your SQL server configuration manager).

Since you're not using RDS, you don't have to put Lambda function in a security group you'd do it by going to Lambda section in your AWS account, choose appropriate Lambda function, and in Network section for that Lambda function choose appropriate VPC, subnet and security group).

Issue that you have with your set-up is that your IP address will change sometimes, so you'd have to change the Lambda function every time that happens. I would urge you to reconsider using local laptop for DB server for something that's going to be used publicly.

Caldazar
  • 2,801
  • 13
  • 21
  • unfortunately for the skill I am building up to, the DB server will have to be outside the cloud. It will be a fixed server though so I will try to see how I can configure the router to help with requests from the internet. – s_om Jun 21 '18 at 07:45
  • 1
    You will need to either have a static IP address, or use a service like FreeDNS (http://freedns.afraid.org/) that would route all requests from certain address that you configure, to your current IP address – Caldazar Jun 21 '18 at 14:45
  • I think we can arrange for assigning a static IP to the DB server. Thank you! – s_om Jun 23 '18 at 11:19