How to encrypt all stored procedure, functions and views in sql server

Question

How to encrypt all stored procedure, functions and views in sql server

binutb

SSCommitted

Points: 1741
More actions
November 13, 2018 at 11:24 am

#380815

Hi,
How to encrypt all stored procedure, functions and views in sql server.
Please advise best method, is available any tool for above.
Regards
Binu

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

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

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

https://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

binutb SSCommitted Points: 1741 More actions · Answer 2

Hi,
Any tool for encrypt all stored procedure

Regards
Binu

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

1. Make sure all your stored procedures are in source control.
2. For each stored procedure you should have a script like this:
IF OBJECT_ID('dbo.myStoredProcedure','P') IS NULL EXEC('CREATE PROCEDURE myStoredProcedure AS') GO ALTER PROCEDURE myStoredProcedure @MyParam1 int , @MyParam2 varchar(10) --with encryption AS ....

Then all you need do is concatenate all the scripts together into one file, then do a find and replace, replacing "--with encryption" with "with encryption"
then run the script.
You'll also need something similar for functions and views.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Also understand that doing such encryption is mostly futile if you goal is to protect the code. There are tools and techniques all over the internet for doing such an encryption.

--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)

binutb SSCommitted Points: 1741 More actions · Answer 5

Hi,
Can you advise a download link for encrypt stored procedure

Regards

Binu

Johnson Welch SSCoach Points: 18773 More actions · Answer 6

You can encrypt SQL database objects like functions, stored procedures, triggers using WITH ENCRYPTION option along with the CREATE script. Beaware of fact that procedures once encrypted, there is no straight forward way to decrypt them in SQL Server. Have a look:
https://www.mytecbits.com/microsoft/sql-server/encrypting-stored-procedure

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Hi,
Can you advise a download link for encrypt stored procedure
Regards
Binu

Sure. Google is your friend but here's one. Notice that it shows not only how to encrypt a stored procedure but also how anyone with high enough privs can break the encryption. The first reference at the bottom of the article also shows how to decrypt encrypted procs and the like which, like I said earlier, makes it virtually useless to encrypt such things.
https://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

I haven't encrypted T-SQL in years so my disclaimer is that I don't know if the decryption is still that easy in the later versions of SQL Server but the article is only 5 years old and I've not heard of anything that would make it more difficult.

--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)

mfconners Say Hey Kid Points: 707 More actions · Answer 8

Jeff Moden - Tuesday, November 13, 2018 10:59 PM
I don't know if the decryption is still that easy in the later versions of SQL Server but the article is only 5 years old and I've not heard of anything that would make it more difficult.

The SQL Server needs to read the unencrypted code to execute it. You could only make it more difficult by requiring a decryption key at every execution. That would have a lot of unintended consequences.

It's kind of like encryption on DVDs. It was broken almost immediately because the decryption keys need to be an open secret. You wanted to watch that movie on your computer, right?

Mike Conners