6.2 is what I already had loaded so I went with it. I will try the latest.

I tried the URL and when I went to test the connection I got an error about "authenticationScheme=NTLM" being invalid. Once I removed attribute, I am now getting "Driver can not be loaded" at least from the Management Portal. If I try it manually via Terminal to test connection it fails with a -1. When I look at the JDBC Gateway log I am seeing the following..

.%Net.Remote.Gateway..%WriteOutput.....May 16, 2023 1:48:05 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>.WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path.
 

• Driver name: com.microsoft.sqlserver.jdbc.SQLServerDriver
• URL: jdbc:sqlserver://CPDDB.osumc.edu:1739;database=CPD;trustServerCertificate=true;integratedSecurity=true;domain=osumc;authentication=NotSpecified
• Class path: 

Do I need a Certificate installed on Linux for that to work?

We were using a Local Windows Authentication account with a Hardcoded user name and password for our jobs to connect to MS SQL for the longest time, but it was frowned upon as everyone knew the user and password to sign into the database. So we were asked to make a Service Account on the Domain through Active Directory. In our AIX/Linux world we had not use Active Directory authentication so it became an issue to try to figure out how to make it work. That's when I discovered jTDS. I am going to try what @Jeff pointed out below to see if I can make it work as I could never find the correct connection string to make the Microsoft Driver to work for us.

Wow how did you pin point that down? I never did see a ERROR #5023: Remote Gateway Error: java.lang.AbstractMethodError within the Operation/Process that would of shown me that.

Is there a better method to using Domain Service Accounts in Active Directory to connect to MS SQL through JDBC on Unix/Linux other than jTDS?

We are using the  jTDS (jtds-1.3.1.jar) driver because we are using Domain Service Accounts to access the databases. We are doing this because I could not get the Microsoft JDBC drivers to authenticate properly using the Domain Service Accounts in Active Directory being on Unix/Linux. 

:>java -version
openjdk version "1.8.0_362"
OpenJDK Runtime Environment (build 1.8.0_362-b09)
OpenJDK 64-Bit Server VM (build 25.362-b09, mixed mode)
 

Yes with WRC we saw a new Connection using the %JDBCGateway created each time. So if we already had %JDBCGateway running and executed each statement it would spawn a new connection that would say Established when you do a netstat -anp at the OS Level.

I used the Linked Table Wizard to create the code for the table, then added the Class Method as a way to query the table in the one case...

/// Generated by the Link Table wizard on 2020-12-07 13:29:25.  Note that you can access the data in this class only when the external database is accessible.

Class osuwmc.CPD.vProviderNPIEns Extends %Library.Persistent [ Not ProcedureBlock, SqlRowIdPrivate, SqlTableName = vProviderNPIEns, StorageStrategy = GSQLStorage ]

{

/// Specifies details for the SQL Gateway Connection that this class uses

Parameter CONNECTION = "jTDS-CPD-Prod,NOCREATE";

/// Specifies the external database that this class uses

Parameter EXTDBNAME = "Microsoft SQL Server";

/// Determines if INSERT statements for this external table attempt to retrieve auto-generated keys.  Set this to 0 if this external table does not support auto generated keys.

Parameter EXTERNALGENERATEDKEYS = 1;

/// Specifies the external table to which this class refers

Parameter EXTERNALTABLENAME = "dbo.vProvider_NPI_Ens";

Property DoctorNumber As %String(EXTERNALSQLNAME = "DoctorNumber", EXTERNALSQLTYPE = 1, MAXLEN = 6) [ ReadOnly, Required, SqlColumnNumber = 2, SqlFieldName = DoctorNumber ];

Property NPI As %String(EXTERNALSQLNAME = "NPI", EXTERNALSQLTYPE = 12, MAXLEN = 10) [ ReadOnly, SqlColumnNumber = 3, SqlFieldName = NPI ];

Index MainIndex On (DoctorNumber, NPI) [ IdKey, PrimaryKey ];

Index ProviderDoctorNumber On DoctorNumber;

ClassMethod FindNPI(DoctorNumber As %String) As %String [ SqlProc ]

{

  set NPI = ""

  &sql(SELECT NPI into :NPI

    FROM osuwmc_CPD.vProviderNPIEns WHERE DoctorNumber=:DoctorNumber)

  IF SQLCODE <0 {write "SQLCODE error ",SQLCODE," ",%msg QUIT}

  ELSEIF SQLCODE = 100 {write "Query return no results" QUIT}

  quit NPI

}

Storage GSQLStorage

{

<ExtentSize>111002</ExtentSize>

<Property name="%%ID">

<Selectivity>1</Selectivity>

</Property>

<StreamLocation>^osuwmc.CPD.vProviderNPIEnsS</StreamLocation>

<Type>%Storage.SQL</Type>

}

}

