Need help connecting to Amazon Web Services (AWS) using AutoIT MySQL UDF

[poila]

Hi,

I recently got a request to transfer databases from a Microsoft Server to an online Amazon Web Services (AWS) server.

I was able to export all the data from MySQL Workbench and use phpMyAdmin to import the data into that AWS instance.

The issue is that my AutoIT program uses a MySQL UDF (see: '?do=embed' frameborder='0' data-embedContent>>) and it uses ODBC connectors.

When I changed the string containing the path of the server from "localhost" to the provided IP address, it could not detect any connection, even though I could access that IP address from my Chrome browser and access phpMyAdmin.

I am also wondering and searching for how do I really use PuTTY.exe and PSftp.exe as well as the private key to connect via SSH in the background.

How do I accomplish this with AutoIT? Is this approach recommended, or is there a more elegant and secure way of connecting to AWS EC2 instance with AutoIT?

Additionally, is it necessary to tweak some settings in AWS (eg. my.conf) to enable incoming connections from specified IP addresses?

Thanks!

Edited October 28, 2014 by poila

[poila]

(Bumping thread...)

Attempted to solve the problem of trying to connect to MySQL database in AWS via SSH, but was unsuccessful.

Read this link prior ('?do=embed' frameborder='0' data-embedContent>>) but still confused.

I've already set remote port forwarding to 3306 in /etc/my.cnf, still confused; what else is required to enable SSH connection, if used in conjunction with the MySQL UDF?

port=3306
bind-address=0.0.0.0

I could connect to the AWS instance using PSftp.exe (get from a Windows Command Prompt and open the remote working directory

(I didn't write it into AutoIT syntax though... not sure if this is needed to ensure a connection to a remote MySQL database via SSH):

psftp.exe userNameAtAWS@XX.XX.XX.XX -i privateKeyFile.ppk

AutoIT Code sample:

#include "MySQL.au3"
; This UDF is by cdkid

Global Const $serverUser = "serverUser"
Global Const $serverPass = "serverPassword"

Global $const_fileServerName = "server.ini"

; server.ini contains the text, "XX.XX.XX.XX", which is the IP address accessible by browser but not by AutoIT

Func connectToMySQL() ;     Establishes connection with mySQL Database
    Local $mySQLServerName = FileRead($const_fileServerName)
    $global_normalSQLInstance = _MySQLConnect($serverUser, $serverPass, "databaseName", $mySQLServerName)
    $global_SQLInstance = $global_normalSQLInstance
    If $global_normalSQLInstance = 0 Or $global_tutorialSQLInstance = 0 Then
        Return 0
    Else
        Return 1
    EndIf
EndFunc

Small update: Using PuTTY again, I used the command sudo iptables -L -n and found out that all incoming connections are allowed (these are the firewall rules).

So I realised that it might have to do with the security group's rules in that AWS instance. I don't have direct access to it, so I might update this post again once I got hold of the engineer with access rights to it.

Edited October 29, 2014 by poila

[poila]

(Bumping thread... again... and if it annoys you... FORGIVE ME!)

Seriously though...

Turns out that this is not so much of an AutoIT issue but more of an AWS Security Groups issue.

Simple solution is open port 3306 via AWS Console, add MySQL to list of programs with permission to allow access to port 3306, restart MySQL like this:

sudo service mysqld restart

And re-ping to the specific IP address for that AutoIT program (eg. XX.XX.XX.X).

For another sanity check, use telnet to check if the MySQL connection can be done remotely.

(Do this via Command Prompt in Windows)

telnet XX.XX.XX.X 3306 

Problem solved. Note that however, because that AutoIT program is accessing the MySQL server from AWS, the performance will be significantly hit because of longer-distance connections.

Additionally, table names in the MySQL database are CaSe-SenSiTive. This means a major refactoring of the AutoIT script if it gets unsanitary (using myTable and MyTable interchangeably... yay).

Edited November 7, 2014 by poila