Even after we've locked down an application, by tightly securing permissions and doing all data access and modifications through stored procedures, it's still advisable to disguise any business rules used in our stored procedures. The best way to do this is by encrypting our stored procedures.
We can encrypt our stored procedures by using the WITH ENCRYPTION clause, immediately before the AS clause of the procedure creation. Doing so will cause the procedure definition to be passed through the CRYPTO API on both read and write.
Why are we
While talking about stored procedure encryption, we need to know how Microsoft stores the descriptions for stored procedure objects. While the
CREATE TABLE [dbo].[syscomments] ( [id] [INT] NOT NULL , [number] [SMALLINT] NOT NULL , [colid] [SMALLINT] NOT NULL , [status] [SMALLINT] NOT NULL , [ctext] [VARBINARY] (8000) NOT NULL , [texttype] AS (CONVERT(SMALLINT, (2 + 4 * ([status] & 1)))) , [language] AS (CONVERT(SMALLINT ,0)) , [encrypted] AS (CONVERT(BIT,([status] & 1))) , [compressed] AS (CONVERT(BIT,([status] & 2))) , [text] AS (CONVERT(NVARCHAR(4000), case IF ([status] & 2 = 2) THEN (uncompress([ctext])) ELSE [ctext] END)) ) ON [PRIMARY]
The primary key for this table is the combination of the ID (representing the object ID) and number columns. Essentially, every stored procedure will have at least one record in this table with the number 1. If the stored procedure is longer than either 4000 or 8000
The key storage area is the 8000 byte VARBINARYCTEXT column. You can see that the human -readable TEXT column is actually a calculated column, based on the CTEXT column. During the CREATEPROCESS function, Microsoft does a quick scan of the data that you want to store in CTEXT . If all the data is in the ASCII 256-character code set, Microsoft will flip the status column 2 1 bit and stored the data as ASCII. Microsoft calls this as "compressed".
If it has characters outside the 256-character code set, Microsoft will leave the status column 2
When Microsoft encrypts the stored procedure, each single character in the definition is represented by one of the 65536 Unicode 2-byte characters; obviously it is uncompressed in the
column. At the same time, Microsoft
In expanding symmetric key the first Unicode character is encrypted with the seed key. Then the second character's seed key depends upon the seed key and the value of the first character. This an improvement over SQL 7.0, which was easily vulnerable to a brute force attack. However, even in SQL Server 2000, the encryption can be easily broken under certain conditions.
The condition for breaking the encryption is that you should be able to modify the description for the stored procedure definition. In other words, you have to use the ALTER PROCEDURE statement on the existing stored procedure and replace it with something totally different. However, in doing this, there is a risk that you might end up losing the original definition, thereby messing up the stored procedure and your application.
Never try to decrypt a stored procedure on a production implementation. Rather, copy the implementation to a development environment and try there.
A demo of the process to XOR the encrypted definition with a known definition is readily available after a search on the Internet. The demos that I've seen don't break past the single
record of 4000 characters. However, using the theory behind this demo, we've been able to write a
As we discussed, when a
One of the most important data that should generally be stored in an encrypted form is the information pertaining to credit cards. We can think of nothing more
We will assume that our regular database is called as SalesOrder , which will hold the encrypted credit card number. Next to it is a very small database, called SalesOrderEncryption .
The SalesOrder and SalesOrderEncryption tables have the same database owner login ID as the master database. Remember, this is a production implementation and there shouldn't be much else on this server, such as the pubs or Northwind database. The SalesOrderEnryption database only has one or more stored procedures; no user tables. These stored procedures use the OLE Automation extended stored procedures to access encryption DLLs on the operating system.
Now, here's the trick. The seed value for the encryption algorithm is encrypted in the stored procedure in
. This seed value is analogous to the PIN for an ATM card – it is not stored
Only users who have a right to access or change the credit card
The back up solution, though, is particularly elegant. Since the SalesOrderEnryption is so small and unchanging, all we do is backup the database to disk, write the backup to two CDs, give one to the client/owner of the application for safekeeping, and one to a custodian in our company for offsite storage. After deleting this one-time backup, we never backup the SalesOrder and SalesOrderEnryption database to the same media. Thus, if the media is compromised, it's like your ATM card being stolen. They don't have the PIN, or in our case, the symmetric key encryption seed. In the event of a catastrophic failure of the server, we can rebuild the SalesOrder database from backup tapes and the SalesOrderEnryption from one of the CD-burned backup copies.
database to a development server is also elegant. We know that development environments are notably less secure than production environments. However, since the production
database is not
We also recommend that in your environment you also audit all object creation and deletions. Thus, if someone were to try and alter an encryption procedure in the SalesOrderEncryption database, this can be recorded and pursued with vengeance.
Repeatedly, we come across instances where experts will ask us to drop one or the other system stored procedure or system extended stored procedure. This usually happens with an extended stored procedure and is in response to a buffer overflow. A buffer overflow allows a malicious user to execute "code of their choice" on the operating system. This "code of their choice" is usually a privilege elevation, where they give
Generally, the culprit function involved is a non-removable in-built function, such as the PWDENCRYPT function or a DBCC feature. Rather than trying to remove them, you should apply fixes. Microsoft is extremely quick to fix these types of buffer overflow problems when they appear.
C2 Administrator's and User's Security Guide
, Microsoft does recommend removing stored procedure functionality. It is an
The C2 Guide recommends that two sets of stored procedures be removed. The first set is the Meta Data Services stored procedures found in the msdb database. They can be readily identified by the r_i prefix. The other set of stored procedures that you might consider removing are the Merge Replication Stored Procedures. These are any stored procedure with merge in the name, and the large percentage of the undocumented stored procedures that begin with SP_MS . There are ten pages of these stored procedures documented in the C2 Guide , so we will not repeat them here.
Remember that you only need to remove these procedures if you need to meet the C2 requirements.
When SQL Server 2000 first shipped out, it didn't support SSL encryption. We need to have Service Pack 1 installed, to use it. The notes about its use are sparse at best, in SQL Server Books Online. Instead, Microsoft buried this functionality and the SetCert tool necessary for its setup in the SQL Server 2000 Resource Kit.
To set up SSL encryption, you will need to buy the SQL Server 2000 Resource Kit. Alternatively, it is available in a TechNet subscription (which often also has to be bought). Before you get a certificate, you need to know two things:
Every client machine that is going to connect to the server must recognize the certificate authority (CA) that issues the certificate to the server. This is usually a default configuration for such mainline CAs as Verisign or Thawte. If you run your own CA, you will need to make sure that
The name field on the certificate needs to be the fully qualified DNS name of the SQL Server, for example, server.division.corp.company.com. If you change the server name later on, you'll need to get a new certificate issued.
Installing the certificate and setting up SSL encryption for a SSL server involves two steps:
Importing the certificate into the server certificate store
To import the certificate, we need to perform the following steps:
Obtain a certificate from a CA. It is usually a single file. Place it in a secure location on the server.
Start Microsoft Management Console (MMC) by typing MMC.EXE at the command prompt.
Using the Console Add-Remove Snap-in … choice, add the Certificate snap-in for the Local Computer.
Expand the options under Certificate (Local Computer). Right-click on Personal. Click the Import choice.
Follow the Import wizard to select the file that we saved in step 1 and import it into the server.
To designate the imported certificate for use by SQL Server, follow these steps:
Ensure that Service Pack 1 for SQL 2000 is installed
Start the SetCert utility from the SQL Server 2000 Resource Kit
From the Instance Grid , select the instance of SQL Server you want to modify and click Set
From the Select Certificate dialog box, select the certificate you wish to designate for use by the SQL Server instance
Click OK and then click Close
This will enable SSL support for your SQL 2000 server.