The SQL CE and IIS 7.5 story:

Last week I thought I should migrate my SQL CE 3.5 sample applications connecting and opening a Sql Compact Edition Database from Windows Server 2003 to Windows 7. In fact, I tried migrating them both to Win 7 and Win 2008 R2 and straight away ran into issues. I worked on it further on figured out that he same code runs just fine when I connect through a Windows/Console application, its only when going through IIS, its throwing the below error:

“SqlCEException: The operating system does not support the Encryption Mode provided”

Honestly, it did not make much sense to start with as the same Web application works like a charm on my Windows Vista and Windows 2003 machines. Surely, it had to do something with IIS as the web server is the only component that has the change across my version of Windows.

Note: In Windows 2003/Vista/Windows 2003 R1, the version of IIS that comes bundled is IIS 7; however, in Windows 7/Windows 2008 R2, the IIS version is 7.5.

Of course, I have tried connection string variations inside my code to attempt to use the proper encryption mode, and all three options (engine default, platform default, ppc32003 compatibility) yielded the same results, that SqlCeException being thrown which in fact I was expecting because logically there should not be anything that I need to change in the code as the same piece works perfectly in IIS 7.

Resolution:

I continued my research and figured out that in IIS 7.5 the Default Application Pool (My Sql CE app is running under the Default App Pool) runs under an identity called Application Pool Identity.

This is a virtual user account that is introduced to simplify user management and it seems to lack the required privileges to connect to Sql CE Database. Please read more about this below:

https://learn.iis.net/page.aspx/624/application-pool-identities/

To confirm on my understanding, I compared the working IIS and found that in all the working scenarios, my application pool was running under NetworkService account. I changed the App pool identity in my IIS 7.5 Server, recycled it and when I re-ran my application voila!! It works !!

I am yet to figure out what exactly are the differences in terms of permission/authentication when we use the new Virtual User identity in IIS 7.5 and yet to do the root cause analysis of the error (may be granting some permissions/roles to the Virtual User would also resolve this error and run it successfully under Application Pool Identity as well), but I hope that this post is going to help our users to at least resolve the error and get everything up and running in a timely manner. Once I do the RCA and figure out the actual reason for the error, I would update my post.