Web Development Home

CS U675 & CS G175

http://www.ccs.neu.edu/teaching/web/

Introduction       Syllabus       Requirements and Expectations

Startup       Accessing SQL       Connection Strings

Introduction to Connection Strings

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. -- Richard Rasala

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.

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 the connection strings are placed in the web.config file at the top level of your web site. To handle the differences between the local and deployment environments, I normally maintain two such files in my local web site folder. The second file is named webForServer.config. These files are identical except in the connection strings section. If I modify one of the files, I immediately modify the other in the same way to maintain the parallelism. On my local machine, web.config contains the appropriate local settings with username-password. I never deploy this file. Instead, I copy the parallel file webForServer.config to the server, delete the web.config on the server, and then rename webForServer.config to be web.config. This discipline keeps everything working properly and securely.

Recently, I have found it convenient to maintain two small files that contain just the proper connection strings. I name these two files connections.config and connectionsForServer.config to remind me which version is used remotely on my development machine and which version is deployed to the server. These extra files simplify maintaining the parallelism since if make an error I can reconstruct the web.config I use locally and then rebuild the file webForServer.config that I use for deployment.

Back to the top

Connection Strings for web.config on the Server

Below is an extract from the connection strings section of my file web.config as it appears on the server. 

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  
  <!-- Other configuration settings -->

    <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" />

        <!-- Added to cooperate with the LINQ to SQL Designer-->
        <add name="rasalaConnectionString"
             connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=rasala;integrated security=True;Persist Security Info=True"
             providerName="System.Data.SqlClient" />
        <add name="pubsConnectionString"
             connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=pubs;integrated security=True;Persist Security Info=True"
             providerName="System.Data.SqlClient" />
        <add name="NorthwindConnectionString"
             connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=Northwind;integrated security=True;Persist Security Info=True"
             providerName="System.Data.SqlClient" />
        <add name="AdventureWorksConnectionString"
             connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=AdventureWorks;integrated security=True;Persist Security Info=True"
             providerName="System.Data.SqlClient" />
        <add name="AdventureWorksDWConnectionString"
             connectionString="Data Source=mssql2005.development.ccs.neu.edu;Initial Catalog=AdventureWorksDW;integrated security=True;Persist Security Info=True"
             providerName="System.Data.SqlClient" />
    </connectionStrings>

  
  <!-- Other configuration settings -->
  
</configuration>

Let us discuss this XML step-by-step.

We have actually extracted the framework of the XML file with the XML header <?xml ... ?> and the main <configuration> tag so that you may see the context. All web.config settings are placed within the <configuration> tag.

The <connectionStrings> block is an XML block that is nested one level within the <configuration> tag. In my files, I place this block just below the <configSections> block so it is easy to find. It is an ASP.NET requirement that the <configSections> block be the first block within the <configuration> tag.

The <connectionStrings> block is the only block in web.config that will be different on the server as compared to what is on the remote machine on which you will develop your web site using Visual Studio 2008. Therefore, you must maintain the parallelism of all other sections of web.config between the server and your remote machine.

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 our SQL 2005 Server whose server address is currently named: 

    mssql2005.development.ccs.neu.edu

This database is specified in the connection string in the segment: 

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

which is the same for all databases.

The 5 databases that I connect to within this overall database are:

The 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 any particular connection string in other files to have a consistent naming convention.

Now we come to security. Since the above configuration code is the code for deployment on the server, we can take advantage of the fact that IIS and SQL Server 2005 will collaborate to permit any web site to access any databases that may be accessed by the owner of the web site. Thus my web site can access my personal database and the 4 public read-only databases. The way this is specified is by the following chunks of the connection string:

When you are testing on a remote machine, you are not within the domain of the servers so you are not automatically trusted. This means that you must replace the first chunk above with a username and password pair.

Also, you must establish a 1433 tunnel to the database in Secure Shell (as described separately) and you must refer to the database as 127.0.0.1 so that your connection goes via the Secure Shell tunnel. See Accessing SQL.

