blog.edlington.net

Resolving: “A network-related or instance-specific error occurred while establishing a connection to SQL Server” error ASP.NET .NET MSSQL LINQ

Our recent ASP.NET project has began to grow bigger and bigger, so yesterday I decided to grab all business logic from the project, separate to components and put them into class libraries. Mostly I used some automated copying process, but in ‘ClientManagerClassLibrary’ I had to recreate my ‘ClientProfile’ LINQ to SQL class using the built-in wizard in Visual Studio 2008 SP1 (…sure you know the process: Add new item -> LINQ to SQL class -> dropping the table and stored procedures into designer -> Save). After separating, the project still worked fine on my developer machine, not like on my server. I got this error:

SQL Server 2005 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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) ”

I could not imagine what the hell is wrong with SQL Server. My connection string and SQL configuration the same on both machine and I didn’t modify that. So what else?
The solution have found in LINQ codebehinds.

Here’s the file structure of my class library:

Well, let’s take a look at the DataContext method inheritance in ClientProfile.designer.cs codebehind:

ClientProfile.designer.cs:

public ClientProfileDataContext() :
	base(global::ClientManagerClassLibrary.Properties.Settings.Default.gattohu_gatto_lightConnectionString, mappingSource)
 { 
	 OnCreated(); 
 }

Settings.Designer.cs:

[global::System.Configuration.ApplicationScopedSettingAttribute()]
[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.Configuration.SpecialSettingAttribute(global::System.Configuration.SpecialSetting.ConnectionString)]
[global::System.Configuration.DefaultSettingValueAttribute("Data Source=LEV-DEV\\SQLEXPRESS;Initial Catalog=gattohu_gatto_light;Integrated"+" Security=True")]
public string gattohu_gatto_lightConnectionString
{
	get
	{
		return ((string)(this["gattohu_gatto_lightConnectionString"]));
	}
}

As may you suppose, the only place we’ll define the connection string is the web.config file, so comment this whole stuff out (or delete it)!

web.config:

<add connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=gattohu_gatto_light;..." />

Returning to designer.cs, modify that to get the connection string from the web.config file:

ClientProfile.designer.cs:

public ClientProfileDataContext() :
	base(global::System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString, mappingSource)
{
	OnCreated();
}

If Visual Studio complaining about ConfigurationManager, you need to add reference to the System.Configuration .NET assembly.

7 more things to check if the error still present:

1. Make sure your database engine is configured to accept remote connections

Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
Click on Surface Area Configuration for Services and Connections
Select the instance that is having a problem > Database Engine > Remote Connections
Enable local and remote connections
Restart instance

2. Check the SQL Server service account

If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application

Usually the format needed to specify the database server is machinename\instancename
Check your connection string as well:
<connectionStrings> <add name=”SampleConnectionString” connectionString=”Data Source=machinename\instancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName=”System.Data.SqlClient”/> </connectionStrings>

4.You may need to create an exception on the firewall for the SQL Server instance and port you are using

Start > Run > Firewall.cpl
Click on exceptions tab
Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn), and port (default is 1433)
Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

7. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver

Start > Run > cmd
netstat -ano| findstr 1433
telnet myserver 1433
ping -a myserver Check what ports are IP addresses are being returned.

Comments