Problems with accessing database remotely

I'm writing an web app in VS2005.

I've created a product.mdf database through VS2005's Server Explorer (to my knowledge, I'm not using Sql Express). (Side question. The .mdf files are a stand alone xml structure used as a database, correct?).

When I execute locally, everything works great. But when I moved my code to where it is being hosted (remote computer). It's throwing the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This is my connection string:

<addname="MyConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Product.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

What is my problem?

There is no SQL Server 2005 Express running on the remote server.

Jos

|||

Actually, you ARE using SQL Express. The Visual Studio environment does a great job in hiding this - but behind the scenes a SQL Express database engine is running on your local machine (you can see it in the Services.msc explorer). Your web application is using the web.config connection string tofind this SQL Express database.

As the previous reply says, the error message is due to the application trying to find a SQL Express database engine running on the remote computer and serving a database called Product.mdf.

The solution to your problem may be to install SQL Express on the remote computer. You will then need to copy (attach) the Product.mdf database to the remote database server.

However, if your "remote computer" is actually a hosting provider - they may not (and most don't) support SQL Express - and may insist that you use SQL Server Enterprise instead, which is a far more robust option anyway.

|||

Why I got the error message?

The error message you got was due to the using of SQL Express in your local (development) machine.

Ok, how to solve this issue?

It is easy. Eiteher use SQL Express on your production (e.g. hosting server) [NOT Recommended] or do the following:

Attach your .mdf file to a database (e.g. new database) in the sql server of the hosting company.
Change your ConnectionString to be as following:

<add name="MyConnectionString1" connectionString="Server=YourSqlServerName;Database=MyDatabase;Integrated Security=True;" providerName="System.Data.SqlClient"/>

YourSqlServerName: Type the name of the sql server as provided by the hosting company.

MyDatabase: The database you are using which reside in the hosting company sql server.

Note: The above ConnectionString is used with "Windows Authentication", for the "Mixed Mode" use the following ConnectionString:

<add name="MyConnectionString1" connectionString="Server=YourSqlServerName;Database=MyDatabase;User ID=MyUserID; Password:MyStrongPassword"providerName="System.Data.SqlClient"/>