Post reply

Stored Procedure's Security

  • August 25, 2011 at 1:12 pm

    #1373452

    Devil's Bloody Advocate (8/25/2011)

    My uderstanding is that the only decryptors of stored procedures was based on sql7 and before. When sprocs are encrypted within SQL 2005 & 2008 there is no way of decrypting them (happy to be proved wrong on this if someone can provide the link).

    RedGate's SQLPrompt can decrypt 'encrypted' procs. It's not encryption. It's obfuscation and some additional security, nothing more. Won't stop someone for more than 5 minutes, if that.

    http://www.red-gate.com/products/sql-development/sql-prompt/features

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 25, 2011 at 2:48 pm

    #1373508

    Ouch! Live and Learn

    Thanks for that Gail

  • August 25, 2011 at 4:20 pm

    #1373541

    Robert Cain (5/4/2010)

    Access? Good heavens I hope not.

    You might look at SQL Server Compact Edition, not sure your exact requirements but it's small, light weight, and if you give your database a password it will automatically encrypt the entire database.

    Some downsides: It's single user, the db cannot be stored on the lan but must be on a local drive, there's a 4gb limit, and no stored procs. However since it's all running on your desktop you can rewrite the stored procs as methods and not lose speed.

    Pluses: It can be embedded entirely within your app, doesn't require admin privlidges to install, or a seperate install of any kind. It's a DLL you simply reference and it gets bundled up with the rest of your app.

    It's not quite clear from your original post if you need to have multiple users on the network hitting your app, or if this will all reside on a single desktop. If it's a desktop app and the database is only used to store data, you might look at SSCE. I did quite a few posts on it, http://arcanecode.com/category/sql-server-compact-edition/ .

    Depending on your needs and how far along you are in the development lifecycle it may or may not be a good fit, but IMHO much better than Access.

    One other thought, you should probably balance possibilities. How likely is it that someone would actually take the time to try and decrypt the thing? It would take some effort to find the tool to decrypt "WITH ENCRYPTION", not something a casual user is going to want or be able to do. I'd only consider going beyond if you are in a highly competitive market and think a competitor might gain some big advantage over you by trying to reverse engineer your app. If so, you might then decide it's worth the investment to go farther.

    There are other comparatively severe limitations to CE, as well. No views, no UDF's, all character based columns must be NTEXT, NVARCHAR... there is no NVARCHAR(MAX). And, IIRC, you can't do dynamic SQL if you need it.

    IMHO, CE should be thought of as "just a place to store data". Of course, if that's what you're looking for and you intend to do most DB access using embedded code, it's perfect.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply