Stored Procedure encryption - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Stored Procedure encryption

Author

Topic

cbecerra
Starting Member

38 Posts

Posted - 2003-03-26 : 13:47:14

Hi guys:

I'm attemting to encrypt 1300 stored procedures and abviously I do not want to do it one by one. I have not been able to come up with a method to do this nor have I found any tool in the internet to help with this task. I thought perhaps someone in this group might have some ideas.

Thanks
.Cesar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-26 : 13:53:30

How about you script out all of the stored procedures into one file, then do a replace on:
AS

Change it to:

WITH ENCRYPTION
AS

Just use notepad or any other text editor to do it.

Tara

cbecerra
Starting Member

38 Posts

Posted - 2003-03-26 : 14:03:19

I actualy script all the stored procedures and thought of doing just what you sujested, but this still would require editing one by one. I'm trying to find a method that will just do them all at once.

.Cesar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-26 : 14:06:10

Why would you have to do them one by one? Just put them all into one file and then do the REPLACE ALL in the text editor. Of course, do a couple of them first using REPLACE, then use REPLACE ALL when you know that it is doing what you need it to.

The key thing is that ALL of the stored procedures go into ONE file and not into individual files.

Tara

cbecerra
Starting Member

38 Posts

Posted - 2003-03-26 : 14:34:51

Yes that will probably work. I'll give it a try...

Thanks

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-03-26 : 14:35:19

I use Visual Studio.Net to write all of my sql code. I can do a find and replace in multiple files with it. Each sproc, view, udf, etc is in its own file.

In my New SPROC template, i always have "--WITH ENCRYPTION" in it, so i can do a find and replace on that if needed.

VS.Net also has a utility that creates a command file for selected SQL files. The command file uses OSQL to open and execute the selected files, so it would be relatively easy to execute the create statements for all 1300 SPROCs.

-- monkey

Edited by - monkeybite on 03/26/2003 14:35:55

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-03-26 : 14:39:17

In case you're wondering, here is what the command file contains:


@echo off
REM: Command File Created by Microsoft Visual Database Tools
REM: Date Generated: 3/26/2003
REM: Authentication type: Windows NT
REM: Usage: CommandFilename [Server] [Database]

if '%1' == '' goto usage
if '%2' == '' goto usage

if '%1' == '/?' goto usage
if '%1' == '-?' goto usage
if '%1' == '?' goto usage
if '%1' == '/help' goto usage

osql -S %1 -d %2 -E -b -i "[dbo].[spr_AppAddVersion].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_CustListOne].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListAssy].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListOne].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListPurchHist].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListRouting].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListRoutingComp].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListShipment].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartListWhereUsed].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_PartSearch].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_POListOneDetail].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_POListOneHeader].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_POListOneLineItem].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_VendListOne].sql"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "[dbo].[spr_VendListPart].sql"
if %ERRORLEVEL% NEQ 0 goto errors

goto finish

REM: How to use screen
:usage
echo.
echo Usage: MyScript Server Database
echo Server: the name of the target SQL Server
echo Database: the name of the target database
echo.
echo Example: MyScript.cmd MainServer MainDatabase
echo.
echo.
goto done

REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done

REM: finished execution
:finish
echo.
echo Script execution is complete!
:done
@echo on

It'll do this for any number of .SQL files in my db project.

-- monkey

cbecerra
Starting Member

38 Posts

Posted - 2003-03-26 : 15:03:11

I'm not familiar with vs.net but this seems like an elegant solution I will try to implemented.

Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-26 : 17:40:38

Here is a stored proc that will go through and encrypt all the procs in your database.

Before you use it, you have to

Promise that you have a backup of all your scripts, preferably in source control and that you will not come crying here asking how to decrypt them when it all goes wrong



CREATE PROCEDURE encryptAll AS
DECLARE 	@current_proc varchar(255),
		@current_text varchar(8000),
		@cmd varchar(255)
