Install encrypted stored procedure?

Hello SQL-Server-Performance.com!<br /><br />This is my first posting in this forum, so I would like to briefly introduce myself:<br /><br />My name is Jörg Stryk, and I'm working as independent freelancer; precisely within the MS Dynamics NAV (formerly Navision) business (for details please check out my web-page: <a href='"http://www.stryk.info/"' target='_blank' title='"http://www.stryk.info/"'>www.stryk.info</a>).<br />When dealing with NAV, I also have to work with SQL Server, especially as there are some severe perfromance issues. Thankfully I got plenty of useful advice from sql-server-performance.com [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br />My intention is to dig deeper into the SQL Server world, especially in TSQL. And according to this, I would like to ask you for some support!<br /><br />Here my current issue:<br /><br />I want to provide a <b>stored procedure</b> to my "audience" (published as freeware in the web), but to protect my source code I have encrypted this procedure.<br />Now the question is: <i>What would be the easiest way to provide this procedure to others, so that they could easily install it?</i><br />As mentioned: I don't want to roll-out my original - unencrypted - TSQL to generate the procedure.<br />My actual approach is this:<br />I create a small installation database (to publish this db for download), which includes only one stored proceduce called <b>ssi_install</b> (encrypted). This procedure should include the code to create the "real" procedure in the target database, executing like <b>exec ssi_install 'TargetDB'</b>.<br />The problem is, that from my install-db I can <u>not</u> create a procedure on a different database. Any ideas?<br /><br />Thanks a lot in advance!<br />Best regards,<br />Jörg<br /><br /><b>Jörg A. Stryk</b><br /><font size="1">Freelance NAV System Consultant (MCP, MBSCP)</font id="size1"><br /><b>STRYK System Improvement</b><br />www.stryk.info

you could use dynamic sql [exec (use newdb ddl for your proc)] to install in another db, but that would be interceptable by anyone knowing profiler.

Stryk,

I face a similar situation as yours. I have my own installer and have similar requirements as you do. I'm looking to either incorporate my batchfile 'installer' into either a Wise, MSBuild or InstallShield application to protect myself for code distribution.

If you do go the encrypted sp route, be aware that the algorithim for encrypting stored procedures is widely known and you cannot solely rely on encryption to protect your code. If they want a look at it, they can. Your best bet is to create a very specific and straightforward license to protect yourself from reverse engineering.

The CLR option is for 2005 only. And while it is an option, it may not be the best option. The CLR isn't a blanket replacement for T-SQL and stored procedures and in some cases isn't the place for logic. You could be doing a performance disservice by creating CLR procedures only because you want to protect your code instead of using proper procedures. I'd be cautious tossing the "Use CLR procedures" as a blanket answer...(IMO).

