Matching up SQL CE DLL version numbers

The Abstract:

While developing a Windows Mobile device application on two different computers, I thought I’d installed SQL Server Compact Edition 3.5 SP2 on both of them, but ran into some version difference issues. In researching it, I couldn’t find good information about the different versions of the System.Data.SqlCompactCe DLL, so thought some future developers might enjoy what I found out, all in one place.

The Problem:

After checking the project into source control on one computer and fetching it on the other, I’d see a broken reference to System.Data.SqlServerCe:

Missing reference to System.Data.SqlServerCe
Missing reference to System.Data.SqlServerCe

And I received the following message (and about 100 errors – one for every reference to the namespace in my code) when I attempted to compile:

Warning 10
Could not resolve this reference. Could not locate the assembly “System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, processorArchitecture=MSIL”. Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors.

If I dropped the reference, re-added it on my second computer (BTW – only one version was listed in my .NET reference list on both machines) and checked it back in, then I’d get the same situation on my first computer with the following error message:

Warning 10
Could not resolve this reference. Could not locate the assembly “System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, processorArchitecture=MSIL”. Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors.

The Research:

I had a DLL version mismatch – a general problem when you’ve got multiple computers you’re developing something on, since you have to ensure the same versions and packages are installed on each. However, searching for details on the different versions of this DLL in the wild wasn’t fruitful – did I have SQL Compact 3.5 RTM installed (3.5.0.0)? If I’d installed v3.5 SP2, why was it shown as 3.5.1.0 (shouldn’t it be 3.5.2.0?) on one machine, but as 3.5.0.0 the other? To top that off, both versions (v3.5.0.0 and v3.5.1.0) were in my GAC as MSIL – what?

It turns out that when you add a reference to this DLL in a Compact Framework project, Visual Studio is using the following version (depending on whether you’re on an x86 or x64 machine):

x86: C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll
x64: C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll

There, I found my issue – that file version was a different version on each machine (3.5.5692.0 on one and 3.5.8080.0 on the other). To find out why, I had to dig into the registry, where I found all kinds of version information about the SQL Server Compact Edition components I’d installed, the DLL version, and the service pack levels:

x86 or x64 components
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Compact Edition\v3.5\ENU

or if you want to see x86 components installed on your x64 machine:
HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server Compact Edition\v3.5\ENU

There, you’ll find a combination of “Version” and “ServicePackLevel” entries for every SQL CE component you have installed, and it will shed some light. Here are the version numbers I found (for the various components):

SQL Compact 3.5 RTM
ServicePackLevel: 0
Version: 3.5.5386.0

SQL Compact 3.5 SP1
ServicePackLevel: 1
Version: 3.5.5692.0

SQL Compact 3.5 SP2
ServicePackLevel: 2
Version: 3.5.8080.0

So how did I end up with the SP1 version installed on one machine, but not on the other? Since I’d installed SQL Compact SP2 on both machines, I was baffled. As it turns out, Visual Studio 2008 RTM deploys SQL Compact 3.5 RTM (3.5.5386.0), and applying Visual Studio 2008 SP1 patches that to the SP1 version (3.5.5692.0) – notice the VSToolsVersion/VSToolsServicePackLevel values in the registry location above, which reflect your current service pack level of Visual Studio 2008.

The Solution:

While I’d installed VS 2008 SP1 on both machines, I’d installed the actual SQL Compact 3.5 SP2 for Windows Mobile (EDIT 2016-10-31: This link has been updated to the current location for this download) package on only one machine and not the other – this left the DLL on one machine patched to SP1 (courtesy of Visual Studio) and the other machine with a fully-patched SP2 DLL (courtesy of the actual SP2 Windows Mobile installer).

I confirmed this by running the SP2 for Windows Mobile installer, and it patched my DLL right up to the full SP2 version – problem resolved, and the project now opens up on both workstations with no need for any DLL reference swapping.

The Moral:

Honestly, a bit embarrassing and a pretty amateur move where some vigilance could have saved me quite a bit of aggrivation.

ENSURE YOU’RE DEPLOYING ALL THE SAME PACKAGES ON ALL DEVELOPMENT MACHINES! JUST BECAUSE SOMETHING ALREADY EXISTS ON ONE MACHINE, DON’T ASSUME IT’S THE SAME VERSION!

Generate an x509 certificate with an SHA256 signature hash

When authenticating with a vendor using a custom webservice, the vendor requested that we use an x509 certificate with a 2048 byte key and an SHA256 hash (sometimes referred to as SHA2, though SHA2 actually refers to the group of hashes containing SHA256, 384, and 512). Since I’d used IIS to generate our certificate (IIS will only generate a certificate using an SHA1 hash), and it involved quite a bit of research to get a certificate with an SHA256 signature hash on it, I wanted to detail the steps here:

  1. First, download and install OpenSSL from Shining Light. The “Light” version of the package will do, since you’re only using basic functionality.
  2. Generate your Certificate request (CSR), specifying an SHA256 signature hash
    1. openssl req -nodes -sha256 -newkey rsa:2048 -keyout C:\SomeFolder\PrivateKey.key -out C:\SomeFolder\CertificateRequest.csr
    2. You’ll be prompted for a few certificate fields, including your state, company name, computer name on your certificate, etc. Enter these as they come up.
  3. This will generate two files – PrivateKey.key (which contains the un-encrypted version of your private key – protect this file, as somebody who obtains it along with your signed public key can impersonate you), and CertificateRequest.csr (your certificate signing request, which is not sensative).
  4. Though this isn’t required, if you want to confirm what you’re entered in the CSR, you can view the details using another OpenSSL command line
    1. openssl req -in C:\SomeFolder\CertificateRequest.csr -text -noout
  5. Now that you have your CSR, submit it to whatever signing authority you use – for us, it was Verisign, but there are any number of different CAs out there that can sign it.
  6. Once your CA has signed the CSR, you’ll get back either a binary p7b file (which we’ll called SignedKeyFromCA.p7b) containing your certificate signed public key (and, possibly, the certificate chain your CA used as well), or either a binary or base64 CER file containing just your certificate. Whatever you receive back, you’ll need to convert it to a Base64 CER (called SignedKeyFromCA.cer here), since that’s what OpenSSL expects.
  7. To combine your private key with the signed public key to create a certificate:
    1. openssl pkcs12 -export -in c:\Temp\SignedKeyFromCA.cer -inkey c:\Temp\openssl.key -out SignedKeyPair.p12
    2. Since you’re exporting your private key in this file, you’ll be required to encrypt it with a password, which OpenSSL will prompt you for (twice).
  8. You’ve now got your signed key pair – SignedKeyPair.p12. You can either use this pkcs12 file in your code, or you can import in into your web server (assuming it supports SHA256 hashes). IIS7, for example, supports importing this certificate and using it for SSL, but just doesn’t support generating a SHA2 CSR in the first place.

Enjoy! If you have any issues, please feel free to post a comment and I’ll do my best to answer it!