I often forget that the simplest way (IMHO) to connect to a Microsoft SqlServer 2008 database is to use the web.config connection for making a Trusted connection. Basically, it keeps you from having to put a username and password in the web.config and also from having to keep track of different username and passwords on different systems (like where you deploy to for example). The simplest connection string I can think of looks like this:
<add name="MyConnName" connectionString="Server=.;Database=mydbcatalogname;Trusted_Connection=True;"
providerName="System.Data.SqlClient" />
Notice that I’m using the “.” as the server name. This allows me to reference the local system where my sqlserver lives. Not necessarily a best practice, but often is the case.
Upside
One big benefit is if you fall victim to a disk scraping attack, or someone gets a hold of your source (maybe from version control?), you don’t give up your passwords.
Downside
If you SqlSever is not on the system system as your web project or application, then this become more tricky because of the cross system authentication issues. If you have active directory installed on both systems, then this works also.