Instructions on How to for de-encrypting SMAT DB from the command line.

[Lawrence Nelson]

We are preparing to make a data lake with ORM messaegs that are being sent in our Lab system.

We run SMAT db encrypted.

We will be looking to pull ‘yesterdays’ SMAT DB file each day – de-encrypt it and move it so our dba team can work with it to make dashboards etc with PowerBi.

So to repeat the subject line –

Looking for the command line instruction for de-encrypting SMAT DB files.

Thank you.

Lawrence Nelson

 

Lawrence Nelson
System Architect - MaineHealth IT

Topic

[Jim Kosloskey]

Do you have Charlie Bursell’s hcismatdb utility?

email: jim.kosloskey@jim-kosloskey.com

[Lawrence Nelson]

Hi Jim –

I’m going with a No on that. Is there not a single command that can be issued?

To make sure I’m clear – this is for SMAT db and not SMAT (before db) which I recall having to decrypt once years ago.

 

 

Lawrence Nelson
System Architect - MaineHealth IT

[Jim Kosloskey]

I sent you my copy via email.

The date in the comments of the proc is 07/30/2015. I think this is the latest but if Charlie or anyone else has a later version perhaps they can post here and get you a copy.

email: jim.kosloskey@jim-kosloskey.com

[Jeff Dinsmore]

Tcl syntax to open an encrypted DB file:

<sqlite command> <dbHandle> <dbFilePath> -readonly <boolean> -key <encryptKey>

Once open, you can query the DB using dbHandle as usual

Jeff Dinsmore
Chesapeake Regional Healthcare

[Lawrence Nelson]

Hi Jeff –

2 questions (requests)

 

1

Could you express this string with a less techical example (or an actual example) –

I know it will be different for our location but I’m not picking up everything you are stating in this string below.

<sqlite command> <dbHandle> <dbFilePath> -readonly <boolean> -key <encryptKey>

2 Where would you actually get the encryption key from?

Thank you for the response

Lawrence Nelson
System Architect - MaineHealth IT

[Jim Kosloskey]

Lawrence,

By default the key for SMAT DB encryption is the site name.

So if you will execute the code from outside the site where the encryption took place, you will need to have a method to provide the site name.

Does hcismatdb not accomplish what you need?

email: jim.kosloskey@jim-kosloskey.com

[Lawrence Nelson]

Hi Jim –

It’s been a holiday week for me – and Monday as well.

I took a very quick look at the script – and it looks to quite lengthy – as opposed to a single command line to decrypt a SMAT db file.

Lawrence

Lawrence Nelson
System Architect - MaineHealth IT

[Jeff Dinsmore]

Lawrence,

If we assume our our process is in the epic_ib site, the Tcl syntax would be:

sqlite3 dbHandle /opt/cloverleaf/cis19.1/integrator/epic_ib/exec/processes/epic_adt_ib/adt_ib.smatdb -readonly 1 -key epic_ib

Once the DB is open, you can query it with SQL – again this is Tcl syntax:

set numMsgs [db eval “select count(*) from smat_msgs”]

Jeff.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Lawrence Nelson]

Jeff –

Excuse my lack of knowledge –

but is what you’ve stated the command to decrypt and encrypted SMAT file or about pulling it into a external database – or a combination of both?

Regards,

Lawrence

Lawrence Nelson
System Architect - MaineHealth IT

[Lawrence Nelson]

Jeff –

Sorry – I read backwards in this chain and see what you are stating about decrypting. Thanks.

The current issue this leaves me with is that I’m not actually looking to decrypt it – into a database at the time (the sqlite3 dbhandle piece)  – I’m looking to decrypt it period – as a file only  – so it can be moved unencrypted by our EFT processes to where our database team can then work with it in their environment (not via ODBC).

Lawrence Nelson
System Architect - MaineHealth IT

[Jim Kosloskey]

Lawrence,

Well I would simply place a command line entry for hcismatdb in a O/S script Korn shell or whatever you are using with the appropriate arguments- or – you can exec hcismatdb from inside a Tcl proc with the appropriate arguments. Your choice.

But since I think you said the goal was to create a file of extracted entries from SMATDB I suspect you just want to place the file in a directory for subsequent processing so a O/S script would probably work well.

Maybe we should discuss off-line – email me.

email: jim.kosloskey@jim-kosloskey.com

[Charlie Bursell]

You want to copy the database as unencrypted to another location?   Remember an SQLite database is simply a file and can easily copied.

