The Essential Problem

A basic task in web development is to supply data to a web site from a database such as SQL Server 2005 and to update that data from the web site provided that the user is properly authorized. One difficulty that arises is that when the site is being created on your local machine, the site is not in the same environment as it will eventually be when deployed on the real IIS server.

The essential problem is that once deployed your site may be authenticated automatically to your data on the database server but when you are testing locally this is not the case. Therefore in a local test you must use a username-password pair but in deployment on the IIS server you absolutely do not want to put such security data in any place where it might be publicly read.

These notes are based on how I configure my local machine and my web site on the server so I can test locally and have good confidence that the site will behave the same way when deployed. My thanks to Peter Douglass for an important suggestion that will be described in the next section. — Richard Rasala

Introduction to Connection Strings

The concept of connection string provides an abstraction that permits you to concentrate the differences between the local settings and the deployment settings in a single place. Normally all connection strings are placed in the ~/web.config file at the top level of your web site.

However, as Peter Douglass recently discovered, it is possible to place the connection string data in a separate file and make ~/web.config simply reference that separate file. In this way, ~/web.config becomes independent of the details of the connection strings needed for different situations. This is much better.

Following Peter Douglass, the connection strings will be placed in a file that is named:

~/connectionStrings.config

In ~/web.config, the following block of XML is placed as a “top level” block anywhere after the opening configSections XML block.

<connectionStrings configSource="connectionStrings.config">
</connectionStrings>

With this design, ~/web.config may be used everywhere without changes.

It turns out that there are 3 different versions of connection strings depending on the location of the SQL connection. Let me summarize first and then get into the complete details below.

Connection between a Remote Machine and the SQL Server

Data Source=127.0.0.1
Passwords must be used.
The SSH 1433 tunnel must be active.

Connection between a CCIS Lab Machine and the SQL Server

Data Source=mssql2005.development.ccs.neu.edu
Passwords must be used.

Connection between the IIS Server and the SQL Server

Data Source=mssql2005.development.ccs.neu.edu
Passwords should never be used under any circumstances!

To keep my sanity in managing these differences, I keep 4 versions of ~/connectionStrings.config on my laptop.

~/connectionStrings.config

This is the working version that is used for local testing and development. If I change this file, I test immediately to see if everything works or if something has broken.

~/connectionForRemote.config

This is the backup version of ~/connectionStrings.config. Should I make changes that fail, I can use this backup to restore the file to its original working state.

~/connectionForCCIS.config

This version is used if I want to load files onto the Linux file system and work from the CCIS lab network. Since the Windows machines in the lab can access the Linux file system for storage, it is possible to do Visual Studio 2008 development in the lab and keep the files stored in the Linux file system.

In this scenario, ~/connectionStrings.config is a copy of ~/connectionForCCIS.config.

~/connectionForServer.config

This is the production version of ~/connectionStrings.config for deployment on the IIS Server. To deploy this file, I do two steps:

Copy ~/connectionForServer.config to the IIS Server using SFTP.
Rename ~/connectionForServer.config to be ~/connectionStrings.config.

Unless you need to add connection strings, this step may be done once.

Remember: Passwords should never be used in ~/connectionForServer.config.

Back to the top

Connection Strings for a Remote Machine

This section discusses ~/connectionStrings.config as it is set up on a remote machine and backed up in ~/connectionForRemote.config.

Below is a copy of the file ~/connectionStrings.config as it appears on my remote machine except that my passwords have been replaced by ....

Your remote ~/connectionStrings.config file should be structurally similar but:

You must do find-and-replace to change every instance of my username rasala to your username.
You must do find-and-replace to change every instance of ... to your SQL password.

<?xml version="1.0" encoding="utf-8" ?>
<connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="LocalSqlServer"
     connectionString="Data Source=127.0.0.1;Initial Catalog=rasala;User ID=rasala;PWD=...;Persist Security Info=True"
     providerName="System.Data.SqlClient" />
    <add name="rasalaCS"
     connectionString="Data Source=127.0.0.1;Initial Catalog=rasala;UID=rasala;PWD=...;Persist Security Info=True"
     providerName="System.Data.SqlClient" />
    <add name="pubsCS"
     connectionString="Data Source=127.0.0.1;Initial Catalog=pubs;UID=rasala;PWD=...;Persist Security Info=True"
     providerName="System.Data.SqlClient" />
    <add name="NorthwindCS"
     connectionString="Data Source=127.0.0.1;Initial Catalog=Northwind;UID=rasala;PWD=...;Persist Security Info=True"
     providerName="System.Data.SqlClient" />
    <add name="AdventureWorksCS"
     connectionString="Data Source=127.0.0.1;Initial Catalog=AdventureWorks;UID=rasala;PWD=...;Persist Security Info=True"
     providerName="System.Data.SqlClient" />
    <add name="AdventureWorksDWCS"
     connectionString="Data Source=127.0.0.1;Initial Catalog=AdventureWorksDW;UID=rasala;PWD=...;Persist Security Info=True"
     providerName="System.Data.SqlClient" />
