locked
How to hide not all but a specific database on a SQL Server 2008 R2 instance? RRS feed

  • Question

  • Hello everyone,

          I need help from all the SQL Server database security experts out there. Any solution/help or work-around will be really appreciated. Here is the scenario; our client is using our application which is a windows forms application having in backend as a database under SQL Server 2008 R2 instance. We have successfully installed the SQL Server software with mixed mode authentication on client side, our application is running smoothly and everything is working fine. Our database on which application is running is quite intuitive, in the sense, all employees information is stored in EmployeeMaster and all departments information is stored in DepartmentMaster and so on. It consists of more than 500 such tables, stored-procedures and views. We have encrypted stored-procedures and views, problem is only with tables. We don't want table structure to be visible to anybody in SSMS (or tables must be hidden from all the users) but at the same time the tables should be accessible from the application. And we don't want to go with the option of encrypting the tables data and changing the names of all the tables to T001,T002..etc. as that is not feasible. And one last thing, we cannot restrict 'sa' login's password, it has to be shared with the client.

    Regards,

    Sumit R. Santani

    Thursday, July 24, 2014 4:56 AM

Answers

  • It is not fully clear to me whether you want to lock out plain users from the database or if you want to lock out also the DBAs.

    If you want to lock out plain users only, there are a couple of choices (of which the better ones will require you re-architect the application).

    If you want to lock out the client entirely, the answer is: don't install the database at the client at all, but host the database yourself or at a hosting provider (a.k.a. "the cloud"). If you install it locally, the local admin staff will have access to the tables. (Which they undoubtedly will take benefit of when they need to help the users with imperfections in your database.)

    The way to control what the client can do and not do is through a license agreement.

    By the way, there is no such thing as encryption of stored procedures and views. The proper term is obfustication, and anyone who wants to retrieve the code can do so. (But a license agreement can disallow such actions.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Friday, July 25, 2014 8:49 AM
    • Marked as answer by Sofiya Li Thursday, July 31, 2014 6:51 AM
    Thursday, July 24, 2014 8:15 AM
  • Also, will you please enlighten me more on what you said as "couple of choices" if I want to lock out plain users only?

    Assuming that you are selling a solution to a client, it's more an issue for your client's IT department, that do want their users to connect with Excel or SSMS.

    Anyway, I have a brief overview here:
    http://www.sommarskog.se/grantperm.html#Othermethods

    Again my question on that is how much reliable will be that license agreement?

    That depends on the lawyer you engage to write the license agreement.

    How would I come to know what they are doing with our database?

    Trust. Trust is extremely important. It is very difficult to make business if you don't trust people.

    That said, there are basically two reasons you want to hide the application from the client. One is protecting intellectual property. To this end, I know of nothing else than trust. And maybe after all, your application is not so fantastic that it is worth stealing anyway.

    The other reason is that you don't want the client to change it, and then open a free support case when he has messed up. To this end there are some technical approaches you can take to detect such tampering. For instance, you can sign all stored procedures with certificates using ADD SIGNATURE WITH SIGNED BLOB, and then ship only the public key of the certificate. This permits SQL Server to verify the signature, but the client cannot change the signature and sign it with that certificate, because the private key is not available to them.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Friday, July 25, 2014 8:48 AM
    • Marked as answer by Sofiya Li Thursday, July 31, 2014 6:51 AM
    Thursday, July 24, 2014 10:10 PM

All replies

  • It is not fully clear to me whether you want to lock out plain users from the database or if you want to lock out also the DBAs.

    If you want to lock out plain users only, there are a couple of choices (of which the better ones will require you re-architect the application).

    If you want to lock out the client entirely, the answer is: don't install the database at the client at all, but host the database yourself or at a hosting provider (a.k.a. "the cloud"). If you install it locally, the local admin staff will have access to the tables. (Which they undoubtedly will take benefit of when they need to help the users with imperfections in your database.)

    The way to control what the client can do and not do is through a license agreement.

    By the way, there is no such thing as encryption of stored procedures and views. The proper term is obfustication, and anyone who wants to retrieve the code can do so. (But a license agreement can disallow such actions.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Friday, July 25, 2014 8:49 AM
    • Marked as answer by Sofiya Li Thursday, July 31, 2014 6:51 AM
    Thursday, July 24, 2014 8:15 AM
  • Thanks a lot for a quick response Mr. Erland. So, can I say that playing with logins, users and roles won't help me in this scenario? And I got your point regarding obfustication of stored procedures and views.

    Also, will you please enlighten me more on what you said as "couple of choices" if I want to lock out plain users only? Provide me links (if any). And I believe the license agreement which you said will be between my company and their clients. Again my question on that is how much reliable will be that license agreement? How would I come to know what they are doing with our database?

    Thanks again!

    Regards,

    Sumit R. Santani

    Thursday, July 24, 2014 11:24 AM
  • Also, will you please enlighten me more on what you said as "couple of choices" if I want to lock out plain users only?

    Assuming that you are selling a solution to a client, it's more an issue for your client's IT department, that do want their users to connect with Excel or SSMS.

    Anyway, I have a brief overview here:
    http://www.sommarskog.se/grantperm.html#Othermethods

    Again my question on that is how much reliable will be that license agreement?

    That depends on the lawyer you engage to write the license agreement.

    How would I come to know what they are doing with our database?

    Trust. Trust is extremely important. It is very difficult to make business if you don't trust people.

    That said, there are basically two reasons you want to hide the application from the client. One is protecting intellectual property. To this end, I know of nothing else than trust. And maybe after all, your application is not so fantastic that it is worth stealing anyway.

    The other reason is that you don't want the client to change it, and then open a free support case when he has messed up. To this end there are some technical approaches you can take to detect such tampering. For instance, you can sign all stored procedures with certificates using ADD SIGNATURE WITH SIGNED BLOB, and then ship only the public key of the certificate. This permits SQL Server to verify the signature, but the client cannot change the signature and sign it with that certificate, because the private key is not available to them.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Friday, July 25, 2014 8:48 AM
    • Marked as answer by Sofiya Li Thursday, July 31, 2014 6:51 AM
    Thursday, July 24, 2014 10:10 PM