Encrypting All Procedures in a Database

Question

Encrypting All Procedures in a Database

haichells

SSCrazy

Points: 2713
More actions
February 22, 2019 at 11:34 pm

#379452

Dear All,
I have a requirement where i need to encrypt all my stored procedures in a existing database. Do we have any easy way to complete this?
Also is there any way to open a encrypted procedure?
Thanks in Advance!
Chelladurai

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

haichells - Friday, February 22, 2019 11:34 PM
Dear All,
I have a requirement where i need to encrypt all my stored procedures in a existing database. Do we have any easy way to complete this?
Also is there any way to open a encrypted procedure?
Thanks in Advance!
Chelladurai

Quick question, why do you need to encrypt the stored procedures?
😎

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

The only way I know is to script all the stored procedures and add the line "with encryption"
I'd create a script for each stored procedure like this:
IF OBJECT_ID('[dbo].[mySP]','P') IS NULL EXEC ('CREATE PROCEDURE [dbo].[mySP] AS') GO ALTER PROCEDURE [dbo].[mySP] -- WITH ENCRYPTION AS SELECT * FROM myTable GO
You can then concatenate all the files together into one file. Then do a find and replace (find "-- WITH ENCRYPTION" replace with "WITH ENCRYPTION") Then you can install them on any database as either encrypted or not.
Why haven't you got all your stored procedures in source control?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

There are ways to open encrypted procedures. So, properly secure and control access to the database and the server. That's a better mechanism than encrypting procedures. Further, you shouldn't be putting anything in procedures except query code which shouldn't need to be encrypted (there is literally nothing proprietary about SELECT * FROM TABLE WHERE X = Y). What is the real problem you're attempting to solve?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 4

Just a note from someone working with an encrypted vendor database. I understand that they're trying to protect their proprietary code by encrypting their stuff, but it makes it seriously painful for our local DBA team to troubleshoot problems in the databases we're hosting because we can't see what is going on when problems occur. We actually have to open support tickets with the vendor, wait for them to identify the issue, then wait longer for them to resolve the issue... This process can take weeks, once even took a year. In the meantime, our users are blaming us for the inability to get certain things done.

It's very high maintenance. And what's worse, you need to remember every time a new proc or function is created to encrypt it. They don't auto-encrypt if everything else is encrypted. Then you have to decrypt them when doing updates or troubleshooting... Serious PITA.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

As Grant said, the normal encryption can easily be reversed, not secure in any way, shape or form. On Brandie's point, intellectual property cannot be protected by encryption or other means of obscurity, it must be a legally binding contractual contract!
😎

For any advice, I'll still need an answer to the initial question: "why do you need to encrypt the stored procedures?"

If the OP does not answer, this thread goes to the PNG bucket 😉

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

haichells - Friday, February 22, 2019 11:34 PM
Dear All,
I have a requirement where i need to encrypt all my stored procedures in a existing database. Do we have any easy way to complete this?
Also is there any way to open a encrypted procedure?
Thanks in Advance!
Chelladurai

I don't have the recent link but I remember a similar request just a couple of weeks ago. It took Solomon Rutzky about 10 minutes to decrypt the whole shebang. IIRC, products like RedGate SQL Compare auto-magically decrypt the code, as well. The requirement to encrypt the code is a total waste of time, effort, and money.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

The short answer is you cannot encrypt code. You can obfuscate it with the "WITH ENCRYPTION" option, but this is easily reversable, as pointed out above. SQL Compare does this for users, so you can't protect the code.

Stop trying.