SQL Server Performance

Install encrypted stored procedure?

Discussion in 'General Developer Questions' started by stryk, Feb 22, 2007.

  1. stryk New Member

    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=':D' />]<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
  2. alzdba Member

    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.
  3. satya Moderator

    Welcome to the forum![<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Using SQL 2005 you can handle such situation using CLR procedurs.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
  4. Haywood New Member

    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).
  5. stryk New Member

    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
  6. Haywood New Member

    <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 />
  7. stryk New Member

    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
  8. Haywood New Member

  9. stryk New Member

    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
  10. Adriaan New Member

    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.
  11. stryk New Member

    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
  12. Adriaan New Member

    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 ...
  13. stryk New Member

    Ahhhh! Yeah, the EXEC did the trick - works perfect! [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />Thank you very much!<br /><br />Best regards, [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<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
  14. Adriaan New Member

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

    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
  16. Mark Brummel New Member

    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
  17. stryk New Member

    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

Share This Page