go to post Scott Roth · Jul 2, 2018 Thanks.I am now to the point where I am getting Error message: LDAP error: 0 - Success. How can a Success be a Failure?ZAUTHENTICATE(ServiceName,Namespace,Username,Password,Credentials,Properties) PUBLIC {#include %occErrors#include %sySecurity#include %sySite#include %syLDAP#define LDAPServer $Get(^OSUMCLDAP("Server"))#define WindowsLDAPServer 1#define WindowsCacheClient 0#define UseSecureConnection 1#define UnixCertificateFile $Get(^OSUMCLDAP("LDAPKey"))_"certnew.pem"#define WindowsBaseDN "dc="_$Get(^OSUMCLDAP("Domain"))_",dc=edu"#define WindowsFilter "sAMAccountname"#define WindowsAttributeList $lb("displayName","department","mail")s $zt="Error" s Status = 0 i Password="" {s Status= $SYSTEM.Status.Error($$$InvalidUsernameOrPassword)g Error } i $$$WindowsLDAPServer{s AdminDN=$Get(^OSUMCLDAP("User"))s AdminPW=$Get(^OSUMCLDAP("Pass")) } #;The following line sets up the internal LDAP structures. i $$$ISWINDOWS,$$$UseSecureConnection {s LD=##Class(%SYS.LDAP).Init($$$LDAPServer,636) } else {s LD=##Class(%SYS.LDAP).Init($$$LDAPServer) } i LD=0 {s Status=##Class(%SYS.LDAP).GetLastError()s Status="Init error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status)g Error } s Status=##Class(%SYS.LDAP).SetOption(LD,$$$LDAPOPTXTLSCACERTFILE,$$$UnixCertificateFile) i Status'=$$$LDAPSUCCESS{s Status ="SetOption error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) g Error } s Status=##class(%SYS.LDAP).StartTLSs(LD) i Status'=$$$LDAPSUCCESS{s Status=Status_",ldap_setoption(Certificate) - "_##class(%SYS.LDAP).Err2String(Status)g Error } s Status=##Class(%SYS.LDAP).SimpleBinds(LD,AdminDN,AdminPW) i Status'=$$$LDAPSUCCESS {s Status = Status_", ldap_Simple_Bind(AdminDN) - "_##Class(%SYS.LDAP).Err2String(Status) #;w !,Statusg Error } i $$$WindowsLDAPServer {s Filter=$$$WindowsFilter_"="_Username } i $$$WindowsLDAPServer {s AttributeList=$$$WindowsAttributeList } i $$$WindowsLDAPServer {s BaseDN=$$$WindowsBaseDN } s SearchScope=$$$LDAPSCOPESUBTREE s Timeout=30 s SizeLimit=1 s Status=##Class(%SYS.LDAP).SearchExts(LD,BaseDN,SearchScope,Filter,AttributeList,0,"","",Timeout,"",.SearchResult) i Status'=$$$LDAPSUCCESS {i Status=$$$XLDAPFILTERERROR {s Status="1,User "_Username_" does not exist"w !,Status} else {s Status=Status_",ldap_Search_Ext - "_##Class(%SYS.LDAP).Err2String(Status)}g Error } s NumEntries=##Class(%SYS.LDAP).CountEntries(LD,SearchResult) i NumEntries=-1 { s Status=##Class(%SYS.LDAP).GetError(LD) s Status=Status_",ldap_Count_Entries - "_##Class(%SYS.LDAP).Err2String(Status) g Error }i NumEntries=0 {s Status="1,User "_Username_" does not exist" g Error } i NumEntries>1 {s Status="1,LDAP Filter is not unique" g Error } s CurrentEntry=##Class(%SYS.LDAP).FirstEntry(LD,SearchResult) i CurrentEntry=0 {s Status=##Class(%SYS.LDAP).GetError(LD) s Status=Status_",ldap_FirstEntry - "_##Class(%SYS.LDAP).Err2String(Status)g Error } s DN=##Class(%SYS.LDAP).GetDN(LD,CurrentEntry) i Password="" {s Status="1,ldap_Simple_Bind("_DN_") - password cannot be null"g Error } s Status=##Class(%SYS.LDAP).SimpleBinds(LD,DN,Password) i Status'=$$$LDAPSUCCESS {s Status=Status_",ldap_Simple_Bind("_DN_") - "_##Class(%SYS.LDAP).Err2String(Status)g Error } s Attribute=##Class(%SYS.LDAP).FirstAttribute(LD,CurrentEntry,.Ptr) while (Attribute'="") { s Values=##Class(%SYS.LDAP).GetValuesLen(LD,CurrentEntry,Attribute) #;Values:"_Values s Properties("Attributes",Attribute)=Values s Attribute=##Class(%SYS.LDAP).NextAttribute(LD,CurrentEntry,.Ptr) } s Properties("Username")=Username s Properties("FullName")=$li(Properties("Attributes","displayName")) k Properties("Attributes","displayName") s Properties("Comment")=$li(Properties("Attributes","department")) k Properties("Attributes","department") s Properties("EmailAddress")=$li(Properties("Attributes","mail")) k Properties("Attributes","mail") i $d(SearchResult) d ##Class(%SYS.LDAP).MsgFree(SearchResult) s GroupFilter="(&(objectClass=group)(member:1.2.840.113556.1.4.1941:="_DN_"))" s GroupAttributes="" s Status=##Class(%SYS.LDAP).SearchExts(LD,BaseDN,$$$LDAPSCOPESUBTREE,GroupFilter,GroupAttributes,0,"","",10,0,.GroupSearchResult) #;GroupSearch Status: "_Status i Status'=$$$LDAPSUCCESS {w !,"SearchExts error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status)g Error } s GroupNumEntries=##Class(%SYS.LDAP).CountEntries(LD,GroupSearchResult) i GroupNumEntries=-1 {s Status=##Class(%SYS.LDAP).GetError(LD)s Status=##Class(%SYS.LDAP).Err2String(Status)g Error }w ! i GroupNumEntries=0 {w !,"No nested groups for "_Username_" found"g Done } i GroupNumEntries>0 { } s GroupCurrentEntry=##Class(%SYS.LDAP).FirstEntry(LD,GroupSearchResult) i GroupCurrentEntry=0 {s Status=##Class(%SYS.LDAP).GetError(LD)w !,"FirstEntry error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status)g Error } s Groups="" While (GroupCurrentEntry'=0) {s GroupDN=##Class(%SYS.LDAP).GetDN(LD,GroupCurrentEntry)i GroupDN="" {s Status=##Class(%SYS.LDAP).GetError(LD)w !,"GetDN Group error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status)g Error}s CN=$p(GroupDN,",",1)s AD=$p(CN,"=",2)s AD=$zcvt(AD,"L") s exists=''$d(^|"%SYS"|SYS("Security","RolesD",AD))i exists{s Properties("Roles") = AD} s GroupCurrentEntry=##Class(%SYS.LDAP).NextEntry(LD,GroupCurrentEntry) }Donei +$d(LD) d ##Class(%SYS.LDAP).UnBinds(LD) #;w !,"SystemOK "_$SYSTEM.Status.OK() q $SYSTEM.Status.OK()Error s $zt="" i $d(SearchResult) d ##Class(%SYS.LDAP).MsgFree(SearchResult) i $d(GroupSearchResult) d ##Class(%SYS.LDAP).MsgFree(GroupSearchResult) i +$d(LD) s Status=##class(%SYS.LDAP).UnBinds(LD) i $ze'=""{ q $SYSTEM.Status.Error($$$CacheError,$ze) } else{ q $SYSTEM.Status.Error($$$GeneralError,"LDAP error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status)) }}
go to post Scott Roth · Jun 7, 2018 Haven't tried that. Wasn't sure if it was going to make me do the Authentication before I connect to the external database, or while connecting to the external database.Maybe I am making a mountain out of a mole hill.
go to post Scott Roth · May 29, 2018 After many TRACE statements found I was missing (1) after the FT1 in my syntax. So it should be... request.GetValueAt("FT1(1):16.4.2")
go to post Scott Roth · May 29, 2018 When I look at the message in Message viewer it is showing 06CL as FT1:16.4.2
go to post Scott Roth · May 10, 2018 What type of database is the stored procedure on? If it is Microsoft have you tried VARCHAR(8000)? MAX does not exist in some SQL languages so sometime you have to limit it down to the number of bytes I believe. Though I might be wrong.
go to post Scott Roth · Apr 10, 2018 Just adding a bump to see if anyone has a way to have the system users bypass the delegated sign on without having to hard code the system.THanksScott
go to post Scott Roth · Apr 6, 2018 Using the Ensemble Scheduler we ran into issues when we had multiple schedules running at the same time, and notice it pausing the whole environment just to refresh. That's why we went down this route.It looks like this particular Inbound.SQL.Adapter service that caused the issue had a data lock on the database which caused more issues. So I am wondering if the data lock caused EnsConfigItem() to get in a hung status.
go to post Scott Roth · Mar 15, 2018 I have always called it the other way around with the outbound before the I in the Execute Procedure Parm Array.Method InsertProviderDivisionSp(pRequest As osuwmc.CPD.DataStructures.InsertProviderDivision, Output pResponse As Ens.Response) As %Status{set SPQuery = "{ ?= call usp_Interfaces_Insert_ProviderDivision_Ens(?,?,?,?) }"set par = 4set par(1) = pRequest.DoctorNumberset par(2) = pRequest.Divisionset par(3) = pRequest.UpdatedByset par(4) = pRequest.OrderByset tSC = ..Adapter.ExecuteProcedureParmArray(.InsertDivision,.outputs,SPQuery,"oiiii",.par)if 'tSC write " failed ",tSC quit tSC} or you can call it like...Method InsertProviderPreference(pRequest As osuwmc.CPD.DataStructures.InsertPreferences, Output pResponse As Ens.Response) As %Status{set SPQuery = "{ ?= call usp_Interfaces_Insert_ProviderPreference(?,?,?,?,?) }"set parm = 6set parm(1,"SqlType")=$$$SQLVARCHARset parm(1,"IOTypes")=$$$SQLPARAMOUTPUT set parm(2) = pRequest.DoctorNumberset parm(2,"SqlType")=$$$SQLVARCHARset parm(2,"IOTypes")=$$$SQLPARAMINPUTset parm(3) = pRequest.Preferenceset parm(3,"SqlType")=$$$SQLNUMERICset parm(3,"IOTypes")=$$$SQLPARAMINPUTset parm(4) = pRequest.PreferenceValueset parm(4,"SqlType")=$$$SQLNUMERICset parm(4,"IOTypes")=$$$SQLPARAMINPUTset parm(5) = pRequest.PreferenceDescset parm(5,"SqlType")=$$$SQLVARCHARset parm(5,"IOTypes")=$$$SQLPARAMINPUTset parm(6) = pRequest.UpdatedByset parm(6,"SqlType")=$$$SQLVARCHARset parm(6,"IOTypes")=$$$SQLPARAMINPUT set tSC = ..Adapter.ExecuteProcedureParmArray(.InsertPreference,.outputs,SPQuery,"oiiiii",.parm)if 'tSC write "Failed",tSCquit tSC}
go to post Scott Roth · Mar 14, 2018 Thanks not the exact answer that I was looking for but I think I can make it work somehow.
go to post Scott Roth · Mar 12, 2018 What does your... ##class(SamsReq).%New() look like? Here is what my Request and code look like as a whole. I have used this many times across many services in different fashions. I am using JDBC vs ODBC which maybe a difference, not sure.Class osuwmc.CPD.DataStructures.StartJobRequest Extends (%Library.Persistent, %XML.Adaptor) [ Not ProcedureBlock, SqlRowIdPrivate ]{Property StartJobStatus As %Integer;Storage Default{<Data name="StartJobRequestDefaultData"><Value name="1"><Value>%%CLASSNAME</Value></Value><Value name="2"><Value>StartJobStatus</Value></Value></Data><DataLocation>^osuwmc.CPD59D.StartJobReqE986D</DataLocation><DefaultData>StartJobRequestDefaultData</DefaultData><IdLocation>^osuwmc.CPD59D.StartJobReqE986D</IdLocation><IndexLocation>^osuwmc.CPD59D.StartJobReqE986I</IndexLocation><StreamLocation>^osuwmc.CPD59D.StartJobReqE986S</StreamLocation><Type>%Library.CacheStorage</Type>}}Class osuwmc.CPD.UpdateClarityAddressesFromCPDService Extends Ens.BusinessService [ ClassType = "", ProcedureBlock ]{Parameter ADAPTER = "EnsLib.SQL.InboundAdapter";Parameter REQUESTCLASSES As %String = "EnsLib.SQL.Snapshot";Property InitDSN As %String;Method OnInit() As %Status{Set ..InitDSN = ..Adapter.DSN//Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...Quit $$$OK}Method OnProcessInput(pInput As EnsLib.SQL.Snapshot, pOutput As %RegisteredObject) As %Status{set req=##class(osuwmc.CPD.DataStructures.StartJobRequest).%New()set req.StartJobStatus = pInput.Get("1")set sc = ..SendRequestSync("CPDClarityAddressUpdateBPL",req,.pOutput) Quit sc}}
go to post Scott Roth · Mar 12, 2018 I have used this in the past...Under my Data Settings I use the following as my Query to act like a status kick off for my job...SELECT 1Then my service does...Method OnProcessInput(pInput As EnsLib.SQL.Snapshot, pOutput As %RegisteredObject) As %Status{ set req=##class(osuwmc.CPD.DataStructures.StartJobRequest).%New() set req.StartJobStatus = pInput.Get("1") set sc = ..SendRequestSync("CPDClarityAddressUpdateBPL",req,.pOutput) Quit sc}It appears you are not setting your request variable so there is nothing to send to your DQTT.
go to post Scott Roth · Mar 9, 2018 Can I still add code in Business Studio and it maintain integrity to be able to still open in the BPL editor? I added the following in my while loop, but then the BPL hung. <assign property='..%Process.%SessionId' value='""'/>
go to post Scott Roth · Mar 1, 2018 Instead of storing it at the Credentials level, I have created a Global to store the LDAP server information. However when I am calling $Get(^OSUMCLDAP(Server)) nothing is being returned. Am I not calling it appropriately? Do I need to add an include statement somewhere to make sure it includes the globals? #define LDAPServer $Get(^OSUMCLDAP(Server)) i $$$WindowsLDAPServer{s AdminDN=$Get(^OSUMCLDAP(User))s AdminPW=$Get(^OSUMCLDAP(Pass)) }ThanksScott
go to post Scott Roth · Feb 26, 2018 Sorry I am learning AD/LDAP as I go. I talked with the "powers that be" and they did confirm that I do not see a separate username and password to BIND to LDAP, and that I can use the UserName. So I will make that switch in the code above.Thanks everyone.
go to post Scott Roth · Feb 26, 2018 To verify against LDAP we needed a separate user other than the user trying to log into the system.Also to make it easier on all parties involved instead of using new Attributes in LDAP, I had a group created that I could match up against a role of the same name in Ensemble.So if I have an additional user I need to use to verify as the LDAP search user, how would I store the user name and password in this scenario?
go to post Scott Roth · Feb 26, 2018 ZAUTHENTICATE(ServiceName,Namespace,Username,Password,Credentials,Properties) PUBLIC { #include %occErrors #include %sySecurity #include %syLDAP s LDAPServer="xxxxx.xxxxxx.xxxx" s LD=##Class(%SYS.LDAP).Init(LDAPServer) i LD=0 { s Status=##Class(%SYS.LDAP).GetLastError() //g Error } s LDAPUser = "ensemble.Services" s LDAPPass = "xxxxxxxxxxxxxxxxxxxxxxxxxxx" s Status=##Class(%SYS.LDAP).Binds(LD,"",$lb(LDAPUser,"",LDAPPass),$$$LDAPAUTHNEGOTIATE) i Status'=$$$LDAPSUCCESS q $SYSTEM.Status.Error($$$InvalidUsernameOrPassword) s BaseDN="dc=OSUMC,dc=EDU" s Filter="sAMAccountname="_Username s Attributes=$lb("sAMAccountname","displayName","mail") s Status=##Class(%SYS.LDAP).SearchExts(LD,BaseDN,$$$LDAPSCOPESUBTREE,Filter,Attributes,0,"","",10,1,.SearchResult) i Status'=$$$LDAPSUCCESS { w !,"SearchExts error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) g Done } s CurrentEntry=##Class(%SYS.LDAP).FirstEntry(LD,SearchResult) i CurrentEntry=0 { s Status=##Class(%SYS.LDAP).GetError(LD) w !,"FirstEntry error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) g Done } s DN=##Class(%SYS.LDAP).GetDN(LD,CurrentEntry) s Attribute=##Class(%SYS.LDAP).FirstAttribute(LD,CurrentEntry,.Ptr) while (Attribute'="") { s Values=##Class(%SYS.LDAP).GetValuesLen(LD,CurrentEntry,Attribute) //w Values s Properties("Attributes",Attribute)=Values //w Attributes(Attribute) s Attribute=##Class(%SYS.LDAP).NextAttribute(LD,CurrentEntry,.Ptr) } s Properties("Username")=$li(Properties("Attributes","sAMAccountName")) k Properties("Attributes","sAMAccountName") s Properties("FullName")=$li(Properties("Attributes","displayName")) k Properties("Attributes","displayName") s GroupFilter="(&(objectClass=group)(member:1.2.840.113556.1.4.1941:="_DN_"))" s GroupAttributes="" s Status=##Class(%SYS.LDAP).SearchExts(LD,BaseDN,$$$LDAPSCOPESUBTREE,GroupFilter,GroupAttributes,0,"","",10,0,.GroupSearchResult) i Status'=$$$LDAPSUCCESS { w !,"SearchExts error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) g Done } s GroupNumEntries=##Class(%SYS.LDAP).CountEntries(LD,GroupSearchResult) i GroupNumEntries=-1 { s Status=##Class(%SYS.LDAP).GetError(LD) //w !,"CountEntries Group error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) g Done } w ! i GroupNumEntries=0 { w !,"No nested groups for "_Username_" found" g Done } i GroupNumEntries>0 { //w !,"Found "_GroupNumEntries_" nested groups for user "_Username } #;Get the dn of the first entry returned. s GroupCurrentEntry=##Class(%SYS.LDAP).FirstEntry(LD,GroupSearchResult) i GroupCurrentEntry=0 { s Status=##Class(%SYS.LDAP).GetError(LD) w !,"FirstEntry error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) g Done } s Groups="" While (GroupCurrentEntry'=0) { s GroupDN=##Class(%SYS.LDAP).GetDN(LD,GroupCurrentEntry) i GroupDN="" { s Status=##Class(%SYS.LDAP).GetError(LD) w !,"GetDN Group error: "_Status_" - "_##Class(%SYS.LDAP).Err2String(Status) q } s CN=$p(GroupDN,",",1) s AD=$p(CN,"=",2) s AD=$zcvt(AD,"L") set exists=''$d(^|"%SYS"|SYS("Security","RolesD",AD)) if exists{ s Properties("Roles") = AD w Properties("Roles") } #;Save for later display //w !,AD s GroupCurrentEntry=##Class(%SYS.LDAP).NextEntry(LD,GroupCurrentEntry) } Done i $d(SearchResult) d ##Class(%SYS.LDAP).MsgFree(SearchResult) i $d(GroupSearchResult) d ##Class(%SYS.LDAP).MsgFree(GroupSearchResult) #;Close the connection and free the LDAP in memory structures. i $d(LD) d ##Class(%SYS.LDAP).UnBinds(LD) q $SYSTEM.Status.OK() Error s $zt="" w !,"Cache error: "_$ze g Done }
go to post Scott Roth · Feb 19, 2018 When I run... f i=2:1:$LL(list2) write !,i,"",$p($li(list2,i),",") w i I am getting the output of 2Access.Ensemble.Developer.User2How can I remove the 2 from the beginning and ending of the string, then compare it to the %SYS Roles?ThanksScott
go to post Scott Roth · Feb 19, 2018 I believe it is either a String for a Fixed Sequence. I cut and pasted that from a document given to me to show me the LDAP/AD Attributes. I believe the structure is {CN= ,OU=, DC=, DC= , CN=,OSU=,DC=,DC=.....} so every time we see a CN its a new group which is what I want to key off of.