Solved

encrypt stored procedures

Posted on 2003-12-09
18
1,705 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup job space check 4 44
SQL Activity Monitor detail 2 25
Run an action on recently added records to a table 13 60
Need return values from a stored procedure 8 19
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 shrink a transaction log file down to a reasonable size.

856 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