[RESOLVED] A network-related or instance-specific error occurred while establishing a connection

[weirddemon]

I'm using the Web Site Administration Tool to create users, set roles, etc, all on my development machine. When done, I'm publishing everything to the server.

On my dev machine, everything works great. But after publishing, I either get that error, or everything works without having to log in.

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)

After I published the files, I noticed the App_Data folder wasn't publishing. Which looks like it has the database. So I copied it over, but still no dice.

I Googled this and it seems the most common issue is the connection string. The connection string on the local machine is different than on the server. Which makes sense. But there are no connection strings in the web.cofig file. At least not for this anyway. I have two setup for a different server, but that's irrelevant.

Does anyone know how I'm supposed to specify the connection string, if I'm even supposed to? Or any ideas what else might be the issue?

[sapator]

Hi.
The web site administration tool,that i believe is the asp.net tool you use will probably create your database as a file in the App_Data as you are writing here.
Is this what you want or do you want to put the database in an sql server on the published machine?
If you want the file on the App_Data then your connectionstring on web.config would look somehting like this:

</configSections>
	<connectionStrings>
		<add name="DatabaseConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>

If you want the data in an sql server then it will look like this:

<connectionStrings>
		<add name="SqlServerConnectionStringTest" connectionString="Data Source=MY-SQLSRV\;Initial Catalog=Database2;Integrated Security=True" providerName="System.Data.SqlClient"/>		
	</connectionStrings>

[weirddemon]

Hi.
The web site administration tool,that i believe is the asp.net tool you use will probably create your database as a file in the App_Data as you are writing here.
Is this what you want or do you want to put the database in an sql server on the published machine?
If you want the file on the App_Data then your connectionstring on web.config would look somehting like this:

</configSections>
	<connectionStrings>
		<add name="DatabaseConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>

If you want the data in an sql server then it will look like this:

<connectionStrings>
		<add name="SqlServerConnectionStringTest" connectionString="Data Source=MY-SQLSRV\;Initial Catalog=Database2;Integrated Security=True" providerName="System.Data.SqlClient"/>		
	</connectionStrings>

Sap,

That sounds like what I need, but I'm not sure where to use the connection string. I can't figure out where the web tool sets this data. Somewhere, it has to have a connection string like this, so it knows where to pull the data from, but I can't find it.

Any ideas?

[sapator]

If i understand correctly you are looking for the connection string?
Connection string will be at web.config .
If you don't see that then you must set it manually but i believe asp tool set that for you.
Note that if decide to bypass the asp.net tool and integrate the asp database to an sqlserver then i believe you will have to set it manually again in web.config.
If i didn't understood your question correctly let me know.

[weirddemon]

If i understand correctly you are looking for the connection string?
Connection string will be at web.config .
If you don't see that then you must set it manually but i believe asp tool set that for you.
Note that if decide to bypass the asp.net tool and integrate the asp database to an sqlserver then i believe you will have to set it manually again in web.config.
If i didn't understood your question correctly let me know.

I'll try to clarify.

When I use the web tool to create users, set roles, etc, it's storing this info in the database it creates in the App_Data folder. Somewhere, it has to have a connection string so it knows where to find the database. But I looked in the web.config file and there isn't a connection string.

So I need to know where it stores this info. Unless I've got this all wrong.

[sapator]

Hi.
Yes you will probably have to add this manually.
Reference to my first post and change the names accordingly.I would suggest however to use an sql server for this and not you data directory.

[sapator]

Hmm.
Also how do you control your membership?
Search the web.config for the membership provider and the connectionstring alias will be specified there.If you can't find anything then post you web.config here.I have a 3 day off but someone will be able to help you.

[weirddemon]

The following is my web.config, almost exactly as it appears live. There's nothing here that points to what the tool is doing.

<?xml version="1.0"?>
<configuration>
	<configSections>
		<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
			<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
				<section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
				<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
					<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere" />
					<section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
					<section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
					<section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
				</sectionGroup>
			</sectionGroup>
		</sectionGroup>
	</configSections>
	<appSettings/>
	<connectionStrings>
		<add name="inventoryAddModel" connectionString="Data Source=OSRESTORE;Initial Catalog=inventory;Persist Security Info=True;User ID=Inventory;Password=#CTSeng1"
            providerName="System.Data.SqlClient" />
		<add name="inventoryGetInventory" connectionString="Data Source=OSRESTORE;Initial Catalog=inventory;Persist Security Info=True;User ID=Inventory;Password=#CTSeng1"
            providerName="System.Data.SqlClient" />
	</connectionStrings>

	<system.web>
		<customErrors mode="Off"/>
		<!-- 
            Visual Basic options:
            Set strict="true" to disallow all data type conversions 
            where data loss can occur. 
            Set explicit="true" to force declaration of all variables.
        -->
		<roleManager enabled="true" />
		<compilation debug="true" strict="false" explicit="true">
			<assemblies>
				<add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
			</assemblies>
		</compilation>
		<!--
      The <authentication> section enables configuration 
      of the security authentication mode used by 
      ASP.NET to identify an incoming user. 
    -->
		<authentication mode="Forms" />

		<!--
       The <customErrors> section enables configuration 
       of what to do if/when an unhandled error occurs 
       during the execution of a request. Specifically, 
       it enables developers to configure html error pages 
       to be displayed in place of a error stack trace.

       <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
         <error statusCode="403" redirect="NoAccess.htm" />
         <error statusCode="404" redirect="FileNotFound.htm" />
       </customErrors>
    -->
		<pages>
			<namespaces>
				<clear />
				<add namespace="System" />
				<add namespace="System.Collections" />
				<add namespace="System.Collections.Generic" />
				<add namespace="System.Collections.Specialized" />
				<add namespace="System.Configuration" />
				<add namespace="System.Text" />
				<add namespace="System.Text.RegularExpressions" />
				<add namespace="System.Linq" />
				<add namespace="System.Xml.Linq" />
				<add namespace="System.Web" />
				<add namespace="System.Web.Caching" />
				<add namespace="System.Web.SessionState" />
				<add namespace="System.Web.Security" />
				<add namespace="System.Web.Profile" />
				<add namespace="System.Web.UI" />
				<add namespace="System.Web.UI.WebControls" />
				<add namespace="System.Web.UI.WebControls.WebParts" />
				<add namespace="System.Web.UI.HtmlControls" />
			</namespaces>
			<controls>
				<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			</controls>
		</pages>

		<httpHandlers>
			<remove verb="*" path="*.asmx"/>
			<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
		</httpHandlers>

		<httpModules>
			<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
		</httpModules>
	</system.web>

	<system.codedom>
		<compilers>
			<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4"
       type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
				<providerOption name="CompilerVersion" value="v3.5"/>
				<providerOption name="OptionInfer" value="true"/>
				<providerOption name="WarnAsError" value="false"/>
			</compiler>
		</compilers>
	</system.codedom>

	<!-- 
        The system.webServer section is required for running ASP.NET AJAX under Internet
        Information Services 7.0.  It is not necessary for previous version of IIS.
    -->
	<system.webServer>
		<validation validateIntegratedModeConfiguration="false"/>
		<modules>
			<remove name="ScriptModule" />
			<add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
		</modules>
		<handlers>
			<remove name="WebServiceHandlerFactory-Integrated"/>
			<remove name="ScriptHandlerFactory" />
			<remove name="ScriptHandlerFactoryAppServices" />
			<remove name="ScriptResource" />
			<add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode"
     type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode"
     type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
		</handlers>
	</system.webServer>
	<runtime>
		<assemblyBinding appliesTo="v2.0.50727" xmlns="urn:schemas-microsoft-com:asm.v1">
			<dependentAssembly>
				<assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
				<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
			</dependentAssembly>
			<dependentAssembly>
				<assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
				<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
			</dependentAssembly>
		</assemblyBinding>
	</runtime>

	<system.net>
		<mailSettings>
			<smtp from="...">
				<network host="..." port="25" userName="..." password="..." defaultCredentials="true" />
			</smtp>
		</mailSettings>
	</system.net>
</configuration>

It's confusing the heck out of me.

[sapator]

Right.
2 reason i can think of.
1)As you haven't specified the membership settings and connectionstring the membership is derived from the default machine.config of you computer.
2)The tools is programmed with a standard asp.net database and it is using the default membership provider so by default it works.
Can you try and set the membership on your own?You need a custom membership provider and custom role provider,then bind your database on the membership provider.
If you need help on that one let me know.
P.S. How about testing it on sql, there is no way on earth that it will just work without you doing nothing on an sql server db.

[weirddemon]

Right.
2 reason i can think of.
1)As you haven't specified the membership settings and connectionstring the membership is derived from the default machine.config of you computer.
2)The tools is programmed with a standard asp.net database and it is using the default membership provider so by default it works.
Can you try and set the membership on your own?You need a custom membership provider and custom role provider,then bind your database on the membership provider.
If you need help on that one let me know.
P.S. How about testing it on sql, there is no way on earth that it will just work without you doing nothing on an sql server db.

