I am trying to connect to a new instance of SQL Server 2008 R2 Express using web config for WCF web service. We use IIS 7.5 and .net 4.0. Everything is local (i.e. - all on the same machine - the database and the webservice). We also have a default instance of SQL Server 2008 R2 and another instance of SQL Server 2008 R2 express all on this same machine (these two are actually not used anymore but are still on the machine). The web config we are using looks like below (we use sql authentication):
Using SSMS, I can connect with the MachineName\Instance or as I have above .\Instance. Either works with SSMS.
Get the error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)|DataAccess|ExecuteNonQuery"
Mentions pipes in the error message but we are using the shared memory. But I did try turning on pipes - didn't work.
All of this worked when we were using the default instance of SQL Server 2008 R2 trial version but that expired (just dev machine) so we had to setup the Express version which we can't get to work.
Tried all of the typical things to try for the connection issue even though most of those are for remote connections. I know the instance is running. I've allowed remote connections (but shouldn't be needed). Do I still need to open ports if this is a local version? What else can I try? Thanks!
