0

I have a C# app written using Entity Framework 4.3.1.0 that talks to a SQL Server 2008 user instance on my development machine in Visual Studio 2010. It also works on my laptop in Visual Studio. But on the customers desktop PC, with SQL Server 2008 R2 I can't connect to the database with the application. The error is

The underlying provider failed on Open

All three computers are Windows 7 64-bit, but the app is 32 bit.

I suspected the connection string in myapp.exe.config, but it is the same as the one I use in Visual Studio.

In doing the research I have come up with two possibilities.

  1. Does the same connection string work with SQL Server 2008 and SQL Server 2008 R2?
  2. Does the location of the .mdf / .ldf files matter, I initially installed them in the application folder, but there may be a better place, like a predefinded AllUsersAppData. Is the application folder read-only for the server files?

Here is the ConnectionStrings section from app.config. I don't know if both are required.

  <connectionStrings>
    <add name="DataClass.Properties.Settings.SWDatabaseEntities" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SWDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
    <add name="SWDatabaseEntities" connectionString="metadata=res://*/SWDatabaseModel.csdl|res://*/SWDatabaseModel.ssdl|res://*/SWDatabaseModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQLEXPRESS;attachdbfilename=|DataDirectory|\SWDatabase.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

It started working for some reason after I removed an errant from mcahine.config that an IBM Client had installed. It didn't work right away, but I don't know what else changed. Maybe it was after a reboot.

Rich Shealer
  • 3,362
  • 1
  • 34
  • 59
  • 1
    You should store your data files in SQL Server's data file directory (usually under C:\Program Files\SQL Server\...) and *not* under some user's app data folder. – Aaron Bertrand Aug 15 '12 at 21:14
  • Since this is a `USER INSTANCE` is that still true? What would the folder be called in Setup folder hierarchy? – Rich Shealer Aug 15 '12 at 21:40
  • 1
    Why do you need to use the deprecated `User Instance` feature? Are you using this feature on purpose, and do you understand how it works? Anyway, yes whether you are using the User Instance feature or not, you can place the MDF files in any folder and point the connection string there. But if you're connecting through a live instance that needs to be a folder that the instance can see. Typically this doesn't work by default for a user's profile directory. – Aaron Bertrand Aug 15 '12 at 21:41
  • I didn't know it was deprecated. It was the default in the Entity Frameworks that I set up. It runs a instance of SQL Server with the users credentials. I'm new EF and thought it was normal. – Rich Shealer Aug 15 '12 at 21:45
  • Your setup program may need to determine that at runtime, because I can customize my data folder during SQL Server installation and I can change the default for new databases after installation. – Aaron Bertrand Aug 15 '12 at 21:45
  • http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx – Aaron Bertrand Aug 15 '12 at 21:46
  • could you post up your connection string out of curiosity? What is your app compiled to? x86, Any CPU, etc? – Phil Aug 16 '12 at 05:44
  • @Phil - I added the connection strings. I'm a bit hazy on how they get selected for use. – Rich Shealer Aug 22 '12 at 03:07
  • You shouldn't need to specify the AttachDbFilename parameter if you're using sql express. Just use the initial catalog and the sqlserver service should know where the .mdf and .ldf files are by itself – Phil Aug 22 '12 at 04:30
  • @Phil By default I think by default goes to the application folder. I'm changing this so that the data is in the SQL Server Data folder now and doing away with the User Instance. – Rich Shealer Aug 22 '12 at 08:54
  • it doesn't go to the application folder by default. when you create a database in your server instance, you can change where the .mdf and .ldf files exist in your 'create database' script. SQL server then remembers where this .mdf exists. I do this all the time with regular sql server (A lot of my clients require .mdf and .ldf files to reside on different hard drives, in different folders), i can't believe it would be different for sql express. – Phil Aug 22 '12 at 14:03

0 Answers0