How to encrypt all existing database stored procedures

Question

Is it possible to encrypt all existing SQL Server 2008 database stored procedures AFTER they were created using the SQLCMD script?

The reason I want to do this is as follows:
I would like to develop stored procedures without encryption, so I can easily click Modify in SQL Server Management Studio to check their contents.
However, for deployment, I would like to encrypt them, so I thought that maybe I could write a script that encrypts them only after they are created. For dev systems, I simply did not run the script, and the script was executed on end-user systems.

Answer 1

I have the same problem.

My solution is to put "- WITH ENCRYPTION" in all my stored procedures. This version is used by developers and is stored in the source control.

Then I use a tool (like sed) in my assembly to replace "- WITH ENCRYPTION" with "WITH ENCRYPTION" in the files before I submit them for installation.

For a clean SQL solution, you can use REPLACE.

Answer 2

You might want to check the Encryption of all database stored procedures :

If you ever decide that you need to protect your SQL Store Procedures and thought encryption were a good idea, VERY CAREFUL !!! Encryption of stored database procedures SHOULD NOT be performed without backup files or any source control for stored procedures. The reason I say this is because once they are encrypted, there is no turning. (Yes, there are third-party tools that your code will decrypt, but why solve it.)

This trick is what I developed because my company needed to host the application on a different server, and we were worried that our code was compromised. So, in order to deliver the database, we decided to encrypt all stored procedures. With more than a hundred procedures written, I did not want to open each procedure and insert "With ENCRYPTION" in each stored procedure. (For you who do not know how to encrypt, see How to Protect a Stored Procedure Code [^]). So I decided to make my own C # application the same.

This application is a console application using Visual Studio 2005 and SQL Server 2005. Input parameters: database name, server address, database username and password. once you can provide this data, you are ready to have all your stored procedures encrypted.

I put my application code here as it is. For this code to work, you will need to add the “Microsft.SQlserver.SMO” link to the application, so classes such as “Database” and “Stored Procedure” are available.

BEFORE YOU DO THIS, TAKE A BACKUP!!!!!!! 

 //Connect to the local, default instance of SQL Server. string DB = ""; ServerConnection objServerCOnnection = new ServerConnection(); objServerCOnnection.LoginSecure = false; Console.WriteLine("Enter name or IP Address of the Database Server."); objServerCOnnection.ServerInstance = Console.ReadLine(); Console.WriteLine("Enter name of the Database"); DB = Console.ReadLine(); Console.WriteLine("Enter user id"); objServerCOnnection.Login = Console.ReadLine(); Console.WriteLine("Enter Password"); objServerCOnnection.Password = Console.ReadLine(); Console.WriteLine(" "); Server srv = new Server(); try // Check to see if server connection details are ok. { srv = new Server(objServerCOnnection); if (srv == null) { Console.WriteLine("Server details entered are wrong," + " Please restart the application"); Console.ReadLine(); System.Environment.Exit(System.Environment.ExitCode); } } catch { Console.WriteLine("Server details entered are wrong," + " Please restart the application"); Console.ReadLine(); System.Environment.Exit(System.Environment.ExitCode); } Database db = new Database(); try // Check to see if database exists. { db = srv.Databases[DB]; if (db == null) { Console.WriteLine("Database does not exist on the current server," + " Please restart the application"); Console.ReadLine(); System.Environment.Exit(System.Environment.ExitCode); } } catch { Console.WriteLine("Database does not exist on the current server," + " Please restart the application"); Console.ReadLine(); System.Environment.Exit(System.Environment.ExitCode); } string allSP = ""; for (int i = 0; i < db.StoredProcedures.Count; i++) { //Define a StoredProcedure object variable by supplying the parent database //and name arguments in the constructor. StoredProcedure sp; sp = new StoredProcedure(); sp = db.StoredProcedures[i]; if (!sp.IsSystemObject)// Exclude System stored procedures { if (!sp.IsEncrypted) // Exclude already encrypted stored procedures { string text = "";// = sp.TextBody; sp.TextMode = false; sp.IsEncrypted = true; sp.TextMode = true; sp.Alter(); Console.WriteLine(sp.Name); // display name of the encrypted SP. sp = null; text = null; } } } 

Answer 3

WITH ENCRYPTION means that the code behind proc is not stored in the SysComments table.

You can write a script that exec sp_helptext 'MyProcName' , and get the contents in VarChar (MAX) so that it can easily execute multi-line / large procedures, and then modify the procedure from the original state

 CREATE MyProcName AS SELECT SecretColumns From TopSecretTable 

change CREATE to ALTER and AS surrounded by a space or tab or newline (a good place to use regular expressions) to WITH ENCRYPTION AS

 ALTER MyProcName WITH ENCRYPTION AS SELECT SecretColumns From TopSecretTable 

This will hide all the code for the stored proc on the production server.

You can put this in LOOP or CURSOR (not actually a set-based IMHO operation) for all objects of a particular type and / or naming convention that you want to encrypt, and run them every time you deploy.

Answer 4

I would recommend creating sproc in a multi-line string variable, and then insert or modify it using sp_executesql . The only annoying drawback of this approach is the doubling of single quotes for strings.

 DECLARE @action varchar(max); SET @action = 'CREATE'; /* or "ALTER" */ DECLARE @withEncryption varchar(max); SET @withEncryption = ''; /* or "WITH ENCRYPTION" */ DECLARE @sql varchar(max); SET @sql = @action + ' PROCEDURE dbo.Something' ( .... ) ' + @withEncryption + ' AS BEGIN DECLARE @bob varchar(10); SET @bob = ''Bob''; .... END; '; EXEC sp_executesql @statement = @sql; 

