locked
Create database for software distribution RRS feed

  • Question

  • Hi,

    Sorry, maybe this is a silly question.

    I am planning to distribute software that needs to create a database in the client in SQL Server Express 2012.

    So if the client does not have SQLEXP installed, the software will install it and create the database, but if the client already has SQLEXP, then I would need a sysacount just to create de database (which the software will ask for it)

    Now, the thing is, I don't want anybody, (including sa account) to access my database, see the structure and even alter the data

    is this is possible?

    if not, what could I do to achieve this?

    Thanks in advance


    G.Waters

    Wednesday, August 15, 2018 8:35 PM

Answers

  • Hi G.Waters,

     

    According to your description, my understanding is that you don't want anybody to access your database including the sa account.  If anything is misunderstood, please tell me.

     

    It is impossible to deny permission for the sysadmin account to access your database. If your account has the sysadmin privilege and it is enough for your instance to have the one sysadmin account, you can try to disabled the sa account and not grant other user the sysadmin privilege.

     

    For other users, if you don't map the user to your database, then the user can't access it. You can change the users' properties in SSMS via expanding Security-> expanding Logins-> right clicking the user-> clicking Properties.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by George Waters Thursday, August 16, 2018 7:46 PM
    Thursday, August 16, 2018 2:02 AM

All replies

  • Hi G.Waters,

     

    According to your description, my understanding is that you don't want anybody to access your database including the sa account.  If anything is misunderstood, please tell me.

     

    It is impossible to deny permission for the sysadmin account to access your database. If your account has the sysadmin privilege and it is enough for your instance to have the one sysadmin account, you can try to disabled the sa account and not grant other user the sysadmin privilege.

     

    For other users, if you don't map the user to your database, then the user can't access it. You can change the users' properties in SSMS via expanding Security-> expanding Logins-> right clicking the user-> clicking Properties.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by George Waters Thursday, August 16, 2018 7:46 PM
    Thursday, August 16, 2018 2:02 AM
  • As mentioned, that isn't possible. You should host the database for your client in such situations.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, August 16, 2018 6:46 AM
  • "If your account has the sysadmin privilege and it is enough for your instance to have the one sysadmin account, you can try to disabled the sa account and not grant other user the sysadmin privilege."

    However, is the client are Windows Administrators on the machine, they can just start SQL Server in single-user mode, then now they are sysadmin.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, August 16, 2018 6:48 AM
  • How about creating a new instance of SQL?

    Would that be possible?


    G.Waters

    Saturday, August 18, 2018 11:04 PM
  • >How about creating a new instance of SQL?

    Any Windows Administrator would be able to start your new instance in single-user mode and connect as a sysadmin, or simply stop the new instance and grab the database files.

    David


    Microsoft Technology Center - Dallas
    My blog

    Sunday, August 19, 2018 12:33 AM
  • :(

    Thanks bud !!!


    G.Waters

    Sunday, August 19, 2018 1:33 AM
  • Well, what if I delete all logins (including sa), except for the one my application uses?

    No one will be able to access database, even the PC windows administrator, right?


    G.Waters

    Tuesday, August 21, 2018 12:02 AM
  • Sorry, that won't help. As mentioned, the backdoor is if you start SQL Server in single user mode, a windows admin will get in as sysadmin. It doesn't matter whatever logins you delete. It is a backdoor.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, August 21, 2018 6:48 AM
  • No, it is a dead end. A completely dead end. There is absolutely no way that you can prevent someone who has physical access to a database file to gain access of data or anything else in that database directly without using your application.

    That said, you can make it more difficult, so that it is clear that users who gets in are really trespassing. You would also sign a license agreement with them that would disallow them to access the database outside the application. But that will of course protect you against malicious persons who have no other intent than stealing your intellectual property.

    If you want to provide the database to customers so that they get to the data outside the application, you need to put the database out of reach for you. The logical choice would be the cloud, for instance in Windows Azure or AWS. And you would also make the application a three-tier application, for instance a web application. If you give the users a fat client that connects with a username/password, users can use that combo to log in to the database directly


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 21, 2018 10:44 AM
  • Yes, it's a fat client, but what if the client uses SSL/TLS to communicate to database?, the user and password will not be visible through network, isn't it?

    G.Waters

    Tuesday, August 21, 2018 11:31 PM
  • No, but the username and password will be available on the client.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 22, 2018 7:04 AM
  • No, but the username and password will be available on the client.
    And just to add: it does not help if you encrypt the username/password, since you also must embed the decryption key in the client.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 22, 2018 7:18 AM