Azure Function Connection to third party mysql database

Question

Azure Function Connection to third party mysql database

Jovin Risch 1

Hello

We want to create an API which pulls data from an third party database.

Is there an possible Way to create an connection with python to MySql Database hosted by a local Hoster?

It would be excellent if there is an way, else we have to migrate to an other serverless provider.

Thanks and kind regards,
Jovin

2 answers

Your answer

Answer 1

MayankBargali-MSFT 70,936 Moderator

@Jovin Risch You can connect to any third party database with azure function if the service/database is accessible over internet.
Based on your requirement you can create function app with any of the available triggers (Timer, HTTP etc.) and write your own code to connect to the third party database.

I do see mysql-connector for python which you can configure in requirements.txt so the packages can be installs when publishing to Azure. To start with azure python function you can refer to this document.

Jovin Risch 1 Reputation point

2021-08-05T08:11:26.04+00:00

@MayankBargali-MSFT

Thanks for your confirmation. But we came to that idea because when we want to connect with the mysql-connector-python-8.26, but the library cannot be found.
Any ideas ? we testet every syntax in the requirements.txt file

So we found an other libary mysql-connector-python-rf, where we get an Access denied Error, but it tries to connect to the wrong host.

Do you have any experience with that mysql python libarys?
MayankBargali-MSFT 70,936 Reputation points Moderator

2021-08-05T08:33:13.21+00:00
@Jovin Risch Does your requirement.txt looks like below :

azure-functions mysql-connector-python

But in case if module or dependencies cannot be installed using pip then you need to use build native dependencies or enable remote build.
You can refer to python troubleshooting document for more details.

When using mysql-connector-python-rf if you are getting the access denied from your third party database then either it could be credential issue or your third part database denied the connection as there might be some firewall rules or your function/local development environment IP might not be whitelisted at database end.

Answer 2

@MayankBargali-MSFT

Thanks for your replay

I will send you the information which confirms that my statment ist true.

*With following config we control the host:

*Here is the code:

import os
import mysql.connector

RCPDB_CONNECTION = None

def get_rcpdb_connection():
global RCPDB_CONNECTION
if RCPDB_CONNECTION is None or not RCPDB_CONNECTION.is_connected():
RCPDB_CONNECTION = mysql.connector.connect(
host=os.environ['RCPDB_HOST'],
user=os.environ['RCPDB_USER'],
password=os.environ['RCPDB_PASS'],
database=os.environ['RCPDB_DB']
)
return RCPDB_CONNECTION

*But we still got the Error (from Livemetrics):

Exception while executing function: Functions.CoverLetterData <--- Result: Failure Exception: ProgrammingError: 1045 (28000): Access denied for user 'mysmarth_it'@'20.203.129.224' (using password: YES) Stack: File "/azure-functions-host/workers/python/3.9/LINUX/X64/azure_functions_worker/dispatcher.py", line 398, in _handle__invocation_request call_result = await self._loop.run_in_executor( File "/usr/local/lib/python3.9/concurrent/futures/thread.py", line 52, in run result = self.fn(*self.args, **self.kwargs) File "/azure-functions-host/workers/python/3.9/LINUX/X64/azure_functions_worker/dispatcher.py", line 602, in _run_sync_func return ExtensionManager.get_sync_invocation_wrapper(context, File "/azure-functions-host/workers/python/3.9/LINUX/X64/azure_functions_worker/extension.py", line 215, in _raw_invocation_wrapper result = function(**args) File "/home/site/wwwroot/CoverLetterData/init.py", line 121, in main data = get_coverletter_data_by_guid(guid) File "/home/site/wwwroot/CoverLetterData/init.py", line 59, in get_coverletter_data_by_guid connection = get_rcpdb_connection() File "/home/site/wwwroot/shared/rcpdb.py", line 10, in get_rcpdb_connection RCPDB_CONNECTION = mysql.connector.connect( File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/init.py", line 179, in connect return MySQLConnection(*args, **kwargs) File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 95, in init self.connect(**kwargs) File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/abstracts.py", line 716, in connect self._open_connection() File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 208, in _open_connection self._do_auth(self._user, self._password, File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 144, in _do_auth self._auth_switch_request(username, password) File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 177, in _auth_switch_request raise errors.get_exception(packet)

MayankBargali-MSFT 70,936 Reputation points Moderator

2021-08-09T11:58:05.7+00:00

@Jovin Risch Can you confirm what happens when you run the code locally without the function runtime as per the error looks like the authentication issue or your third part database denied the connection as there might be some firewall rules as mentioned in my previous comment.
Jovin Risch 1 Reputation point

2021-08-11T07:22:43.03+00:00

@MayankBargali-MSFT When I run the code locally the connector is able to reach the MySQL DB without any problems. We use the same credentials and have the outbound IP's from Azure Functions whitelisted.

What is really strange in the log. Is that the host address gets changed. Our DB is on the IP 217.26.56.93 but in the log we see the function tries to connect to a different IP in the case above to 20.203.129.224. No matter what we set as the environment variable for our host in Azure.

It seams to us that azure is preventing us to connect to this DB or rather that IP.

Do you have any ideas? We really appreciate your help on this.
MayankBargali-MSFT 70,936 Reputation points Moderator

2021-08-11T08:41:03.05+00:00

@Jovin Risch You should whitelist the function app outbound IP addresses.

When a function app that runs on the Consumption plan or the Premium plan is scaled, a new range of outbound IP addresses may be assigned. When running on either of these plans, you can't rely on the reported outbound IP addresses to create a definitive allowlist. To be able to include all potential outbound addresses used during dynamic scaling, you'll need to add the entire data center to your allowlist.

You can find the datacenter IP from here.
Jovin Risch 1 Reputation point

2021-08-11T08:59:17.213+00:00

@MayankBargali-MSFT Thank you! We will whitelist all IP's from our region and LogicApps and try again.

Share via

Azure Function Connection to third party mysql database

2 answers

Your answer