locked
Secure SQL Database RRS feed

  • Question

  • Hi

    I am not so much expertise in SQL Server.

    I want to do

    1. Secure database with a password.

    2. Only be accessable with that password.

    3. Not to be accessed with windows authentication mode.

    The thing is client also uses SQL Server management studio and I don't want to share/view the structure of the database.

    Kindly suggest

    • Moved by Xi Jin Friday, August 26, 2016 6:51 AM
    Thursday, August 25, 2016 6:53 PM

Answers

  • If you are trying to protect you intellectual property while selling your product to someone who will use the DB on their computer, you should view this as a legal problem. Your protection is in your end-user license.

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

    Friday, August 26, 2016 2:47 PM
  • Hello,

    You can not protect a database with a password. You can only define which Windows/SQL login can access the database and which object (table, views, SP, etc) within the database.

    And: A SQL Server Admin can always access any database and can change those permissions as he like; there is no way around.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 26, 2016 4:52 AM
  • This works perfectly if the user is the owner of the database. Look 
    that …

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see 
    master, tempdb, dbteste
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    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, August 26, 2016 5:51 AM
  • Can't be done. Once the database is on a client machine, there is nothing to stop anyone with permissions in Windows to access it and do whatever they want with it.

    But as Rick says, you can spell it out in the license agreement that this is not permitted.

    Or put the database in SQL Database in Azure - now you can limit the client's control in the database

    Saturday, August 27, 2016 9:22 PM
  • Access databases are much different. Access is a shared file database. The password allows your local computer to the database file stored remotely. SQL Server is a client/server database. The server accesses the database file (through the SQL Server process) and offers it to authorized users. This is more efficient, but it means that the server computer reads and understands the data. The admins of the server computer can therefor view the data, by directly viewing the computer memory if nothing else. If your business need does not allow you to trust your hosting company then the hosting model will be difficult. Microsoft does offer a technique for that, called Always Encrypted. https://msdn.microsoft.com/en-us/library/mt163865 However, this is principally for protecting some portion of the data which is particularly sensitive. (There are also ways the client application can encrypt the data before sending it to the server.) The computer admin could still view the data, but some of it would be encrypted and unintelligible. And the administrators would be able to view the database structure (if they wanted to).

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

    Monday, August 29, 2016 3:51 PM

All replies

  • Hello,

    You can not protect a database with a password. You can only define which Windows/SQL login can access the database and which object (table, views, SP, etc) within the database.

    And: A SQL Server Admin can always access any database and can change those permissions as he like; there is no way around.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 26, 2016 4:52 AM
  • Hi There,

    If you/your team is administering the database server then you can create a user and give permissions on specific objects. you can create views for tables and give permissions to only those views.

    ask your client to connect to the database using that account.

    if your client is part of any existing AD groups that have permissions on the server then you will have no control.

    Good luck

    Kumar

    Friday, August 26, 2016 5:32 AM
  • This works perfectly if the user is the owner of the database. Look 
    that …

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see 
    master, tempdb, dbteste
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    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, August 26, 2016 5:51 AM
  • If you are trying to protect you intellectual property while selling your product to someone who will use the DB on their computer, you should view this as a legal problem. Your protection is in your end-user license.

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

    Friday, August 26, 2016 2:47 PM
  • Can't be done. Once the database is on a client machine, there is nothing to stop anyone with permissions in Windows to access it and do whatever they want with it.

    But as Rick says, you can spell it out in the license agreement that this is not permitted.

    Or put the database in SQL Database in Azure - now you can limit the client's control in the database

    Saturday, August 27, 2016 9:22 PM
  • Thanks for your reply.

    When we buy a shared SQL Server database plan from our hosting provider, they give us a userid and a password to connect to the particular database. However, when we connect with that id and password, we saw many other databases on the object explorer but we are not authorized to access that. We can only and only access our database and neither we are authorized to login in windows authentication mode.

    Secondly, when we use SQLite database or MS Access database file, we can put password on that and no one is authorized to open that database other than my program (which us having a password to open).

    I don't want to block windows authentication mode, let it be open but I just wanted to:

    1. Create a user that can login and have full right acess

    2. Restrict all other user not to acess that database.

    3. If admin or other user have that password they are allowed to access that db otherwise don't.

    I am using SQL Server 2012 version.

    Or please provide an alternate solution.

    Thanks


    Monday, August 29, 2016 9:31 AM
  • Access databases are much different. Access is a shared file database. The password allows your local computer to the database file stored remotely. SQL Server is a client/server database. The server accesses the database file (through the SQL Server process) and offers it to authorized users. This is more efficient, but it means that the server computer reads and understands the data. The admins of the server computer can therefor view the data, by directly viewing the computer memory if nothing else. If your business need does not allow you to trust your hosting company then the hosting model will be difficult. Microsoft does offer a technique for that, called Always Encrypted. https://msdn.microsoft.com/en-us/library/mt163865 However, this is principally for protecting some portion of the data which is particularly sensitive. (There are also ways the client application can encrypt the data before sending it to the server.) The computer admin could still view the data, but some of it would be encrypted and unintelligible. And the administrators would be able to view the database structure (if they wanted to).

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

    Monday, August 29, 2016 3:51 PM