Yeah, I'm going to need help with the membership provider. I've tried looking this up, but I can't seem to find any new relevant information.

I created my connection string:

<connectionStrings>
	<add name="memberSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

Setup the authentication (in system.web):

<authentication mode="Forms">
			<forms loginUrl="login.aspx"
					protection="All"
					timeout="30"
					name="AppNameCookie"
					path="/FormsAuth"
					requireSSL="false"
					slidingExpiration="true"
					defaultUrl="default.aspx"
					cookieless="UseCookies"
					enableCrossAppRedirects="false" />
		</authentication>

And then the very basic authorization:

<authorization>
			<!--<deny users="?" />-->
			<allow users="*" />
		</authorization>

So now I have two problems. I'm not sure how to create the membership provider, which will link my data to the database via the connection string, correct? And The authorization is killing me.

Every time I try setting up a role base authorization, it exception occurs on the location node. It tells me that the location node isn't a child node of system.web. But, everything I've read says I'm supposed to put it there. And putting it outside of system.web, never worked before.

Any ideas?

[weirddemon]

Never mind about the authorization. It works outside system.web now. But I'm still not sure about the membership provider and how I'm supposed to link everything with the connection string and DB.

[weirddemon]

I'm making some progress it looks like. I'm using the following membership provider that I found in... someone's codebank entry.

<membership>
			<providers>
				<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="memberSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>
			</providers>
		</membership>

Before, the website would not even load. It threw a 500 server error. No it loads, except when I try to log in, I get an error:



Is there anything I need to do with the log in control? Or am I missing something else?

[weirddemon]

So I decided to try and simplify the membership string and came up with this:

<membership>
            <providers>
                <add name="DWUR_Dashboard_Provider"
                     type="System.Web.Security.SqlMembershipProvider"
                     connectionStringName="memberSqlServer"
                     applicationName="DWUR_Dashboard"
                     minRequiredPasswordLength="5"
                     minRequiredNonalphanumericCharacters="0" />
            </providers>
        </membership>

But now I'm coming back to my original issue. Which I'm not sure is a good thing or a bad thing.

[sapator]

Hi.
This is a helpful thread you must look at.
http://www.vbforums.com/showthread.php?t=629477

Report back with issues.
P.S. Also can you answer me about the sqlexpress thing?Is it mandatory?

[gep13]

I'll try to clarify.

When I use the web tool to create users, set roles, etc, it's storing this info in the database it creates in the App_Data folder. Somewhere, it has to have a connection string so it knows where to find the database. But I looked in the web.config file and there isn't a connection string.

So I need to know where it stores this info. Unless I've got this all wrong.

Based on the above, it sounds very much like you are using SQL Server Express Edition, as sap has mentioned. During the installation of Visual Studio on your machine SQL Server Express would have been installed, so on your machine, things just work.

In order for this to work on your other machine, you are going to need SQL Server Express installed. If this is a production server, then using SQL Server Express is not a good idea, instead you should be using a full instance of SQL Server. In order to do that, you are going to need to take the schema and data from the SQL Server Express database and migrate it into a database under SQL Server.

Can you confirm that you are using a full SQL Server instance on your other system?

Gary

[weirddemon]

Based on the above, it sounds very much like you are using SQL Server Express Edition, as sap has mentioned. During the installation of Visual Studio on your machine SQL Server Express would have been installed, so on your machine, things just work.

In order for this to work on your other machine, you are going to need SQL Server Express installed. If this is a production server, then using SQL Server Express is not a good idea, instead you should be using a full instance of SQL Server. In order to do that, you are going to need to take the schema and data from the SQL Server Express database and migrate it into a database under SQL Server.

Can you confirm that you are using a full SQL Server instance on your other system?

Gary

Hm... that would make sense. However, SQL Server Express is installed on my PC and the server. So, that shouldn't cause an issue, right? That's what you're getting at correct, Gary?

[gep13]

Hello,

If it is installed on both, then it "should" just work, however, depending on whether you are fully overriding the setting for the Membership Provider in your web.config file, ASP.Net will fallback to using the settings contained within the machine.config in the .Net Framework folder on the machine.

To ensure that you have the correct connection string, you can look here:

http://connectionstrings.com/

Gary

[weirddemon]

Hello,

If it is installed on both, then it "should" just work, however, depending on whether you are fully overriding the setting for the Membership Provider in your web.config file, ASP.Net will fallback to using the settings contained within the machine.config in the .Net Framework folder on the machine.

