Test JDBC Connection to a Database Server

[tim210]

Background

ServiceNow instances can be setup to import data from external databases using JDBC. This setup is documented under Data Sources->JDBC type data source in the documentation:

JDBC type data source

But when there are issues with this JDBC connection it can sometimes be difficult to see where the issue lies, especially if the JDBC connection for the Data Source is being made via a MID Server. The attached program is a simple Java program that uses either the Microsoft JDBC driver for Microsoft SQL Server (Using the JDBC Driver | Microsoft Docs ) or the MariaDB Connector (https://downloads.mariadb.org/connector-java/ ) for MariaDB/MySQL to try to connect to a remote database server.

If the connection is successful no queries are run and no data is changed, the program just reports 'Connected Successfully'. If the connection attempt isn't successful it reports the full error message from the JDBC driver. As well as often giving a more useful error message this program removes ServiceNow code from the equation and can make it clearer whether a connection problem is a an environmental issue (e.g. network issue) or not, rather than a ServiceNow issue.

How to Use

Download the attached file jdbcconntest.zip,
extract it and follow the instructions in README.txt

Usage Examples

Successful connection to an MS SQL Server Database:

$ java -jar jdbcconntest.jar

Choose the type of database server to connect to (1 - Microsoft SQL Server, 2 - MySQL/MariaDB): 1

Please enter the hostname of the database server to login to: localhost

Please enter the port number of the database server to login to: 1433

Please enter the name of the database schema/catalog to access: TestDB

Please enter the database username to login with: test_local

Please enter the database user's password:

Connected Successfully

Unsuccessful connection to an MS SQL Server Database (wrong port number):

$ java -jar jdbcconntest.jar

Choose the type of database server to connect to (1 - Microsoft SQL Server, 2 - MySQL/MariaDB): 1

Please enter the hostname of the database server to login to: localhost

Please enter the port number of the database server to login to: 1234

Please enter the name of the database schema/catalog to access: TestDB

Please enter the database username to login with: test_local

Please enter the database user's password:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1234 has failed. Error: "Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

Unsuccessful connection to an MS SQL Server Database (wrong password):

$ java -jar jdbcconntest.jar

Choose the type of database server to connect to (1 - Microsoft SQL Server, 2 - MySQL/MariaDB): 1

Please enter the hostname of the database server to login to: localhost

Please enter the port number of the database server to login to: 1433

Please enter the name of the database schema/catalog to access: TestDB

Please enter the database username to login with: test_local

Please enter the database user's password:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'test_local'. ClientConnectionId:15d20592-de39-4eee-b371-ee55b0f194d8

Successful connection to a MariDB or MySQL Database:

$ java -jar jdbcconntest.jar

Choose the type of database server to connect to (1 - Microsoft SQL Server, 2 - MySQL/MariaDB): 2

Please enter the hostname of the database server to login to: localhost

Please enter the port number of the database server to login to: 3306

Please enter the name of the database schema/catalog to access: istanbulp3_16001

Please enter the database username to login with: glide

Please enter the database user's password:

Connected Successfully

Unsuccessful connection to a MariDB or MySQL Database (wrong port number):

$ java -jar jdbcconntest.jar

Choose the type of database server to connect to (1 - Microsoft SQL Server, 2 - MySQL/MariaDB): 2

Please enter the hostname of the database server to login to: localhost

Please enter the port number of the database server to login to: 3399

Please enter the name of the database schema/catalog to access: istanbulp3_16001

Please enter the database username to login with: glide

Please enter the database user's password:

java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=localhost)(port=3399)(type=master) : Connection refused (Connection refused)

Unsuccessful connection to a MariDB or MySQL Database (invalid username):

$ java -jar jdbcconntest.jar

Choose the type of database server to connect to (1 - Microsoft SQL Server, 2 - MySQL/MariaDB): 2

Please enter the hostname of the database server to login to: localhost

Please enter the port number of the database server to login to: 3306

Please enter the name of the database schema/catalog to access: istanbulp3_16001

Please enter the database username to login with: invaliduser

Please enter the database user's password:

java.sql.SQLInvalidAuthorizationSpecException: Access denied for user 'invaliduser'@'localhost' (using password: YES)

[Jace Benson]

Is this still used. How helpful is it?

[martin e]

very usefull, just used it while troubleshooting connectivity to a back-end maria db with jdbc.

[Jens Gerber]

Hi Martin,

I have the same or similar problem.

Since upgrade from madrid to NY the midserver cannot connect the external MariaDB (mysql).

java.sql.SQLInvalidAuthorizationSpecException: Access denied for user....

In the past it works. And the connection with the test tool jdbcconntest is working fine with same host, db, user , password, port.

Maybe you can a little bit describe what was your problem ?

Thank you.

Best regards,

Jens