ASP.NET 3.5 For Dummies®

22.4. Fix the @#$%*& SQL Connection

Connecting ASP.NET to a remote SQL Server or SQL Server Express database is error-prone and frustrating. If you manage to connect the first time without any grief, run out and buy a lottery ticket — you're on a roll!

The connection string usually resides in the web.config file in the <connectionStrings> section. You need to tweak this while trying to connect deployed pages to the data. Here's a typical SQL Server Express connection string as used within a local project:

<add name="JulieDVDConnectionString1" connectionString=
"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|

\JulieDVD.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

The preceding uses SQL Express, an attached file, and Windows authentication. Odds are, those settings make no sense to your host service.

The same connection — revised to use the host service's SQL Server — might look similar to this:

<add name="JulieDVDConnectionString1" connectionString=
"Data Source=SQLB2.webcontrolcenter.com;Initial Catalog=juliedvd;

Persist Security Info=True;User ID=julie;Password=nottellingyou!"
providerName="System.Data.SqlClient" />

The preceding connection string uses the URL (SQLB2.webcontrolcenter.com) of a SQL Server on the Internet as its ...