locked
Encrypting Stored Procedures -- Does anyone really do it? RRS feed

  • Question

  • I wonder if anyone here has actual experience with encrypting all of an application's stored procedures. Although it is a bit of a hassle, it seems a good way to protect intellectual property. We obfuscate our C# code and so obfuscation of our SQL code seems appropriate as well. I'd certainly hate to see our DB fall into the hands of our competitors. Summarizing what I've gleaned from the web:

    Advantages of encryption are:

    1. It makes very difficult for a casual DBA to view our intellectual property. A determined effort to by a DBA violate our license is required.
    Disadvantages of encryption are:
    1. It can be broken by skilled database administrators using
      1. Third party tools
      2. Free scripts available on the Web
      3. SQL Profiler to observe the running procedure.
    2. It does have some impact on performance.
    3. It makes release builds more cumbersome to create.
    4. It precludes scripting the DB for installation since the clear text of the sprocs would be in the installation script. So instead of scripting, we would have to restore a backup of a DB with its sprocs already encrypted to the customer’s server.

    Our installation developer is worried about difficulties installing (and perhaps rolling back) patches and new releases. I was going to argue for encryption anyway, but I am reconsidering. If our competitors ever got a copy, they are precisely the ones who would pay for a decryption tool.

    Anyway, I'd love to hear about anyone's experience with sproc encryption. Thanks in advance.

    Tuesday, December 18, 2012 9:32 PM

Answers

  • The only purpose with obfustication is to tell the honest guy "no trespassing". But only barely. Apex SQLEdit will gladly show the content of an obfuscated procedure. I think it asks a confirmation question, but you may not notice.

    To protect your intellectual property, you need good lawyers and a good license agreement. If that is not sufficient for you, consider a hosted solution.

    A small correction: Profiler will not show statements from obfuscated procedures, nor can you see query plans.

    The installation thing is not so much of a problem, if you still everything into an MSI.

    However, what can be a hassle is the customer gets performance problems and you need a query plan or a trace from the site.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 18, 2012 10:45 PM

All replies

  • The only purpose with obfustication is to tell the honest guy "no trespassing". But only barely. Apex SQLEdit will gladly show the content of an obfuscated procedure. I think it asks a confirmation question, but you may not notice.

    To protect your intellectual property, you need good lawyers and a good license agreement. If that is not sufficient for you, consider a hosted solution.

    A small correction: Profiler will not show statements from obfuscated procedures, nor can you see query plans.

    The installation thing is not so much of a problem, if you still everything into an MSI.

    However, what can be a hassle is the customer gets performance problems and you need a query plan or a trace from the site.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 18, 2012 10:45 PM
  • Erland,

    Thank you for your response. I was coming around to your view but you brought me about sharply.

    Thanks also for all your outstanding SQL assistance here and elsewhere. You are the best!

    Jeff Roughgarden

    For the record, I'd like to add that one of our concerns was that some aspects of our licensing policy were being enforced with a stored procedure and others depend on the existence of a uniqueness constraint. I am able to get around the risk of these being hacked, despite being clear text, by moving the enforcement check to dynamic SQL inside our obfuscated C# code and also checking that the constraint exists and is not disabled using dynamic SQL inside our C#. This is one of the very few times I've found dynamic SQL (e.g.,

                       var cnnX = new SqlConnection(_strCnn);
                        if (cnnX.State == ConnectionState.Closed) cnnX.Open();
                        var strSQL = "select count(*) from ....'";
                        var cmdY = new SqlCommand(strSQL, cnnX) { CommandType = CommandType.Text};
                        var sntCount = short.Parse(cmdY.ExecuteScalar().ToString());

    to be worthwhile.

    Wednesday, December 19, 2012 2:17 AM
  • I don't know how you do this license enforcement, but I like to draw your attention to procedure signing. As you may know you can sign a procedure with a certificate, and one way to do the signing is with a signed blob. This permits you to ship a procedure and sign it on the installation with only the public key of the certificate being present in the database. That is, you can verify that the procedure is signed with the correct thumbprint. If someone tampers with the SP it is not possible to sign it with the certificate, since the private key is absent.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 19, 2012 10:28 PM