How to: Secure Connection Strings When Using Data Source Controls

How to: Secure Connection Strings When Using Data Source Controls 

When working with data source controls it is recommended that you centralize the location of your connection strings by storing them in the application's Web.config file. This simplifies the management of connection strings by making them available to all of the ASP.NET pages in a Web application. In addition, you do not need to modify numerous individual pages if your connection string information changes. Finally, you can improve the security of sensitive information stored in a connection string, such as the database name, user name, password, and so on, by encrypting the connection string section of the Web.config file using protected configuration.

To store a connection string in the Web.config file

Open the Web.config file for your application. If a Web.config file does not already exist, create a text file named Web.config and add the following content:

<?xml version="1.0"?>
<configuration
        xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
    <connectionStrings>
    </connectionStrings>
    <appSettings/>
    <system.web>
    </system.web>
</configuration>
In the connectionStrings element, create an add element for each connection string you will use in your Web application. Include the attributes shown in the following table.
Attribute Description
name
A name for this connection string configuration object. This name will be used by data source controls and other features to reference the connection string information.
connectionString
The connection string to the data source.
providerName
The namespace of the NET Framework data provider to use for this connection, such as System.Data.SqlClient, System.Data.OleDb or System.Data.Odbc.
A completed connectionStrings element might look like the following example:
<connectionStrings>
  <add 
    name="NorthwindConnection" 
    connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;" />
</connectionStrings>
Save and close the Web.config file.
You can now reference the connection string for your data source control by referring to the name you specified for the name attribute.
In the ConnectionString attribute for your data source control, use the connection string expression syntax to reference the connection information from the Web.config file.
The following example shows a SqlDataSource control in which the connection string is read from the Web.config file:
<asp:SqlDataSource ID="ProductsDataSource" Runat="server"
    SelectCommand="SELECT * from Products"
    ConnectionString="<%$ ConnectionStrings: NorthwindConnection %>"
</asp:SqlDataSource>

To encrypt connection string information stored in the Web.config file

At the Windows command line, run the ASP.NET IIS registration tool (aspnet_regiis.exe) with the following options:

1.The -pe option, passing it the string "connectionStrings" to encrypt the connectionStrings element.
2.The -app option, passing it the name of your application.
3.The aspnet_regiis.exe tool is located in the %systemroot%\Microsoft.NET\Framework\versionNumber folder.
The following example shows how to encrypt the connectionStrings section of the Web.config file for an application named SampleApplication:

aspnet_regiis -pe "connectionStrings" -app "/SampleApplication"

When the command has finished, you can view the contents of the Web.config file. The connectionStrings configuration section will contain encrypted information instead of a clear-text connection string, as shown in the following example:
<configuration>
   <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
      <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
         xmlns="http://www.w3.org/2001/04/xmlenc#">
         <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
         <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
            <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
               <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
               <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
                  <KeyName>RSA Key
                  </KeyName>
               </KeyInfo>
               <CipherData>
                  <CipherValue>WcFEbDX8VyLfAsVK8g6hZVAG1674ZFc1kWH0BoazgOwdBfinhcAmQmnIn0oHtZ5tO2EXGl+dyh10giEmO9NemH4YZk+iMIln+ItcEay9CGWMXSen9UQLpcQHQqMJErZiPK4qPZaRWwqckLqriCl9X8x9OE7jKIsO2Ibapwj+1Jo=
                  </CipherValue>
               </CipherData>
            </EncryptedKey>
         </KeyInfo>
         <CipherData>
            <CipherValue>OpWQgQbq2wBZEGYAeV8WF82yz6q5WNFIj3rcuQ8gT0MP97aO9SHIZWwNggSEi2Ywi4oMaHX9p0NaJXG76aoMR9L/WasAxEwzQz3fexFgFSrGPful/5txSPTAGcqUb1PEBVlB9CA71UXIGVCPTiwF7zYDu8sSHhWa0fNXqVHHdLQYy1DfhXS3cO61vW5e/KYmKOGA4mjqT0VZaXgb9tVeGBDhjPh5ZlrLMNfYSozeJ+m2Lsm7hnF6VvFm3fFMXa6+h0JTHeCXBdmzg/vQb0u3oejSGzB4ly+V9O0T4Yxkwn9KVDW58PHOeRT2//3iZfJfWV2NZ4e6vj4Byjf81o3JVNgRjmm9hr9blVbbT3Q8/j5zJ+TElCn6zPHvnuB70iG2KPJXqAj2GBzBk6cHq+WNebOQNWIb7dTPumuZK0yW1XDZ5gkfBuqgn8hmosTE7mCvieP9rgATf6qgLgdA6zYyVV6WDjo1qbCV807lczxa3bF5KzKaVUSq5FS1SpdZKAE6/kkr0Ps++CE=
            </CipherValue>
         </CipherData>
      </EncryptedData>
   </connectionStrings>
