Comb the tangled SQL server 2005 connection string and deploy the database to remote SQL server

No matter how many ADO .net application I did and will write in the past and future, near and far, there is no way I can remember the long connection strings in indecipherable machine language. 

Normally I would do a quick search and grab whatever turns up on the first spot and stuff it in my web.config file. Normally I use access, normally it would work. Occasionally I also used SQL server 7.0. However that feels so embarrassingly backward.  So recently and finally I decided to switch to the .NET IT database: SQL server 2005. I use the express version. I got quite some error messages during the process, such as :

named pipes Provider, error: 40 - Could not open a connection to SQL Server (even though I was able to connect to the database using my Visual studio IDE and SQL server 2005 management studio. However my .aspx pages simply could not get talk to the SQL server)

SELECT permission denied on object 'AgentInfo', database 'master', schema 'dbo'

(A simple search actually turns up a deluge of calls for help regarding these messages)

 So I digged into the ADO.connection class and tried to make sense of the madness.

This is a typical connection string to a local SQL server express instance that attaches a dabase file (stored in the connectionStrings section of Web.config):

  <connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" providerName="System.Data.SqlClient"/>
  </connectionStrings>

This is a connection string to point to a remote SQL server,

  <connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="server=xxx.xxx.xxx;Initial Catalog=DBname;uid=username;pwd=password" providerName="System.Data.SqlClient"/>
  </connectionStrings>

The keywords: 

Data Source (alternatively: Server/Address/Addr/Network Address) :The name or network address of the instance of SQL Server to which to connect.

Integrated Security (or Trusted_Connection): defaulted to false. When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no, and sspi. SSPI is the same as true

AttachDBFilename: The name of the primary file, including the full path name, of an attachable database.

Initial Catalog / Database: The name of the database 

The following script could be used to create and grant necessary rights and assign roles to an account to access a remote SQL server.

USE Master

GO

Create Database DBname

GO

Use DBname

GO

EXEC sp_addlogin 'DBuser', '*****','DBname'

EXEC sp_grantdbaccess 'DBuser', 'DBUser'

EXEC sp_addrolemember 'db_owner', 'DBUser'

EXEC sp_addrolemember 'db_datareader', 'DBUser'

EXEC sp_addrolemember 'db_datawriter', 'DBUser'

GO