In the Second Case I used the Linked Table Wizard, but call the sql code from the DTL, so I am not sure how that would be an issue.

Working with WRC, our custom code was a copy of EnsLib.Email.AlertOperation that referenced older code that was not referenced anymore in 2022.1. Updated the referenced code and was able to get it to work.

I see what you mean in 2022.1 it will not compile but however it does compile in 2018.1.3.

2022.1

Compilation started on 05/02/2023 08:05:23 with qualifiers 'cuk'

Compiling class osuwmc.AlertEmail

Compiling routine osuwmc.AlertEmail.1

ERROR: osuwmc.AlertEmail.cls

ERROR:  osuwmc.AlertEmail.1(13) : MPP5646 : ##expression on '$$macroText^%occMessages($lb("""Ensemble Alert from configuration item '%1' in system '<PROTECT> 202 macroText+51^%occMessages ^IRIS.Msg("Ensemble","en",2740803238),/ensemble/TEST/mgr/enslib/' on node '%3'""","""Ensemble"""))' failed with an error: %2

 TEXT:      Set pMailMessage.Subject = pMailMessage.Subject _ $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble Alert from configuration item '%1' in system '%2' on node '%3'""","""Ensemble"""))),pAlertRequest.SourceConfigName,tSystemName,tNodeName)

ERROR:  osuwmc.AlertEmail.1(16) : MPP5646 : ##expression on '$$macroText^%occMessages($lb("""Ensemble Alert from configuration item '%1' on system '<PROTECT> 202 macroText+51^%occMessages ^IRIS.Msg("Ensemble","en",3633416132),/ensemble/TEST/mgr/enslib/'""","""Ensemble"""))' failed with an error: %2

 TEXT:      Set pMailMessage.Subject = pMailMessage.Subject _ $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble Alert from configuration item '%1' on system '%2'""","""Ensemble"""))),pAlertRequest.SourceConfigName,tSystemName)

ERROR:  osuwmc.AlertEmail.1(25) : MPP5646 : ##expression on '$$macroText^%occMessages($lb("""Ensemble alert email triggered at %1 [<PROTECT> 202 macroText+51^%occMessages ^IRIS.Msg("Ensemble","en",2328442859),/ensemble/TEST/mgr/enslib/ UTC]""","""Ensemble"""))' failed with an error: %2

 TEXT:    Set tTimeMessage = $select(..IncludeUTCTimes: $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble alert email triggered at %1 [%2 UTC]""","""Ensemble"""))),tNowLocal,tNow), 1 : $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble alert email triggered at %1""","""Ensemble"""))),tNowLocal))

ERROR:  osuwmc.AlertEmail.1(25) : MPP5646 : ##expression on '$$macroText^%occMessages($lb("""Ensemble alert email triggered at %1""","""Ensemble"""))' failed with an error: <PROTECT> 202 macroText+51^%occMessages ^IRIS.Msg("Ensemble","en",3301903811),/ensemble/TEST/mgr/enslib/

 TEXT:    Set tTimeMessage = $select(..IncludeUTCTimes: $$FormatText^%occMessages(,tNowLocal,tNow), 1 : $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble alert email triggered at %1""","""Ensemble"""))),tNowLocal))

ERROR: osuwmc.AlertEmail.cls

ERROR:  osuwmc.AlertEmail.1(14) : MPP5646 : ##expression on '$$macroText^%occMessages($lb("""Ensemble ManagedAlert from configuration item '%1' in system '<PROTECT> 202 macroText+51^%occMessages ^IRIS.Msg("Ensemble","en",197323985),/ensemble/TEST/mgr/enslib/' on node '%3'""","""Ensemble"""))' failed with an error: %2

 TEXT:      Set tMailMessage.Subject = tMailMessage.Subject _ $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble ManagedAlert from configuration item '%1' in system '%2' on node '%3'""","""Ensemble"""))),tManagedAlert.SourceConfigName,tSystemName,tNodeName)