</configuration>
Leave the command prompt open for later steps.
Determine the user account or identity under which ASP.NET runs by retrieving the current WindowsIdentity name.
The following example shows one way to determine the WindowsIdentity name:
VB
<%@ Page Language="VB" %>
<%
Response.Write(System.Security.Principal.WindowsIdentity.GetCurrent().Name)
%>
C#
<%@ Page Language="C#" %>
<%
Response.Write(System.Security.Principal.WindowsIdentity.GetCurrent().Name);
%>

Note:

By default, on Windows Server 2003 with impersonation for an ASP.NET application disabled in the Web.config file, the identity under which the application runs is the NETWORK SERVICE account. On other versions of Windows, ASP.NET runs under the local ASPNET account.
The user account or identity under which ASP.NET runs must have read access to the encryption key used to encrypt and decrypt sections of the Web.config file. This procedure assumes that your Web site is configured with the default RsaProtectedConfigurationProvider specified in the Machine.config file named "RsaProtectedConfigurationProvider". The RSA key container used by the default RsaProtectedConfigurationProvider is named "NetFrameworkConfigurationKey".
At the command prompt, run the aspnet_regiis.exe tool with the following options:
The -pa option, passing it the name of the RSA key container for the default RsaProtectedConfigurationProvider.
The identity of your ASP.Net application, as determined in the preceding step.

The following example shows how to grant the NETWORK SERVICE account access to the machine-level "NetFrameworkConfigurationKey" RSA key container:

aspnet_regiis -pa "NetFrameworkConfigurationKey" "NT AUTHORITY\NETWORK SERVICE"

To decrypt the encrypted Web.config file contents, run the aspnet_regiis.exe tool with the -pd option. 

The syntax is the same as encrypting Web.config file contents with the -pe option except that you do not specify a protected configuration provider. The appropriate provider is identified in the configProtectionProvider attribute for the protected section.
The following example shows how to decrypt the connectionStrings element of ASP.NET application SampleApplication.
aspnet_regiis -pd "connectionStrings" -app "/SampleApplication"
.................................................................................................................................................................

ThreadAbortException

ERROR: ThreadAbortException Occurs If You Use Response.End, Response.Redirect, or Server.Transfer

 REASONS:

If you use the Response.End, Response.Redirect, or Server.Transfer method, a ThreadAbortException exception occurs. You can use a try-catch statement to catch this exception.

CAUSE:

The Response.End method ends the page execution and shifts the execution to the Application_EndRequest event in the application's event pipeline. The line of code that follows Response.End is not executed.

This problem occurs in the Response.Redirect and Server.Transfer methods because both methods call Response.Endinternally.

RESOLUTION:

To work around this problem, use one of the following methods:

•             For Response.End, call the HttpContext.Current.ApplicationInstance.CompleteRequest method instead ofResponse.End to bypass the code execution to the Application_EndRequest event.

•             For Response.Redirect, use an overload, Response.Redirect(String url, bool endResponse) that passes false for theendResponse parameter to suppress the internal call to Response.End. For example:

•               Response.Redirect ("nextpage.aspx", false);

                                                                                               

If you use this workaround, the code that follows Response.Redirect is executed.

•             For Server.Transfer, use the Server.Execute method instead.

Session Management Techniques

Session Management Techniques

Before we proceed, let us see what all session management techniques are present in the ASP.NET framework.

·       1.   In-Proc.

·         2.  SQLServer.

·             3. StateServer.

How to configure Sessions

To configure the session management we need to specify the settings in the web.config file. Typical settings inweb.config looks like:

<sessionState mode="InProc" 

                stateConnectionString="tcpip=127.0.0.1:42424" 

                sqlConnectionString="Data Source=.\SQLEXPRESS;Trusted_Connection=Yes;" 

                cookieless="false" 

                timeout="100"/>

Let us see what each of these attributes mean.

