[BUG] MSSQL connectivity error on named database instance

[kktam]

Describe the bug The Mirth documentation indicate the for Microsoft SQL server, named instance is supported. However, when default, or customer Microsoft JDBC adaptors are used, the Mirth connect returns with error and indicate it cannot connect and create the database because "Driver net.sourceforge.jtds.jdbc.Driver claims to not accept jdbcUrl".

To Reproduce Setup steps (if required). Example:

1. Install Mirth
2. Download from microsoft-jdbc-driver-for-sql-server
3. Configure Mirth to use Microsoft SQL server as database.
4. Use following connection string (provided by Mirth technical support) . Use named instance for a new database as "jdbc:sqlserver://MYSERVERNAME\SQL_MIRTH:1433;databaseName=mymirthdb;encrypt=true;trustServerCertificate=true"
5. use driver in mirth properties "database.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver"
6. copy driver to c:\Program Files\Mirth Connect\server-lib\database
7. copy whole Microsoft JDBC driver package to c:\Program Files\Microsoft JDBC DRIVER 10.2 for SQL Server\
8. Save configuration in mirth.properties and Server Manager.
9. Start server.

Expected behavior Mirth Connect find the SQL server instance and install the new database and saids connection successful.

Actual behavior Mirth fails to connect, in the Mirth Log, the following exception is logged:

ERROR 2022-04-14 15:40:13,262 [Main Server Thread] com.mirth.connect.server.Mirth: Error establishing connection to database, retrying startup in 10000 milliseconds
java.lang.RuntimeException: Driver net.sourceforge.jtds.jdbc.Driver claims to not accept jdbcUrl, jdbc:sqlserver://MYSERVERNAME\SQL_MIRTH:1433;databaseName=mymirthdb;encrypt=true;trustServerCertificate=true
                at com.zaxxer.hikari.util.DriverDataSource.<init>(DriverDataSource.java:84)
                at com.zaxxer.hikari.pool.PoolBase.initializeDataSource(PoolBase.java:318)
                at com.zaxxer.hikari.pool.PoolBase.<init>(PoolBase.java:108)
                at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:99)
                at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:94)
                at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:131)
                at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:58)
                at org.apache.ibatis.session.defaults.DefaultSqlSession.getConnection(DefaultSqlSession.java:220)
                at org.apache.ibatis.session.SqlSessionManager.getConnection(SqlSessionManager.java:221)
                at com.mirth.connect.server.Mirth.startup(Mirth.java:249)
                at com.mirth.connect.server.Mirth.run(Mirth.java:162)

Screenshots Driver copied in the various Mirth folder locations Mirth log mirth.log Mirth properties mirth.properties.txt

Environment (please complete the following information):

• OS: Windows 2019 Development and Data Center version. But also tested in Windows 10.
• Java Distribution/Version [Java 8 Update 311, Java SE Development Kit 8 Update 311 64 bit]
• Connect Version [3.1.2]
• Database: SQL Server 2019 Enterprise, also tested with Development version.
• Microsoft JDBC driver - does not work with native driver inside Mirth 3.1.2, need to upgrade using microsoft-jdbc-driver-for-sql-server

Workaround(s) No known work around has been found. Must use default instance only.

Additional context N/A.

[ab-20-m]

Does the mirth.properties file on your Mirth Server correctly match the contents of the attached mirth.properties.txt? I was able to successfully connect to a SQL Server 2019 Dev named instance with the same style JDBC URL and database.driver setting once I got the changes to stick in mirth.properties. I was using Notepad to edit and because the default ACL doesn't grant Users read/write it ended up dumping out to mirth.properties.txt until I adjusted the ACL for my user account to have full permission to the file.

[pacmano1]

MSSQL uses dynamic ports for the other listening instances, I'd expect that is your issue here. Or at least the below is a way to fix that.

Please see https://popdock.eonesolutions.com/knowledge-base/how-to-find-the-tcp-port-number-your-sql-instance-is-listening-on.

I just set TCP Port in IPAll section to 1434, which is one up from the default instance.

You don't need the instance name in the sql server connect string.

Note tables created and mirth running:

[pladesma]

Others have given good suggestions on troubleshooting your issue. One other thing to consider is the JDBC driver that you've added. We package the 8.4.1 MSSQL JDBC driver with Connect. Have you tried using that driver to see if it makes a difference?

[kktam]

Thank for you for input. we will need some time to test the new suggestions on our platform.

Since dynamic ports, and firewalls was mentioned as one potential solution, we like to mentioned that we have also enabled TLS 1.2 and set TLS 1.2 as default and only supported protocol. We like to ask if this may be a factor in causing this issue?

[cturczynskyj]

It looks like there is a parameter you can use in the jdbc connection string to specify which TLS protocols to be considered during secure connections. You can try adding:

sslProtocol=TLSv1.2