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.
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.
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:
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:
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:
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 =@Year
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 =@Year
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.
Next, right-click on the stored procedure itself and observe the drop-down menu. The “Modify” option is now 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.
Option 3:
Using another code sample from above, we can verify that our stored procedure is encrypted.
sp_HelpText usp_FetchSalesByYear;
Results:
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.
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.
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
and when it was altered to include encryption.
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:
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.
- Triggers are database objects that are used to execute a set of instructions when certain conditions are met. You can read more about SQL Server triggers here.
- Views are virtual tables that allow users to view data from multiple tables. A great starting point article on SQL Server Views can be found here.
- Functions are database objects that are used to perform calculations on data. An article on the basics of SQL Server Functions can be found here.
- Tables can be encrypted to ensure that only authorized users can access the data. Learn more about SQL Server Tables here.
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
