Intermittent error with service account being used for database connector

[eyselman]

I have a server setup that hosts Mirth as well as a SQL Server database containing application data that Mirth transforms to HL7 (this isn't the Mirth database).

I have a service account (mirthsql) that has permissions to the database and all of the tables within the database that I use for the database connector. I have several channels setup (both inbound and outbound) that use the same user for the database connections, however I get an error on 2 of the channels and I don't know why. The mirthsql user works on 1 database writer but not the other; works on one database reader but not the other.

Error: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

Code:

Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
URL: jdbc:sqlserver://localhost:1433;databaseName=databasename;domain=do mainname;integratedsecurity=true;authenticationsch eme=NTLM;

I've tried the URL without integrated security and this is the error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'mirthsql'.

I also have a transform step on an outbound channel (source is database, destination is HL7). The database connection is setup with the mirthsql user, and it consistently gets connected to the database. However, In the transform step, I'm running a script to format OBX segments

Code:

//Connection Info:
var driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
var address = 'jdbc:sqlserver://localhost:1433;databaseName=databsename;domain=domain;integratedsecurity=true;authenticationscheme=NTLM;
var username = 'mirthsql';
var password = 'password';

var dbConn = DatabaseConnectionFactory.createDatabaseConnection (driver,address,username,password);

The above works for my username, but the mirthsql account has this error:

DETAILS: Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

When Integrated security is false:

DETAILS: Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'mirthsql'.

When I've tried to the set the var username to 'domain\mirthsql' then this is the error:

DETAILS: Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "databasename" requested by the login. The login failed.

The driver, the connection URL and the username / password are the same on every channel, just on some channels it works and on others it doesn't. The mirthsql user has the db_owner role on the database so doesn't seem to be a permissions issue and since Mirth and the database are hosted on the same server, I didn't think it was firewall. I'm not sure what else could be the issue and I'm hoping for some guidance on what to check or what to troubleshoot next. I'm trying to pass the support to another person, but I can't do it while my username and password are hardcoded (especially in the transform script) and I need it to be the mirthsql user.

[pacmano1]

If you post here and the forums, reference both posts in both places.

[jonbartels]

Does SQL Server limit the number of connections for your user or to the whole DB?

[eyselman]

It should be unlimited concurrent connections:

[ab-mg-23]

1. What version of Mirth?
2. What version of SQL Server?
3. What version of MS SQL Server JDBC Driver?
4. Is the corresponding native library loaded on the system path?
5. Are the channels using resource directories to load newer/older MSJDBC driver vs what's included with Mirth?
6. Are the URLs equivalent down to casing(ie: integratedSecurity vs integratedsecurity) for the working and non-working channels?

It almost sounds like there's connectivity issues between the server and the domain controller. There's some tracing/logging options with the driver that might reveal some more info about the issues but there should also be Windows event logs about NTLM auth errors.

[pacmano1]

I did suggest on the forum post to use something like https://github.com/pacmano1/Mirth-Snippets/blob/main/executeSharedDBStatement.js so the OP isn't chasing down connect strings and is using a shared connection (subject to volume)

[eyselman]

1. Mirth version:
   Mirth Connect Server 4.4.1

2. SQL Server version:
   SQL Server Management Studio 15.0.18369.0
   SQL Server Management Objects (SMO) 16.100.46041.41
   Microsoft Analysis Services Client Tools 15.0.19342.0
   Microsoft Data Access Components (MDAC) 10.0.17763.6189
   Microsoft MSXML 3.0 6.0
   Microsoft .NET Framework 4.0.30319.42000
   Operating System 10.0.17763

3. I could not find a readme file with the jdbc version nor did I see a JDBC folder of any kind in the SQL Server program files.

4. Not sure what to check for here - we've kept Mirth and SQL server OOB

5. The channels are using the drivers included with Mirth

6. URLs are all the same, I've copied and pasted back and forth and same results.

Checked the communication with the domain controllers, and didn't see any issue there, no errors in the event logs.

I think I need to figure out the JDBC driver with SQL server - that might be where the issue is. Thanks for the list of things to look at.

[pacmano1]

select @@VERSION

Would return the version of SQL server.

[pacmano1]

Also the code block posted above doesn't have the case sensitive parameters @ab-mg-23 already mentioned and the closing quote is missing on the address line.

e.g should be (but the domain would need to be fixed.). I might expect NTLMv2 also.

// Connection Info:
var driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
var address = 'jdbc:sqlserver://localhost:1433;databaseName=databasename;domain=MYDOMAIN;integratedSecurity=true;authenticationScheme=NTLM';
var username = 'mirthsql';
var password = 'password';

var dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password);

[eyselman]

select @@Version

Microsoft SQL Server 2019 (RTM-GDR) (KB5042214) - 15.0.2120.1 (X64) Jul 31 2024 23:58:18 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)