mode	This specifies the type of session management we want to use. it could be InProc,SQLServer, and StateServer
stateConnectionString	If we use StateServer as session management technique then this specifies the location of the server that is handling the session data.
sqlConnectionString	If we use SQLServer as session management technique then this specifies the  databaseconnectionstring that will store the session data.
cookieless	This specifies whether we will be using cookies to identify sessions or we want session info appended in URL. It could be true or false.
timeout	This specifies the time for which the session should be active. after this much time of inactivity the session will expire.

NOW lets see how can we apply these techniques in additional with other security measures .

I have created a login page and a welcome page that is redirected when correctly loggedin...............

THE DESIGN PAGE:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="sessionmanagement.aspx.cs" Inherits="sessionmanagement" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title>Login Form with session Stored in InProc</title>

</head>

<body>

<form id="form1" runat="server">

<center><div>

<table>

<tr>

<td>

Username:

</td>

<td>

<asp:TextBox ID="txtUserName" runat="server"/>

<asp:RequiredFieldValidator ID="rfvUser" ErrorMessage="Please enter Username" ControlToValidate="txtUserName" runat="server" />

</td>

</tr>

<tr>

<td>

Password:

</td>

<td>

<asp:TextBox ID="txtPWD" runat="server" TextMode="Password"/>

<asp:RequiredFieldValidator ID="rfvPWD" runat="server" ControlToValidate="txtPWD" ErrorMessage="Please enter Password"/>

</td>

</tr>

<tr>

<td>

</td>

<td>

<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />

    <asp:Label ID="warninglbl" runat="server" Text="" Visible="false"></asp:Label>

</td>

</tr>

</table>

</div>

</center>

</form>

</body>

</html>

THE CODE BEHIND PAGE:

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class sessionmanagement : System.Web.UI.Page

{

    string sqlconn = ConfigurationManager.ConnectionStrings["sqlTestconn"].ConnectionString.ToString();

    public static int countclicked = 0;

    protected void Page_Load(object sender, EventArgs e)

    {

        this.SmartNavigation = true;

        if (!IsPostBack)

        {

          

            try

            {

                warninglbl.Visible = false;

             string ComputerName = Request.ServerVariables["REMOTE_HOST"];

             string IPAddress = Request.ServerVariables["REMOTE_ADDR"];

             string Browser = Request.ServerVariables["HTTP_USER_AGENT"];

            }

            catch (Exception ex)

            {

            Response.Write(ex.Message);

           

            }

          

        }

        if (countclicked > 3)

            {

                Session["isbanned"] = true;

            }

            else

            {

                Session["isbanned"] = false;

            }

    }

    private void Login()

    {

        string username = txtUserName.Text;

        string password = txtPWD.Text;

        SqlConnection conn = null;

        try

        {

            btnSubmit.Enabled = false;

            conn = new SqlConnection(sqlconn);

            if (conn.State == ConnectionState.Closed)

            {

                conn.Open();

            }

            // SqlCommand cmd = new SqlCommand();

            string sqlstr = "select * from login where username='" + username + "' and password='" + password + "'; ";

            SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);

            DataTable dt = new DataTable();

            da.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                string dbname = dt.Rows[0]["username"].ToString();

                string dbpass = dt.Rows[0]["password"].ToString();

                if (dbname == username && dbpass == password)

                {

                    Session["username"] = username;

                    Session["islogged"] = true;

                    ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "loginmsg", "alert('CONGRASS... YOU HAVE BEEN REDIRECTED TO USER PAGE!!!')", true);

                  

                    Response.Redirect("WelcomePage.aspx");

                  

                }

                else

                {

                    ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "loginmsg", "<script>alert('WRONG USERNAME OR PASSWORD!!')</script>", true);

                    Session["islogged"] = false;

                }

            }

            else

            {

                ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "errmsg", "alert('WRONG USERNAME OR PASSWORD!!PLEASE TRY AGAIN')", true);

            }

        }

        catch (System.Threading.ThreadAbortException lException)

        {

            // do nothing

        }

        catch (Exception ex)

        {

           Response.Write(ex.Message);

        }

        finally {

            btnSubmit.Enabled = true;

            conn.Close();

            conn = null;

       

        }

   

    }

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

        if (!(Boolean)Session["isbanned"])

        {

            Login();

        }

        else {

         ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "errmsg", "alert('YOU HAVE TRIED MORE THEN 3 TIMES!!!! YOUR ACCOUNT HAS BEEN LOCKED FOR TODAY PLEASE CONTANCT THE ADMIN>>')", true);

        }

        countclicked++;

        if (countclicked > 3)

        {

            Session["isbanned"] = true;

            btnSubmit.Enabled = false;

            warninglbl.Visible = true;

          warninglbl.Text= "YOU HAVE TRIED MORE THEN 3 TIMES!!!! YOUR ACCOUNT HAS BEEN LOCKED FOR TODAY PLEASE CONTACT THE ADMIN>>>>>'";

        }

    }

}