There is a final connection string rasalaConnectionString which duplicates rasalaCS. Why the duplication? The reason is that this connection string follows the default pattern for LINQ to SQL queries introduced in ASP.NET 3.5. When you use the LINQ to SQL designer tool, the tool generates automatic C# code that must not be changed manually. When connecting to my personal database, this automatic code uses the long connection string name. Hence, this additional connection string was inserted by the designer tool into the web.config file on my remote machine and I have copied that definition with adjustments into the file for the server.

Back to the top

Connection Strings for web.config on the Remote Development Machine

Below is an extract from the connection strings section of my file web.config as it appears on my remote machine. Your remote web.config file should be structurally similar. 

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  
  <!-- Other configuration settings -->

    <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" />
           
        <!-- Added to cooperate with the LINQ to SQL Designer-->
        <add name="rasalaConnectionString"
             connectionString="Data Source=127.0.0.1;Initial Catalog=rasala;User ID=rasala;PWD=...;Persist Security Info=True"
             providerName="System.Data.SqlClient" />
        <add name="pubsConnectionString"
            connectionString="Data Source=127.0.0.1;Initial Catalog=pubs;UID=rasala;PWD=...;Persist Security Info=True"
            providerName="System.Data.SqlClient" />
        <add name="NorthwindConnectionString"
            connectionString="Data Source=127.0.0.1;Initial Catalog=Northwind;UID=rasala;PWD=...;Persist Security Info=True"
            providerName="System.Data.SqlClient" />
        <add name="AdventureWorksConnectionString"
            connectionString="Data Source=127.0.0.1;Initial Catalog=AdventureWorks;UID=rasala;PWD=...;Persist Security Info=True"
            providerName="System.Data.SqlClient" />
        <add name="AdventureWorksDWConnectionString"
            connectionString="Data Source=127.0.0.1;Initial Catalog=AdventureWorksDW;UID=rasala;PWD=...;Persist Security Info=True"
           providerName="System.Data.SqlClient" />
    </connectionStrings>

  <!-- Other configuration settings -->
  
</configuration>

Since, for security, you should have your SSH 1433 tunnel running and should access the database via that tunnel, you must change the setting used on the server: 

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

to the corresponding setting for a remote machine that uses the SSH 1433 tunnel: 

    Data Source=127.0.0.1

Notice that 1433 is an SSH port setting. Database connections will automatically use port 1433. The above Data Source will direct the database access request to your own machine (!) and SSH will securely redirect that access request to the real SQL Server database at CCIS.

Warning: Do not replace 127.0.0.1 by localhost. This convention valid in may situations is not respected in connection strings.

Note also that you must NOT be running a local copy of SQL Server or SQL Server Express because that will cause a second executable to try to manage port 1433 and everything will break.

As we mentioned a moment ago, integrated security also cannot be true if you are working on your remote machine. Hence you must replace the chunk 

    integrated security=True;

in each connection string with a chunk that looks like: 

    User ID=rasala;PWD=...;

Here the User ID is your standard CCIS login-id. The password is the special SQL password auto-generated for you when you ran the Startup program. Presumably, you have already run this program and have the SQL password.

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:Panel runat="server" ID="GridView2">
    
        <asp:GridView
            ID="GridViewUsingSQL1"
            DataSourceID="StateDataSqlSource1"
            runat="server" >
        </asp:GridView>
        
        <asp:SqlDataSource
            ID="StateDataSqlSource1"
            runat="server"
            ConnectionString="<%$ ConnectionStrings:rasalaCS %>" 
            SelectCommand="SELECT Name, Abbr, Population FROM USState.Data ORDER BY [Population] DESC">
        </asp:SqlDataSource>
    
    </asp:Panel>

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.

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 StateDataSqlSource1 whose type is SqlDataSource. The .aspx attribute assignment: 

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

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

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

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

Back to the top

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 extract above, what we do is to remove the default definition and then replace it with a connection to our own personal database.

There are other aspects to setting up logins and roles that we will not get into on this page.

Back to the top