Encrypting/Decrypting Files using VBA

[willknapp]

I'm putting together an access database (2007) that tracks client inquiries (or as everyone else calls them, trouble tickets) and I want to be able to attach files to the inquiry record. My first attempt was to use a linked table with an attachment field, but given the quantity of inquiries we receive, we'd hit the 2GB max after about a month.

My second attempt was to store the attachments in a network location, but that's not possible because of security issues. We've created a user-level security interface within the DB, but of course, in order for users to be able to access any of the files attached to the inquiry, they would need access to the entire network location where the files are stored. Our MIS department is not keen on this idea, as many of the attachments contain non-public information. (I work for a mortgage service provider.)

Most recently, we attempted to save the files as BLOBs in a SQL Server 2008 R2 table, but we're running into an issue updating the record when the file is anything larger than 400K. We've tried several different work arounds (ADO, DAO, SQL Insert queries, updated ODBC Drivers) and we just can't get anything to work. Further, the SQL DBAs are getting a little fed up with me, so hopefully we can figure out another way.

Finally, I'm thinking that if there's a way to encrypt the files when they're moved to the network location, and the only way to decrypt them is via a key or GUID stored with the attachments record, this would solve my problem.

Has anyone tried something like this before? I know almost nothing about APIs and services, but I'm told this is the direction I need to start looking...

Thanks!

Will

 

[nanscombe]

Password protect documents, workbooks, and presentations

I've no idea if this would help you but Word seems to allow you to use AES 128-bit advanced encryption. Computer generated password, possibly stored in an encrypted form in the database, together with the file path?

Possibly have the filepath derived from an ID?

I save my digital photos with a name like 001-02027907.jpg. From that name I can derive it's relative path.

..\Documents\001-02\027\001-020279xx\001-02027907.jpg

..\Documents\AAA-AA\BBB\AAA-AABBBCxx\AAA-AABBBCnn.jpg

Just trying to stir your imagination.

 

[nanscombe]

Another possibly interesting bit of reading.

Using Passwords to Protect Access to Word and Excel Documents

There maybe something you could adapt.

But, as usual, someone could probably crack the password. What is it with Microsoft products and their encryption?

 

[way2bord]

My second attempt was to store the attachments in a network location, but that's not possible because of security issues. We've created a user-level security interface within the DB, but of course, in order for users to be able to access any of the files attached to the inquiry, they would need access to the entire network location where the files are stored. Our MIS department is not keen on this idea, as many of the attachments contain non-public information. (I work for a mortgage service provider.)

Feel free to correct me, but this doesn't make any sense at all.
The security issues present when applying standard network are no greater than any of the other options you've described - quite a bit less, really.

You make a network folder, you assign it appropriate security, you give the credentials to that specific folder only to the database to bestow on end-users as necessary. If the end-user can access your database, they likely have "Full access" to the entire network anyway (read/write/modify is a minimum for any access database file).

 

[willknapp]

Feel free to correct me, but this doesn't make any sense at all.

I guess I'm not being clear - allow me to use an example:

The database provides Paul access to Inquiry #123. George has access to Inquiry #456. Each needs access to the attachments associated with their own inquiry, but not the other. The attachments are stored in the following locations:

\\Root\attachments\123 - Paul has access, George does not.
\\Root\Attachments\456 - George has access, Paul does not.

At some point, though, George might pass along his inquiry to Paul, in which case Paul would then need access to ...\456 which he didn't have before.

Now, if there was a way for VBA to provide this type of dynamic folder access, then we'd be good to go, but I haven't figured out how to do that. Or, if there was someway to make the folder password protected with some sort of GUID that could then be stored with the inquiry record (invisible to the user) that would work too, but I haven't been able to figure out how to password protect folders in VBA.

Does that make more sense?

 

[willknapp]

The security issues present when applying standard network are no greater than any of the other options you've described - quite a bit less, really.

If I were able to store the files within the tables of the database (as attachments in access, or as varbinary(max) records in SQL Server) the users would only be able to view the attachments associated with the inquiry they were viewing. The users won't have direct access to the tables due to (yes, I know, Access's sub-standard) built-in protections, blocking the navigation window, only operating in runtime environment, etc.

We're really not trying to prevent attacks from outside malicious hackers...otherwise, we wouldn't be using Access. That said, we don't want to make it incredibly easy for the users to access the files they're not supposed to see, either.

 

[nanscombe]

What sort of files are they? PDFs, Word documents?

 

[willknapp]

What sort of files are they? PDFs, Word documents?

Anything and everything - .MSG files (the inquiries are initiated by incoming emails), .PDFs, .DOCs., .XLS are the most prominent, but I've even seen some .wav files come through. I'm currently converting the files into binary streams, then saving the streams into .DAT files. If I could figure out how to password protect those, then I'd be all set!

 

