How to have customers secretly run SQL Server Update Scripts?


Our customers will each have their own copy of our DB, which could even be the Express version or otherwise. The DB will need to change on occasion (both data and structure) so each customer will need to run the script changes to their own DB. Of course, we don't want them to see any scripts (to keep it tamper-proof, and keep the DB names secret).

What's the recommended way to do something like this? Through an msi? If so, how is it created? We've never done this before, so not sure what the "best practice" is for creating and/or running SQL DB scripts when it needs to be run by the customers without them necessarily knowing it's happening.

Not sure if it matters, but we are using VB.NET, Visual Studio 2010, and SQL Server 2008 R2.
jjsatherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You could have a look at SQL Shield 5 for SQL Server 2008 R2 released

http://www.sql-shield.com/
0
CodeCruiserCommented:
I think the best way is to create a separate exe which just executes the sql queries as any other program. You can include the queries within the exe or make the exe generic, use a text file with encrypted queries which the exe decrypts and executes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjsatherAuthor Commented:

I've never worked with encrypted text files, but I'm up for learning. I'd also like to try to break down your suggestion into practical terms to see if I'm tracking with you.

Let's assume multiple DB versions, and each version will have various proc changes via alter scripts, 2-3 pages long each. Of course, we don't want to run procs for versions older than the DB, so we make a large text file with various delimiters, like this...

Version:1.1.1
START PROC
alter proc ProcName1
[blah blah for 3 pages]
END PROC
START PROC
alter proc ProcName2
[blah blah for 4 pages]
END PROC
Version:1.1.2
START PROC
alter proc ProcName3
[blah blah for 3 pages]
END PROC
START PROC
alter proc ProcName2
[blah blah for 4 pages]
END PROC
Version:1.1.3
START PROC
alter proc ProcName4
[blah blah for 3 pages]
END PROC
START PROC
alter proc ProcName5
[blah blah for 4 pages]
END PROC
START PROC
alter proc ProcName5
[blah blah for 4 pages]
END PROC

Now I encrypt the text file. My program reads the current DB version (suppose it's 1.1.1) and decrypts the text file above. It reads the relevant scripts and executes them. (Which in this case, would be Version 1.1.2 and following, ignoring the Version 1.1.1 scripts.)

Is this what you're saying? If so, I'm not opposed to it, but I assumed there'd be a more elegant solution, with tools already in place. But if what I'm asking for is very specific to us, then if we need to build it from the ground up, then so be it.

Otherwise, I'm trying to think if it'd be worthwhile to have these scripts in a SQL dbf file? Or maybe an XML file?
0
CodeCruiserCommented:
Yeah that's similar to what I said (I was not aware of versions). I have not seen any tool to do this sort of stuff.
0
lcohanDatabase AnalystCommented:
You could create one encrypted file (using cryptoapi for instance) for each set of SQL code objects/ALTER TABLE changes belonging to the same version and one control script/exe that reads the local version currently running then chooses the right file to execute against the DB. Assuming you don't have too many versions out there I think this may be easier to do then parse a encrypted text file to get only a portion of it and execute it against a sql db.
http://www.freevbcode.com/ShowCode.asp?ID=804
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.