How to send secure update scripts to my customers

Question

question

Bob asked Mar 9, '10

How to send secure update scripts to my customers

I have a database product that is sold to outside companies. All of the stored procedures in the original database where compiled "With Encryption", so that our customers cannot view the business logic.

From time to time, we need to send an update to these customers, with changes to the stored procedures (for example, customization, debugging, etc.). I need to know how I can send a script file to my customers that will update the stored procedure, but prevent them from viewing it.

It seems like this must be a fairly common thing that companies need to do, but I am having no luck in finding out how to do this. Any help would be appreciated.

Bob

stored-procedures script encryption

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-09T17:11:46Z

CirqueDeSQLeil answered Mar 9, '10

I have seen some companies that compile an exe that will run all of the scripts that need to be updated. However, once you provide the script to them to be updated on their server, they will be able to view it. There are plenty of methods to decrypt an encrypted stored procedure.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman commented · Mar 09, 2010 at 07:30 PM

This is probably the best way to go. Have an executable that will update all the procedures and run the update "with encryption" both makes it easier for them to implement and provides some protection from casual viewing. To echo everyone else here though, the with encryption provides limited security. This does not mean you should not use it, but you should use it knowing it is a flimsy lock at best.

1 ·

Bob commented · Mar 09, 2010 at 07:31 PM

Can you point me in the right direction as to how to build such an exe that will properly communicate with SQL2008? Thanks!

0 ·

Answer 2 · 2010-03-09T17:25:24Z

Matt Whitfield answered Mar 9, '10

+1 @CirqueDeSQLeil - encrypted stored procedures should really be named 'barely hidden' stored procedures.

However, if you are just wanting to stop the casual observer, then you could do something like this, which is actually really cheesy:

SELECT CONVERT([varbinary],'PRINT ''hello''')
-- returns 0x5052494E54202768656C6C6F27

DECLARE @sql [varchar](MAX)
SET @sql = CONVERT([varchar], 0x5052494E54202768656C6C6F27)
EXEC (@sql)

You could put it in an EXE (thus the +1 at Cirque).

Or you could make a CLR procedure to do it, and distribute the assembly.

All of these methods can and will be broken by anyone who is really interested in looking. Especially seeing as the place they will end up is about as secure as Somalia's coastline.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bob commented · Mar 09, 2010 at 07:04 PM

My goal is just to stop casual viewing, as I am aware of the problems you cite. It's more like "obscuring" or "encoding" rather than "encryption". However, a lot of people may look at the queries if they were in the open, but very few of them will do it if it requires active reverse-engineering. Back in SQL 7.0, I accomplished this by scripting the encrypted SP (7.0 allowed this) and sent my customers the resulting binary string with the SQL's undocumented decrypt function. This worked fine for 7.0, 2000 and 2005. In 2008, this no longer works. I am looking for an 2008 equivalent.

0 ·

Matt Whitfield ♦♦ commented · Mar 09, 2010 at 07:26 PM

I would think you're best off creating a stored procedure that takes an encrypted varbinary, decrypts and runs it. Use some obfuscated method like to above to create the procedure. Not bullet proof, but enough of a pain to put off the casual viewer.

0 ·

question