Skip to main content

Stored Procedure Encryption - SQL Server

Because of some security policies we need to require our code to be safe from Users who are going to use SQL Server database and objects of them and some outside threat. We have different different users to access the database objects are used for application.

We have so many ways to encrypt data, but here I am talking about the code encryption. With this encryption security, Users can execute the stored procedures but cannot view the code. Let us check the how the Stored Procedures can be encrypted. 
-- Creating demo objects

USE demo
GO

-- Creating table
IF (OBJECT_ID('UserMaster','U')> 0)
DROP TABLE UserMaster
GO

CREATE TABLE UserMaster
(
UserId INT,
UserName VARCHAR(100),
UserPwd NVARCHAR(100)
)

GO

-- Inserting demo records
INSERT INTO UserMaster
SELECT '1','User1','pwd1'
UNION ALL
SELECT '2','User2','pwd2'
UNION ALL
SELECT '3','User3','pwd3'

GO
Now we will create two Stored Procedures with and without encryption.
-- Creating Stored Procedure without encryption
CREATE PROCEDURE GetUserDataWithoutEncrypt
AS
BEGIN
SET NOCOUNT ON 

SELECT 
UserId,
UserName,
UserPwd
FROM UserMaster

END
GO

-- Creating Stored Procedure with encryption
CREATE PROCEDURE GetUserDataWithEncrypt
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON 

SELECT 
UserId,
UserName,
UserPwd
FROM UserMaster

END
GO
Executing both above created stored procedures, and you can see there as both giving the same output while Users run them,

EXEC GetUserDataWithoutEncrypt
EXEC GetUserDataWithEncrypt
GO



Finally, we will check the text of these both stored procedures.

-- Viewing text of Stored Procedure without Encrypted
EXEC SP_HELPTEXT GetUserDataWithoutEncrypt


-- Viewing text of Stored Procedure with Encrypted
EXEC SP_HELPTEXT GetUserDataWithEncrypt


Doing the same thing for Encrypted Stored Procedure from UI and see what happens.



Let me what you think for the stored procedure encryption and share your comment the if you have used it.

Comments

  1. Yes we have used SQL Server Code Encryption in the past. However our group elected against using it because of how easy it is to find programs to decrypt the code. Just search Google. You may be surprised as to the number of free and low cost apps that have been written to decrypt SQL Servers code Encryption. If you are really serious about encrypting your code you will need to find a third party tool to do the job.

    ReplyDelete
  2. Hi.
    Nice tip, but I have a little question:
    How can I (as admin user) edit ENCRYPTION object (SP or function)?
    Itai.

    ReplyDelete
  3. I think, we can not.

    But you have the text code of SP then you can alter it.

    ReplyDelete
  4. Encrypting your procedures like this is a bad idea for several reasons. Two off the top of my head are:

    1. This makes performance tuning much more difficult
    2. This form of encryption (really obfuscation) is easily defeated.

    Really all you do with this is succeed in making the support of your database more difficult when you do this.


    -js

    ReplyDelete

Post a Comment