MVC 3 ASPNETDB.MDF SqlExpress Database Connection String on Web Hosting

Question

I think this is more of a connection string issue that MVC 3, so sorry in advance.

I've created a website in MVC 3 with the default database that is created when you register a new user. It works fine local but not on my web hosting where I receive the following 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)

The default connection strings are:

<connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
  providerName="System.Data.SqlClient" />
    <add name="ASPNETDBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
  providerName="System.Data.SqlClient" />

Should I specify a connection string with an explicit IP address of the server or retain the .\SQLEXPRESS command? If I need to specify a username and password then what should I use? I've never used a local SQLExpress database before so I'm clueless (which I'm sure is apparent).

Answer 1

If you cannot use SQL Express, then you could consider an embedded SQL Engine:

SQL Compact Edition or SQLLite

I've never used SQL CE, but SQLLite is just a single DLL.

Obviously you don't have all the features of a fully fledged database engine, but they are still pretty powerful.

Answer 2

Generally you will need use a connection string with a specific IP Address, Username, Password, etc.

Who are you hosting with? Generally a .net host provider will give you the connection string to use to connect to your database. You can copy and paste in to you web.config. Also, when they create the database for you, they generally specify the username, password, and database name so you will have to change all of that.

This is visual studio has Web.config.debug and Web.config.Release with the xml transformations. This way when you publish to the site, the connection string is automatically changed from your local connection string to the connection string used in your production environment.