Encrypt SQL Server Stored Procedures to Protect Source Code

By:   |   Updated: 2023-01-18   |   Comments   |   Related: > Encryption


Problem

As data becomes increasingly more valuable and companies strive to protect it, strong security measures are essential. Encryption is a must if you want to keep data safe from unauthorized access. In addition to keeping the data safe, there are also measures that can be taken to keep your processes and code safe too.

If you have ever created a stored procedure in your SQL database and want to keep the code hidden for business or security reasons, you should encrypt the stored procedure so that general users or hackers cannot access the details of what the stored procedure is doing and how it's doing it.

Solution

In this article, we will take a look at how to encrypt stored procedures and what you need to know.

To get started, we will create a sample table, fill it with data, and create a stored procedure to use this table. Next, we will encrypt the stored procedure and look at ways to verify that the stored procedure is actually encrypted. For the most part, it's a lot easier than you might think. With that said, let's get started by building our test table in our test database.

Creating the Test Table and Data

In this section, we are just building a basic test table to work with. Make sure you are doing this in a test environment and not in your production environment.

Create a test table.

CREATE TABLE dbo.EmployeeSalesChart
(
  colID INT IDENTITY
, empId INT NOT NULL
, empFirstName VARCHAR(20) NOT NULL
, empLastName VARCHAR(20) NOT NULL
, SalesYear INT NOT NULL
, SalesAmount NUMERIC(9,2)
);
GO

In this section, we are merely adding some generic data that was pulled from the AdventureWorks2019 sample database with some modifications of course.

INSERT INTO dbo.EmployeeSalesChart
(empID, empFirstName, empLastName, SalesYear, SalesAmount)
VALUES 
  ( 101, 'Morgan', 'Gray', 2019, 12000) 
, ( 102, 'Sean', 'Alexander', 2019, 14000) 
, ( 103, 'Natalie', 'Walker', 2019, 10000) 
, ( 104, 'Devin', 'Hall', 2019, 18000) 
, ( 105, 'Austin', 'Walker', 2019, 19000) 
 
, ( 101, 'Morgan', 'Gray', 2020, 9000) 
, ( 102, 'Sean', 'Alexander', 2020, 8000) 
, ( 103, 'Natalie', 'Walker', 2020, 17000) 
, ( 104, 'Devin', 'Hall', 2020, 22000) 
, ( 105, 'Austin', 'Walker', 2020, 14000) 
 
, ( 101, 'Morgan', 'Gray', 2021, 28000) 
, ( 102, 'Sean', 'Alexander', 2021, 30000) 
, ( 103, 'Natalie', 'Walker', 2021, 27000) 
, ( 104, 'Devin', 'Hall', 2021, 22000) 
, ( 105, 'Austin', 'Walker', 2021, 13000) 
 
, ( 101, 'Morgan', 'Gray', 2022, 14000) 
, ( 102, 'Sean', 'Alexander', 2022, 11000) 
, ( 103, 'Natalie', 'Walker', 2022, 10000) 
, ( 104, 'Devin', 'Hall', 2022, 13000) 
, ( 105, 'Austin', 'Walker', 2022, 16000) 
GO

Creating a Stored Procedure

In this example, we will create a stored procedure on the "EmployeesSalesChart" table. We are also creating the variable "@Year" to use as a reference for the "SalesYear" column. Other than that, it's a straightforward stored procedure. Also note that we are not using the "SELECT *" (select all) statement, but rather we are listing only the columns we want to show when the stored procedure does run.

CREATE PROC usp_FetchSalesByYear
@Year INT
AS
BEGIN
   SET NOCOUNT ON;
   SELECT 
      empId, 
      empFirstName, 
      empLastName, 
      SalesYear, 
      SalesAmount
   FROM dbo.EmployeeSalesChart 
   WHERE SalesYear = @Year
END;
GO

Verifying the Stored Procedure

Once you have created the stored procedure, confirm that it exists. You can do this by simply expanding your test database, expanding the "Programmability" folder, and finally the "Stored Procedures" folder.

list of database objects

If you right-click on the stored procedure, you will notice in the drop-down menu that appears, the "Modify" stored procedure option is available, not grayed out.

modify object in ssms

We can select Modify and get the actual stored procedure code.

We can also run this script to get the code in the stored procedures.

SELECT O.name, M.definition, O.type_desc, O.type 
FROM sys.sql_modules M 
INNER JOIN sys.objects O ON M.object_id=O.object_id 
WHERE O.type IN ('P');
GO

Results:

stored procedure text

You probably noticed that I have two stored procedures in this test database, there is a reason for that. In a moment, we are going to encrypt one of the stored procedures and run the above script once again so you can see the difference between the two when one is encrypted and the other is not.

For now, just notice that in the "definition" column, you can see the details of the stored procedure that we created.

Another option for viewing the details or definition of the stored procedure is by using another stored procedure. SQL Servers built-in stored procedure "sp_Helptext". This will return the entire script that makes up the body of the stored procedure.

sp_HelpText usp_FetchSalesByYear;

Results:

stored procedure text

Either option will generate the same results as far as the definition is concerned.

Let's run the stored procedure just to verify the result set. We will need to provide a parameter for this stored procedure to determine which year we want the stored procedure to retrieve data from.

EXEC usp_FetchSalesByYear 2021;

Results:

result set

Encrypting a Stored Procedure in SQL Server

We can encrypt stored procedures to help protect them from being viewed or edited. Encrypting a stored procedure prevents anyone with access to the database from viewing or changing the code, but it does not prevent the procedure from being executed.

