?
Solved

encrypt stored procedures

Posted on 2003-12-09
18
Medium Priority
?
1,761 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:
Dishan Fernando earned 508 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9903399
sp_helplogins
0
 
LVL 8

Expert Comment

by:Dishan Fernando
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

809 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