Skip to content

Azure Linux App Service Database Connection Test Tool Box

Jump to bottom
renhanli edited this page Jan 14, 2021 · 9 revisions

Overview

In this tool box, you can find sample code and test scripts used to run database connection test.

These code and scripts are tested on Azure Linux App Service with built-in docker images.

PHP

The following code was tested on Azure Linux App Service with PHP 7.4 built-in docker image.

  • PHP MySQL connection tester

    Source code link:

    MySQL PHP Client

    How to use:

    1. Create a new folder under /home (e.g. /home/test). Then, upload all the following the files to the new created folder

      • mysqlconnect.php
      • mysqlconnect.sh
      • BaltimoreCyberTrustRoot.crt.pem

    2. Go to WEBSSH (https://[webapp-name].scm.azurewebsites.net/webssh/host)

    3. Go to the place where you uploaded the code and run "mysqlcontect.sh" script.

      Provide MySQL server URL, username, password, Database name as the following.

      The script will print out connection succeeded or not.

      root@5097a5fe6168:/home/phptest# ls
BaltimoreCyberTrustRoot.crt.pem  mysqlconnect.php  mysqlconnect.sh

root@5097a5fe6168:/home/phptest# ./mysqlconnect.sh
MySQL server: rhlmysql.mysql.database.azure.com
Username: mytest@rhlmysql
Password: Database: test
Run connection test:
Cannot load Zend OPcache - it was already loaded
Successfully connect to MySQL.

      If connection failed, it will print out detailed error messages.

      root@5097a5fe6168:/home/phptest# ./mysqlconnect.sh
MySQL server: rhlmysql.mysql.database.azure.com
Username: mytest@rhlmysql
Password: Database: test
Run connection test:
Cannot load Zend OPcache - it was already loaded
[29-Dec-2020 06:56:20 UTC] PHP Warning:  mysqli_real_connect(): (HY000/1045): Access denied for user 'mytest'@'52.175.56.14' (using password: YES) in /home/phptest/mysqlconne
ct.php on line 14
Failed to connect to MySQL: Access denied for user 'mytest'@'52.175.56.14' (using password: YES)

Node.js

The following code was tested on Azure Linux App Service with Node 14LTS built-in docker image.

  • Node.js MySQL connection tester

    Source code link:

    MySQL Node Client

    How to use:

    1. Create a new folder under /home (e.g. /home/test). Then, upload all the following the files to the new created folder

      • mysqltest.js
      • mysqlconnect.sh

    2. Go to WEBSSH (https://[webapp-name].scm.azurewebsites.net/webssh/host)

    3. Go to the place where you uploaded the code and run "mysqlcontect.sh" script.

      This script will check whether "mysql" package is installed or not. It only install the it when needed.

      So, you can repeatedly run this script. It will only install mysql package under the /home/xxxx/node_modules folder at the first time.

      root@9cfd45df4148:/home/nodetest# ls
mysqlconnect.sh  mysqltest.js  node_modules  package-lock.json

root@9cfd45df4148:/home/nodetest# ls node_modules/
bignumber.js  isarray               readable-stream  string_decoder
core-util-is  mysql                 safe-buffer      util-deprecate
inherits      process-nextick-args  sqlstring

      Provide MySQL server URL, port, username, password, Database name as the following.

      The script will print out connection succeeded or not.

      root@9cfd45df4148:/home/nodetest# ./mysqlconnect.sh
npm info it worked if it ends with ok
npm info using npm@6.14.8
npm info using node@v14.15.0
npm timing npm Completed in 470ms
npm info ok
mysql package installed.
MySQL server: rhlmysql.mysql.database.azure.com
Port: 3306
Username: mytest@rhlmysql
Password: Database: test
>>>>>>>>>Run connection test
Connection established.
[
  RowDataPacket { Database: 'information_schema' },
  RowDataPacket { Database: 'mysql' },
  RowDataPacket { Database: 'performance_schema' },
  RowDataPacket { Database: 'sys' },
  RowDataPacket { Database: 'test' }
]
Done.

      If connection failed, it will print out detailed error messages.

      # ./mysqlconnect.sh
npm info it worked if it ends with ok
npm info using npm@6.14.8
npm info using node@v14.15.0
npm timing npm Completed in 495ms
npm info ok
mysql package installed.
MySQL server: rhlmysql.mysql.database.azure.com
Port: 3305
Username: mytest@rhlmysql
Password: Database: test
>>>>>>>>>Run connection test
!!! Cannot connect !!! Error:
/home/nodetest/mysqltest.js:31
        throw err;
        ^

Error: connect ETIMEDOUT
    at Connection._handleConnectTimeout (/home/nodetest/node_modules/mysql/lib/Connection.js:409:13)
    at Connection._handleConnectTimeout (/home/nodetest/node_modules/mysql/lib/Connection.js:409:13)
    at Object.onceWrapper (events.js:421:28)
    at Socket.emit (events.js:315:20)
    at Socket._onTimeout (net.js:483:8)
    at listOnTimeout (internal/timers.js:554:17)
    at processTimers (internal/timers.js:497:7)
    --------------------
    at Protocol._enqueue (/home/nodetest/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/home/nodetest/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/home/nodetest/node_modules/mysql/lib/Connection.js:116:18)
    at Object.<anonymous> (/home/nodetest/mysqltest.js:27:6)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)
    at internal/main/run_main_module.js:17:47 {
  errorno: 'ETIMEDOUT',
  code: 'ETIMEDOUT',
  syscall: 'connect',
  fatal: true
}

JAVA

The following code was tested on Azure Linux App Service with JAVA|11-java11 & TOMCAT|9.0-java11 built-in docker image.

https://docs.microsoft.com/en-us/java/api/overview/azure/mysql?view=azure-java-stable

  • JAVA MySQL connection tester

    Source code link:

    MySQL JAVA Client

    How to use:

    1. Create a new folder under /home (e.g. /home/test). Then, upload all the following the files to the new created folder

    2. Go to WEBSSH (https://[webapp-name].scm.azurewebsites.net/webssh/host)

    3. Go to the place where you uploaded the code and use "java -jar" to run the executable jar file.

      You also need to provide MySQL server URL, username and password as the following.

      For example:

      • argument[0]: "jdbc:mysql://[servername].mysql.database.azure.com:3306/test?useTimezone=true&serverTimezone=UTC"
      • argument[1]: "[user]@[servername]"
      • argument[2]: "your-password"

      The script will print out connection succeeded or not.

      b678a9270dff:/home# java -jar MysqlDBConnection.jar "jdbc:mysql://rhlmysql.mysql
.database.azure.com:3306/test?useTimezone=true&serverTimezone=UTC" "mytest@rhlmy
sql" "******"
Picked up JAVA_TOOL_OPTIONS: -Djava.net.preferIPv4Stack=true
mysql
performance_schema
sys
test

      If connection failed, it will print out detailed error messages.

      b678a9270dff:/home# java -jar MysqlDBConnection.jar "jdbc:mysql://rhlmysql.mysql
.database.azure.com:3306/test?useTimezone=true&serverTimezone=UTC" "mytest@rhlmy
sql" "*******"
Picked up JAVA_TOOL_OPTIONS: -Djava.net.preferIPv4Stack=true
java.sql.SQLException: Access denied for user 'mytest'@'52.160.85.217' (using password: YES)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
        at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
        at JavaDatabase.MysqlDBConnection.App.main(App.java:36)

    JAVA SQLServer connection tester

    Source code link:

    SQLserver JAVA Client

    How to use:

    1. Create a new folder under /home (e.g. /home/test). Then, upload all the following the files to the new created folder

    2. Go to WEBSSH (https://[webapp-name].scm.azurewebsites.net/webssh/host)

    3. Go to the place where you uploaded the code and use "java -jar" to run the executable jar file.

      You also need to provide SQL server URL, username, password and database name as the following.

      For example:

      • argument[0]: "jdbc:sqlserver://rhlsqltest.database.windows.net:1433"
      • argument[1]: "renhanli@rhlsqltest"
      • argument[2]: "your-password"
      • argument[3]: "database-name"

      The script will print out connection succeeded or not.

      b678a9270dff:/home# java -jar SQLserverConnection.jar "jdbc:sqlserver://rhlsqlte
st.database.windows.net:1433" "renhanli@rhlsqltest" "root@123" "rhl-sqlserver"
Picked up JAVA_TOOL_OPTIONS: -Djava.net.preferIPv4Stack=true
Going to use the following connection string test the connection to SQL server:
jdbc:sqlserver://rhlsqltest.database.windows.net:1433;database=rhl-sqlserver;user=renhanli@rhlsqltest;password=root@123;encrypt=true;trustServ
erCertificate=false;loginTimeout=30;
Successfully connected to database.

      If connection failed, it will print out detailed error messages.

      b678a9270dff:/home# java -jar SQLserverConnection.jar "jdbc:sqlserver://rhlsqlte
st.database.windows.net:1433" "renhanli@rhlsqltest" "root@123" "rhl-sqlserver"
Picked up JAVA_TOOL_OPTIONS: -Djava.net.preferIPv4Stack=true
Going to use the following connection string test the connection to SQL server:
jdbc:sqlserver://rhlsqltest.database.windows.net:1433;database=rhl-sqlserver;user=renhanli@rhlsqltest;password=root@123;encrypt=true;trustServ
erCertificate=false;loginTimeout=30;
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server 'rhlsqltest' requested by the login. Client with IP address '52.160.85.217
' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master da
tabase to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect. ClientC
onnectionId:39af1d1b-55a9-44bf-b2d6-30db4f5f2479
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
        at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:283)
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:129)
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:5173)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3810)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:94)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3754)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2562)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2216)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2067)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1204)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:825)
        at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
        at JavaDatabase.SQLserverConnection.App.main(App.java:27)

