-1

I'm working on a project using SQL Server 2008 Express. I had an instance called SQLEXPRESS and I used to connect to the DB via this connexion string :

<add name="MyConn" connectionString="metadata=res://*/MyDataModel.csdl|res://*/MyDataModel.ssdl|res://*/MyDataModel.msl;provider=System.Data.SqlClient;
provider connection string=&quot;data source=PCNAME\SQLEXPRESS;initial catalog=DbName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

This connection string was generated by .edmx file (I'm working on an ASP.NET MVC project, database first, and Entity Framework) and it worked fine.

I recently installed SQL Server 2016 with a Developer Licence. During the install process, I used the default instance, which is MSSQLSERVER. For information, the generated InstanceID is MSSQL13.MSSQLSERVER.

I have a SQL project in my solution to regenerate my tables on my new SQL Server; I could connect easily to the server via the wizard.

I also used SQL Server Management Studio to verify everything was fine on the server, and it works perfectly (tables are generated, connection was OK).

However, my code is unable to connect to the server and systematically failed to the first call with error 26 - can't locate server instance

Here is what I've tried so far:

  • Change the data source parameter of the connexion string to localhost , ., .\MSSQLSERVER, .\PCNAME, PCNAME.MSSQLSERVER, PCNAME.MSSQL13.MSSQLSERVER, DOMAIN.MSSQLSERVER, DOMAIN\PCNAME => Doesn't work
  • Verify SQL Service are running => They are
  • Create an .udp file, connect to my server, and copy/paste the generated connection string : Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data Source=PCNAME => Doesn't work
  • Regenerate my .edmx file (and the connection stirng) => Doesn't work
  • Verify this suggestion => It was already correct

Why does all the connection wizards seems to work (SSMS, .udl file, .edmx generator, my DB project), whereas my code is unable to reach the server? I didn't change any line of code.

EDIT : The crazy thing is I have a log database on the same server, the connexion string is

<add name="NLogConn" connectionString="Data Source=localhost;initial catalog=DBLOG;integrated security=True" providerName="System.Data.SqlClient" />

And it is actually logging the network error !! So is it really a connection string error or an EF error ? I'm using the latest EF version, 6.1.3 on each project referencing EF (The main and the DAL layers)

user2687153
  • 427
  • 5
  • 24

1 Answers1

0

Before going off the deep end on what it could be let's start with a working connection string I just tested on my local machine on Visual Studio 2017 Enterprise with Entity Framework 6.1.3.

<connectionStrings>
    <add name="TesterEntities" connectionString="metadata=res://*/Tester.csdl|res://*/Tester.ssdl|res://*/Tester.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=Tester;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

The marked up connection string is:

=&quot;data source=.;initial catalog=Tester;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;

For my test connection I am using my local server for a default instance also of 2016 developer edition(. is abbreviation for this). I am using integrated security and my database name(initial catalog) I am targeting is Tester.

There are a few things to consider if Entity Framework(EF) is not properly hitting a target.

  1. What version of EF are you using? Before MS moved to Core, I believe the last .NET full edition of EF was 6.1.3. Ensure you are on the most current version on Nuget download.
  2. If you go from Express to a full fledged SQL the connection string's 'provider' often will not be the same I believe. Or it could be the local default instance before sql is installed is different. Ensure you don't overwrite an existing connection string with this. The provider should be: 'provider=System.Data.SqlClient'
  3. You are referencing a project of EF in ANOTHER project. Each project that references EF should be having the NuGet package for EF as well as a config file with the connection string. EG: I have a console project for testing things I write and reference another project called 'EF Testing' and I don't have an app config with a connection string. It won't work.
djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • 1 - I'm using EF 6.1.3, the latest version. 2 - Correct me if I'm wrong, but if i use EF, I guess i MUST use System.Data.EntityClient as provider ? 3 - Each project referencing EF has its own config file (only my main project and the DAL) and I verified everywhere the connexion string was the same. Also, I made an edit to my original post – user2687153 Jun 12 '17 at 15:31
  • The connection string you gave in your example is not an EF connection string. You need all the metadata: 'metadata=res://*/Tester.csdl..etc' to show that this connection string is referencing generated files made by EF. – djangojazz Jun 12 '17 at 15:33
  • I copied/pasted all the `metadata=res://` in the first one I gave `MyConn` (which seems not working). The other one, indeed, is not generated by EF and doesn't need these metadatas. – user2687153 Jun 13 '17 at 07:09
  • Well to use EF you need that so saying another configuration works, do you use ADO.NET? Also you look like you have ran a lot of connections. Have you tried creating a new edmx file and then hitting 'New Connection'? When you do the discovery it should be doing a test connection for you. It cannot create objects on the EDMX surface unless it can connect in the first place. – djangojazz Jun 13 '17 at 16:45