</connectionStrings>

Back to the top

Connection Strings for a CCIS Lab Machine

This section discusses ~/connectionStrings.config as it is set up on a CCIS lab machine and backed up in ~/connectionForCCIS.config on your remote machine.

Below is a copy of the file ~/connectionForCCIS.config as it appears on my remote machine except that my passwords have been replaced by ....

Your file ~/connectionForCCIS.config file should be structurally similar but:

You must do find-and-replace to change every instance of my username rasala to your username.
You must do find-and-replace to change every instance of ... to your SQL password.

<?xml version="1.0" encoding="utf-8" ?>
<connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="LocalSqlServer"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=rasala;User ID=rasala;PWD=...;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="rasalaCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=rasala;User ID=rasala;PWD=...;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="pubsCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=pubs;User ID=rasala;PWD=...;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="NorthwindCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=Northwind;User ID=rasala;PWD=...;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="AdventureWorksCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=AdventureWorks;User ID=rasala;PWD=...;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="AdventureWorksDWCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=AdventureWorksDW;User ID=rasala;PWD=...;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Back to the top

Connection Strings on the IIS Server

This section discusses ~/connectionStrings.config as it is set up on the IIS Server and backed up in ~/connectionForServer.config on your remote machine.

Below is a copy of the file ~/connectionForServer.config as it appears on my remote machine. This file has no passwords.

Your file ~/connectionForServer.config file should be structurally similar but:

You must do find-and-replace to change every instance of my username rasala to your username.
This file should never have passwords!

<?xml version="1.0" encoding="utf-8" ?>
<connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="LocalSqlServer"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=rasala;integrated security=True;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="rasalaCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=rasala;integrated security=True;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="pubsCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=pubs;integrated security=True;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="NorthwindCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=Northwind;integrated security=True;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="AdventureWorksCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=AdventureWorks;integrated security=True;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
    <add name="AdventureWorksDWCS"
         connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=AdventureWorksDW;integrated security=True;Persist Security Info=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Back to the top

Discussion of the Connection Strings

Let us discuss the XML in the sections above in a step-by-step fashion but let us put off the stuff related to the mysterious connection string LocalSqlServer until the end.

The next 5 blocks within the <connectionStrings> block each set up a connection string to a particular database within the SQL 2005 Server whose server address is currently named:

mssql2005.development.ccs.neu.edu

For the CCIS lab and the IIS Server scenarios, this database is explicitly specified in the connection string:

Data Source=mssql2005.development.ccs.neu.edu

In constrast, in the remote machine scenario such as your own laptop or desktop, you must have the 1433 tunnel active and you must use:

Data Source=127.0.0.1

In particular, in this scenario, no mention is made of the real target SQL Server. That is done within the tunnel specification in your SSH software.

The 5 databases on the SQL Server that I connect to are:

rasala: my personal database
pubs
Northwind
AdventureWorks
AdventureWorksDW

Each individual database is specified by the part of the connection string given by Initial Catalog=.

Note that the first database is my personal database and the next 4 databases are sample databases provided by Microsoft and are set up on our server as read-only.

You will notice that I name each connection string by appending the characters CS to the database name. This convention is certainly arbitrary but it is helpful when referring to a particular connection string in other files to have a consistent naming convention.

Now we come to security.

If you are connecting from your own remote machine or from a CCIS lab machine you will not be automatically trusted. Therefore, you must supply a username and password as follows:

User ID=rasala;PWD=...;

You will need to replace rasala with your username and ... with your SQL password.

On the other hand, the connection between your web site on the IIS server and your area on the SQL server is behind a firewall and is trusted. The way this is specified is by the following chunks of the connection string:

integrated security=True
Persist Security Info=True

The fact that trust exists and these specifications are present in a connection string means that a username and password are not necessary on the IIS Server. Never put your username and password on the IIS Server!

Back to the top

Using Connection Strings in `.aspx` Files

The following code extracted from the file StateData.aspx in the statedata directory on the Rasala site shows how the connection string rasalaCS is used to bind the state data in the database to the cells in Grid View #2.