Hi all!<br /><br />Thanks a lot for your replies. Hmm, seems to be tricky business [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br />Actually I tried "dynamic SQL", but the "USE" command is not permitted within my installation procedure.<br />The CLR feauture is not an option because I'm dealing with 2000 in this case.<br /><br />@Haywood: Could you tell me more about your "batch file installer"?<br /><br />Regarding "encryption", well, I know that this is not failsafe. But those who could "decrypt" the code, those would also be able to generate the same functionality of my procedure themselves, too [}<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Kind regards,<br />Jörg<br /><br /><br /><b>Jörg A. Stryk</b><br /><font size="1">Freelance NAV System Consultant (MCP, MBSCP)</font id="size1"><br /><b>STRYK System Improvement</b><br />www.stryk.info

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by stryk</i><br />Thanks a lot for your replies. Hmm, seems to be tricky business [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br />Actually I tried "dynamic SQL", but the "USE" command is not permitted within my installation procedure.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /> Actually, you can USE in DynamicSQL. Search for a post by me last week or so, I wrote a Dynamic USE for someone else here, it's posted somewhere.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />@Haywood: Could you tell me more about your "batch file installer"?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Sure, it's basicly a batch file that accepts a few parameters (login, pass, server...) and pipes those to a series of sql scripts through OSQL and a couple of 'helper' vbscript files.<br /><br />My reasons for wanting to protect the installer are because the majority of my hard work is in the installer and not neccessarily in the resulting t-sql code after installation. <br /><br />The t-sql code generated is valuable, but due to the encryption issues I mentioned before, I _know_ that my t-sql code can and will be reverse engineered (as you pointed out as well). Hence the strict licensing remark and the desire to protect the installation code is a higher priority.<br /><br />

Hello Gordon (Haywood)!<br /><br />Thanks for your advice! Well, I browsed your postings - and there were a lot [<img src='/community/emoticons/emotion-5.gif' alt='' />] - but I couldn't find this "dynamic USE" thing ...<br />Maybe "I didn't see the forest because of the trees" (German proverb)?!<br /><br />Could you kindly give me another hint? Thanks a bunch in advance!<br /><br />Best regards,<br />Jörg<br /><br /><b>Jörg A. Stryk</b><br /><font size="1">Freelance NAV System Consultant (MCP, MBSCP)</font id="size1"><br /><b>STRYK System Improvement</b><br />www.stryk.info

See the first reply to:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19278

Now I tried this, but it still doesn't work [V]


use [SSI] -- this is the db from where the installation should start
go

declare @stmt nvarchar(1000),
@db varchar(30) -- usually, the name of the target db is set via parameter
set @db = 'Navision'

set @stmt = '
USE [' + @db + ']
CREATE PROCEDURE [Test] AS SELECT db_name()'

exec (@stmt) -- error is raised:

/*
Msg 111, Level 15, State 1, Procedure Test, Line 3
'CREATE/ALTER PROCEDURE' muss die erste Anweisung in einem Abfragebatch sein.
*/

Jörg A. Stryk
Freelance NAV System Consultant (MCP, MBSCP)
STRYK System Improvement
www.stryk.info

Strictly speaking, USE is not part of SQL, so you have to separate it from the rest of your script with GO:

set @stmt = ' USE [' + @db + ']
GO
CREATE PROCEDURE dbo.[Test] AS SELECT db_name()'

I would also strongly suggest that you add the dbo prefix to the procedure name to avoid the object being created with another owner.

Hi again!

Thanks a lot for your advice. But I'm afraid, I still can not get it going [xx(]


use [SSI] -- this is the db from where the installation should start
go
declare @stmt nvarchar(1000),
@db varchar(30) -- usually, the name of the target db is set via parameter
set @db = 'Navision'

set @stmt = '
USE [' + @db + ']
GO
CREATE PROCEDURE dbo.[Test] AS SELECT db_name()'

exec (@stmt) -- error is raised:

/*
Msg 102, Level 15, State 1, Line 3
Falsche Syntax in der Nähe von 'GO'.
Msg 111, Level 15, State 1, Line 4
'CREATE/ALTER PROCEDURE' muss die erste Anweisung in einem Abfragebatch sein.
*/

I also tried other "variants", but without success ...

Jörg A. Stryk
Freelance NAV System Consultant (MCP, MBSCP)
STRYK System Improvement
www.stryk.info

Should have tested before I posted ... Here's the real workaround: skip GO altogether, and use EXEC on a string within the command string.

set @stmt =
'USE [' + @db + '] EXEC (''CREATE PROCEDURE dbo.[Test] AS SELECT db_name()'')'

... and of course you still have to execute the whole string ...

Ahhhh! Yeah, the EXEC did the trick - works perfect! [<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br />Thank you very much!<br /><br />Best regards, [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br />Jörg<br /><br /><b>Jörg A. Stryk</b><br /><font size="1">Freelance NAV System Consultant (MCP, MBSCP)</font id="size1"><br /><b>STRYK System Improvement</b><br />www.stryk.info

You see how much fun can be had with T-SQL.[<img src='/community/emoticons/emotion-4.gif' alt='' />]

I think you can also use xp_execresultset procedure...

Here is the example... I use in one of my code to enable log restore jobs...

BEGIN
-- Enable all Restore log jobs ....
select 'exec sp_update_job @job_id = '''+Convert(Varchar(255),job_id) +''', @enabled = 1 ' AS sql INTO ##temp
from msdb.dbo.sysjobs (NOLOCK) where name like '%Restore - Log%'
EXEC master..xp_execresultset N'SELECT sql FROM ##temp, N'MSDB'
END


MohammedU.
Moderator
SQL-Server-Performance.com

Hi Jorg,

Good to see you posting on this forum also.

What we use at SQL Perform is a tool called SQL Shield to protect our NAV (navision) performance toolkit.

It works like a charm.

Good luck on your company, you can always contact me for any help.

Mark Brummel
MVP - Microsoft Dynamics NAV
SQL Perform consultant
www.brummelds.com

Hi Mark!

Nice to meet you here, too! [8D]
Could you give me some more info about this "SQL Shield"?

Best regards,
Jörg

--- Edit ---
Not necessary anymore, found it in the WWWeb!

Jörg A. Stryk
Freelance NAV System Consultant (MCP, MBSCP)
STRYK System Improvement
www.stryk.info