x

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

more ▼

asked Mar 09, 2010 at 05:03 PM in Default

Bob gravatar image

Bob
31 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Mar 09, 2010 at 05:11 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4.5k 10 11 15

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.
Mar 09, 2010 at 07:30 PM TimothyAWiseman
Can you point me in the right direction as to how to build such an exe that will properly communicate with SQL2008? Thanks!
Mar 09, 2010 at 07:31 PM Bob
(comments are locked)
10|1200 characters needed characters left

+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.

more ▼

answered Mar 09, 2010 at 05:25 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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.
Mar 09, 2010 at 07:04 PM Bob
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.
Mar 09, 2010 at 07:26 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x412
x69
x38

asked: Mar 09, 2010 at 05:03 PM

Seen: 1054 times

Last Updated: Mar 09, 2010 at 05:03 PM