[BUG] Nextgen Connect cannot auto create mssql database on first connect

[kktam]

Describe the bug
When Nextgen connect is installed and first try to connect with an existing Microsoft SQL server 2019. The Mirth Connect Server manager sometimes show the server has started. But inside the SQL server instance, the database has not been created (empty).

SQL server 2019 is required to work with TLS 1.2.

To Reproduce
Setup steps (if required). Example:

1. install Nextgen connect.
2. Open Mirth Connect Server Manager
3. setup Database with options type = sqlserver, URL = jdbc:ktds:sqlserver://localhost:1433/mirthdb;create=true;update=true;ssl=request
4. set apply save configuration
5. (option tried with and without step 5) open c:\Program Files\Mirth Connect\conf\mirth.properties
   - add database.url = com.microsoft.sqlserver.jdbc.SQLServerDriver
6. (option with 5) download and install Microsoft JDBC driver and follow instructions and extract to c:\Program Files\Microsoft JDBC DRIVER 10.2 for SQL Server\ and also copy mssql-jdbc-10.2.0.jre8.jar to c:\Program Files\Mirth Connect\server-lib\database

Steps to reproduce the behavior:

• see above

Expected behavior
When I open mirthdb database using Microsoft SQL Server Management Studio and refresh, the mirthdb should be full of tables created by Nextgen connect.

Actual behavior
A clear and concise description of what actually happens.

Screenshots
If applicable, add screenshots to help explain your problem. - may add later

Environment (please complete the following information):

• OS: Windows Server 2019 Enterprise, No Service pack
• Java Distribution/Version Oracle JDK8 (jdk-8u311-windows-x64.msi)
• Connect Version 3.9.1 (but also tested 3.12)
• Database : SQL Server 2019 Enterprise RTM 15.0.20000.5 (X64) Sept 24, 2019

Workaround(s)
No known workaround. Only got the installation working with Apache Derby.

Additional context

[pacmano1]

For version 3.12:

See #5087 (reply in thread) for screenshots for both jtds and the MS driver.

Mirth's database should already be created on the SQL server before starting Mirth. I would create the db, create a named user and assign that user db_owner permissions on the db you created.

Also note page 443 of the latest user manual where it discusses trusted certs (applies to the MS driver, not jtds)

Please read https://forums.mirthproject.io/forum/mirth-connect/support/180890-updating-microsoft-jdbc-driver-for-mirthdb#post180979.

Also, please read the documentation on mssql jdbc and make sure all the parameters attempted in your post work. The examples connections strings in mirth.properties do not look like the one you posted above. I think createdb=true is a Derby thing only but confirm that.

You do not need to install the mssql jdbc driver for mirth 3.12.

Lastly, it is always helpful to post relevant parts of mirth.log since it typically indicates what problems are.

[pacmano1]

@kktam can you confirm whether or not you were able to get your stuff working? This issue should probably be over in discussions and not classified as a bug (yet).

[kktam]

Attach is my mirth.log. It appears the default SQL server driver cannot understand the new connection string. Can this be made to work with the default driver, or if I need to install a Microsoft JDBC driver what are the steps to change the drivers in Nextgen connect 3.12. (or 3.9.1)?

mirth.log

[pacmano1]

Please paste your exact connect string. Or a screenshot of it.

For 3.12:

If the sql server and mirth server are on the same machine, this uses the jtds driver:

For the above do not use the database.driver setting in mirth.properties. The above connect string uses the jtds driver.

---

For the MSSQL Driver, do the below, note the use of database.driver

[kktam]

yes, my missing configuration is database.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver after using the new jdbc url.

The NextGen connect now creates a database (with tables) in SQL server with both version 3.9.1 and 3.12.

My installation is working now. Thank you very much for all your help.