Solved

encrypt stored procedures

Posted on 2003-12-09
18
1,714 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 56
SQL Get Store Procedure Column Name As Row 5 41
SQL Server Express or Standard? 5 30
Need to trim my database size 9 26
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

733 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