Entity Framework, Pre-Login Handshake and SSL Certificate Chain Errors

During the process of constructing a data access layer for a SharePoint project I kept running into SSL errors trying to construct my data model using the Entity Framework. I began the project with an Azure database which, surprisingly enough, worked like a champ – Visual Studio 2010 picked right up on my connection string and imported my DB structure without incident. When I tried to switch over to a SQL instance on my development SharePoint farm, however, things took a turn for the worst. Every time I tried to connect to the database to generate my model I received the following error:

"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.)"

After wasting a bunch of time searching around the interwebs for a solution, it became apparent that the self-signed certificate on the remote SQL instance was the cause of my headaches. Self-signed certs are fine on a local box but no good at all when you try to access the SSL port from a different machine. Trouble is, I couldn’t run Visual Studio on the actual server as the Azure components don’t play nice with SharePoint, so switching over to the same box with the cert on it wasn’t an option. After some fiddling around with the entity framework connection string (not the database connection string), I was able to actually get data from a database that matched the Azure schema on my dev SQL instance by adding "trustServerCertificate=True" to the connect parameters (thanks to Wade Wegner). That was a step in the right direction, proving that VS actually could communicate with SQL regardless of SSL certificates, but I had created a new database schema and needed to generate an entirely new entity model – all attempts to do so were still blocked by the database import wizard on the last step:

I knew the EF connection string would work if I could just get past the wizard. So I generated an empty model and tried to update it from the database – same wizard, same results. Then it occurred to me that the model is just an XML file with an .edmx extension. Surely the connection string was specified in there somewhere. So I compared the working model file (that was originally generated from the Azure database) against the empty model file I had just created and found the entity connection string name specified in two places – <edmx:ConceptualModels>/<Schema>/<EntityContainer> and <edmx:Mappings>/<Mapping>/<EntityContainerMapping>. I’m not Entity Framework expert by any stretch of the imagination so I wasn’t sure what the implications of changing these settings would be but since all I had was an empty model anyway I figured I might as well try it. So I modified each setting to the following:

After saving the .edmx file, I opened the project again in Visual Studio, right-clicked on the empty model and selected "Update model from database". Success! I could now import all my tables and stored procedures. While the wizard wouldn’t recognize the remote server and generate the proper connection string a pre-defined string with the right variables worked just fine. For reference, this is what the functional connection string looked like:

If you run into the same issue I would recommend creating a simple database first in a local instance (such as SQL Express) and generating a data model from just to get a valid entity connection string. Then copy that string into a new web.config entry, change the name and server/credentials, create a new empty model, close visual studio, edit the .edmx file, put the name of the modified entity connection string into the correct EntityContainer and EntityContainerMapping nodes, then re-open the project and update the empty model from the correct database.

Hope that helps!