SQL Server encryption using self signed SSL certificate. Querying from ASP.NET 3.5.

Question

I've created a self-signed cert for testing encryption between my web application and the SQL Server.

When attempting to query the database using "Encrypt=Yes;" in the connection string, I receive the following message:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Background
I received an identical message when first attempting an encrypted connection from management studio. This was resolved by installing the self-signed cert into my Trusted Certificate Authorities.

Question
Is there a way I can get ASP.NET to trust the certificate the same way my user account does?

Answer 1

Using impersonation works on my local development machine: <identity impersonate="true"/> (in system.web of my web.config)

Thus ensuring that when ASP.NET connected to the database, it used my user credentials, and my user trusts the self-signed cert.

Note - this fails if attempting to view the site on the development server.

Answer 2

OK the proper answer for this lay in adding the self-signed cert to the certificate store.

The wrong way
Installing the certificate by double-clicking the .cer file on the server
- This adds the cert for the currently logged in user only, which is why impersonation worked in some cases.

The right way
Using CertMgr.exe to install the certificate.
- You can find CertMgr.exe in a Windows SDK, or apparently in Visual Studio 2005's bin folder. It's not in VS2008.
- You must run CertMgr.exe under a Local Machine Administrator account. A Domain account with local administrator privileges will not work
- Run CertMgr.exe to add the certificate to the localmachine trustedpublishers stores, by running both of the following commands:
- certmgr /add Your.Certificate.Filename.cer /s /r localmachine root
- certmgr /add Your.Certificate.Filename.cer /s /r localmachine trustedpublisher

Also note you can't use wildcards when referring to the certificate filename. (/add *.cer will fail.)