[nanscombe]

Would the contents need to be accessed through your database or would it basically tell people where to find the documents?

I starting to think password protected Zip files, or something similar. The password being kept in the database.

 

[willknapp]

Would the contents need to be accessed through your database or would it basically tell people where to find the documents?

I starting to think password protected Zip files, or something similar. The password being kept in the database.

The contents need to be accessed through the database. Currently, the database automatically opens the files in their native application, and I'd like to retain this functionality, as this product is being developed for the lowest common denominator (i.e., automate as much as possible). Further, opening the file directly allows me to retain a single copy, where as an unzipped file could potentially create a version control disaster.

Here's the ridiculous work around I'm trying now:

Convert the file into a binary datastream
Convert the data stream into a .dat file
open the .dat file as a Word Document.
Save the word document with a GUID/Password (stored with the record)
Rename *.doc to *.dat (to further confuse any would-be-browsers)

Now, the question is, when I reverse this process, will the binary survive? (So far, I've tested In and Out up to the point of saving the .dat as a word doc - I'm worried that's where this is going to shirt the berd).

Rename *.dat to *.doc
Open the word file with the GUID/Password stored in the record
Save the word file as a .dat file
Convert the .dat file into a data stream.
Convert the datastream back into the original file type.

I'll post my results...

 

[nanscombe]

If people did not need to amend the files I wonder if you could achieve something through an embedded browser object pointing at an internal Web Server?

We all access numerous types of documents from various sources, none of which we have direct access to, and maybe they could be opened by a hyperlink?

The access database could be used to push the data to where it needs to go, the users wouldn't need to know where, and the web server would just feed it back as required.

Possibly even get Access to store / retrieve the files via an FTP server? That way users would never be able to get at the fies directly.

 

[way2bord]

I guess I'm not being clear - allow me to use an example:

The database provides Paul access to Inquiry #123. George has access to Inquiry #456. Each needs access to the attachments associated with their own inquiry, but not the other. The attachments are stored in the following locations:

\\Root\attachments\123 - Paul has access, George does not.
\\Root\Attachments\456 - George has access, Paul does not.

At some point, though, George might pass along his inquiry to Paul, in which case Paul would then need access to ...\456 which he didn't have before.

Now, if there was a way for VBA to provide this type of dynamic folder access, then we'd be good to go, but I haven't figured out how to do that. Or, if there was someway to make the folder password protected with some sort of GUID that could then be stored with the inquiry record (invisible to the user) that would work too, but I haven't been able to figure out how to password protect folders in VBA.

Does that make more sense?

The contents need to be accessed through the database. Currently, the database automatically opens the files in their native application, and I'd like to retain this functionality

ie. End-User does not directly access the file. The file is opened by Access - all credentials are stored by Access, not by the enduser.

VBA pseudocode:
if EndUserCredential=True, allow Access to open file, else don't open file.
if George "passes inquiry" to another end-user, Paul, PaulCredentials = PaulCredentials + GeorgeCredentials.

ie.
\\Root\attachments\123 - MSACCESS has access. Paul does not have access, George does not.
\\Root\Attachments\456 - MSACCESS has access. George does not, Paul does not.

Am I oversimplifying?

 

[willknapp]

ie. End-User does not directly access the file. The file is opened by Access - all credentials are stored by Access, not by the enduser.

VBA pseudocode:
if EndUserCredential=True, allow Access to open file, else don't open file.
if George "passes inquiry" to another end-user, Paul, PaulCredentials = PaulCredentials + GeorgeCredentials.

ie.
\\Root\attachments\123 - MSACCESS has access. Paul does not have access, George does not.
\\Root\Attachments\456 - MSACCESS has access. George does not, Paul does not.

Am I oversimplifying?

I had considered that at one point, but I couldn't figure out how to make the credentials stay with Access, rather than the user.

 

[way2bord]

I had considered that at one point, but I couldn't figure out how to make the credentials stay with Access, rather than the user.

A few ways to go about it...

pseudocode as follows...

run:
"runas /user:username@yourdomain.com explorer"

OR

declare an object, open up a command window:
"net use \\server\share <password>> /USER:\\LOGONSRV\USERNAME"


You'd save the appropriate server password / domain in Access, and verify individual user credentials within Access itself.

Also, (however...?)...Saving this server name / passwords hardcoded in an access application is frowned upon for good reason. It will work, but there is some risk associated with doing this...

 

[willknapp]

Also, (however...?)...Saving this server name / passwords hardcoded in an access application is frowned upon for good reason. It will work, but there is some risk associated with doing this...

Presumably, the end users wouldn't be able to see this username since, due to the app being a runtime version, all the code will be converted to binary, correct?