Find Electronic Invoices That BizTalk RosettaNet Failed Sending to EDI Partner

Folks from a fellow team are trying to solve a sporadic problem where for some unknown reason their RosettaNet application sometimes fails submitting invoices to our EDI partner, which is OpenInvoice.

In the meanwhile they asked for help with finding the invoices for which all attempts to send them have failed. Below is a query that I wrote for them.

A few comments about how it works.

Our BizTalk configuration includes ESB Toolkit. If an application fails, the exception is recorded in the EsbExceptionDb database. Next, we use BizTalk Accelerator for RosettaNet (BTARN) for the electronic document exchange with our partners who support RosettaNet. The messages sent and received are stored in the BTARNDATA database. The query is a RIGHT JOIN; it find error records (exceptions) for which there is not any matching record among the successfully sent messages.

--the invoices that failed all sending attempts
WITH XMLNAMESPACES ('http://www.api.org/pidXML/v1.0' as pidx)
select failed.*
from (
 select SI.Invoice.value('pidx:InvoiceNumber[1]', 'varchar(90)') as SuccessInvoiceNumber
 from [BTARNDATA].[dbo].[MessagesFromLOB] lob
 CROSS APPLY (select cast(lob.ServiceContent as xml)) as XmlContent(CData)
 CROSS APPLY (select try_cast(XmlContent.CData.value('
   declare default element namespace "http://schemas.microsoft.com/biztalk/btarn/2004/LOBMessage"; 
   /ServiceContent[1]', 'varchar(max)') as xml)) as XmlInner(Msg)
 CROSS APPLY XmlInner.Msg.nodes('/pidx:Invoice/pidx:InvoiceProperties') as SI(Invoice)) succeeded
right join (
 select FailedInvoiceNumber, max(DateTime) as LastErrorTime
 from (select 
  FI.Invoice.value('pidx:InvoiceNumber[1]', 'varchar(90)') as FailedInvoiceNumber,
  f.DateTime
  FROM [EsbExceptionDb].[dbo].[Fault] f
  inner join [EsbExceptionDb].[dbo].[Message] m on f.FaultID = m.FaultID
  inner join [EsbExceptionDb].[dbo].[MessageData] md on m.MessageID = md.MessageID
  CROSS APPLY (select cast(md.MessageData as xml)) as XmlData(Msg)
  CROSS APPLY XmlData.Msg.nodes('/pidx:Invoice/pidx:InvoiceProperties') as FI(Invoice)) allfailed
 GROUP BY FailedInvoiceNumber) failed on succeeded.SuccessInvoiceNumber=failed.FailedInvoiceNumber
where succeeded.SuccessInvoiceNumber is null

Closing OpenText eDOCS DM Series

'The Leader in Enterprise Information Management' is what the title of the www.opentext.com front page says. Well, it's hard to tell whether it's true or not, because OpenText is a very closed company: you won't find any open user or developer communities on their products; there is very little information on what their products are. What a potential customer is left to do is to trust the title and order a pre-sales presentation by calling the 1-800- number.

I really liked this blog post by OpenText CEO, especially that sentence in one of its first editions, where Mark Barrenechea said something like 'We at OpenText deeply care about security of our products', which had been then removed, possibly, because of the blog post was gathering low stars from readers...

In my opinion, each of these three security issues in OpenText EIM is sever, but if exploited in a combination, can put an enterprise on the knees. It's all up to a hacker's imagination and mercy.

OpenText: if you are reading my blog and have anything to say, the comments section is open. If you might have already fixed some or all issues described in this blog, please feel free to let me know and I'll be happy to add an update.

I wish OpenText customers the very best. I'm moving on and leaving eDOCS DM behind.

Auto Logon Security Hotfix Considerations

If OpenText released patches P6 and P1 for DM 5.3.1 and DM 10, I hadn't seen them. That's fine, my playground has changed and I've been away from supporting OpenText products. However, I analyzed the Auto Logon hotfix and this is what I found.

Yes, if you had custom code that relied on the Auto Logon feature (sample one and two), the hotfix would cause your code to stop working. However, the hotfix doesn't seem re-design the insecure architecture of the Auto Logon feature. What it does is the 'friend from foe' distinguishing, where the friend is OpenText's DM clients and the foe is any custom code with DM API.  Said differently, DM Server still allows DM Extensions to log in a user without sending the user's password or any security token obtained from an identity provider server or anything.  It seems simply checking for specific parameters that the client application sends on the logon.  It appears very similar to how DM Server still allows SQL Passthrough for DM Extensions when Allow SQL Passthrough is disabled.  Needless to say that this type of 'protection' is not too hard to work around by simply mimicking the behavior of the 'trusted' application (DM Extensions in this case).

Ask OpenText Support: how would you utilize the DM Server's Auto Logon feature in a custom built application with DM API?  And if they refuse to give you a code sample or say it's not available for custom applications or only OpenText DM Extensions have this feature, then this is it - a back door to the server, for DM Extensions use the same API as all other developers who write custom code for eDOCS DM.

Disclaimer:
The information here is, to my best knowledge, believed to be true and provided as-is and without any warranty of any kind.

SQL Pass-Through Super Feature in eDOCS DM

DM API exposes a 'SQLService', the feature of eDOCS DM Server which allows a DM developer to execute SQL queries on DM database. At the first glance it may seem as a cool feature: a developer doesn't have to develop any specialized service for his own DM application extensions to get access to the database -- simply use DM API and enjoy the direct database access. I saw many custom built DM applications that employed this feature named SQL Passthrough. Developers love it!

All right, let's list what you can do with SQL Passthrough in eDOCS DM:

• Run any SQL query on DM database server to the extent of DOCSADM database login permissions.
• Being a very powerful login by default, DOCSADM allows you reading and modifying any data.
• It also can read and modify database schema.
• Create and execute database code, e.g. stored procedures.
• If the database server is Oracle, DOCSADM can grant additional permissions to itself (by default it can).
• Queries are anonymous at the database level. That is, it's impossible to distinguish DM Server's core functionality queries from those originated from DM Server's SQLService queries at the database server, because all of them are executed via the DOCSADM login.

There may be more. You name it.

Thus, if we look from the security perspective, this feature is a huge security issue. Not for the fact that it is SQL injection prone, and not because it has no security access control, that could be either impersonation or a dedicated database login for such queries. The main security issue comes from the fact that you are not the only developer for your enterprise document management system. Potentially any DM user can bring his own custom built application and run SQL queries against the DM database.

In theory, by exploiting the SQL Passthrough feature, a hacker can even gain control over your Windows Server where DM Server is running: change an existing document's path stored in the database to the server's local path and upload an executable to that location via DM Extensions or DM API as an 'existing version' of the 'document'.

Just to sum it up, DM API SQL Service allows execution of arbitrary code on the DM database anonymously, and it cannot be disabled (see below). If you ever see such a 'feature' in an end-user product, EIM in this case, run away from it as if it is a wildfire.

Well, there is Allow SQL Passthrough in server options and eDOCS DM Administration Guide warns about potential security risk when enabling it. The checkbox in the server settings and the warning in the documentation may make one think that when Allow SQL Passthrough is unchecked, the feature is disabled. No, it's not.

OpenText (Hummingbird) developers had created SQL Passthrough for themselves in the first place and not for third-party DM developers as one may think. From DM Server logs you can see that eDOCS DM Extensions makes calls to SQLService, even if Allow SQL Passthrough is disabled. To accomplish this, DM Extensions always sends a constant string application name and a GUID to the server along with a user's login credentials. The application name and GUID are supposed to be a secret, but when OpenText sent out a customer alert regarding the Auto Logon security breach, they seemed so scared at that time, that were offering the GUID to anyone who would request it from OpenText Support. The application name and GUID - the so-called license key - that DM Extensions uses to unlock the full potential of DM API is very easy to discover by debugging the DM WCF Service. A hacker doesn't really need to ask OpenText for it. And it doesn't have to be a public knowledge to qualify as a security breach. It simply exists and OpenText cannot easily change these secrets because of their backward compatibility commitments.

Overall this SQL Passthrough is a huge security issue and, unfortunately, you, as a DM Administrator, cannot completely address it. However, there are still some things you can and should do to lower the risk of your enterprise documents destroyed or stolen:

1. Disable Allow SQL Passthrough in server settings. It's insufficient as explained above, but at least you'll cut off lazy hackers who don't care about the secret GUID.
2. Closely monitor the Application Event Log on your DM Server: If a SQL query ends with an error, the error is added to the log. You should analyze errors for suspicious queries. The challenges are A) DM Extensions generates a few queries which always yield an error - you'll need to filter out those and B) An intruder may be smart enough to ensure his SQL queries are correct and won't fail in your environment.
3. Enable DM Server logs and monitor them in real-time for any calls to SQLService. You will need to find a way to distinguish calls made by DM Extensions or DM Webtop from those by an intruder.
4. Work with DBA and tighten DOCSADM login permissions as much as possible: no schema updates, no permission grants, etc.

That's about it...

Disclaimer:
The information here is provided as-is and without any responsibility of any kind. It is intended for eDOCS DM Administrators to help them understand the security issue and find ways of addressing it. The facts given are deemed true for eDOCS DM 5.3.1 P5 / DM 10 and earlier versions.

Plain Text Passwords in eDOCS PEOPLE Data Table

This is one among other security issues with OpenText eDOCS DM that requires attention. The explanation below is given to DM administrators along with my suggestion on how the issue can be addressed to prevent the exposure of your enterprise's documents.

If you look at the DOCSADM.PEOPLE table, you can notice the USER_PASSWORD column. Depending on for how long eDOCS DM was used in your company, you can find more or less records with passwords:

[SQL]
select  USER_ID, USER_PASSWORD, LAST_LOGIN_DATE from DOCSADM.PEOPLE where USER_PASSWORD is not null and ALLOW_LOGIN='Y'

These passwords are specific to DM, means that they have nothing to do with users' Windows account passwords, that is a good thing - Windows accounts are not compromised. However, the credentials still can be used to log into eDOCS server via eDOCS Extensions for Windows Explorer and Webtop (Cyberdocs). You can try.

At this point one may tell: 'Well, non-encrypted passwords in DB, who cares? Many old applications were written like this. Not so many people at our enterprise have direct access to eDOCS DB. Low risk. We shall be all right. '

The issue is actually worse than it may seem at the first glance. Here is why:

1. Potentially any person with read access to your DM database can find another user's password and then use it to act on that user's behalf, or may be just look at others documents. This intrusion is very difficult to detect if ever possible.

2. Anyone with write access to the database can set a password for another user, use it, wipe it out and no one would ever notice this was happening.

3. In fact, DM API has a 100% documented (read 'legal') method for reading these passwords in plain text as they are. Technically speaking, this grants read-only access to passwords to anyone with an existing login in DM. The method is a generic DM search service that is called with the following parameters:

SearchObject = "v_peoples",
Criteria = new Dictionary<string, object> { { "ALLOW_LOGIN", "Y" } },
ReturnProperties = new List<string> { "USER_ID", "USER_PASSWORD", "GROUP_ID", "NETWORK_ID", "LAST_LOGIN_DATE", "%FILTER_DISABLED_ROWS" }

It returns all people's accounts. The resultset can be easily filtered on the client side to find accounts with stored passwords.

4. DM API provides methods to anonymously write into the database! I have a separate post regarding this "feature" named SQL Pass-through. What it means for this breach is that DM API can be used to mock up passwords and then re-login with then on another user's behalf, same as item #2 above.

Items ##3 and 4 are very important to keep in mind. You should not forget them when thinking who has read-write access to your DM database. Potentially every DM user has.

Okay, what you, as administrator, can do to patch this breach.

• First, find the user account records with passwords and erase the passwords. 
• Second, ensure all your DM accounts are linked to user Windows accounts; never create or use DM-only accounts. 
• Next, create an INSERT and UPDATE triggers on the PEOPLE table to prevent passwords saving into the USER_PASSWORD column. 
• Last but not least, create alerts to notify yourself when there is an attempt to save a password into the PEOPLE table. It's up to you how you can accomplish it. This can be a tricky task.

Good luck.

Disclaimer:
The information here is provided as-is and without any responsibility of any kind. It is intended for eDOCS DM Administrators to help them understand the security issue and find ways of addressing it. The facts given are deemed true for eDOCS DM 5.3.1 P5 / DM 10 and earlier versions.

DRF File and PCDOCS Link Handler for eDOCS DM

Couple years ago I created a handler for DRF files, OpenText eDOCS DM document links. The purpose of the tool was to give users the ability to open eDOCS DM documents in read-only mode if they (the users) do not want to install eDOCS DM Extensions.

Today I'm publishing the tool, its full source code, and some basic documentation:
EDocsLinkHandler

The best part of this package is, of course, the full source code (C# / .NET 4 / Visual Studio 2013). It's my implementation of DM WCF API helpers, and it even includes unit tests! :-)

Although the helpers only implement the logon, search, and document download functionality, they may serve as a starting point for anyone developing for DM with WCF API. After two years since the code was written, I still like it, maybe with the exception of the way I implemented the DMSearchResults class... Well, consider it as a free demo. Enjoy!

eDOCS DM Auto Logon Security Issue - FAQ for DM administrators

Foreword:  Earlier today OpenText released a hotfix for this issue. The security hotfix is not available to me and I cannot test it. I hope it works well. Below is my version of FAQ on the Auto Logon Security issue.

Q. Which DM versions are affected by issue DM-32057?

A. All (DM 10 prior to P1, 5.3.1 P5b and earlier, 5.3.0 P5 RU5 and earlier).

Q. What can an intruder do to my library through the Auto Logon breach?

A. It depends on the intruder's goals and imagination. He/she can just sneak into the library and quietly read others documents or he/she can quickly scramble the entire library by replacing real documents with garbage through random logins. OpenText has rated the vulnerability as HIGH. A very good explanation was also given to what HIGH means.

Q. Can I detect an intruder?

A. I'm afraid you cannot. The only method I know is to enable server logs on all your DM servers and watch for suspicious activity by parsing the logs in the real time. The problem is that no one knows which suspicious activity to expect... If a user comes to you and says that the document history shows that she opened the document, which she did not, you'd better believe her than questioning her memory.

Q. Then what should I do to mitigate the vulnerability?

A. Disable Auto Logon in your DM environment as the DM-32057 bulletin suggests.

Q. Will my users like it?

A. No, they will likely hate you for this. They won't be able to understand why DM is the only application that constantly asks them for a password.

Q. Our users' Windows accounts are now locked more often after I disabled Auto Logon. Why?

A. Ask your users to be more accurate when entering their passwords. The DM Server does not tolerate carelessness.

Q. Can I disable the DM WCF service instead of disabling the Auto Logon feature?

A. You can, but this is insufficient. By disabling the WCF Host service you essentially disable the two out of three communication endpoints – HTTP and Net.TCP; the DCOM endpoint remains and can be utilized for an attack. Moreover, by disabling endpoints you reduce the DM Server's availability to your honest clients. UPD: DM WCF Service publishes 5 endpoints, not 2, in DM 5.3.1.

Q. Is it easy for OpenText to fix the issue in eDOCS DM?

A. Probably it was. A hotfix has been released for DM 5.3.x and DM 10. UPD: my opinion on the hotfix.

Q. Did OpenText know about this issue before?

A. They knew about it since 2013.

Q. Why didn't they fix it then?

A. I think they wanted to release DM 10 first. However, you should ask them, not me.

Q. I'm a hacker. The DM admin has just disabled the Auto Logon feature and I no longer can use other users’ logins... :(

A. Oh, man, I feel for you... Well, you can re-enable Auto Logon via the SQL Passthrough by executing a query like the one shown below using DM API:

  UPDATE DOCSADM.DOCSPARMS SET AUTO_LOGIN='Y'

Hey, DM admin: this is a good example of suspicious activity you must be watching for if you care.

Q. Is Auto Logon the only security breach in eDOCS DM?

A. No, it is not. More issues exist. Here are the other two.