1

Using localDB (Express embedded) I would like to encrypt data.
Express does not support TDE.
I can use .NET encrypt the data going in and out.

My question is sort, greater than, and less than, and wildcard queries.
Since the database has encrypted data is there way to do this?

paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • 1
    You could add an order column as suggested at [Creating an Order Column for encrypted data](http://stackoverflow.com/a/12247072/1115360). – Andrew Morton Mar 08 '15 at 15:26
  • @AndrewMorton Order column could solve the sort but I don' think it would solve the gt, lt, or wildcard. – paparazzo Mar 08 '15 at 16:45
  • The order column could have the same values for items of equal sort order, so that problem is potentially solved. The wildcard issue cannot be solved database-side. Would SQL Server Compact Edition, which does support encryption, be a viable alternative? – Andrew Morton Mar 08 '15 at 17:31
  • @AndrewMorton I did not know Compact Edition supported encryption. Thanks. I will look into that. – paparazzo Mar 08 '15 at 17:34
  • @AndrewMorton GT is not really solved with a sort column. I could have gt 'c' and not have any value of 'c' actually in the data. – paparazzo Mar 08 '15 at 17:36
  • @AndrewMorton Compact Edition has a 4 gb limit compared to 10 gb and this app will push the the 10 gb limit. – paparazzo Mar 08 '15 at 18:51
  • 1
    I think you have answered your question: localDB is not suitable, and neither is SQL Server CE. There might be a viable alternative listed in [Stand alone database for Windows Application (WPF)](http://stackoverflow.com/a/5412680/1115360). – Andrew Morton Mar 08 '15 at 19:21
  • @AndrewMorton You want to go ahead and post an answer. – paparazzo Mar 08 '15 at 19:26
  • I wish I could think of some devious method to get round it, such as using several databases with SQL Server CE, but I suspect it would be horribly convoluted and prone to corruption if the wind blew the wrong way. I posted an answer taking into account your further requirement of the DB size. – Andrew Morton Mar 08 '15 at 19:43

1 Answers1

0

You cannot do that (especially w.r.t. wildcards) with localDB as it does not support encryption, and SQL Server CE (which does support encryption) does not allow for the size (~10GB) of database which you have.

Some alternatives (as of the year 2011) are listed in Stand alone database for Windows Application (WPF).

Community
  • 1
  • 1
Andrew Morton
  • 24,203
  • 9
  • 60
  • 84