Python

The following code was tested on Azure Linux App Service with Python 3.8 built-in docker image.

https://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-python?tabs=ubuntu

  • Python MySQL connection tester

    Source code link:

    MySQL Python Client

    How to use:

    1. Create a new folder under /home (e.g. /home/test). Then, upload all the following the files to the new created folder

    2. Go to WEBSSH (https://[webapp-name].scm.azurewebsites.net/webssh/host)

    3. Go to the place where you uploaded the code and run "connectiontest.sh" to run the test.

      The script will create a Python virtual environment under current path. Then activate this venv and install mysql-connector-python package.

      Also this script will check whether .venv created and mysql-connector-python package is installed or not. It will install the it when needed. So, you can repeatedly run this script. It will only setup the virtual environment once.

      You also need to provide MySQL server URL, username and password as the following.

      For example:

      MySQL server: rhlmysql.mysql.database.azure.com

      Username: mytest@rhlmysql Password:

      Database: test

      The script will print out connection succeeded or not.

      root@9d1afe3572f8:/home/temp# ./connectiontest.sh
>>>>>No .venv exist, initial .venv
>>>>>Activate .venv
>>>>>Install mysql-connector-python
Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.22-cp38-cp38-manylinux1_x86_64.whl (18.0 MB)
Collecting protobuf>=3.0.0
  Using cached protobuf-3.14.0-cp38-cp38-manylinux1_x86_64.whl (1.0 MB)
Collecting six>=1.9
  Using cached six-1.15.0-py2.py3-none-any.whl (10 kB)
Installing collected packages: six, protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.22 protobuf-3.14.0 six-1.15.0
WARNING: You are using pip version 20.2.1; however, version 20.3.3 is available.
You should consider upgrading via the '/home/temp/.venv/bin/python3 -m pip install --upgrade pip' command.
====install mysql package done====
MySQL server: rhlmysql.mysql.database.azure.com
Username: mytest@rhlmysql
Password: Database: test
>>>>>Run Mysql connection test
Connection established
[('information_schema',), ('mysql',), ('performance_schema',), ('sys',), ('test',)]
Finished listing databases
Done.

      If connection failed, it will print out detailed error messages.

      root@9d1afe3572f8:/home/temp# ./connectiontest.sh
>>>>>.venv already created, Activate .venv
WARNING: You are using pip version 20.2.1; however, version 20.3.3 is available.
You should consider upgrading via the '/home/temp/.venv/bin/python3 -m pip install --upgrade pip' command.
mysql package installed.
MySQL server: rhlmysql.mysql.database.azure.com
Username: mytest@rhlmysql
Password: Database: test
>>>>>Run Mysql connection test
1045 (28000): Access denied for user 'mytest'@'52.160.85.217' (using password: YES)
Traceback (most recent call last):
  File "mysqlconnection.py", line 23, in <module>
    cursor.execute("show databases")
NameError: name 'cursor' is not defined

  • Python SQLServer connection tester

    Source code link:

    SQLserver Python Client

    How to use:

    1. Create a new folder under /home (e.g. /home/test). Then, upload all the following the files to the new created folder

    2. Go to WEBSSH (https://[webapp-name].scm.azurewebsites.net/webssh/host)

    3. Go to the place where you uploaded the code and run "connectiontest.sh" to run the test.

      The script will create a Python virtual environment under current path. Then activate this venv and install pyodbc package.

      Also this script will check whether .venv created and pyodbc package is installed or not. It will install the it when needed. So, you can repeatedly run this script. It will only setup the virtual environment once.

      You also need to provide SQLserver URL, port, username and password as the following.

      For example:

      SQL server url: rhlsqltest.database.windows.net Port: 1433 Username: renhanli Password:

      Database: rhl-sqlserver

      The script will print out connection succeeded or not.

      (Note: for the first time you run this script, it will take a while to install g++ and pyodbc driver. Be patient to wait.)

      root@0849ce9d4965:/home/test# ./connectiontest.sh
>>>>>No .venv exist, initial .venv
>>>>>Activate .venv
>>>>>Install g++ and pyodbc...
Hit:1 http://security.debian.org/debian-security stretch/updates InRelease
Ign:2 http://deb.debian.org/debian stretch InRelease
...
...
Collecting pyodbc
  Using cached pyodbc-4.0.30.tar.gz (266 kB)
Using legacy 'setup.py install' for pyodbc, since package 'wheel' is not installed.
Installing collected packages: pyodbc
    Running setup.py install for pyodbc ... done
Successfully installed pyodbc-4.0.30
WARNING: You are using pip version 20.2.1; however, version 20.3.3 is available.
You should consider upgrading via the '/home/test/.venv/bin/python3 -m pip install --upgrade pip' command.
====install pyodbc package done====
SQL server url: rhlsqltest.database.windows.net
Port: 1433
Username: renhanli
Password: Database: rhl-sqlserver
>>>>>Run Mysql connection test
Successfully connected to the SQL server
('master', )
('rhl-sqlserver', )

      If connection failed, it will print out detailed error messages.

      root@0849ce9d4965:/home/test# ./connectiontest.sh
>>>>>.venv already created, Activate .venv
WARNING: You are using pip version 20.2.1; however, version 20.3.3 is available.
You should consider upgrading via the '/home/test/.venv/bin/python3 -m pip install --upgrade pip' command.
pyodbc package installed.
SQL server url: rhlsqltest.database.windows.net
Port: 1433
Username: renhanli
Password: Database: test
>>>>>Run Mysql connection test
('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "test" requested by the login. The login failed.
 (4060) (SQLDriverConnect)')
Traceback (most recent call last):
  File "sqlconnection.py", line 21, in <module>
    csr.execute("SELECT name FROM sys.databases")
NameError: name 'csr' is not defined
Clone this wiki locally