SQL Server Express 2008 connection string missing

Question

I have an asp.net application running on my development machine (windows 7 ultimate, IIS 7.5 and visual studio 2010 pro). I also have SQL Server 2008 R2 installed and of course SQL Server Express 2008 that was installed with VS 2010 Pro.

I ran the ASP.NET Configuration wizard, created a few users and rolls for my app. As a result an ASPNETDB.MDF database file was created in the App_Data folder.

The problem I am facing now is that the connection string for the above mentioned SQL Server Express database is missing in web.config of my application as is the element for membership.

But my application is running fine. I can log in, log out etc. The Rolls are also working as expected.

(I would also like to point out that my Application also accesses a database on SQL Server 2008 R2 on my local machine and it can also do the same on the production server.)

I uploaded my Application to the production server, created the membership database there (SQL Server 2008), transfered the data over and so on. I then added a connection string for that SQL Server database in web.config file. However, I cannot connect to the SQL Server if I try to login. The error is as follows: 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)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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 think (though I am not sure) my application tries to access SQL Server Express on the remote host. This is because the connection string is somewhere there embedded in it.

If I made myself clear then my question is: How to configure the connection string or where to find it? Because it is not there in the web.config.

I am new to ASP.NET and SQL Server. Any help will be highly appreciated. Thanks.

Here's the web.config

<configuration>
<connectionStrings>
<add name="somename" connectionString="Data Source=remotehost;Initial Catalog=remotedb;User Id=username;Password=password"providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<customErrors mode="Off"></customErrors>
<roleManager enabled="true" />
<authentication mode="Forms" />
<compilation debug="true" strict="false" explicit="true" targetFramework="4.0" />
</system.web>
</configuration>

That's all there is to it my web.config.

The connection String above was added by me for the remote server.

Answer 1

I have found an answer to my question. The connection string for ASPDBNET.MDF does not normally appear in web.config if you're creating your website from scratch (in my case).

The application uses a machine wide connection string defined in the machine.config file. This is named "LocalSqlServer".

Usually this LocalSqlServer connection string targets a local SQL Server Express by default.

Therefore, it fails on remote host which uses a full blown SQL Server.

To upload your website to a remote server, some adjustments in your web.config are required to point to new server which is usually a SQL Server.

To do that add a new connection string to your web.config file and name it LocalSqlServer. Except for name add everything else normally like Data Source, Initial Catalog and so forth.

To make it work on the remote host add a clear element just before the opening connectionStrings element. Here's what your connection string should look like for the remote SQL Server.

<connectionStrings>
    <clear/>
    <add name="LocalSqlServer" connectionString="Data Source=remotehost;Initial Catalog=remoteDB;User Id=username;Password=password" providerName="System.Data.SqlClient" />
</connectionStrings>

And you're done. This is what I had done for my ASP.NET website that I created from scratch. Now its membership login, logout, users and rolls etc are working perfectly.

Thanks for reading.