Stored Procedure and Functions Encryption

For the last couple of weeks, I was thinking about finding a solution to encrypt the stored procedures of my database in SQL Server 2005. The reason behind this goal is that normally our stored procedures contain complex business logic. This business logic is the intellectual property of the company and it is our job to keep it safe and protected.
There are many third party solutions and ‘WITH ENCRYPTION’ in SQL Server 2005 but the encrypted code is readable with the help of any free Decoders available on the internet. We needed a better solution to keep the code encrypted yet executable.
After searching through the internet, I came up with an idea ‘Why not to create SP at runtime and drop it after executing?’ I got this idea by the post of someone in one of the SQL Server Forums on this subject. And I feel that the forum poster was not serious about the idea.
But I thought why not to give it a try.

Core Logic

The logic is very simple. I decided to keep the actual code of the stored procedure in a table. This table will have the following columns;

a. tID int
b. vSQL varchar
c. SQL_Encrypted varbinary
d. SNO tinyint

ID will be used to refer and execute the actual code.

Explanation

In the following steps, I will tell you how I performed this task;

Step 1:

Create a table tblSQLTable. This table will be encrypted. Here is the CREATE TABLE statement for tblSQLTable;

if Not Exists(Select * from sys.objects where name = 'tblSQLTable' and Type = 'U')
begin
CREATE TABLE [dbo].[tblSQLTable](
[tID] [int] NOT NULL,
[SNO] [int] NOT NULL,
[vSQL] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SQL_Encrypted] [varbinary](max) NULL,
CONSTRAINT [PK_tblSQLTable] PRIMARY KEY CLUSTERED
(
[tID] ASC,
[SNO] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

end
else
begin
Delete from tblSQLTable
end

The column vSQL will have the original text of the stored procedure. SQL_Encrypted will contain the encrypted text of the stored procedure. Initially, I will work with vSQL column to explain my point. At last, I will discuss usage and implementation of SQL_Encrypted column.

Step 2:

Open the stored procedure that you want to encrypt. To insert the code in the table we will have to delete the SET statements at the top of each stored Procedure. After this, replace the single quote with two single quotes. Replace ALTER PROCEDURE with CREATE PROCEDURE and add # operator at the beginning of the stored procedure name. Finally, create a simple Insert statement for tblSQLTable against the respective stored procedure code.
Actual Stored procedure;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: Atif Sheikh
-- Create date: 18-May-2010
-- Description: Sp to be Encrypted
-- =============================================



ALTER PROCEDURE sp_who123
@pInt int,
@pVarchar varchar(10)
AS
BEGIN
SET NOCOUNT ON;
Select @pInt, @pVarchar
PRINT 'SP Executed successfully…'
END

Insert statement of stored procedure;
Insert into tblSQLTable (tID,vSQL)
Select 2,'
CREATE PROCEDURE #sp_who123
@pInt int,
@pVarchar varchar(10)
With Encryption
AS
BEGIN
SET NOCOUNT ON;
Select @pInt, @pVarchar
Print ‘'SP Executed successfully…’'
END '

I have added 'WITH ENCRYPTION' in the above statement so that the code may not be visible in SQL Profiler at the time of execution.

Step 3:

Now that we have inserted a record in tblSQLTable with the actual stored procedure code, now is the time to execute it. Replace the code of stored procedure with the following code;

ALTER PROCEDURE [dbo].[sp_who123]
@pInt int,
@pVarchar varchar(Max)
AS
BEGIN
SET NOCOUNT ON;

Declare @vSql varchar(max)
Select @vSql = vSQL
from dbo. tblSQLTable
Where tID = 1


Exec(@vSql)
exec [dbo].[#sp_who123] @pInt,@pVarchar
Drop Procedure [dbo].[#sp_who123]
END

Important thing to note is that the parameters are the same. Application will call this stored procedure normally. The body of the stored procedure is changed. We are retrieving the record from tblSQLTable according to its respective ID. Create the stored procedure from tblSQLTable with EXEC statement. As the name of stored procedure is starting with # sign in the tblSQLTable, it will be created in the session. Execute the newly created stored procedure with # sign with the same parameters and finally, drop it. That’s it.

Step 4:

Now the question rises that still the code can be seen in the tblSQLTable. Answer is that we will encrypt the table column having code of the stored procedures. I applied encryption to tblSQLTable as;

Create Master Key;

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

Create Certificate;

CREATE CERTIFICATE sp_who123_SPs
WITH SUBJECT = 'sp_who123 Stored Procedures';
GO

CREATE SYMMETRIC KEY StoredProcedures_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE sp_who123_SPs;
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY StoredProcedures_Key11
DECRYPTION BY CERTIFICATE sp_who123_SPs;


Now updating the SQL_Encrypted column;

UPDATE tblSQLTable
SET SQL_Encrypted = EncryptByKey(Key_GUID('StoredProcedures_Key11')
, vSQL, 1, HashBytes('SHA1', CONVERT( varbinary(max), tID)));
GO

And finally, check the SQL_Encrypted column;

SELECT vSQL, SQL_Encrypted
AS 'Encrypted SQL',
CONVERT(varchar(max),
DecryptByKey(SQL_Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary(max), tID)))
)
AS 'Decrypted SQL' FROM tblSQLTable;
GO

The above query should give the following result;

Now use the SQL_Encrypted column in the actual stored procedure (as the vSQL column containing actual text should be deleted and should not be there.) After applying encryption to the tblSQLTable, the actual stored procedure will look like this;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: Atif Sheikh
-- Create date: 12-May-2010
-- Description: Encryption SP
-- =============================================

ALTER PROCEDURE [dbo].[sp_who123]
@pInt int,
@pVarchar varchar(Max)
AS
BEGIN
SET NOCOUNT ON;


Select @vSql = CONVERT(varchar(MAx),
DecryptByKey(SQL_Encrypted, 1 ,
HashBytes( ‘SHA1’, CONVERT(varbinary(Max), 1))))
from dbo.tblSQLTable
Where tID = 1

Exec(@vSql)
exec [dbo].[#sp_who123] @pInt,@pVarchar
Drop Procedure [dbo].[#sp_who123]

END

The Key to open the Key will be executed when the connection is made.

To check and call the stored procedure from the query window, here is the code;

-- This will be done in application soon after connection is made
OPEN SYMMETRIC KEY StoredProcedures_Key11 DECRYPTION BY CERTIFICATE sp_who123_SPs;
----------------------------------
exec [dbo].[sp_who123] 2,'Atif'

The above statements will be passed from the application normally.
To Add All Stored Procedures on the Fly
Now I will discuss to add all stored procedures. First Create Certificate and Symmetric Keys. Then execute the given code. Remember to take backup of the database. The function to get the parameters of stored procedure with Default values is;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author: Atif
-- Create date: 24-May-2010
-- Description: Get Default Value of a Parameter
-- =============================================
ALTER FUNCTION [dbo].[fnGetParameterDefaultValue]
(
@pSPName varchar(1000),
@pParameterName varchar(100),
@pDataType varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @pOutPut varchar(1000)
Declare @vStartPosition int
Declare @vStartPosition2 int
Declare @vStartPosition3 int
Declare @vStartPosition4 int
Declare @vStartPosition5 int
Declare @vSPText varchar(max)
Declare @vSPText2 varchar(max)


-- Get the text fro syscomments (first 4000 characters if length of SP is > 4000)
SELECT @vSPtext = (SELECT text FROM syscomments
WHERE id = object_id(@pSPName) and colid=1 and number = 1)

Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000)

if IsNull(@vSPtext,'') = ''
begin
-- Exit if SP Name Not found in syscomments....
Select @pOutPut = ''
RETURN @pOutPut
end


Set @pOutPut = ''

While 1=1
Begin
-- Get the position of the parameter definition.
Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText)
-- Check if parameter exists
if @vStartPosition > 0
begin
-- Get the Definition String
select @vSPText = RIGHT ( @vSPText, DataLength(@vSPText)-(@vStartPosition -1))

-- Get the string breaker
if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0)
or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText) > 0)
begin
Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1
Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1
Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1
Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1
Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1

