On a recent project I had to decrypt an eConnect stored procedure – something you typically never have to do when using eConnect.
eConnect provides both Pre and Post procedures for modifying business logic before passing a transaction through eConnect to Microsoft Dynamics GP (I advise against modifying any Dynamics GP stored procedures as it may cause problems down the road with upgrades).
In my case, I was working on an integration using SmartConnect to insert a payables transaction into Dynamics GP. (If you haven’t used SmartConnect before, it’s a terrific tool for creating quick integrations to GP from other sources including flat files, SQL, CRM etc.; but that’s a blog for another day.)
It was a pretty straightforward integration using eConnect and leveraging the taPMTransactionInsert procedure. However, I was running into issues because of an error caused by code written by a previous consulting firm in the post procedure taPMTransactionInsertPost. The previous firm had encrypted the procedure, preventing me from troubleshooting my issue, and I had to decrypt the eConnect stored procedure to proceed with the integration (another benefit of decrypting the procedures is to see what eConnect is doing; to get a peak under the hood and understand what is happening when eConnect is pushing data into Microsoft Dynamics GP).
There are numerous options for decrypting stored procedures. I found multiple sites with different methods. One method I have used in the past is creating a SQLDecrypt() stored procedure that I can run against any stored procedure in the database. The key to using this stored procedure is to ensure you’re logged in using a Dedicated Admin Connection (DAC). I have been successful using this method in the past, but in this instance, due to the complexity of the post procedure, it didn’t work. The DDL code produced by the procedure wouldn’t execute.
Instead, I found dbForge SQL Decryptor. This is a free utility available by Devart which allows you to decrypt any stored procedures that are encrypted using the WITH ENCRYPTION option. It cannot be used to break security keys, certificates, or passwords; but for eConnect procedures this will not be an issue.
Here are the steps I used to decrypt the eConnect post procedure I needed to view using SQL Decryptor:
1. Connect to Server
When connecting to the server, be sure to show Advanced Settings and select the Dedicated Administrator Connection Mode. If you don’t do this, you won’t be able to decrypt a single object; the program will decrypt all objects.
2. Browse for SQL Object
In the Object Explorer, expand the database, and then stored procedures. You’ll need to scroll down to find your object, as I haven’t found a filter option. You’ll notice that all of the encrypted objects have locks on the object icon.
3. Decrypt the Object
Once you find the object, right click and select Show DDL script. This will show you the script in a query window. You can review it or cut and paste to another window. You can also decrypt in place and it will replace the encrypted stored procedure with the decrypted one. Be sure to make a backup first.
That’s it. Super simple. I was able to read through the code and adjust my integration to work with the pre-written post procedure.
If you have any questions or want to learn more about eConnect or SmartConnect, please feel free to contact me at email@example.com.
Jeff Fenn, BI/Dev Practice Manager
Posted by: Jakob Bechgaard