New post
AllAccepted answers
Scott Roth · Oct 6, 2023

While thinking about this, I am wondering if using an encryption key might be a possible solution as well that way there is limited access to the password, and the key would just have to be regulated to be updated every so often. With using a AD service account, the password still needs to be updated or kept up to date, and still needs to be passed into the shell script for Terminal to open.

I have submitted the following IDEA...

https://ideas.intersystems.com/ideas/DPI-I-466

Scott Roth · Aug 3, 2023

The issue was with the code that I was using in my Business Operation that was not calling the SSL Configuration. I have since reverted my Business Operation code back to the more standard code that is described in the Documentation, however I am still running into issues making sure the correct format of the Header is being sent in the REST API call, and how to interpret the JSON that is being returned by the REST API.

Scott Roth · Aug 3, 2023

According to WRC there is a memory leak with the way the Class Path of the driver is being handled. The reason for this issue was that I had the Class Path defined within the SQL Gateway. I thought this was only due to the jTDS driver we were using, but it is an overall issue with any Java Gateway Service. So I updated the Java Gateway service I was using for the Microsoft JDBC Connection for that Database to now have the Class Path of the driver, and I have not seen issues since.

Scott Roth · Jul 20, 2023

For others I figured out the issue. Had to use the Base64 formatted Certificate Chain (p7b) from Windows ADCS (Active Directory Certificate Service).

  1. Download Base64 p7b to /etc/pki/ca-trust/source/anchors/ in RedHat
  2. Change ownership group to include irisusr
  3. Change permissions to Read (666)
  4. Convert p7b to pem
  • sudo openssl pkcs7 -in xxxxx.p7b -print_certs -out xxxxx.pem

When I went through testing the request I got the following...

DEVCLIN>set request=##class(%Net.HttpRequest).%New()

DEVCLIN>set request.Server = "xxxxxxxxxxxx"

DEVCLIN>set request.Port=443

DEVCLIN>set request.SSLConfiguration="OSUWMC"

DEVCLIN>set request.Https=1

DEVCLIN>set tSC=request.Get("/",2)
HTTP/1.1 200 OK
ACCEPT-RANGES: bytes
CACHE-CONTROL: private
CONTENT-ENCODING: gzip
CONTENT-LENGTH: 467
CONTENT-TYPE: text/html
DATE: Thu, 20 Jul 2023 20:08:54 GMT
ETAG: "b072b0f23afdd01:0"
LAST-MODIFIED: Fri, 02 Oct 2015 17:51:21 GMT
NTCOENT-LENGTH: 701
SERVER: Microsoft-IIS/8.5
X-POWERED-BY: ASP.NET

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>IIS Windows Server</title>
<style type="text/css">
<!--
body {
        color:#000000;
        background-color:#0072C6;
        margin:0;
}

#container {
        margin-left:auto;
        margin-right:auto;
        text-align:center;
        }

a img {
        border:none;
}

-->
</style>
</head>
<body>
<div id="container">
<a href="http://go.microsoft.com/fwlink/?linkid=66138&amp;clcid=0x409"><img src="iis-85.png" alt="IIS" width="960" height="600" /></a>
</div>
</body>
</html>

Scott Roth · Jul 6, 2022

An issue I see with the code is with the value you are specifying with ConvertDateTime. If you are sending YYYY-mm-dd your code needs to account for that "%Y-%m-%d" 

set parms(1) = ##class(Ens.Rule.FunctionSet).ConvertDateTime("2022-01-01","%Y-%m-%d","%q(1)")
set parms(2) = ##class(Ens.Rule.FunctionSet).ConvertDateTime("2022-01-05","%Y-%m-%d","%q(1)")

Scott Roth · Jul 6, 2022

How is the date columns defined in your Stored Procedure class file? 

For example I have a InsertProviderSp class file that represents all of the Parameters of the stored procedure, and within it I have a Property DOB As %TimeStamp. 

My method within my Outbound adapter looks like this....

If you are "hardcoding" the dates within the EnsLib.SQL.OutboundAdapter, you could be able to call the above ConvertDateTime by adding lines to for ##class(Ens.Rule.FunctionSet).ConvertDateTime....

I have many cases where I am inserting, update, or querying data from external MS SQL sources to pull into Ensemble, so let me know if you still need help.

Scott Roth · Apr 13, 2022

Yes, I was able to create separate roles, and copy the existing Data Lookup pages, locking the users down to only being able to access that particular page for their lookup tables. There are two class files that would need to be created. 

1. Filter Dialog if you want to limit the users down to their particular tables.

2. A copy of EnsPortal.LookupSettings - which will limit users ability to get to other places within Ensemble. 

I haven't tested this in IRIS just yet as we are in the process of moving to IRIS.