Results 1 to 19 of 19

Thread: Protect Sql Database

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Protect Sql Database

    After connecting to an Instance of Sql Server in SSMS, we can open a DataBase. Is it possible to protect this from unauthorized people to open the database by giving it a password ?.
    We do it in MS-Access. If we double-click/open the database it will ask for password there. I want something like that.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Protect Sql Database

    That's not how it works with SQL Server. SQL Server is a server-based database and security is handled at the server (or more correctly instance) level. You create logins that are able to connect to the instance and you create users at the database level that map to those logins. A user can only connect to a database if they have a login that maps to a user in that database.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    I develop an application in my Home PC. I deploy it in X's PC, say. While doing so, i install Sql server 2005 in the Main PC. 2 or more PCs will be connected in LAN in a workgroup there. As you said the others PCs communicate with the DB using their respective log-in. Anyway in the Main PC, it is possible to physically open the sql server database in SSMS and view tables/structures/datas, etc. Is it not ? I want to know how to protect this. Or am I somewhere wrong in the concept while deploying ?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Protect Sql Database

    Quote Originally Posted by raghavendran View Post
    in the Main PC, it is possible to physically open the sql server database in SSMS and view tables/structures/datas, etc.
    Yes.

    It is possible to make it harder, but if the user can run programs as admin on the computer where the database is stored, you can't stop them.


    If you are developing this for a company and can persuade them that the data needs to be protected, they can put the database on a server that the users can't log in to... but somebody from the company (server admins) will be able to.

    An important thing to think about is whether it needs protecting. Presumably the users can see the data within your application anyway (in a far easier way than installing SMSS), so is it worth hiding it from them?

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    More information about the user scenario is needed here for us to give you a complete answer.

    I did work on Pocket PC's a while back and used MS SQL CE (compact edition) - that DB was able to be encrypted so that others could not see either the data or the structure (I was only worried about data - as it contained images of students).

    I think that MS SQL CE is no longer used - maybe replaced by LocalDB - I'm not 100% sure on this fact.

    Is this a single user application? Do you use STORED PROCEDURES?

    Do you really need to be using a database? More info on the size and scope of your data would be helpful.

    Otherwise - what comes to mind - if you want to protect the "structure" of the database - tables and such - then don't put it on the customer machine or network at all. Put it on a server that only you have access to and allow them to connect to this remote location. Basically the concept of web apps - but the client program does not need to be browser based app. If your program is a Winform it could still talk to "services" that supply the data that is asked for without exposing the database structure.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    Through my vb.net 2008 application, I'll not have any problem for the end-user to view the (queried) datas. I've certain controls where Admin person alone will be able to edit/delete the records in my application. Others (in LAN) can simply view. It's structured like that. I'm more worrying about physically opening the database i.e Going to SSMS, select the DATABASES, then expand TABLES, then select OPEN TABLE,..... Like that. This opening should be avoided/protected with a passcode...

    @si_the_greek:
    How can we make it harder ?

    @szlamany:
    Yes, it's a single user application (Temple_Management: Devotee's_Base, Receipts/Payments, etc). So, certainly a Database is required.

    Otherwise - what comes to mind - if you want to protect the "structure" of the database - tables and such - then don't put it on the customer machine or network at all. Put it on a server that only you have access to and allow them to connect to this remote location. Basically the concept of web apps - but the client program does not need to be browser based app. If your program is a Winform it could still talk to "services" that supply the data that is asked for without exposing the database structure.

    You have shown me a new concept. I'm yet to learn it... Thanks.. I'll learn it...
    Last edited by raghavendran; Apr 7th, 2014 at 02:26 AM.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Protect Sql Database

    I'd create a SQL login, and let the app use that for the login credentials. Then I'd explicitly deny all Windows Authentication logins, except for those that will need access to it directly (like yourself) for maintenance.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    I'd explicitly deny all Windows Authentication logins, except for those that will need access to it directly (like yourself) for maintenance.
    Can you please explain me the method to accomplish this ?

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    Remember that anyone with ADMIN privileges to the machine that the DATABASE is on can "get around any protection". If you are only worried about casual viewing then what TG suggests will work.

    If you are looking to protect trade secrets within your database from all users - then you cannot put the DB on their network.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    @szlamany :
    I'm very much interested in knowing what you have told in your reply in #5. But i don't know where from i need to start.
    1. "Put it on a server"
    2. "and allow them to connect to this remote location."
    3. "If your program is a Winform it could still talk to "services" " - How, How, How ?

    I know that this is not a class-room section. But still another level of help is solicited. I've developed a user-friendly application in a single-user environment, but I'm just blank in this particular chapter viz. "remote connecting database".
    Can you please ?
    Last edited by raghavendran; Apr 9th, 2014 at 08:46 PM.

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    Ok - if you are considering a "remote location" for the database I see two ways to accomplish this. One is very simple and the other more complicated.

    The simple plan requires:

    1) A machine you have control of with a STATIC ip address
    2) Open port 1433 / 1434 on your firewall
    3) Change your app to talks to SQL via the IP address

    Of course this is only really made secure if you are talking to the DATABASE with only STORED PROCEDURES. If you are doing straight SELECT and UPDATES then you have to give access to the tables themselves.

    I've never given TABLE access to users - only give EXEC permissions on SPROCS that way the user would never see raw table data - even if attacking the DB with a different tool (such as SQL Server Management Studio or Excel).

    The more complex plan would require that you:

    1) Create a service to run on the remote machine
    2) This service talks via HTTP POSTS (for example) to the client app
    3) Client app POSTS to this service to "ask for data"
    4) Service accepts request and returns a packet of data
    5) Client app handles the return of data

    I just create this exact scenario on an Android tablet taking to web methods hosted by IIS.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    I understand from your reply that I need to create a mediator like thing, and you call it by the name 'SERVICE' , 'through which' the client machine will communicate to the server machine.
    If you don't mind can you please elaborate it ? How to create a service on the remote machine ? Is it something like Creating a windows service ? How can i make it as a bridge between client and the server machines ?

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    Why did you jump at the complex solution? Are you not using STORED PROCEDURES?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    I already wrote several lines of select/insert/update statements in my vb application. Unfortunately I was not aware of using Stored Procedures.
    Now if i need to use it, again i need to rewrite all these codes in Sql Server itself and call these Procedures in my vb application (where again i need to change the codes accordingly).
    Instead if you can tell about this 'SERVICE' method of connecting to the database, i think it will be a bit easier ? Am I right ? Please help me

  15. #15
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    It will not be easier - as I said in my post. You cannot send a SELECT or INSERT to a home made service. You need to create a communication protocol.

    Simply putting MS SQL on a remote server (from the client) and then changing all your SELECT/INSERT's to SPROCS (which will not be that difficult) will allow you to protect the database. The only access anyone will have is the ability to EXECUTE stored procedures.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    O.K. I'll follow your advise. Please clarify me these things.
    1. For each and every Select/insert/update/delete statements i used in my vb application, i need to create a Stored Procedure in the Sql Server.
    2. And use the respective procedure name in the application, Is it ?
    3. Let me say I have a Select statement like this : "Select A, B, C from Table1". This will retrieve 3 fields viz. A, B, C.
    Now I put this Select statement in a Stored Procedure and i call that SPROCS which is going to produce the same result. How the database is being protected here ?
    By Database protection - you mean in some other cases, not generally in 'select' type statements. Is it ?

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    Well - you gave a simple example in which no "WHERE" clause was in place - a SPROC like that would look like

    Code:
    Use TCSHemp
    Go
    Drop Procedure awcMarStat_autocompleteid
    Go
    Create Procedure awcMarStat_autocompleteid
    as
    Set NoCount On
    
    Select MarStatusDesc, MarStatus
    		from MarStatus_T
    Go
    Grant Execute on awcResident_autocompleteid to TCSUser
    Go
    Exec awcMarStat_autocompleteid
    I do everything in TEXT file scripts (that end in .SQL) - I create these scripts in SQL Server Management Studio.

    The above script sets the database (USE), then it drops the SPROC if it exists and then it CREATE's the SPROC.

    Each of these "batches" of script are separated by the GO command - this is not a SQL command - just a command that SSMS uses to send "batches" of script to the SQL engine.

    After the CREATE you will see a GRANT EXECUTE statement - followed by a "test" EXEC statement that runs the SPROC (so I can see it work).

    In the above example - TCSUser is a SERVER ROLE that I assign to each user that gets access to the DB. Since only EXEC permissions are granted to the ROLE, that means the user cannot ever do a SELECT against that table (MarStatus_T). Only the "data access" you create through SPROC's is allowed.

    This is a SPROC that has a couple of parameters and does an insert

    Code:
    Drop Procedure [dbo].[awc_TextOutputProcess]
    GO
    Create Procedure [dbo].[awc_TextOutputProcess] @ctrlval1 varchar(255), @username varchar(100)
    As
    Set NoCount On
    Declare @TxtFolder varchar(255)
    If @ctrlval1='RelTaxDF'
    Begin
    	Set @TxtFolder=(Select ConfData From TCS_T Where ConfItem='TxtOutputCountyDir')
    End
    Declare @TxtOutId int
    Begin Tran
    Insert into TextOutputProcess_T
    	Select @TxtFolder,@username,GetDate()
    Set @TxtOutId=Scope_Identity()
    Select '["hidesproc", [ "button"] ]~textoutput='+TxtFolder "~textoutput" From TextOutputProcess_T Where TxtOutId=@TxtOutId
    Commit
    The SPROC adds rows to the TextOutputProcess_T table - but in no way exposes the data or even the layout of that table to the user. It also gets data from the table TCS_T - and again give no data or even the layout of that table to the user.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: Protect Sql Database

    Thank u so much. I'll give it a try. I started converting my vb codes with SPROCS. I think I would require not less than 24 hrs to change my pages of codes. I'll come back to you successfully.
    1. 'Only selective users will have access to the database upon giving a Grant permission.' - is where lies the protection part of the DB.
    2. I started this thread with a view to protect the database by not physically opening it in SSMS. You were talking about encryption in your post # 5.
    I will ask about this once I finish "Stored Proceduring............ ! ? " - so this thread would comprise a full study of Protecting a SQL DB....

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Protect Sql Database

    1. Yes - and you can create different DATABASE roles and assign them to different SPROCS thus increasing the "level" of security.
    2. Encryption is something I've only done with MS SQL CE - a database tool designed for mobile devices. Not sure it even exist anymore.

    And your final question...yes - SPROCS are part of a good "security" model for a database.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width