As for the encryption; 2 options.

Use as unencrypted on Cloverleaf or open the SMAT Database using command line logic provided and copy to another database.  Here, Google is your friend 🙂

<b>SQLite – How to copy data from one database to another?</b>

<ol class=”X5LH0c”>
<li class=”TrT0Xe”>Using SQL query. First you attach <b>database</b> new_db with old_db to associate them in the current <b>database</b> connection. Assume that you are connected to the old_db , now run the ATTACH command to attach the new_db : …
<li class=”TrT0Xe”>Using <b>SQLite</b> command-line tool. Open the old <b>database</b> then dump the table

 

 

 

[Lawrence Nelson]

Charlie –

I know I’m being obtuse here – but our db team is not being successful with un-encrypting the SMAT db file – remotely aka OFF of the Cloverleaf platform.

Additionally, we’ve taken your script that Jim K. sent to me – and reviewed the contents in detail. We’ve confirmed the site ID spelling/case – and also that we haven’t set a password on the SMAT db – so it should be the site ID name.

“Using the SQL Cipher version of DB Browser (free to download), I was able to create my own test DB and set the encryption.

When I re-open the test DB, it prompts me for the key value.  If I enter the correct value it opens as expected.  If I enter the wrong value I get the message  “Could not open database file.  Reason: Invalid file format.”    

I get the same message when I use this tool to open the encrypted db with encryption key previously provided, and the un-encrypted one from Cloverleaf (CLOTEST) SMAT opens as well.

 

Lawrence Nelson
System Architect - MaineHealth IT

[Charlie Bursell]

From the web:

<b>SQLite</b> doesn’t support <b>encrypting</b> database files by default. Instead, you need to use a modified version of <b>SQLite</b> like SEE, SQLCipher, SQLiteCrypt, or wxSQLite3.

The Cloverleaf version uses SQLCipher

Probably what you are using has a different encoding method with the sqlite version.

From the command line using the version of sqlite installed with Cloverleaf, with or without a root set I can easily do the following with an encrypted SMAT file:

sqlite myob.smatdb
SQLCipher version 3.15.2 2016-11-28 19:13:37
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> PRAGMAY key = “cbtest”;
Error: near “PRAGMAY”: syntax error
sqlite> PRAGMA key = “cbtest”;
sqlite> .tables
smat_info smat_msgs

sqlite> .quit;

Maybe move the encrypted DB to unencrypted for your use?  See site below:

I have not personally tested this method but give it a try and let us know

https://stackoverflow.com/questions/25132477/how-to-decrypt-an-encrypted-sqlcipher-database-file-on-command-line#:~:text=Decrypt%20the%20database%20to%20a%20plaintext%20database&text=db%20sqlite%3E%20PRAGMA%20key%20%3D%20&#8242;,decrypted%20database%20at%20~%2Fplaintext.

 

[Charlie Bursell]

More info:
I just installed DB Browser for Windows from: https://sqlitebrowser.org/dl/

It installed two executables;  DB Browser (SQLite) and DB Browser (SQLCipher)

I was able to decrypt and view a SMAT file using SQLCipher.

I had to change default Encryption Settings from “SQLCyphe 4 defaults” to “SQLCypher 3 defaults”

But I was then able to enter my site name for password and view and or manipulate the DB.

I hope this helps.

 

[Lawrence Nelson]

Hi Charlie –

I’ve been forwarding your responses to the dba architect I’m working with. He has attempted all of the items you’ve suggested – but without success.

When I send him an un-encrypted SMAT db file – it opens for him without issue.

The encrypted ones are a no go with the use of the site name.

I know that the sites SMAT setting have not been set up with passwords – that would over-write the use of the site name as the password.

I’ve gone in to a test site – and turned on encryption to assure that no password was set –  it’s still a no go. I sent in a file pre and post encryption. The pre opened – the post does not.  So we are at an impasse – and I’m not sure where to go with for next steps.

 

Here is the most recent response from the dba

 

I’ve already tried below with no success.  I question if the password provided for the encrypted SMAT db you sent to me was in fact the correct password.

If it was, I should have been able to open the file with DB Browser (SQLCipher) using either “SQLCyphe 4 defaults” or “SQLCypher 3 defaults” as outlined

below.  The resulting message in either case is the same and implies an incorrect password.  Is there anyone in your group that may have opened an encrypted SMAT file outside of Cloverleaf?

