Orcas March CTP on Vista Database Connections Problem Solved

An anonymous commenter provided the solution to the connection string problem I reported in my March 04, 2007 Orcas March 2007 CTP Installation Problems post: If you set up the Self-Extracting Install version of the Orcas March 2007 CTP on Windows Vista without installing SQL Server 2005 Express, you can't persist Data Connections in Server Explorer. Installing SQL Server 2005 [Express] under any current Windows OS breaks the Project Designer's Properties page and the Workflow Designer. Update 4/25/2007: This fix does not apply to Orcas Beta 1, which doesn't exhibit the preceding problems. See SQL Server Compact Edition FAILS to connect in Server Explorer with Beta 1 in the ADO.NET Orcas forum. Update 3/7/2007: Windows Vista isn't the culprit. "Anonymous" says that the connection string problem also occurs with—and the solution works for—Windows XP SP2. My installation on Windows Server 2003 R2 SP1 under Virtual Server 2005 R2 SP1 Beta 2 didn't have the problem. Inspecting that machine.config file shows that the offending line commented out below wasn't present. I believe I omitted Orcas support for devices from that install. I'll run another test with Windows XP SP2 as the guest OS later this week to confirm. Here's the full text of the comment:

The issue with SQL Connection strings is much larger than what you elude to. It also breaks almost all visual database design tools in Orcas, SQL Server Management Studio and Visual Studio 2005. This includes the table designer, database diagrammer and the view designer.

I have good news though. There is a fix. If you open up the machine.config file you will notice multiple entries for the SQL CE stuff in the system.data section. Comment these out (this will break SQL CE, which is fine for most people) and your SQL Server editing will return. I believe if you comment out 2 of the 3 leaving the version 9 dll intact the SQL CE stuff will remain working.

The commenter is correct. To enable persisting Data Connections in Server Explorer under Windows Vista comment out the line shown in bold from \Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine[.config]:

<system.data>
  <DbProviderFactories>
    <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <!-- <add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Everywhere Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> -->
    <add name="SQL Server Compact Edition Data Provider" invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Compact Edition" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    <add name="SQL Server Compact Edition Client Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server Compact Edition Client" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
  </DbProviderFactories>
</system.data>

The SQL Server CE Data Provider element (for v3.1 disguised as v9.0.242.0) appears to conflict with the SQL Server Compact Edition (SSCE) Data Provider v3.5.0.0, which is the correct version for Orcas March 2007 CTP, when running under Windows Vista. Version 3.5 supports the Microsoft Synchronization Services (OCS) CTP, has new data types, and other improvements. Note: The machine.config file is present only in the .NET Framework 2.0's CONFIG folder. The other framework folders (1, 1.1, 3.0, and 3.5) don't have a CONFIG folder.

Here's a screen capture of the line to edit in your machine.config file (click for full size image):

And just to prove that the fixes work, here's a screen capture of Server Explorer showing two remote SQL Server 2005 Express instances and a local SQL Server 2005 Compact Edition Test.sdf file created when the connection was made (click for full size image):

Caution: Installing Orcas March 2007 CTP without installing SQL Server 2005 Express (SSX) requires that you change the connection strings for many sample applications, such as those for LINQ to SQL, from local user instances of Northwind.mdf to remote instances of the Northwind sample database. My Updated Orcas March 2007 CTP LINQ C# Samples post provides an example for the Sample Queries project's LinqToSqlSamples class.

Note: Here's a link to an ADO.NET Orcas post from a tester with the problem solved here. Two more confirmations here and here.

Update 3/19/2007: Use Orcas's Server Explorer, not SSMS, to manipulate SSCE data files. Server Explorer's Show Table Data context menu choice lets you edit SSCE table data.

Laxmi Narsimha Rao ORUGANTI from Microsoft India posted the following in the Microsoft Synchronization Services for ADO.NET forum regarding SSCE versioning issues:

With SQL Server 2005 (RTM, SP1, SP2), the version of SQL SERVER COMPACT EDITION integrated with it is 3.0/3.1 (Note: DLL versions would still remain as 3.0.xxxx.0). SQL Server CE 3.5 which is part of ADO.NET Sync Services CTP is not integrated with database management tool SSMS. We would encourage you to use ORCAS to manipulate the Sync Services databases created on client side. SQL Server CE 3.5 is fully integrated with ORCAS. Use Database Explorer (or Server Explorer). We are also working towards to integrate with Orcas Express editions but nothing is concrete or materialized yet so no promises.

Technorati tags: Orcas March 2007 CTP, Orcas, Windows Vista, Vista, Data Connections, SQL Server 2005 Compact Edition, SQL Server 2005 CE, SQL Server CE, SSCE, LINQ to SQL, LINQ to Entities