THE WELCOME PAGE:

THE CODE BEHIND PAGE:

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

public partial class session_management_WelcomePage : System.Web.UI.Page

{

    string sqlconn = ConfigurationManager.ConnectionStrings["sqlTestconn"].ConnectionString.ToString();

  

    protected void Page_Load(object sender, EventArgs e)

    {

        this.SmartNavigation = true;

        if (!IsPostBack)

        {

            try

            {

                if (!(Boolean)Session["islogged"])

                {

                    Response.Redirect("sessionmanagement.aspx");

                }

                else

                {

                    string username = Session["username"].ToString();

                    Label1.Text = "WELCOME MR. " + username+" -you have successfully loged in!!!!!!!!!" ;

                }

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message);

                Response.Redirect("sessionmanagement.aspx");

            }

        }

    }

    protected void lgtbtn_Click(object sender, EventArgs e)

    {

        Session["islogged"] = false;

        Session["username"] = "";

        Response.Redirect("sessionmanagement.aspx");

    }

}

/////////////////////////////////////////////////////////////////////////

here we are using the session variables to store the user data.now to store these session we can use three ways as prescribed above in following ways........

IN WEBCONFIG FILE:

FOR InProc:

<sessionState mode="InProc" allowCustomSqlDatabase="true"   sqlConnectionString="Data Source=backupserver\test;Initial Catalog=ASPState;User ID=******;Password=******" cookieless="false" timeout="100"/>

           

FOR SQLSERVER:

<sessionState mode="SQLServer" allowCustomSqlDatabase="true"   sqlConnectionString="Data Source=backupserver\test;Initial Catalog=ASPState;User ID=sa;Password=sa" cookieless="false" timeout="100"/>

FOR STATESERVER:    

<sessionState mode="StateServer" stateConnectionString="tcpip=127.0.0.1:42424" cookieless="false" timeout="100"/>

           

NOW I have given example(above code) for InPROC storege ….now lets see hoe we can store the session variables data in SQLSERVER.

USE THE ABOVE MENTIONED CONFIG IN YOUR WEBCONFIG FILE………i.e for SQLSERTVER

AND THE REST OF THE CONCEPT IS SAME…i.e for programming .

But before you need to install the aspstate database in your sqlserver.

STEPs FOR INSTALLING THE DB:

1.Running  both ASP.NET and classic ASP in the same application pool?

If the application pool is also running classic ASP pages, and those classic ASP pages use .NET 2.0 components, and those classic ASP pages which use .NET components are called before any ASP.NET 1.1 page, then we'll load CLR 2.0 (first come, first serve  ) and of course it will look for his specific ASPState version.

Ok, there are a lot of "if" in this case, but it's still a possibility... not my scenario, through.

2.Where are your session tables?

If you use aspnet_regsql wizard, session tables are not added by default so you need to run the following command:

THE PATH WHERE YOU WILL GET THE EXE iS:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319

THE WIZARD ONLY INSTALLs the asp membership tables etc…

aspnet_regsql.exe -S <servername> -E -ssadd -sstype p 

YOUR DB IS INSTALLED…………..

Are you sure you can run it? 

Of course we still need permissions to access the database... so make sure the account used in your connection string can connect to the database has EXEC permission on the following stored procedures in ASPState database:

• TempGetAppID
• TempGetStateItem
• TempGetStateItemExclusive
• TempReleaseStateItemExclusive
• TempInsertStateItemLong
• TempInsertStateItemShort
• TempUpdateStateItemLong
• TempUpdateStateItemShort
• TempUpdateStateItemShortNullLong
• TempUpdateStateItemLongNullShort
• TempRemoveStateItem
• TempResetTimeout

Well... to make things easier in my sample, I just granted NETWORK SERVICE dbo permission on ASPState and I got my repro up an running.