<asp:GridView
    ID="GridView2"
    DataSourceID="GridView2SqlSource"
    runat="server" >
</asp:GridView>

<asp:SqlDataSource
    ID="GridView2SqlSource"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:rasalaCS %>" 
    SelectCommand="SELECT Name, Abbr, Population FROM USState.Data ORDER BY [Population] DESC">
</asp:SqlDataSource>

Notice that the connection string establishes what database to use. The details of what to select from what table are given in the select command.

Using Connection Strings in `.cs` Files

The following code extracted from the file USStateDAL.cs in the app_code directory on the Rasala site shows how the connection string rasalaCS is extracted from the web.config file and brought into C# code:

protected static readonly string rasalaCS =
    ConfigurationManager.ConnectionStrings["rasalaCS"].ConnectionString;

A SQL connection object may then be constructed as follows:

SqlConnection connection = new SqlConnection(rasalaCS);

For further details on how a SQL command is then set up and used to read data from such a SQL connection, go to Sources Server on the Rasala site, then to the directory app_code, and then to the files USStateDAL.cs, USStateData.cs, USStateList.cs.

Back to the top

A Peek Under the Hood

Here are a few additional comments. The expression in the .aspx file:

<%$ ConnectionStrings:rasalaCS %>

is equivalent to the C# expression:

ConfigurationManager.ConnectionStrings["rasalaCS"].ConnectionString

The .aspx file is setting up a C# object GridView2SqlSource whose type is SqlDataSource. The .aspx attribute assignment:

ConnectionString="<%$ ConnectionStrings:rasalaCS %>"

is equivalent to the following property assignment on the object level:

GridView2SqlSource.ConnectionString =
    ConfigurationManager.ConnectionStrings["rasalaCS"].ConnectionString;

When a SqlDataSource object such as GridView2SqlSource is used, it will automatically open a connection to the server using its ConnectionString property.

Back to the top

Login and the Mysterious Connection String `LocalSqlServer`

ASP.NET 2.0 has introduced tools that make it quite simple to set up logins and roles and then to restrict access to some or all pages based on whether or not a user is logged in and further on what role the user has if logged in.

One unfortunate decision is that these tools appear to default to using a local database rather than a database from a server. This is natural if you want to do a demo out of the box but is probably not what you want in production.

To change what database these tools use, one must change the default connection string for the tools which happens to be LocalSqlServer. As you can see from the extracts above, what we do is to remove the default definition and then replace it with a connection string to our own personal database.

To set up the database tools for login, role management, etc. in your database on the SQL server, you must do the following:

Make sure your 1433 tunnel is active.
Make sure any local SQL-like database is stopped.
Run C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe in a command shell.

Since you will run aspnet_regsql.exe with no command line arguments, you will get a wizard to walk you through the steps.

When you are at the step for SQL access, use 127.0.0.1, use SQL Authentication, use your CCIS username, and use your SQL password.

Further, in the dropdown box where the wizard asks for the database:

Do not select “default”.
Do not use the dropdown box arrow since it does not work.
Instead you must manually type into the dropdown box text field your CCIS username. This is the name of your read-write database on the SQL Server.

Now go to the next step.

The result will be the creation of 11 tables in your database on the SQL Server:

aspnet_Applications
aspnet_Membership
aspnet_Paths
aspnet_PersonalizationAllUsers
aspnet_PersonalizationPerUser
aspnet_Profile
aspnet_Roles
aspnet_SchemaVersions
aspnet_Users
aspnet_UsersInRoles
aspnet_WebEvent

It is a bad idea to edit any of this data directly. Changes should be a result of calling software.

To create and manage users and roles for login and other purposes, use the following menu command in Visual Studio:

Website > ASP.NET Configuration.

Your 1433 tunnel must be active while doing this and while using login and roles.

To study logins and roles, I encourage you examine the files linked below. However, on your site, do NOT publish the usernames and passwords. ☺

Back to the top

The Essential Problem

Introduction to Connection Strings

Connection between a Remote Machine and the SQL Server

Connection between a CCIS Lab Machine and the SQL Server

Connection between the IIS Server and the SQL Server

Connection Strings for a Remote Machine

Connection Strings for a CCIS Lab Machine

Connection Strings on the IIS Server

Discussion of the Connection Strings

Using Connection Strings in .aspx Files

Using Connection Strings in .cs Files

A Peek Under the Hood

Login and the Mysterious Connection String LocalSqlServer

Using Connection Strings in `.aspx` Files

Using Connection Strings in `.cs` Files

Login and the Mysterious Connection String `LocalSqlServer`