Now that we have our table and stored procedure in place and working, let's encrypt stored procedure "usp_FetchSalesByYear" and see what happens. We can modify the existing stored procedure with a simple "ALTER PROC" or "ALTER PROCEDURE" command. You can use either option, they both work the same, and essentially they are the same commands.

Below you can see the stored procedure is the same except for the line "WITH ENCYPTION". That is basically all you need to do to encrypt the stored procedure.

ALTER PROC usp_FetchSalesByYear
@Year INT
WITH ENCRYPTION
AS
BEGIN
   SET NOCOUNT ON;
   SELECT 
      empId, 
      empFirstName, 
      empLastName, 
      SalesYear, 
      SalesAmount
   FROM dbo.EmployeeSalesChart 
   WHERE SalesYear [email protected]
END;
GO

Here is the same example if we are creating the stored procedure for the first time.

CREATE PROC usp_FetchSalesByYear
@Year INT
WITH ENCRYPTION
AS
BEGIN
   SET NOCOUNT ON;
   SELECT 
      empId, 
      empFirstName, 
      empLastName, 
      SalesYear, 
      SalesAmount
   FROM dbo.EmployeeSalesChart 
   WHERE SalesYear [email protected]
END;
GO

Verify a Stored Procedure is Encrypted

SSMS (SQL Server Management Studio) offers several methods of verifying that your stored procedure is encrypted. In the sections below, we will look at four different methods used to verify the encryption on a stored procedure.

Option 1:

First, we simply need to refresh our Stored Procedure folder in the Object Explorer to make sure we are seeing the current status. You can do this by right-clicking on the Stored Procedures column within the Programmability column and choose "Refresh" from the drop-down menu.

.refresh database objects

Next, right-click on the stored procedure itself and observe the drop-down menu. The "Modify" option is now grayed out.

modify object grayed out

Option 2:

Let's re-run one of the code samples from above to see what's going on with our stored procedure definition.

As we did earlier, we will return the "name", "definition", "type_desc", and "type" columns of our current stored procedures.

SELECT O.name, M.definition, O.type_desc, O.type 
FROM sys.sql_modules M 
INNER JOIN sys.objects O ON M.object_id=O.object_id 
WHERE O.type IN ('P');
GO

Notice that our definition value is NULL for the stored procedure that we encrypted, but not for the stored procedure that we did not encrypt.

when the text is encrypted shows a NULL value

Option 3:

Using another code sample from above, we can verify that our stored procedure is encrypted.

sp_HelpText usp_FetchSalesByYear;

Results:

the text is encrypted message

Option 4:

An additional benefit of encrypting a stored procedure is that no one can script the text to a new query window. I often do this when I want to see what a stored procedure is supposed to do or what parameters it may require for execution.

For example, let's script out our encrypted stored procedure or at least try to by following the steps below.

  • Expand your database.
  • Expand the "Programmability" folder.
  • Expand the "Stored Procedures" folder.
  • Right-click on the sample stored procedure "dbo.usp_FetchSalesByYear".
  • From the menu box that appears, select "Script Stored Procedure as" > "CREATE To" > "New Query Editor Window"
  • SSMS will ask you to select the database you want to use, select your test database and click "Connect".

At this point, you will receive an error message, like the one below, that informs you that the stored procedure is indeed encrypted.

the text is encrypted message

Capturing code of Encrypted Stored Procedure with Profiler or Extended Events

All the code that makes up the stored procedure is not completely hidden from the database administrator. You can view the results in SQL Server Profiler. If you're not sure how to start the profiler, just click on "Tools" in SQL Server Management Studio and select "SQL Server Profiler" from the drop-down menu.

sql server profiler

You will see a "Connect to Server" dialogue box appear. Select your desired SQL Server instance and click "Connect". The next dialogue box allows you to change some of the general and events settings. For now, just use the defaults and click "Run".

Within SQL Server Profiler, you can see the actual stored procedure code when the object is created or altered. So, if someone happens to be running a trace or Extended Events (works the same way) when objects are created or altered, they would be able to capture the actual code of the stored procedure.

In the images below, we have a screenshot of when the stored procedure was created

trace output

and when it was altered to include encryption.

trace output

After the stored procedure has been created and when it is executed, if you capture the statements in Profiler or Extended Events you will see the following:

trace output

Additional Objects that can be Encrypted in SQL Server

In addition to stored procedures, other objects can be encrypted in SQL Server. These include triggers, views, functions, and tables.

Decrypting and Source Code

You are probably wondering, how do I decrypt these objects? It is not an easy process. There are several articles that have been written about this as well as third-party tools you can find to decrypt objects. So it is not a 100% secure method, but it takes effort to decrypt objects as well as having the right permissions.

So with that being said, you need to keep the source code in a safe place. This is a good reason to make sure you are using a source control system for your database code. So whenever you need to make changes to the code, you should pull the latest version from source control, make the changes and then ALTER the database object.

Wrapping it up

SQL Server encryption is a powerful security measure that can help protect your data from unauthorized access. Encrypting stored procedures in SQL Server is a simple process and there are a variety of options available.

By following best practices and understanding how encryption works, you can ensure that your data is kept safe. So, take the time to learn how to easily encrypt your SQL Server stored procedures and secure your database before it's too late.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Aubrey Love Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips


Article Last Updated: 2023-01-18

Comments For This Article

















get free sql tips
agree to terms