How to deploy a encrypted procedure on the client machine.

Question

Post reply

How to deploy a encrypted procedure on the client machine.

chandrasekhar.ms

SSC-Addicted

Points: 412
More actions
January 15, 2009 at 11:09 am

#93101

Hi,
In my distributed application i need to deploy some encrypted stored procedure on the client machine with out sharing the sql scripts.
Can someone let me know the suggested way to do this?
If possible please direct me to any tutorial to do that.
Regards,
-Chandra.

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

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

Abhijit More SSCertifiable Points: 6897 More actions · Answer 1

Its not possible to deploy the stored proc w/o script. You have to run the script on client machine.

Abhijit - http://abhijitmore.wordpress.com

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

I am not if I've understood what you said and want, but if you want to restrict the clients from viewing/editing the procedures at their end after deploying then what you can do is script the procedure using WITH ENCRYPTION option like....

CREATE PROCEDURE dbo.SomeProcedure

WITH ENCRYPTION

AS

----here goes the statements

--Ramesh

chandrasekhar.ms SSC-Addicted Points: 412 More actions · Answer 3

Hi Guys,

My question you have a script which can create an encrypted script on the machine you run it.

For ex: You have a script SampleProc.sql and it has the following code

CREATE PROCEDURE SampleProc

WITH ENCRYPTION

AS

SELECT *

FROM sysobjects

Now i want to deploy the same procedure on all client machines without sharing the SQL script. I could be an MSI or any other alternative.

Regards,

-Chandra.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Actually, I never had to do something like this...., so I won't be able to help you much, but there are some options like...

1. You can cipher the code using some algorithm and store it in some table and then write a script that will execute the same by deciphering code.

2. You can build a simple client application

--Ramesh

steveb. SSC-Forever Points: 46733 More actions · Answer 5

Not sure if it is possible to encrypt a proc.

Why do you not use security in SQL, and deny the user the right to view the procedure?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

If the client is SA on the box this is getting deployed it, the encryption is usually not going to be "enough" to prevent them from seeing what the code does. Since SQL Server needs to know how to decrypt the stuff, let's just say that in most cases, there are places you can see the unencrypted version of the script you're trying to run.

Yes - it will make their life difficult, but impossible - probably not.

As to deploying it - you should look at creating an installation script of something that will get run through a compiled language (like .NET). This would be one of those cases where I'd make sure the script itself is embedded inside of the compiled code.

The .NET avenue will likely be useful anyway since you will want it to also install whatever else you might need to enforce the security (like your certs/master Key/etc....).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323504 More actions · Answer 7

remember SQL encryption is not all that strong, even in 2005, a simple Google search will allow anyone that can run a TSQL script see the un-encrypted text of something encrypted. I've done it myself just to confirm that 3rd party scripts are not using cursors or written poorly.

with that said, you could deploy your scripts as obfuscated binary as an option.

[font="Courier New"]--totally obfuscate a command to send to the client:

DECLARE @cmds NVARCHAR(MAX)

DECLARE @obfoo VARBINARY(MAX)

SET @cmds = '

PRINT ''This binary string will execute "CREATE PROCEDURE sp_find":''

create procedure sp_find

@findcolumn varchar(50)

WITH ENCRYPTION

as

begin

set nocount on

select sysobjects.name as TableFound,

syscolumns.name as ColumnFound

from sysobjects

inner join syscolumns on sysobjects.id=syscolumns.id

where syscolumns.name like ''%'' + @findcolumn +''%''

or sysobjects.name like ''%'' + @findcolumn +''%''

order by sysobjects.name

end

'

SET @obfoo = CAST(@cmds AS VARBINARY(MAX))

SELECT @obfoo --the varbinary value to EXEC

DECLARE @_ AS VARBINARY(MAX)

SET @_ =0x0D000A005[snip really long binary string of obfoo above]

EXEC (@_)

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mahatsameer SSC Journeyman Points: 77 More actions · Answer 8

You can use Red Gate's SQL Packager which make exe of your sql scripts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Please note: 6 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

Still, it's a handy idea even if it is 6 years later.

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