if @vStartPosition <= Len(@pParameterName) Set @vStartPosition = 10000000 if @vStartPosition2 <= Len(@pParameterName) Set @vStartPosition2 = 10000000 if @vStartPosition3 <= Len(@pParameterName) Set @vStartPosition3 = 10000000 if @vStartPosition4 <= Len(@pParameterName) Set @vStartPosition4 = 10000000 if @vStartPosition5 <= Len(@pParameterName) Set @vStartPosition5 = 10000000 Select Top 1 @vStartPosition = [value] from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10)) + ',' + Cast(@vStartPosition3 as varchar(10)) + ',' + Cast(@vStartPosition4 as varchar(10)) + ',' + Cast(@vStartPosition5 as varchar(10)) ,',') order by Cast([value] as int) end else begin -- SP text must atleast have AS to break the parameter definition string Set @vStartPosition = CharIndex('AS',@vSPText) - 1 end -- Get the specific Definition String Set @vSPText2 = Left(@vSPText,@vStartPosition) -- Check if you got the right one by data type... if CharIndex(@pDataType,@vSPText2) > 0
begin
--Select 'IN'
--Select @text2
if CharIndex('=',@vSPText2) > 0
begin
-- check the default value
Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2))
-- We have default value assigned here

if Right(@pOutPut,1) = ','
Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1)
end
else
begin
--Set @pOutPut = 'No Default Value Defined...'
-- We DO NOT have default value assigned here
Set @pOutPut = ''
end
--No need to work further with this parameter
BREAK
end
else
begin
--Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000)
-- Cut the SP text short and loop again
Set @vSPText = SubString(@vSPText,@vStartPosition,4000)
end
-- This should never be the case. Just a check....
if Datalength(@vSPText) < poutput =" 'Parameter" poutput =" ''" poutput =" rtrim(ltrim(@pOutPut))" object_id =" a.tid" schema_id =" b.schema_id" tid =" 1721577717" fetch_status =" 0" valterparams =" ''" vactualalterparams =" ''" valtersql =" ''" valtersql2 =" ''" vdeclarestr =" ''" vaddsql =" ''" valterparams =" @vAlterParams" max_length =" -1" is_output =" 1" vactualalterparams =" @vActualAlterParams" is_output =" 1" system_type_id =" a.system_type_id" object_id =" @vtID"> 'sysname'
and b.name <> 'FloatPercentage'
order by parameter_id