SET NOCOUNT ON
DECLARE _Cursor CURSOR FOR
SELECT o.name,c.text
FROM sysobjects o 
INNER JOIN syscomments c ON c.id = o.id
WHERE o.type='p' and o.category=0 and o.name<>'encryptAll' 
and encrypted=0

OPEN _Cursor
FETCH NEXT FROM _Cursor
INTO @current_proc, @current_text
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT ''
PRINT '--Encrypting: ' + @current_proc
PRINT ''

SET @cmd = 'drop procedure ' + @current_proc
EXEC( @cmd)

SET @cmd =  replace(@current_text, 'CREATE PROCEDURE ' + @current_proc, 'CREATE PROCEDURE ' + @current_proc + ' WITH ENCRYPTION')
PRINT @cmd
EXEC( @cmd)
FETCH NEXT FROM _Cursor INTO @current_proc, @current_text
END
CLOSE _Cursor
DEALLOCATE _Cursor

PRINT ''
PRINT '--Encrpytion Process Complete'
PRINT ''
PRINT '--It is RECOMMENDED you save this output to re-create the procedures in the future'
GO

Damian

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-03-26 : 17:42:47

quote:
Here is a stored proc that will go through and encrypt all the procs in your database.

oh man, this is a dangerous script!

-- monkey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-26 : 17:43:37

Wow, that is too cool! I have never encrypted any stored procedures before just because I'm afraid that I would lose the script even if it is stored on a server that is backed up.

Tara

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-26 : 17:53:01

I started to do it after working on a team where one of the developers decided doing bugfixes through enterprise manager on the live server was a good idea. Next time we rolled a release out of source control all hell broke loose.

Also when deploying stuff on client's servers it gives you a little piece of mind that they won't break anything.

But everything I do goes into source control. I only make changes via scripts. Once you get into the habit and enforce that workstyle, it is safe. But we see a whole bunch of questions here about decrypting procedures after someone loses their source code.

Damian

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-03-27 : 15:52:39

Developers should not have the ability to do anything except read data on production servers. Encryption is fairly pointless, its really security by obscurity, anoying to DBA's who have to troubleshoot procedures and may not have (quick) access to developers source control systems (if they have any) and did I mention pointless

it's been broken in every release mainly because the server has to be able to decrypt it. Change control procedures, tight security and source control will do much more than encryption ever will.

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-27 : 15:55:42

quote:

Developers should not have the ability to do anything except read data on production servers.

I am in complete agreement with this. Anytime a developer needs more access here, we log them in and watch everything that they do or we log in and they tell us what to do.

Tara

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-03-27 : 16:00:11

quote:
Encryption is fairly pointless, its really security by obscurity

I tend to agree. However, there are cases where I'll use SPROC encryption in an attempt to protect our intellectual property on servers where we cannot control which users are members of [sysadmin].

-- monkey

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-03-27 : 16:24:01

I agree the best security is by controlled access not encryption. If they can’t get to it, it does not matter weather or not it’s encrypted.

SP scripts take up very little space so BACKUP-BACKUP-BACKUP.

Also if I have to decrypt something I have outsourced in order to troubleshoot a problem I would Kill him.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-03-27 : 18:49:09

quote:
I'll use SPROC encryption in an attempt to protect our intellectual property

Not that I like lawyers or anything but that's what they're for. As I mentioned it doesn't protect anything, in fact I'd say it makes people curious so they'll go to google, type in "decrypt stored procedure" and get lots of hits, pick one and see your code anyway....pointless

I have also seen it used to hide some really shockingly bad code

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

Edited by - jasper_smith on 03/27/2003 18:58:47

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-27 : 19:02:55

I would be VERY surprised to see anything written in TSQL that could be worthy of IP. It needs the schema to give it context. The schema would be worthy.. Just ask SAP!!!

DavidM

"SQL-3 is an abomination.."

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-03-27 : 19:06:33

SAP Schema - now there's an example of security by obscurity

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-27 : 21:55:22