[Note the spaces around the variables.]

All my scripts use this method, which works well when you get used to doubling quotes.

I also use a batch file to call script and SQLCMD command line variables to select different types of behavior, which makes it repeatable and easy to test.

Answer 5

Use this query, which encrypts all procedures in the database.

  CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), sptext NVARCHAR(MAX) NOT NULL, spname NVARCHAR(100) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ) ) DECLARE @sptexttable TABLE ( id BIGINT IDENTITY(1, 1), sptext NVARCHAR(MAX), spname NVARCHAR(100) ) INSERT INTO @sptexttable ( sptext, spname ) SELECT [text], [name] FROM syscomments JOIN sysobjects ON syscomments.id = sysobjects.id AND sysobjects.xtype = 'p' DECLARE @sptext NVARCHAR(MAX) DECLARE @spname NVARCHAR(100) DECLARE @counter INT SET @counter = 1 WHILE @counter <= ( SELECT MAX(id) FROM @sptexttable ) BEGIN BEGIN TRY INSERT INTO #backup ( sptext, spname ) SELECT sptext, spname FROM @sptexttable WHERE id = @counter END TRY BEGIN CATCH END CATCH IF NOT EXISTS ( SELECT [name] FROM sysobjects WHERE [name] = 'ce_LastIndexOf' AND xtype = 'FN' ) BEGIN EXEC ( 'CREATE FUNCTION ce_LastIndexOf ( @strValue VARCHAR(4000), @strChar VARCHAR(50) ) RETURNS INT AS BEGIN DECLARE @index INT SET @index = 0 WHILE CHARINDEX(@strChar, @strValue) > 0 BEGIN SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1 THEN ( LEN(@strValue) - LEN(SUBSTRING(@strValue, CHARINDEX(@strChar, @strValue) + LEN(@strChar), LEN(@strValue))) ) ELSE 1 END SET @strValue = SUBSTRING(@strValue, CHARINDEX(@strChar, @strValue) + LEN(@strChar), LEN(@strValue)) END RETURN @index END' ) END DECLARE @tempproc NVARCHAR(MAX) DECLARE @procindex INT DECLARE @beginindex INT DECLARE @header NVARCHAR(MAX) DECLARE @asindex INT DECLARE @replacetext NVARCHAR(MAX) SET @tempproc = ( SELECT sptext FROM @sptexttable WHERE id = @counter ) IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc)) ) > 0 BEGIN BEGIN TRY SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc)) PRINT @procindex SELECT @beginindex = CHARINDEX('BEGIN', UPPER(@tempproc)) PRINT @beginindex SELECT @header = SUBSTRING(@tempproc, @procindex, @beginindex - @procindex) SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS') - 2 ) SELECT @replacetext = STUFF(@header, @asindex, 10, CHAR(13) + 'WITH ENCRYPTION' + CHAR(13) + 'AS' + CHAR(13)) SET @tempproc = REPLACE(@tempproc, @header, @replacetext) END TRY BEGIN CATCH END CATCH END UPDATE @sptexttable SET sptext = @tempproc WHERE id = @counter --PLAY HERE TO M AKE SURE ALL PROCS ARE ALTERED UPDATE @sptexttable SET sptext = ( SELECT REPLACE(sptext, 'CREATE PROC', 'ALTER PROC') FROM @sptexttable WHERE id = @counter ) WHERE id = @counter SELECT @sptext = sptext, @spname = spname FROM @sptexttable WHERE id = @counter BEGIN TRY EXEC ( @sptext ) UPDATE #backup SET encrypttext = @sptext, encryptstatus = 1 WHERE id = @counter END TRY BEGIN CATCH PRINT 'the stored procedure ' + @spname + ' cannot be encrypted automatically' END CATCH SET @counter = @counter + 1 END SELECT * FROM #backup 

Answer 6

I wrote a cursor, executed and encrypted most of the objects.

  DECLARE cur_ENCRYPT_ANTHING CURSOR READ_ONLY FOR SELECT STUFF(src.definition, CASE WHEN CHARINDEX('AS' + CHAR(13),src.definition,1) = 0 THEN CASE WHEN CHARINDEX('AS ' + CHAR(13),src.definition,1) = 0 THEN CHARINDEX('AS ',src.definition,1) ELSE CHARINDEX('AS ' + CHAR(13),src.definition,1) END ELSE CHARINDEX('AS' + CHAR(13),src.definition,1) END,3,'WITH ENCRYPTION AS' + CHAR(13)) FROM (SELECT o.name , STUFF(RIGHT(sm.definition,LEN(sm.definition) - CHARINDEX('CREATE ',sm.definition,1) + 1),1,6,'ALTER') AS definition FROM sys.sql_modules AS sm JOIN sys.objects AS o ON sm.object_id = o.object_id WHERE CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS BIT) = 0 AND type <> 'TR' ) AS src DECLARE @VLS NVARCHAR(MAX) OPEN cur_ENCRYPT_ANTHING FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN BEGIN TRY EXEC (@VLS) END TRY BEGIN CATCH PRINT ERROR_MESSAGE() PRINT '' PRINT @VLS END CATCH END FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS END CLOSE cur_ENCRYPT_ANTHING DEALLOCATE cur_ENCRYPT_ANTHING