February 22, 2019 at 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
February 23, 2019 at 6:38 am
haichells - Friday, February 22, 2019 11:34 PMDear 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?
😎
February 23, 2019 at 9:10 am
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?
February 25, 2019 at 3:43 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 25, 2019 at 5:11 am
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.
February 25, 2019 at 8:37 am
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 😉
February 25, 2019 at 12:23 pm
haichells - Friday, February 22, 2019 11:34 PMDear 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
February 26, 2019 at 9:34 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply