MSDE and security

  • simonr

    Old Hand

    Points: 342

    Hi all

    We're currently developing a VB application that (currently) uses MSDE as the back end. The data held in MSDE is fairly sensitive and as such we'd like to prevent users from bypassing the app and reading or copying it directly. I had thought that encryption would be the way to do this but am unsure. Any ideas or am I on the wrong path with this one??

    Thanks

    Si

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716172

    Using encryption is really the only way, however, the encryption needs to occur in the client, so the DATA is encrypted when it is stored and decrypted when it is read. Of course, keeping the keys secure is then your issue.

    Steve Jones

    steve@dkranch.net

  • Andy Warren

    SSC Guru

    Points: 119676

    Restricting access using NTFS permissions is important too. They should not have direct access, only via the db engine.

    Andy

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    I think Steve and Andy have indicated the right paths for what you're trying to do, but to clarify, are you looking to

    a) Prevent someone from seeing the information as it goes across the network?

    b) Prevent someone who was rights to the application from accessing the data via the database?

    c) Prevent someone, irrelevant of whether or not they have the application, from accessing the data via the database?

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • simonr

    Old Hand

    Points: 342

    This is a standalone app so (b) hits the nail on the head. The data held by MSDE should only be visible to the app, not to the user....does this sound feasible?

  • Andy Warren

    SSC Guru

    Points: 119676

    Then you should look at approles - perfect for the situation you describe. Kudo's to Brian K for asking the right question!

    Andy

  • simonr

    Old Hand

    Points: 342

    I had a look at app roles and this looks pretty much what I'm after. My only concern is that a user could browse to where the MSDE data files are stored on his hard drive and access them this way...

  • Andy Warren

    SSC Guru

    Points: 119676

    Thats where NTFS comes in. No need for a user to have direct access to the mdf's.

    Andy

  • simonr

    Old Hand

    Points: 342

    The problem is some/most of the clients will be Win 9* and the app will eventually be issued to third parties so directory access control through NTFS will not be possible.

  • Andy Warren

    SSC Guru

    Points: 119676

    Well, you're back to encryption again. Depends on just how sensitive the data is, how much effort to put into it. Might be enough to do some very simple encrypting of the tables with the sensitive data, use XOR or another equally simple technique.

    Andy

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    I'm not real familiar with MSDE with respect to applications development and installation. So if I sound like a complete buffoon, please forgive me after you slap me around a little. However, one should be possible to execute scripts against the db during install to change the sa password and change BUILTIN/Administrators to the Server Administrators fixed server role, removing them from the sysadmin fixed server role. Then you ensure that the user account used by the app has permissions to the database via an application role. Wouldn't this lock things down sufficiently?

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • simonr

    Old Hand

    Points: 342

    Yeah, I see what you're saying. But will this prevent someone browsing to where the mdfs & ldfs are stored on their PC, pulling them into another SQL Svr or MSDE box and looking at what's inside them...?

  • Andy Warren

    SSC Guru

    Points: 119676

    Nope, no way I know of to prevent that. Encrypting the data seems best. I guess an alternative would be:

    1) Stop the service

    2) Save the first couple hundred bytes from the mdf, then overwrite with junk. You'd have to test to see how much you have to do to keep it from loading correctly.

    When it's time to run the app:

    1) Put back the bytes you saved.

    2) Start the service.

    Hows that for a hack? Still wont prevent them from browsing your mdf using Notepad, although obviously it's a lot harder.

    Andy

  • simonr

    Old Hand

    Points: 342

    I guess that's one way! I think encryption is probably a more orthodox approach though 😀

    Time to investigate crypto techniques in VB!

    Thanks for your help guys

    Si

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Andy makes a good point. If you do copy it to another server, the sysadmin role by default is going to get dbo rights. I didn't think about that. As for encryption, try the following:

    http://www.counterpane.com/labs.html

    It's the link to Counterpane Labs (part of Counterpane Internet Security). Counterpane is where Bruce Schneier resides, author of the heralded Applied Cryptography. He's one of the founders.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply