locked
database security issue RRS feed

  • Question

  • Hello All,

    We have workers who go to various locations and they do have laptops given.
    Through laptops they access our application and connect to local db copy.
    When they connect to comapny network they syncronize their local db with main db and exchange updates.

    My question is regarding security of local db at workers laptop.

    Ideally they can only be accessing db through application interface.

    But I am concerved if they get through db
    1. Using management studio.
    2. Using command prompt sqlcmd command. Note are their ant other means of accessing db from above 2 points
    Note: is their any means that these can be restricted for this user but not the admin user
    3. Copy database somehow.

    How to avoid any means of getting to db except through application.

    Plaese help.

    Sincerely
    sanndii777

    Thursday, July 29, 2010 7:05 PM

Answers

  • Hi saandii777,

     

    How to avoid any means of getting to db except through application.

    The administrator of the machine could retrieve the structure information of this database via the following methods:

    ·         Use single-user mode to add valid login
    for more information about “single-user mode”, please refer to the “-m” switch in the following link:
    http://msdn.microsoft.com/en-us/library/ms190737.aspx

    ·         Attach the database to other instance of SQL Server

     

    It means: anyone who is the administrator of the machine could access the structure and the non-encrypted content of the database.

     

    However we could encrypt the data in application before the data is stored to database. Then only the one who knows the encrypting logic could retrieve these encrypted data.

     

    If anything is unclear, please let me know.

    • Marked as answer by Tom Li - MSFT Thursday, August 5, 2010 6:28 AM
    Thursday, August 5, 2010 6:28 AM