To ensure that you have the correct connection string, you can look here:

http://connectionstrings.com/

Gary

I am so confused. I have an idea of how this is supposed to work, but it's not. Which is throwing doubts into this even more.

I started brand new project. My boss wanted me to change the UI anyway, so I just started a new project. Thankfully, the web app isn't all that large at the moment.

I started off with the MS template. So it had everything in place, the membership role and everything. All I did was create a user and a role in the ASP.NET Configuration tool. I copied the data to the server and got the same error.

But this time, I attached the database in VS and looked through the tables. It's all empty. There's no data to be found. However, when I log in in my local PC, it works fine.

So how is there no data in the database, but the log in works fine in my local PC? Does this mean that the app is defaulting to a different database?

[sapator]

I am so confused. I have an idea of how this Does this mean that the app is defaulting to a different database?

Probably.I now see u use 4.0 framework so since i haven't touched it in full i cannot advise on something different in this one.I personally wouldn't have used the auto features for membership and role.
Post the web.config of your local pc and of your work pc and the machine.config of your localpc and work pc.
I won't ask you again about sql server but if you are not bound to sqlexpress your are making it difficult for yourself for no reason.

[weirddemon]

Probably.I now see u use 4.0 framework so since i haven't touched it in full i cannot advise on something different in this one.I personally wouldn't have used the auto features for membership and role.
Post the web.config of your local pc and of your work pc and the machine.config of your localpc and work pc.
I won't ask you again about sql server but if you are not bound to sqlexpress your are making it difficult for yourself for no reason.

I pretty much love you, sapator

I saw your question about sql server earlier, but wasn't sure in what reference it was to.

I never thought about attaching the database to the server because the connection string that was automatically generated, looked like it was pulling it from the App_Data folder.

But, I went ahead and attached to SQL server, changed my connection string accordingly and like magic, it worked perfectly.

Thanks everyone for your help

[sapator]

That's good news!
Glad we could help you out.

[gep13]

Hello,

This resolution suggests to me that SQL Server Express is not installed on the server, as you said it was. In which case, the only two things that you could have done would have been the following:

1. Mount the SQL Server Express Database into your SQL Server Instance (which you have done)
2. Script the SQL Server Express Database to a SQL file and run this on your SQL Server instance in order to create a new database (this would be my preference).

Bottom line, the combination of Visual Studio and SQL Server Express makes the development of a dynamic web site very easy, however there are some complexities, as you have experienced, when it comes to deploying this.

Gary

[weirddemon]

Hello,

This resolution suggests to me that SQL Server Express is not installed on the server, as you said it was. In which case, the only two things that you could have done would have been the following:

1. Mount the SQL Server Express Database into your SQL Server Instance (which you have done)
2. Script the SQL Server Express Database to a SQL file and run this on your SQL Server instance in order to create a new database (this would be my preference).

Bottom line, the combination of Visual Studio and SQL Server Express makes the development of a dynamic web site very easy, however there are some complexities, as you have experienced, when it comes to deploying this.

Gary

SQL Server Express is installed on the server. There is absolutely no doubts about that. I'm the one who installed it. I use it every day to manage the databases.

[gep13]

In which case, the only thing that could be remaining is that the SQL Server Express instance name isn't the same as the default instance name provided within the default web.config configuration for the ASP.Net Membership provider, otherwise the connection string should have worked.

Gary

[weirddemon]

In which case, the only thing that could be remaining is that the SQL Server Express instance name isn't the same as the default instance name provided within the default web.config configuration for the ASP.Net Membership provider, otherwise the connection string should have worked.

Gary

Hm... yeah, that makes sense. But that seems a bit odd. Oh well, though. It works now, so I'm not worried about it.

Thanks again

[gep13]

Given the choice, as stated, I would be creating a new SQL Server Database, and importing the content from the SQL Server Express Database, but that is just me.

Gary

[weirddemon]

Given the choice, as stated, I would be creating a new SQL Server Database, and importing the content from the SQL Server Express Database, but that is just me.

Gary

What would be the benefit? In the end, it all works the same, right? I can't see the difference of using this auto generated database or making a fresh one in SQL Server.

[gep13]

All you have done is attach a SQL Server Express Database to a SQL Server instance, there will still be limitations as to what that underlying file can do, i.e. size of database, optimisations, etc.

Gary

[bhaskarareddy]

hi
refer this link

it will give brief explanation and how to resolve error

http://csharpektroncmssql.blogspot.in/2012/04/network-related-or-instance-specific.html