[inserted picture content he sent reads as follows]

DB Browser for SQLite

Could not open database file.

Reason: Invalid file format.

 

 

 

Lawrence Nelson
System Architect - MaineHealth IT

[Charlie Bursell]

What version of Cloverleaf were these SMAT DB files created under?

If they were created pre-19.1 you will have to use hcismatcrypt as I outlined in the post about using hcidbscript to migrate them to SQLCipher.  You can then open them using DB Browser(SQLCipher).  I have tried and know this works.

If you do not have Cloverleaf 19.1+ you may need to update.  I am not sure what encryption methods were used by previous versions of Cloverleaf.

[Lawrence Nelson]

Well there you go – we are not on 19 yet – we are on the last version of 6.24(?)

 

Lawrence Nelson
System Architect - MaineHealth IT

[Lawrence Nelson]

Charlie – Again sorry for being obtuse but I don’t see any prior references in here when I search for  hcismatcrypt or hcidbscript in this conversation on Clovertech.

The only script I have from this conversation is hcidbsmat.tcl

Are you able to pass me these other scripts?

 

 

 

Lawrence Nelson
System Architect - MaineHealth IT

[Charlie Bursell]

hcismatcrypt  is a utility provided with Cloverleaf.  Maybe some misunderstanding about hcidbscript , probably a typo.  Here I was referring to the script hcismatdb which you have.  Again, please see my post about using hcismatdb for more info on hcismatcrypt.

The hcismatcrypt command is available in version 6.2 but no migrate option.  You can use this command to validate the password or change the password of your SMAT files. Look for the command in your Cloverleaf documentation.

[Charlie Bursell]

Lawrence:

I was playing around with this on my old version of Cloverleaf 6.2.  Here is what you do

setroot and setsite to where SMAT files are located

First, run:  hcismatcrypt validatekey  <Name of SMAT File> <Site Name>

It should return “Successful for command validatekey” if key is correct else it will return:
“Validation failed.
Incorrect password”

If key is not correct and you cannot open as unencrypted, I am at a loss.  you will have to get your key.  Call Support, they have methods.

If the key is correct then run:
hcismatcrypt.htc rekey <sitename> <key>  <SMAT FILE>  “” <NEW FILE>

Note that sitename and key are probably the same.  SMAT FILE is the file to copy
“” means no key.  NEW FILE is name of file to create, i.e., xxx.db.  The give the new file to your analyst.

The new file will be a copy of your SMAT file unencrypted.  I tried it and it works for me.

 

Good Luck!!

[Lawrence Nelson]

Hi Charlie –

I’m sending the decrypted file to my dba partner – I think we have success!

Thank you!

2 follow ups –

What do the 2 quotes between the file names indicate. I had to change your characters to be my ‘keyboards’ version?

First attempt example

clotest::hci> hcismatcrypt rekey t80ndxorders t80ndxorders to_SCC_orders_outQ.20210119234432.smatdb ▒^▒^▒▒^▒^▒ SCC_orders_outQ.20210119234432.db

 

Also

My first attempt gave me this until I stopped all processes – seems odd that all the processes have to be stopped to decrypt a file.

clotest::hci> hcismatcrypt rekey t80ndxorders t80ndxorders to_SCC_orders_outQ.20210119234432.smatdb “” SCC_orders_outQ.20210119234432.db
Warning: Engines are running in current site. Please stop the process and try again.

clotest::hci> hcismatcrypt rekey t80ndxorders t80ndxorders to_SCC_orders_outQ.20210119234432.smatdb “” SCC_orders_outQ.20210119234432.db
Successful for command rekey.

Lawrence Nelson
System Architect - MaineHealth IT

[Charlie Bursell]

The double quotes represent a null for encryption key.  It should result in a file with no encryption.

If the SMAT file is in use it would make sense to stop it first.

Glad you got it working.

[Lawrence Nelson]

Hi Charlie –

One other follow up for my setting up where I’m moving files –

Is this command able to be made to have the decrypted to a different folder/path location?

hcismatcrypt.htc rekey <sitename> <key>  <SMAT FILE>  “” <NEW FILE>

Something like ?

hcismatcrypt.htc rekey <sitename> <key>  <SMAT FILE>  “” <fullpath>//smatdb_decrypt/<NEW FILE>

Lawrence Nelson
System Architect - MaineHealth IT

[Charlie Bursell]

Works for me.  Did you try it?

[Author]

Replies