Solved

encrypt stored procedures

Posted on 2003-12-09
18
1,695 Views
Last Modified: 2010-08-05
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!
0
Comment
Question by:yitz99
  • 5
  • 4
  • 3
  • +2
18 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903286
Yes.   Its built in

eg:

CREATE PROCEDURE myproc WITH ENCRYPTION AS
.......
0
 
LVL 8

Accepted Solution

by:
dishanf earned 127 total points
ID: 9903348
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
 
LVL 1

Author Comment

by:yitz99
ID: 9903355
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 8

Expert Comment

by:dishanf
ID: 9903399
sp_helplogins
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9903402
sorry...
0
 
LVL 34

Expert Comment

by:arbert
ID: 9904217
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904286
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
 
LVL 1

Author Comment

by:yitz99
ID: 9904321
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904387
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904421
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9904435
however select CONVERT(nvarchar(100),PWDENCRYPT('hello'))

doesnt return much at all
0
 
LVL 34

Expert Comment

by:arbert
ID: 9905057
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
 
LVL 1

Author Comment

by:yitz99
ID: 9910210
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
 
LVL 14

Expert Comment

by:puranik_p
ID: 10512833
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
 
LVL 34

Expert Comment

by:arbert
ID: 10513846
Why didn't you post a new question????

SELECT right(convert(varbinary,CONVERT(BINARY,120703)),5)
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 10513870
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
 
LVL 34

Expert Comment

by:arbert
ID: 10513931
NO, this question is closed--your suppose to open a new one......Read the EE guidelines...
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 10514170
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question