Deploying "encrypted" stored procs

Question

Deploying "encrypted" stored procs

FelixG

SSCrazy

Points: 2550
More actions
January 16, 2006 at 1:37 pm

#92846

Hello,
I'm beginning to deploy some stored procedure updates to many branch offices, I could use a standard script to alter existing sps or create the new ones, but I must be sure the sps scripts are not being changed before running the updates.
Is there anyway to deploy "encrypted" stored procs ???
TIA,
Felix

Viewing 12 posts - 1 through 11 (of 11 total)

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

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

You can encrypt your stored procedure code by using the "WITH ENCRYPTION" option. Look up BOL for more information. Once the stored procs. are created with this option, the text won't be visible to users. We use this option when we deploy our code since a lot of our business logic resides in the DB layer.

FelixG SSCrazy Points: 2550 More actions · Answer 2

Thanks rsharma, that's exactly the stuff i needed!!!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

The WITH ENCRYPTION doesn't solve your problem with deploying the scripts, however. The WITH ENCRYPTION is used in the CREATE PROC[EDURE] statement (which is in cleartext and subject to modification as a result).

You say you're deploying these scripts to remote offices. Are there personnel at the sites who have the ability to administer the servers in question and therefore modify the scripts before they are executed? Or are there personnel who are actually running the scripts for you?

K. Brian Kelley
@kbriankelley

FelixG SSCrazy Points: 2550 More actions · Answer 4

Thanks bkelley,

I assume it's possible create the procedure in my development database using the "WITH ENCRYPTION" clause.This will create an encrypted sp, then I could script this encrypted stored proc and deploy it encrypted.

Am I wrong??

FelixG SSCrazy Points: 2550 More actions · Answer 5

I have tested the previous method and it works ... any comments???

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

When you mean you scripted it, what are you using the script the procedure? Do you mean you have something like...

CREATE PROCEDURE myprocedure
  @param1 int
WITH ENCRYPTION AS

Or do you mean when you script it you still have it encrypted where someone can't look at (and modify) the source code?

K. Brian Kelley
@kbriankelley

FelixG SSCrazy Points: 2550 More actions · Answer 7

1- First I create one readable script for may personal use like this:

CREATE PROCEDURE myprocedure

@param1 int

WITH ENCRYPTION AS

2- I run this script on my test database and after that I'll have the stored procedure encrypted.

3- Using "Enterprise Manager/All Tasks/Generate SQL Scripts" over the myprocedure I get this script:

if exists (select * from sysobjects where id = object_id(N'[dbo].[myprocedure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[myprocedure]

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

exec(decrypt(0x4CAFD1771AB283A69C1A88839C439A5182D4765C9BDD2E6316B9DFBDAF51326A47536E45D0205B5A3D527B67A81B9CC317C520E3844EDE86EA87530728A9A0E4E521772F31F3798F544E57C82D7989ABB69DD8D2B90518CBA5890CE6B3A3A1936E99F8616260191578682633CC9AACC7F9DC906F3D27E16AC0717CBEAAB7FE4781DB4196BB1603951A298431

))

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

so I can deploy this last script where the text of the sp is encrypted.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

That covers the 80-20 rule. If someone wanted to decrypt the stored procedure,

it would be fairly easy. Just execute the statement on a SQL Server they control

and then see what stored procedure appeared.

K. Brian Kelley
@kbriankelley

FelixG SSCrazy Points: 2550 More actions · Answer 9

Certainly they could see which stored proccedures appeared, but the actual code of the sp will be encrypted. That's the point.

This is not the "whole" security demanded though... just the first step.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

If they have log on capabilities to the SQL Server, they can see the code of the stored procedure. Take your outputted script from Enterprise Manager and run it in a clean database. Then do an sp_helptext on the stored procedure. The code is now visible. Remember, sp_helptext EXECUTE rights default to public on the master database and since guest has to be enabled in master... you guessed it, anyone can execute it.

Don't get me wrong, this works well because it stops the merely curious, at least in transport. Therefore it sounds like a reasonable solution for your situation.

K. Brian Kelley
@kbriankelley

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 11

Hi,

I am also looking at encrypting all my existing stored procedure.

I am thinking of giving my remote office a script to encrypt current

SP that is not encrypted.

Have any one of you done it.

I know that I have to read the original

SP code and append 'with encryption'

and apply it.