I am a developer tasked with maintaining a legacy application that is written in VB6. With the upcoming introduction of GDPR we are working on an encryption solution for our users. Our users use a variety of different versions of SQL Server and a variety of OS's. They host their own databases on their own servers.

I have come up with an idea of using column level encryption on the tables using certificates installed on the database server, then using a view to retrieve unencrypted data. Then using an INSTEAD OF trigger to update the encrypted data.

This means no changes to the application for data access. Unfortunately, because we connect via an ADODB.Recordset in many places the system will not allow the data to be updated (it throws an OLE DB generated error).

My next idea was to implement object model classes within the VB6 code and save via a stored procedure. This will involve allot of laborious code changes so we would ideally like to avoid this.

Does anybody have any experience solving this issue or any advice regarding implementing the correct form of encryption to enable our clients to be compliant?

Thanks in advance

  • Between "VB6", "different versions...variety of OS's", and "they those their own databases" I'm going to say that anything that fits all your requirements is going to be awful to develop, maintain, and troubleshoot. I'd push back and say that is isn't reasonable to expect this to be compliant given all of these factors, not the least of which being that you don't even own the servers the data sits on! Perhaps the path of least resistance is to begin only supporting a newer versions of SQL and require them to be Enterprise so you can use TDE and sleep soundly at night. Jan 16 '18 at 18:55
  • 1
    ...or 2016+ and use Always Encrypted (which is more secure than TDE, works on all editions, and only has to impact the important columns). Though this means no VB6 - which will celebrate its 20th birthday later this year. Jan 16 '18 at 19:15
  • "the system will not allow the data to be updated" Are you sure you implemented your INSTEAD OF triggers correctly. IE to return no results, and the correct rowcount. If so, the client shouldn't be able to tell you used a trigger. Jan 16 '18 at 22:20

I hope it is permitted to mention our third party product, NetLib Encryptionizer. It provides Transparent Data Encryption for all versions and editions of SQL Server including Express.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.