MySQLdb._exceptions.OperationalError: (2002, "Can't connect to server on '.database.windows.net') | Django+Azure+MySql

Question

I'm having issues connecting my Django app in my local machine to MySql Database in Azure? I added my IP in the Rules and am connecting with this:

    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST': '<servername>.database.windows.net',
        'PORT': '3306',
        'NAME': '<database_name>',
        'USER': '<admin>@<servername>',
        'PASSWORD': '<cecret>',
        'OPTIONS': {'ssl': {'pem': 'tls.pem'} }
    },

I can connect using AzureDataStudio, but not with this configuration in django. I Nmaped my host, found a bunch of open ports but 3306 and 1433 are bound to Sql servers.

Django's runserver shows MySQLdb._exceptions.OperationalError: (2002, "Can't connect to server on '<servername>.database.windows.net' (115)") with this configuration even if I have that server and database within it running.

One example php query string in Azure portal has:

$conn = new PDO("sqlsrv:server = tcp:<server_name>.database.windows.net,1433; Database = <database_name>", "<admin>", "{your_password_here}");

So, I'm assuming I should connect to 1433 but only 3306 works from DataStudio. From python manage.py runserver it shows django.db.utils.OperationalError: (2013, "Lost connection to server at 'handshake: reading initial communication packet', system error: 104") if I try port 1433. I'm at the limit of my knowledge regarding this.

Correction-1: 3306 doesn't seem to work with Azure DataStudio. But using 1433 in Django settings won't even initialize connection.

Answer 1

You need to install the MySQL connector in your local machine to connect the Python with Azure Database for MySQL using below command.

pip install mysql-connector-python

Later, on server's Overview page, make a note of the Server name and Server admin login name.

Add the code example to the file. In the code, replace the <mydemoserver>, <myadmin>, <mypassword>, and <mydatabase> placeholders with the values for your MySQL server and database.

Note: SSL is enabled by default on Azure Database for MySQL servers. You may need to download the DigiCertGlobalRootG2 SSL certificate to connect from your local environment. Replace the ssl_ca value in the code with path to this file on your computer.

import mysql.connector
from mysql.connector import errorcode

# Obtain connection string information from the portal

config = {
  'host':'<mydemoserver>.mysql.database.azure.com',
  'user':'<myadmin>@<mydemoserver>',
  'password':'<mypassword>',
  'database':'<mydatabase>',
  'client_flags': [mysql.connector.ClientFlag.SSL],
  'ssl_ca': '<path-to-SSL-cert>/DigiCertGlobalRootG2.crt.pem'
}

# Construct connection string

try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()

  # Drop previous table of same name if one exists
  cursor.execute("DROP TABLE IF EXISTS inventory;")
  print("Finished dropping table (if existed).")

  # Create table
  cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
  print("Finished creating table.")

  # Insert some data into table
  cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150))
  print("Inserted",cursor.rowcount,"row(s) of data.")
  cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154))
  print("Inserted",cursor.rowcount,"row(s) of data.")
  cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100))
  print("Inserted",cursor.rowcount,"row(s) of data.")

  # Cleanup
  conn.commit()
  cursor.close()
  conn.close()
  print("Done.")

Refer the steps given in this official tutorial to deploy the same.

Answer 2

Apparently I was using connection string and backend that didn't support the "MySql database" I was using. I'm still vague on how it worked but here it goes.

I created a new Azure Database for MySql servers and created a new Database there. Then used connection strings as provided by @UtkarshPal-MT in my original Django's DATABASE={} entry. Didn't have to do anything else. It just connected.

Note: You do compulsorily require to pass that certificate.

Edit1: If you're using this, as I did.

Then you need to use this mssql-django external backend with proper drivers. You can find details on using this backend in that PyPi page.