Set @vAlterParams = Stuff(@vAlterParams,1,1,'')
Set @vActualAlterParams = Stuff(@vActualAlterParams,1,1,'')

Set @vAlterSQL = ' ALTER PROCEDURE ' + @voName + ' ' + @vAlterParams
+ ' AS
BEGIN
SET NOCOUNT ON;

Declare @vSql varchar(max)

Set @vSql = ''''
'

Select @vAlterSQL2 = @vAlterSQL2 + '
Select @vSql' + Cast(SNO as varchar(10)) + ' = CONVERT(varchar(MAX),
DecryptByKey(SQL_Encrypted, 1 ,
HashBytes( ''SHA1'', CONVERT(varbinary(Max), tID))))
from dbo.tblSQLTable
Where tID = ' + Cast(@vtID as varchar(100)) + '
And SNO = ' + Cast(SNO as Varchar(10))
,@vDeclareStr = @vDeclareStr + 'Declare @vSQL' + Cast(SNO as Varchar(10)) + ' varchar(MAX) ' + Char(10) + Char(13)
,@vAddSQL = @vAddSQL + '+ @vSQL' + Cast(SNO as Varchar(10))
From tblSQLTable
where tID = @vtID

Set @vAddSQL = '
Set @vSQL = ' + Stuff(@vAddSQL,1,1,'')

Set @vAlterSQL = @vAlterSQL + @vDeclareStr + @vAlterSQL2 + @vAddSQL + '
print @vSql
Exec(@vSql)
exec ' + @voName2 + ' ' + @vActualAlterParams + '
Drop Procedure ' + @voName2 + '
END'

print @vDeclareStr
print @vAlterSQL2
print @vAddSQL

Exec (@vAlterSQL)
Set @vAlterSQL = ''
Fetch Next from C1 into @vtID,@voName,@voName2
end
Deallocate C1

commit

I hope you will not get any error in the above code. I tested on 3 databases of different domains and it was working fine. After executing the above code, you can delete the column vSQL from tblSQLTable.

I will not discuss Encryption here in detail. Just one thing to mention is that the EncryptByKey returns varbinary with a maximum size of 8000 bytes.
I hope this will help someone who is on SQL Server 2005 and looking for encryption of stored procedures. The same pattern can be applied on functions but I have not tested it yet on functions.

I know that there are some performance issues like possible Temp DB contention in this solution. I will be expecting a lot of suggestions and comments in this regard so that we together can make this solution more workable and efficient.