locked
Secure SQL-server database installed on a clients computer RRS feed

  • Question

  • I am making a product that we install on a clients computer. It is a WPF-client. In the client i save data in tables in a local installed SQL-server database. This database is installed with the wpf-client on the clients computer. I do not have the ability to access the clients computers. We will install SQL-server express on the clients computer with the installation files.

    My question is how i can secure the installation?
    I dont want the client to access the database and make changes to data or passwords ...

    Tips on where I can learn more about this and suggestions on how I should do is appreciated !

    Oscar Andersson


    Andersson. Systemdeveloper .Net

    Thursday, October 3, 2013 2:02 PM

Answers

  • A member of the local Administrators group can always access an instance of SQL Server installed on that computer as a member of the sysadmin fixed server role. So the computer owner cannot be locked out of the database.

    What can you do?

    • Protect your intellectual property with your license. This is a legal protection, not physical.
    • Create an application that is complex enough to deserve the protection of your intellectual property. If your application is complex, people won't try to reproduce it.
    • You can create stored procedures with the encryption option. This is a weak protection, but it will stop the casual person from poking around.
    • Use various means such as application roles and users without logins to restrict the ability of non-admins to change data. Protect the integrity of data with constraints and triggers. Consider using login triggers to restrict the capabilities of non-admin logins.
    • You can encrypt data before you pass it to SQL Server by shipping a certificate with your application. This is probably a marginal idea. It's a lot of trouble and presumably the user knows (and needs to know) their own data anyway.
    • Or don't store important portions of your application on the client computer. Architect your application as a web service and keep your super app on a computer that you control.

    I'm sure there are other things that you can do to meet specific needs. What is your essential requirement?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, October 3, 2013 3:59 PM

All replies

  • A member of the local Administrators group can always access an instance of SQL Server installed on that computer as a member of the sysadmin fixed server role. So the computer owner cannot be locked out of the database.

    What can you do?

    • Protect your intellectual property with your license. This is a legal protection, not physical.
    • Create an application that is complex enough to deserve the protection of your intellectual property. If your application is complex, people won't try to reproduce it.
    • You can create stored procedures with the encryption option. This is a weak protection, but it will stop the casual person from poking around.
    • Use various means such as application roles and users without logins to restrict the ability of non-admins to change data. Protect the integrity of data with constraints and triggers. Consider using login triggers to restrict the capabilities of non-admin logins.
    • You can encrypt data before you pass it to SQL Server by shipping a certificate with your application. This is probably a marginal idea. It's a lot of trouble and presumably the user knows (and needs to know) their own data anyway.
    • Or don't store important portions of your application on the client computer. Architect your application as a web service and keep your super app on a computer that you control.

    I'm sure there are other things that you can do to meet specific needs. What is your essential requirement?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, October 3, 2013 3:59 PM
  • Keep important data ,logic whatever within a application and do not store in SQL Server... You are providing to the client compiled EXE file that will contains all sensible data.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, October 4, 2013 7:50 AM
  • I have to save data about the clients activities like time they use a program/time they are on a webpage. Results to questions (pass or fail) asked at regular intervals...

    Then i synchronize this local database with a external central database. It will not be that sensitive data in the local database. But i dont want user to change data or remove data Before we have synchronize the data with the central database.


    Andersson. Systemdeveloper .Net

    Friday, October 4, 2013 1:53 PM
  • A client can break an application in thousands of ways. Deleting critical files, messing with the registry, etc. Of course we presume they won't. After all, they installed the application because they want the application to work.

    Normally, you should try to provide low privileged access to customers and discourage them from using sysadmin credentials. They should want to run the application that way. Provide stored procedures for the routine actions in the database. That will make their activity more controlled and predictable.

    If there is some particular thing you don't want them to do, perhaps a trigger could warn them to stop. For example a trigger that fires on delete operations that affect more than one row. To continue they would have to disable the trigger. But that's just a wild suggestion because I don't know your situation.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, October 7, 2013 9:35 PM