如何从MySQL中的Azure函数连接到Azure托管Python数据库？

Question

问题

我们在Azure函数上的Python应用程序无法连接到我们的Azure托管MySQL数据库。在使用func start进行本地测试时，该函数运行良好。连接到数据库或执行游标操作都没有问题。然而，当部署在Azure上时，我们会得到下面的错误。

我们已经尝试使用不同的SSL证书，并从数据库中完全关闭SSL。到目前为止，一切都没有奏效。

错误

2055: Lost connection to MySQL server at '<server_host>', system error: 1 [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1076)
Traceback (most recent call last):
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/network.py", line 436, in switch_to_ssl
    self.sock.do_handshake()
  File "/usr/local/lib/python3.7/ssl.py", line 1139, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLError: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1076)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/site/wwwroot/<script_folder>/__init__.py", line 88, in main
    host=os.environ['MYSQL_HOST'],
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/__init__.py", line 177, in connect
    return MySQLConnection(*args, **kwargs)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 104, in __init__
    self.connect(**kwargs)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/abstracts.py", line 781, in connect
    self._open_connection()
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 288, in _open_connection
    self._ssl, self._conn_attrs)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/connection.py", line 197, in _do_auth
    ssl_options.get('version', None))
  File "/home/site/wwwroot/.python_packages/lib/site-packages/mysql/connector/network.py", line 444, in switch_to_ssl
    errno=2055, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.InterfaceError: 2055: Lost connection to MySQL server at '<server_host>', system error: 1 [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1076)

连接代码

import mysql.connector

conn = mysql.connector.connect(
    user=os.environ['MYSQL_USER'],
    password=os.environ['MYSQL_PASSWORD'],
    database=os.environ['MYSQL_DATABASE'],
    host=os.environ['MYSQL_HOST'],
)
cursor = conn.cursor()

要求

astroid==2.3.3
azure-common==1.1.23
azure-core==1.1.1
azure-functions==1.0.7
azure-identity==1.1.0
azure-keyvault==4.0.0
azure-keyvault-certificates==4.0.0b7
azure-keyvault-keys==4.0.0
azure-keyvault-secrets==4.0.0
azure-storage-blob==12.1.0
certifi==2019.11.28
cffi==1.13.2
chardet==3.0.4
cryptography==2.8
idna==2.8
importlib-metadata==1.3.0
isodate==0.6.0
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
more-itertools==8.0.2
msal==1.0.0
msal-extensions==0.1.3
msrest==0.6.10
mysql-connector-python==8.0.18
numpy==1.17.4
oauthlib==3.1.0
pandas==0.25.3
portalocker==1.5.2
protobuf==3.11.1
pycparser==2.19
PyJWT==1.7.1
pylint==2.4.4
pyOpenSSL==19.1.0
python-dateutil==2.8.1
pytz==2019.3
requests==2.22.0
requests-oauthlib==1.3.0
six==1.13.0
typed-ast==1.4.0
urllib3==1.25.7
wrapt==1.11.2
zipp==0.6.0

Answer 1

您需要指定SSL CA文件。从以下地址下载证书：

https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem

将该文件包含在您的构建中。修改下面的示例代码以指定路径。

请注意以下屏幕截图。在您的代码中指定证书颁发机构的完整路径：

import mysql.connector

conn = mysql.connector.connect(
    user=os.environ['MYSQL_USER'],
    password=os.environ['MYSQL_PASSWORD'],
    database=os.environ['MYSQL_DATABASE'],
    host=os.environ['MYSQL_HOST'],
    ssl_ca='/home/site/wwwroot/ssl/BalitimoreCyberTrustRoot.crt.pem'
)
cursor = conn.cursor()

Answer 2

固定- Pymysql

遗憾的是，微软帮助台无法解决mysql.connector.的问题我们发现SSL 包能够使用SSL文件安全地连接。下面是我们的数据库连接的最终代码。

import pymysql
import pymysql.cursors
import pathlib

def get_ssl_cert():     
    current_path = pathlib.Path(__file__).parent.parent    
    return str(current_path / 'BaltimoreCyberTrustRoot.crt.pem')

### SQL Process
# Connection
conn = pymysql.connect(
    user=os.environ['MYSQL_USER'],
    password=os.environ['MYSQL_PASSWORD'],
    db=os.environ['MYSQL_DATABASE'],
    host=os.environ['MYSQL_HOST'],
    cursorclass=pymysql.cursors.DictCursor,
    ssl={'ssl': {'ssl-ca': get_ssl_cert()}}
)
cursor = conn.cursor()