ERROR:  osuwmc.AlertEmail.1(17) : MPP5646 : ##expression on '$$macroText^%occMessages($lb("""Ensemble ManagedAlert from configuration item '%1' on system '<PROTECT> 202 macroText+51^%occMessages ^IRIS.Msg("Ensemble","en",466437042),/ensemble/TEST/mgr/enslib/'""","""Ensemble"""))' failed with an error: %2

 TEXT:      Set tMailMessage.Subject = tMailMessage.Subject _ $$FormatText^%occMessages(##safeexpression($$macroText^%occMessages($lb("""Ensemble ManagedAlert from configuration item '%1' on system '%2'""","""Ensemble"""))),tManagedAlert.SourceConfigName,tSystemName)

Detected 6 errors during compilation in 0.091s.

2018.1.3

Compilation started on 05/02/2023 08:13:51 with qualifiers 'cuk'

Class osuwmc.AlertEmail is up-to-date.

Compilation finished successfully in 0.199s.

We found that if we execute EnableConfigItem from the shell on the OS level we can kick off multiple processes (multi-threading) up to 125 instances much faster than doing this from an Object Script in Terminal.

I did not get the same error, and it was successful.

Alex, 

I tried that but now I am receiveing... ERROR #5002: ObjectScript error: <UNDEFINED>zDecodeBase64HL7ToFile+22^osuwmc.Functions.1 *%C(10)

ClassMethod DecodeBase64HL7ToFile(base64 As %Stream.GlobalBinary, Ancillary As %String, FileName As %String) As %String

{

  set ArchDir = "/ensemble/data/transfer/AncillaryPDF/"

  set ArchAncDir = ArchDir_Ancillary_"/"

  set FaxDateDir = ArchAncDir_$PIECE($ZDATE($HOROLOG,7)," ",1)_"-"_$PIECE($ZDATE($HOROLOG,7)," ",2)_"-1/"

  if '##class(%Library.File).DirectoryExists(ArchDir)

  {

    do ##class(%Library.File).CreateDirectory(ArchDir)

  }

  if '##class(%Library.File).DirectoryExists(ArchAncDir)

  {

    do ##class(%Library.File).CreateDirectory(ArchAncDir)

  }

  if '##class(%Library.File).DirectoryExists(FaxDateDir)

  {

    do ##class(%Library.File).CreateDirectory(FaxDateDir)

  }

  set Oref = ##class(%FileBinaryStream).%New()

  ///$$$LOGINFO(FaxDateDir_FileName)

  set Oref.Filename = FaxDateDir_FileName

  Do base64.Rewind()

  While 'base64.AtEnd {

      set ln = base64.ReadLine()

      set:ln[%C(10) base64.LineTerminator=$C(10)

      set lnDecoded = $system.Encryption.Base64Decode(ln)

    do Oref.Write(lnDecoded)

  }

  Do Oref.%Save()

  set PDFFilePath = FaxDateDir_FileName

  return PDFFilePath

}

By Adding a timeout of 2, and setting the force flag I was able to get it down from 18 min to roughly 11 min. If I remove the timeout and force flag, and watched the output I could possibly find those problematic children. I wonder if using Ens.Job would be any easier as the objects have been disabled its a matter of getting the Jobs to stop and send the unbind. 

Has anyone used Ens.Jobs before to quickly bring down a Namespace in a controlled environment? We need to unsure the unbinds are sent so we can move to a new IP address on a different section of the network.

Using the suggestions below, looping through the Config Items doesn't appear to be the issue. I ran into an issue with the UpgradeProduction. In total it took 18 min to shutdown all the Services in just one namespace when I looped through all the Config Items, disabled them, and then Updated the Production with no timeout or force to shut them down. Is there a way to make UpgradeProduction run faster beside setting the timeout or force?

Thanks @Jeffrey Drumm, I had the same thoughts. I am working with other team members in discussing the options to see what is best.

I need to be able to loop through all the Services/Operations to shut them down to ensure that the TCP Disconnect is sent. 

We had a consultant create us a script that uses Regex to loop through and shutdown those Services/Operations, but running the script to bring everything down takes a good 10 min or so to disable all the Service Operation Objects.

In testing the cut over when we did our Test Environment in total it took us 20 min to bring down Ensemble (2018.1.3) and bring up IRIS (2022.1) with the network changes.

But we forgot this step and had to ask different systems to restart their Interfaces because they were still hung on the previous connections and don't have framework to realize the connection was no longer connected.

Is there a way to be able to cutdown the response time of EnableConfig item?