Data providerMapping for sql CE Database?

Topics: Data Access Application Block
Aug 27, 2008 at 9:02 PM
Edited Aug 27, 2008 at 9:04 PM
I have a win app that accesses a CE database.  For deployment, I want to encrypt the connection string information.

I am accessing the DB using the 3.1 Data App Blocks.  The system works great with non-encrypted connection strings. 

I first tried using the enterprise library configuration tool to encrypt the strings, and that worked on my machine, but as soon as I tried it on another machine, it bombed.

I found this thread that creates a custom provider for a regular SQL DB that can decrypt the string before the DB is created.
http://www.codeplex.com/entlib/Thread/View.aspx?ThreadId=25045

This worked perfect for a regular SQLServer DB.

When I changed the class to inheirate from 

Microsoft.Practices.EnterpriseLibrary.D

ata.SqlCe.SqlCeDatabase instead of SqlDatabase, I get the error:

"Could not load type 'System.Data.SqlServerCe.SqlCeProviderFactory' from assembly 'System.Data.SqlServerCe, version=9.0.242.0, Culture=neutral, PublicTokenKey=89845dcd8080cc91'

This error occurs in the base constructor of the new class when it is trying to create the DB object (the decryption part works fine).


Just for giggles I added a reference to System.Data.SqlServerCe to the project, but that conflicted with the EnterpriseLibrary.Data.SqlCe reference.

Error 103 Assembly 'Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' uses 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' which has a higher version than referenced assembly 'System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' c:\Program Files (x86)\Microsoft Enterprise Library 3.1 - May 2007\Bin\Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.dll DAL.EncryptedSqlDatabase

What I find weird is that this error message says that Enterprise Library uses Version 9.0.242.0.  The version I have on my machine is 3.5, so where does 9.0.242.0 come from?

Anyone succesfully used encrypted connection strings on a CE database in a win app deployed through ClickOnce?

 

Aug 28, 2008 at 3:16 PM
Hi,

There are two things going on here. On the one hand, encrypted configuration requires special handling when moved from one computer to a different one; you can learn about this in http://msdn.microsoft.com/en-us/library/ms998283.aspx#paght000006_webfarmscenarios, but I think you'll need to work extra hard to make it work for ClickOnce since system-level setup is required. On the other hand, the assembly version numbers really went from 9.0.242.0 to 3.5.0.0 in SqlCE v3.5; try adding this to your config file to perform an explicit redirection:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"
>
    <
dependentAssembly
>
      <
assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral"
/>
      <
bindingRedirect oldVersion="9.0.242.0" newVersion="3.5.0.0"
/>
    </
dependentAssembly
>
  </
assemblyBinding
>
</
runtime>


Regards,
Fernando
Aug 28, 2008 at 4:28 PM
Fernando.  The runtime redirect worked great. 

Your comments on the extra work because of ClickOnce was why I wanted to go with the alternative method described in the link in my original post.

With your bindingRedirect, the encryption worked with ClickOnce with no additional effort on my part.

Thank you for the assist.

Alan
Aug 28, 2008 at 5:11 PM
Hi Alan,

I'm sorry I missed the encrypted config file. Now about the redirect, on a second though I don't think it belongs to the app's config file; if you were using the app in a host in which the 9.0.242.0 version of Sql CE was installed the app wouldn't work. On the other hand, expecting the user to perform system-wide redirects or update the config file in the hard-to-find location where ClickOnce stores it is far from ideal.

How much control do you have over the hosts where your app is installed?

Regards,
Fernando
Aug 28, 2008 at 6:00 PM
Interesting point.  For the initial release we have total control over what is installed on the device since the app is targeted for company owned resources.  I have also made SQL CE 3.5 a prerequisite of the ClickOnce publish.  None of our company devices have this installed, so I am pretty sure this wont be an issue.

The mismatch on versions comes (I think) from the fact that I started initial development with the Beta version of SQL CE 3.5 and MS Ent lib 3.1.  I have not been able to find any guidance as to how to switch to the 3.5 version within the solution, so I don't have to do this redirect.  (I am new to win apps, so don't have a lot of insight as to how this works yet).

The next release will upgrade to MS Ent Lib 4.0, and a preliminary proof of concept project leads me to believe that the version issue will go away after this is done.  It's certinaly going to be one of the first features to be implemented in the next version's development cycle so I feel secure that it will work.

More widespread deployments won't happen until the next release, but you bring up a great point for our testing process.

Alan