encrypt stored procedures

I have an application that I am developing, and a large amount of business logic is being coded into the stored procedures.  I would like to protect the privacy of this code.

Does anyone know of a way to encrypt T-SQL for distributed applications?

Thanks!
LVL 1
yitz99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
Yes.   Its built in

eg:

CREATE PROCEDURE myproc WITH ENCRYPTION AS
.......
0
Dishan FernandoSoftware Engineer / DBACommented:
Yes. You can Encrypit using "WITH ENCRYPTION" Key word
but it can be decrypt using following sps.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5

But I thing no Other way to do It.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yitz99Author Commented:
Aha
From msdn:
"ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication."

Maybe that will do it.  Just distribute the replicated database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dishan FernandoSoftware Engineer / DBACommented:
sp_helplogins
0
Dishan FernandoSoftware Engineer / DBACommented:
sorry...
0
arbertCommented:
Just remember, this is NOT foolproof and you can also download a proc off the internet to reverse the encryption--the tool is 100% accurate too.

If you're really concerned about your business logic being "hijacked", create a DLL and call your logic....
0
ShogunWadeCommented:
If I recall corectly there are two encyption procedures in SQL sp_pwdencrypt and sp_encrypt.   The latter is ised when you specify WITH ENCRYPTION  these can be decrypted by a simple conversion from varbinary to varchar.

Given that the text is held in syscomments, perhaphs (never tried it, so backup your db before testing this) you could modify this table with your own encryption technique.
but I must stress ---- never tried it, so backup your db before testing this !!!!!
0
yitz99Author Commented:
Arbert,

We'd like to have everything.  We want the robustness of stored procedures, and to secure our code as well.  Of course that might not be possible, but we're trying!

ShogunWade,

What does sp_pwdencrypt do?
0
ShogunWadeCommented:
sp_pwdencrypt  is used to store passwords for SQL users.   It is much more secure than sp_encrypt but there is no built in mechanism to switch between them.

0
ShogunWadeCommented:
Correction to previos .........................

its the ENCRYPT () function that is used to store stuff in syscomments not sp_encrypt..


Here is an example of how it is not as secure ase you would like.

select CONVERT(nvarchar(100),ENCRYPT('hello'))

returns   'hello'
0
ShogunWadeCommented:
however select CONVERT(nvarchar(100),PWDENCRYPT('hello'))

doesnt return much at all
0
arbertCommented:
Well, since you can't lock everyone out of the database, you don't have a lot of options.  You buy a third party tool to manage you proc encryption, you use the built in SQL Server encryption (that can be unencrypted), or you encapsulate the logic in your program or a DLL you can call.

0
yitz99Author Commented:
Our current plan is to lock everyone out, and to only give access via our interface, and this approach has drawbacks as well.

What kind of 3rd party tools are out there to manage encryption?
0
puranik_pCommented:
ShogunWade,

CAN YOU PLEASE HELP ME OUT WITH THE BINARY TO VARCHAR CONVERSION.

SELECT convert(varbinary,CONVERT(BINARY,120703))
RETURNS,
0x00000000000000000000000000000000000000000000000000000001D77F

AND I WANT LAST 5 CHARS OF THE RESULT. THAT IS..1D77F

WILL POST A "POINTS FOR" Q FOR U.

THANKS IN ADVANCE.
0
arbertCommented:
Why didn't you post a new question????

SELECT right(convert(varbinary,CONVERT(BINARY,120703)),5)
0
puranik_pCommented:
its not showing me anything. (empty string is returned)

and instead of posting a new question, I think you people are surely expert in binary area then its better to post it here.
0
arbertCommented:
NO, this question is closed--your suppose to open a new one......Read the EE guidelines...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.