All replies

  • This is difficult. You can create a server trigger to disconnect if the connecting application is not what you expect. See the results from "SELECT program_name FROM sys.dm_exec_sessions WHERE @@SPID = session_id"

    But applications can lie about their names when they connect. So this should only be used to prevent unskilled people from playing around. This won't stop someone who really knows SQL Server.

    The best thing to do, is to use the permissions in the database to restrict peoples activity, such that you don't care how they connect. They can only do valid things anyway. That is, they can execute a procedure, and as long as they do it properly regardless of the client tool, they are fine. But this means you must have most or all of your business rules implemented in SQL Server. DBA's like to do this. Developers like to put their business rules on the client. In reality, sometimes the business rules should be in both places.

    Other choices require some password to be coded into the application, which could be a vulnerabiltiy. Look into application roles. Or create a user without login based on a certificate or asymmetric key. The application knows how to open the cert/key, and then uses EXECUTE AS to use the user without login permissions. The person connecting doesn't know how to open the cert/key, so they must go through the app.

    Also, if these workers are running around with laptops, it's likely they are able to login as administrators. Local adminstrators can access the SQL Server, either using their admin credentials or by copying the database files. So if these workers are admins on the laptops, you have additional problems. And you should use BitLocker and/or transparent data encryption in case the laptops are stolen. Otherwise the data can be exposed.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Sudhagar.g Monday, August 2, 2010 7:57 AM
    Thursday, July 29, 2010 10:53 PM
  •  

    1. You can consider tighetening the security of the laptop users on SQL Server Databases. Grant only the minimal required access.

    2. Use Logon Trigger to validate by which the connection made to SQL Server and restrict if the connection made by SQLCMD  or SSMS from the localhost on given login names.

    3. Consider SQL Server 2008 feature of Transparent Data Encryption ( TDE )


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Saturday, July 31, 2010 8:42 AM
  • Thankyou

    I would try these features would surely ask If I come across any questions.

    One more question out of curiosity.
    Is there any other means besides SSMS/sqlcmd/of course my application to access db


    Thanks
    saandii777

    Monday, August 2, 2010 12:41 PM
  • Hello Sivaprasad,

    Thanks for your input.
    I read the artcile on logon trigger which you provided along with some other articles on logon trigger.

    All articles specify limiting user sessions and stuff.

    But no article has suggestion on restricting login throght SSMS/SQLSMD.
    If you have particular link it will be helpful or any assisted script.

    Thanks
    saandii777

    Monday, August 2, 2010 1:36 PM
  • Looking back at Rick's post, he mentions triggers which can check for the Program_Name and, if it is Management Studio or SQLCMD, you can reject the login. 

    Of course, he also mentioned that the ProgramName is easy to fake with the connection, so it is not really reliable.  So, a login trigger would only keep out SSMS and SQLCMD users who did not know a lot about connection strings, but someone who knows could fake it out.

    RLF

    Monday, August 2, 2010 2:49 PM
  • Thank you Sir for your reply.

    I would explain the scenerio little bit in detail.
    Actually I am setting up merge replication for my company.

    Our workers (roaming users) are given a laptop where sql server express edition is installed and workers use front end to communicate to local db.
    Note: workers have no idea of userid/password used in front end for connecting with db.

    Now in order to syncronize the local db with company main db We have to run merge agent at worker laptop.
    Here worker will be provided with a utility as in interface to run merge agent.
    Now here I need to give userid password for connecting to local db in this utility.

    My concern is of security of local db and to avoid any misuse of it.

    One concern I identified is that what if they know the userid /password used in running merge agent utility.
    Note: I would take proper care security wise of not exposing it.

    But I am thinking  in terms of what if  this wall is broken somehow.
    So I planned as suggested in the thread above to lock ssms/sqlcmd excess for account used for running merge(for connecting to local db)

    Please tell if you feel any idea which is full proof and also I wanted to know is there any means other than SSMS/SQLCMD/MyApplication that they can access the db. I have actually no idea other than these.

    I hope I have made you all more clear on my scenerio and I had to introduce merge replication information here for making things more clearer.

    Thanks
    saandii777

    Monday, August 2, 2010 5:17 PM
  • Regarding general local database access by the laptop user:

    1. Is the user a local administrator of his laptop?  Or do you have that locked down?
    2. Is the BUILTIN\Administrator group granted sysadmin rights on SQL Express?

    Obviously, if the user is a sysadmin on his SQL Server Express database, he can do anything he want to locally.  Depending on knowledge and desire, of course.  So you want to avoid letting him be administrator of the Express server on the laptop.  The general advice already received is:  Limit their rights.  This is just another part of that recommendation.

    In terms of what other software can be used to access the SQL Server, anything that can use an ODBC / OLE DB connection to SQL Server.  For example, Microsoft Access, Excel, and plenty of other tools. 

    If you are concerned with the laptop user manipulating the data manually, you will need to do something to detect that, such as a 'tripwire' system.  Perhaps your application can also store a (kept well secret) hash of each row in a hash column, as well as a hash per table column for each affected table.  Perhaps you could then detect external manipulation. (This will not expose external reading of the data.) 

    This is not quite what you are after, but perhaps it will be educational:
    http://www.sans.org/reading_room/whitepapers/detection/building-tripwire-system-sql-server_1580

    FWIW,
    RLF

     

     

    Tuesday, August 3, 2010 3:27 PM
  • Hello Sir,

    Thank you for your detailed response.
    No the user is not a local administrator of the laptop.

    A would designate this user as Disconnected User having limited capabilities on Laptop

    The disconnected user will have access to
    1. use application (front end)
    2. Run merge agent through some utility (exe file or windows application) having only execute rights.
    3. Disconneted user will have full access to his my document folder only.

    Actually I want to restrict disconnected user to do following things
    1. cannot install anything on the laptop.
    2. No usb or printer port access.
    3. No SSMS or SQLCMD access by deny rights on their exe file.
    4. No access to microsoft excel and access

    Atcually I was little concerned as what if secuirty is broken and under that case how can I still prevent disconnected user to do things

    So I thought making barriers sort of.
    1. At first hand assumingly he will never come to know sql autheticated username and password.
    2. If it does than we will try to access database using SSMS/SQLCMD/access/excel those will be restricted using logon triggers.
    3. Still if user gets through all these I am planning to lock tables/view for that sql account.

    However I have not thought of securing .mdf/.ldf db files as in yet.
    I am just under impression that when disconnected user go to data/log folder he will be denied access.
    Is their any other means still of capturing .mdf/.ldf files as access rough SSMS/SQLCMD/access/excel will be restricted.

    I hope you have some better appraoch than me to share.

    Summary:disconneted user has limited access to resources(allowed by admin) on the laptop.
    But only concern is things must not be exposed somehow by lack of thinking from my side.

    Sincerely,
    saandii777

    Wednesday, August 4, 2010 2:25 AM
  • You are on the right track. The next level of risk to consider, is what happens if the laptop is stolen. Then the thief can either reinstall the operating system, or take the hard drive out and attach it to another computer. Then as the administrator of the computer they can set permissions on the file folders, and take the SQL Server mdf/ldf files. Attach them to a new instance of SQL Server and read them. The defense, is to encrypt the folder (the thief won't have the key), or the entire hard drive (Windows BitLocker), or if you have SQL Server 2008 Enterprise (unlikely) use Transparent Data Encryption to encrypt the mdf/ldf files.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, August 4, 2010 2:38 PM
  • Since you are restricting the rights a user has on his laptop, that is a step forward.  With regard to preventing installing of software, use of USB ports, etc. these are not SQL Server issues but are solved through policies. 

    The policies may be operating system policies or procedural policies.  (For example, perhaps your company stuffs the USB ports with epoxy so that no one can use them.)

    With regard to the protect the files beyond access to the file, Rick has pointed you toward encryption. 

    RLF

    Wednesday, August 4, 2010 6:26 PM
  • Hi saandii777,

     

    How to avoid any means of getting to db except through application.

    The administrator of the machine could retrieve the structure information of this database via the following methods:

    ·         Use single-user mode to add valid login
    for more information about “single-user mode”, please refer to the “-m” switch in the following link:
    http://msdn.microsoft.com/en-us/library/ms190737.aspx

    ·         Attach the database to other instance of SQL Server

     

    It means: anyone who is the administrator of the machine could access the structure and the non-encrypted content of the database.

     

    However we could encrypt the data in application before the data is stored to database. Then only the one who knows the encrypting logic could retrieve these encrypted data.

     

    If anything is unclear, please let me know.

    • Marked as answer by Tom Li - MSFT Thursday, August 5, 2010 6:28 AM
    Thursday, August 5, 2010 6:28 AM
  • Thank you all for much needed help and suggestions.

    Just to highlight we have sql express edition on laptops and sql server 2008 standard edition where main db is.

    I am very much looking forward to experiment with encryption/ hard disk safety using bit locker or kill switch on laptops.

    I would certainly get back as I progress with it.

    Thank you
    saandii777
    Thursday, August 5, 2010 7:47 AM