1

Let's assume that I have a C# multi-user client-server application and SQL Server 2008 Express as the database. It's an application that is supposed to be installed on customer's PCs, where everyone who works for the company can have access, i.e. the physical access to the .mdf file is free, so that one can easily copy the .mdf file to his/her machine, attach it and view all the DB structure and stored procedures' code I have developed.

I've read lots of info regarding to this issue all directing me to use free EFS (Encrypting File System ) of Windows. Consequently, I achieved to encrypt my .mdf file by a specific Windows user account which I created specially for this, set up a password for the user, so that no one can log on as my user and decrypt the file. Everything was fine, but I came across with dilemma that a user by which a customer logs on will almost always be in Local Admin group, so they can easily reset password for my user and log on as my user to decrypt the file. I can not exclude their user from admin group.

Could anyone help me out solve this problem, please? Or maybe there is a possibility to decrypt the file without having created additional user so that they could not decrypt the .mdf file?

I would really appreciate any help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
khurshed_nosirov
  • 248
  • 4
  • 20
  • 1
    As a general rule, if someone else physically controls the system then you can't prevent them accessing the data somehow. Perhaps you can make it difficult, but ultimately you can't stop them if they try hard enough. I would think hard about what you're trying to protect and what it's worth to you. See my answer to [this question](http://stackoverflow.com/questions/5645097/protect-stored-procedure-by-deny-view-definition); it may be better to handle this with a contract than a technical solution. – Pondlife May 03 '13 at 15:51
  • 1
    So everyone is supposed to be free to copy the database attach it locally and use it... but they also can't? – Andy May 03 '13 at 15:58
  • @Pondlife Of course I understand that if someone intends to access the .mdf he/she can achieve this, but what I want is to make this job as difficult as possible. – khurshed_nosirov May 03 '13 at 18:02
  • @Andy Locally I can disable all Windows accounts, so that it will be impossible to authenticate to SQL Server Express instance by Windows authentication..but still there is a possibility that .mdf file can be copied to other machine, attached on that machine's Sql Server instance and viewed. This is the problem I'm looking a solution for. – khurshed_nosirov May 03 '13 at 18:10
  • I understand now, that wasn't clear to me. – Andy May 03 '13 at 20:06

2 Answers2

2

EFS encrypts files with a random file encryption key (FEK) and encrypts that key with a RSA key belonging to the user ... this RSA key ist protected by DPAPI ... if the system is setup with a key recovery agent, the FEK is also encrypted for the RSA key of the key recovery agent (this might be the local admin, but can be changed, even to no key recovery agent)

if the local admin changes the password of a user, their DPAPI master key is left untouched, still protected by the old user password ... changing the password won't give you access to the DPAPI master key, and therefore won't give you access to the users private RSA key ...

if the station is member of a domain, the domaincontroller will have a decryption key that will allow decryption of a DPAPI master key ...

DarkSquirrel42
  • 10,167
  • 3
  • 20
  • 31
  • @DarkSquirrek42 Thank you for a irrefragable answer! Maybe it's another question, but just out of curiosity, is there any way to hide the user by which encryption was performed when logging on to Windows? Because I feel it will be a bit discomfort for a client to choose everytime his/her user. – khurshed_nosirov May 06 '13 at 10:21
  • the file will always "know" the keys the FEK was encrypted for and therfore it will know the user ... EFS can't hide that ... windows can't hide that ... even if you delete the account ... the file will still know the security identifier ... however, you can have your service run as another user ... but the credentials will be stored somewhere (or entered every time the service stars) ... and as soon as the credentials are known, security is broken ... – DarkSquirrel42 May 06 '13 at 23:08
1

Short of moving the data store to the cloud and having your application use web services, I don't think there's much you can do. I'm making the assumption that you're trying to protect some IP you have in the database. If you can get the logic into the client or server part of your application, you can obfuscate your application and then not have to worry so much about this problem. The data I presume is owned by your users anyway, I'd be pretty annoyed at a company trying to lock my own data away from me by encrypting the database.

The only real solution I think though is to address this via a sales contract that limits what they can do with your software even if they decompiled it.

Andy
  • 8,432
  • 6
  • 38
  • 76