DecryptionKey & ValidationKey - Generation

Hi,

 

I discussed once (May 2007) sharing keys while using ‘Forms Authentication Across Applications’

The discussion was about sharing the same authentication keys Across Applications Located On The Same Or Different Web Servers in the same domain.

 

In the topic we discussed when need to share the same authentication cookie across separate web servers, then you need to specify the decryptionKey and validationKey manually.

You cannot allow the ASP.NET Framework to generate these keys automatically because you need to share the keys across the different web servers.

 

<machineKey

  decryption="AES"

  validation="SHA1"

  decryptionKey="306C1FA852AB3B0115150DD8BA30821CDFD125538A0C606DACA53DBB3C3E0AD2"

  validationKey="61A8E04A146AFFAB81B6AD19654F99EA7370807F18F5002725DAB98B8EFD19C711337E269.... " />

 

You can use the below code to generate these random character sequences for you.

 

Note:

When using AES, you need to set the decryption key to a random sequence of 64 hex characters.

When using SHA1, you need to set the decryption key to a random sequence of 128 hex characters.

 

using System;

using System.Text;

using System.Security;

using System.Security.Cryptography;

 

class App {

  static void GetSequence(string argv, int len)

  {

    byte[] buff = new byte[len/2];

    RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();

    rng.GetBytes(buff);

    StringBuilder sb = new StringBuilder(len);

    for (int i=0; i<buff.Length; i++)

      sb.Append(string.Format("{0:X2}", buff[i]));

    Console.WriteLine(sb);

  }

}

 

The above code sample is based from an article entitled "How To: Configure MachineKey in ASP.NET 2.0," located at the Microsoft MSDN website (msdn.microsoft.com).

 

Hope this helps.

 

Regards,

Arun Manglick

 

---

Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.

Forms Authentication Across Domains

Hi,

 

I discussed once (May 2007) sharing keys while using ‘Forms Authentication Across Applications’

The discussion was about sharing the same authentication keys Across Applications Located On The Same Or Different Web Servers in the same domain.

 

Here we’ll see – Sharing the same authentication keys Across Domains

 

A browser cookie is always domain relative. For example, the Amazon website cannot read cookies set by the Barnes and Noble website, which is a good thing. However, you might discover that you need to share authentication information Across Websites With Different Domains.

You can work around this problem by passing an authentication ticket in a query string parameter rather than in a cookie. There is nothing to prevent you from passing query strings between domains.

The web configuration file in Listing 21.6 includes an enableCrossAppRedirects attribute that enables sharing authentication tickets across domains.

 

Note: To enable this scenario, you must configure your applications to accept authentication tickets passed in a query string.

 

<?xml version="1.0"?>

<configuration>

  <system.web>

    <authentication mode="Forms">

      <forms enableCrossAppRedirects="true" />

    </authentication>

 

    <machineKey

      decryption="AES"

      validation="SHA1"

      decryptionKey="306C1FA852AB3B0115150DD8BA30821CDFD125538A0C606DACA53DBB3C3E0AD2"

      validationKey="61A8E04A146AFFAB81B6AD19654F99EA7370807F18F5002725DAB98B8EFD19C711337..." />

 

  </system.web>

</configuration>

 

 

Sub Page_Load()

  Dim cookieName As String = FormsAuthentication.FormsCookieName

  Dim cookieValue As String = FormsAuthentication.GetAuthCookie(User.Identity.Name, False).Value

  lnkOtherDomain.NavigateUrl &= String.Format("?{0}={1}", cookieName, cookieValue)

End Sub

 

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

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

</head>

<body>

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

    <div>

 

    <asp:HyperLink

        id="lnkOtherDomain"

        Text="Link to Other Domain"

        NavigateUrl="http://www.OtherDomain.com/Secret.aspx"

        Runat="server" />

 

    </div>

    </form>

</body>

</html>

 

 

Hope this helps.

 

Regards,

Arun Manglick

 

---

Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.

Connection Pooling in DotNet

Abstract

The process of establishing a database connection can be time consuming depending upon network connectivity. Connection pooling is a viable option if the network is an issue, and database server resources are available. I'll begin by discussing connection pooling and examining how you may use it in your .NET applications.

Introduction

Connecting to the database is resource intensive and a relatively slow operation in an application.

There are several steps involved with establishing a database connection.

 

·         First, you will establish a connection to the database server over the network.

·         Next, the connection string is parsed and the user authenticated.

·         Finally, the connection is established, and operations may be performed.

Connection pooling allows an application to maintain ownership of a database connection.

A Connection Pool is a container of Open & Reusable Connections. A Connection Pool is released from the memory when the last connection to the database is closed. The basic advantage of using Connection Pooling is an improvement of performance and scalability while the main disadvantage is that one or more database connections, even if they are currently not being used, are kept open.

Connection Pooling gives you an idle, open, reusable connection Instead Of Opening A New One Every Time A Connection Request to the database is made. When the connection is closed or disposed, It Is Returned To The Pool And Remains Idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive. This article discusses what Connection Pooling is all about and how Connection Pooling can be used efficiently to boost the performance and scalability of applications.

An interesting note: Connection pooling is utilized (by default) unless otherwise specified.

How does a Connection Pool work?

Connection pools are actually containers that contain open and reusable connections. Multiple pools can exist in the same application domain at the same point in time, but Connection Pools cannot be shared across application domains. Note that one pool is created per unique connection string. A Connection Pool is created the first time a request for a connection to the database comes in with a unique connection string. Note that if another request comes in with a different connection string for the database, another Connection Pool would be created. Hence, we have one Connection Pool per connection string and not per database.

The pool can house connections up to the maximum limit as specified in the connection string that was used to connect to the database.

When a request for a new connection is served from the Connection Pool, it is done without creating a new one, i.e., the connections are re-used without creating new ones. Therefore, it improves the performance and scalability of your applications. When your application closes an open connection, it is returned to the pool where it waits until a Reconnect Time Out Period Expires. This is the period within which it waits to connect to the same database using the same credentials. If none comes in within this period, the connection to the database is closed and the Connection Instance Is Removed From The Pool.

A Connection Pool is maintained internally by the Connection Pool Manager. When a request for a subsequent connection comes in, the Connection Pool Manager searches the pool for the availability of a free connection and returns it to the application if one is available. The following points elaborate how the Connection Pool Manager works- the operations that it performs when a request for a new connection comes to it.

·         If any unused connection is available, it returns the connection.

·         If all connections are used up, a new connection is created and added to the pool.

·         If the number of connections reaches the maximum number of connections in the pool, the requests are queued until a connection becomes free for reuse.

Parameters controlling Connection Pooling

Max Pool Size	The maximum number of connections allowed in the pool. The default value is 100
Min Pool Size	The minimum number of connections allowed in the pool. The default value is zero
Enlist	Signals whether the pooler automatically enlists the connection in the creation thread's current transaction context. The default value is true

e.g.

 

DataSource=TestServer;Initial Catalog=Northwind; User ID=Chester;Password=Tester;Max Pool Size=50;Min Pool Size=5;Pooling=True;

You should refer to the documentation if you're using a .NET Data Provider other than SQL Server. Other data providers may have more pooling options. A good example is the Oracle Data Provider, which offers two options -- Decr Pool Size and Incr Pool Size -- for controlling how a connection pool may shrink or grow.

Improving Connection Pooling Performance

·         We should always open the connections late and release them early; in other words, immediately after we are done using it.

·         Connections should be opened only at the time when they are actually required. Otherwise it would decrease the number of available connections in the Connection Pool and, hence, have detrimental effects to the operation of the Connection Pool and the application's performance.

·         The connections should be explicitly released immediately when we are done using it. This would facilitate better Connection Pooling as the connection would be returned to the pool and be available for reuse

Below are two code snippets:

SqlConnection sqlConnection = new SqlConnection(connectionString); try { sqlConnection.Open(); //Some Code } finally { sqlConnection.Close(); }
using(SqlConnection sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); //Some Code }

Few more points for better utilization of the Connection Pool.

·         Always open connections when needed and close it immediately when you are done using it.

·         Close the user-defined transactions before closing the related connections.

·         Ensure that there is at least one connection open in the pool to maintain the Connection Pool.

·         Avoid using connection pooling if integrated security is being used.

Conclusion

A Connection Pool, a container of connection objects, remains active as long as there are active or open connections in it. When a request for new connection comes in with a connection string, a new Connection Pool is allocated. We can improve the performance and scalability of our applications by using the same connection string in our applications.

 

However, we should use Connection Pooling appropriately as using it inappropriately might have negative effects to the overall performance of our applications. MSDN says, "Connection pooling is a powerful functionality that can improve your applications' performance. But if you aren't a good lifeguard, your connection pools can become a detriment instead of a benefit. For e.g.

 

“If an application is in constant communication with a database, then connection pooling may be optimal since the need to open/establish connections is negated, thus performance improves. On the other hand, an application that runs nightly does not need to maintain a pool since it does nothing with the database the rest of the day. Use your best judgment when deciding whether to use connection pooling.”

 

Hope this helps.

 

Regards,

Arun Manglick

 

 

 

---

Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.

SQL Server Cache Dependency - Polling Approach

Using the SQL Server Cache Dependency :

To utilize the new SQL Server Cache Dependency run setup of your SQL Server database using the aspnet_regsql.exe tool.

Found at C:\Windows\Microsoft.NET\Framework\v2.0xxxxx\. This tool makes the necessary

modifications to SQL Server so that you can start working with the new SQL cache invalidation features.

Follow these steps when using the new SQL Server Cache Dependency features:

1. Enable your database for SQL Cache Dependency support.

2. Enable a table or tables for SQL Cache Dependency support.

3. Include SQL connection string details in the ASP.NET application’s web.config.

4. Utilize the SQL Cache Dependency features in one of the following ways:

Ø  Programmatically create a SqlCacheDependency object in code.

Ø  Add a SqlDependency attribute to an OutputCache directive.

Ø  Add a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.

This section explains all the steps required and the operations available to you.

See Below are the possible options.

Aspnet_regsql.exe -?

-d <Database Name>
-ed	Enable a database for SQL CacheDependency
-dd	Disable a database for SQL CacheDependency
-et	Enable a table for SQL CacheDependency
-dt	Disable a table for SQL CacheDependency
-t <Table Name>
-lt	List all the tables enabled for SQL CacheDependency

1.	Enabling Databases for SQL Server Cache Invalidation	aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Northwind" -ed
2.	Enabling Tables for SQL Server Cache Invalidation	aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Northwind"  -et -t Categories
3.	Include SQL connection string details in the ASP.NET application’s web.config	<configuration> <connectionStrings> <add name="Northwind" connectionString="data source=XIPL0060;initial catalog=Northwind; user id=sa;password=sa;persist security info=True;packet size=4096"/> </connectionStrings> <system.web>     <caching>       <sqlCacheDependency enabled="true" pollTime="5000">         <databases>           <add  name="Northwind"  connectionStringName="Northwind" />         </databases>       </sqlCacheDependency>     </caching> </system.web> </configuration>

Here:

Name	Provides an identifier to the SQL Server DB
Connectionstringname	Connection String Name
pollTime	Not required for SQL Server 2005 Spedifies time intrerval to poll.

Few more things to know (these are not part of any steps):

1.	Looking at the Tables That Are Enabled	aspnet_regsql.exe -S localhost -U sa -P password -d Northwind –lt
2.	Disabling a Table for SQL Server Cache Invalidation	aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -t Products –dt
3.	Disabling a Table for SQL Server Cache Invalidation	aspnet_regsql -S localhost -U sa -P wrox -d Northwind –dd

Note: That disabling an entire database for SQL Server cache invalidation also means that every single

table contained within this database is also disabled.

If you now open the Northwind database in the SQL Server Enterprise Manager, you can see that the

AspNet_SqlCacheTablesForChangeNotification table has been removed for the database.

Step 4: Utilizing the SQL Cache Dependency features in one of the following ways

Now that the web.config file is set up and ready to go, the next step is to actually apply these new

capabilities to a page.

Ø  Programmatically create a SqlCacheDependency object in code.

Ø  Add a SqlDependency attribute to an OutputCache directive.

Ø  Add a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.

Approach 1 - Using Programmatically :

private void BindGrid() {      GridView1.DataSource = GetDataTable();    GridView1.DataBind(); }
public DataTable GetDataTable () {     DataTable dt = null;     SqlConnection cnn = null;     SqlCommand cmd = null;     DataSet ds = null;     SqlDataAdapter da = null;     string cn = string.Empty;     dt = HttpContext.Current.Cache["HashData"] as DataTable;     if (dt == null)     {             cn = ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;             cnn = new SqlConnection(cn);             cnn.Open();             cmd = new SqlCommand("SELECT * FROM EMPLOYEE where pub_id < 800", cnn);             ds = new DataSet();             da = new SqlDataAdapter(cmd);             da.Fill(ds);             dt = ds.Tables[0];             SqlCacheDependency myDependency = new SqlCacheDependency("Pubs", "Employee");             //SqlCacheDependency myDependency = new SqlCacheDependency(cmd);  // Both will do             HttpContext.Current.Cache.Insert("HashKey", dt, myDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);     }     return dt; }

Approach 2 - Using OutputCache :

This is used when SQLDataSourceControls is used for GridView sourcing.

<%@ Page Language=”VB” %>

<%@ OutputCache Duration=”3600” VaryByParam=”none” SqlDependency=”Northwind:Customers”%>

<script runat=”server”>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Label1.Text = “Page created at “ & DateTime.Now.ToShortTimeString ()

End Sub

</script>

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

<head runat=”server”>

<title>Sql Cache Invalidation</title>

</head>

<body>

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

<asp:Label ID=”Label1” Runat=”server”></asp:Label><br />

<br />

<asp:GridView ID=”GridView1” Runat=”server” DataSourceID=”SqlDataSource1”></asp:GridView>

<asp:SqlDataSource ID=”SqlDataSource1” Runat=”server”

SelectCommand=”Select * From Customers”

ConnectionString=”<%$ ConnectionStrings:AppConnectionString1 %>”

ProviderName=”<%$ ConnectionStrings:AppConnectionString1.providername %>”>

</asp:SqlDataSource>

</form>

</body>

</html>

To add more than one table, you use the OutputCache directive shown here:

SqlDependency=”database:table;database:table”

Approach 3 - Using Request Object :

Please take help of internet.

Hope this helps.

Regards,

Arun

---

Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.

SQL Server Cache Dependency - Two Approaches

Using the SQL Server Cache Dependency :

 

Enabling SQLDependency has two apporoaches.

 

·         Polling Based

·         Notification Based

 

Polling Approach:

 

With polling, the database server maintains information about when particular tables have last been updated in a table named AspNet_ SqlCacheTablesForChangeNotification.

This is the table that ASP.NET uses to learn which tables are being monitored for change notification and also to make note of any changes to the tables being monitored. The table has three columns

 

Ø  tableName : Contains names of the tables enabled for SQL cache invalidation.

Ø  notificationCreated : shows the date and time when the table was enabled for SQL cache invalidation.

Ø  changeId : Used to communicate to ASP.NET any changes to the included tables. ASP.NET monitors this column for changes and, depending on the value, either uses what is stored in memory or makes a new database query.

 

The ASP.NET RUNTIME PERIODICALLY POLLS THE DATABASE to check what tables have changed since they were entered into the cache. Those tables whose data has been modified have their associated cache items evicted.The process is as below.

 

ASP.NET makes a separate SQL Server request on a completely different thread to the AspNet_ SqlCacheTablesForChangeNotification table to see if the changeId number has been incremented. If the number is changed, ASP.NET knows that an underlying change has been made to the SQL Server table and that a new result set should be retrieved. When it checks to see if it should make a SQL Server call, the request to the small AspNet_SqlCacheTablesForChangeNotification table has a single result. With SQL Server cache invalidation enabled, this is done so quickly that you

really notice the difference.

 

The Stored Proceduire used is : AspNet_SqlCachePollingStoredProcedure

 

This approach requires below steps when using the new SQL Server Cache Dependency features:

 

1. Enable your database for SQL Cache Dependency support.

2. Enable a table or tables for SQL Cache Dependency support.

3. Include SQL connection string details in the ASP.NET application’s web.config.

4. Utilize the SQL Cache Dependency features in one of the following ways:

 

Ø  Programmatically create a SqlCacheDependency object in code.

Ø  Add a SqlDependency attribute to an OutputCache directive.

Ø  Add a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.

 

 

Notification/Push Approach:

 

With notification, the database automatically alerts the ASP.NET runtime when the results of a particular query have been changed since the query was last executed, at which point the cached items associated with the query are evicted.

 

The notification option requires less setup than polling and is more granular since it tracks changes at the query level rather than at the table level.

Unfortunately, notifications are only available in the full editions of Microsoft SQL Server 2005 (i.e., the non-Express editions). However, the polling option can be used for all versions of Microsoft SQL Server from 7.0 to 2005.

 

You must perform two configuration steps to enable Push SQL cache dependencies:

 

·         You must Configure Your Database by enabling the SQL Server 2005 Service Broker.

·         You must Configure Your Application by starting the notification listener.

 

 

However there are significant limitations on the types of queries that you can use with Push dependencies. Here are some of the more significant limitations:

 

·         The query must use two-part table names (for example, dbo.Movies instead of Movies) to refer to tables.

·         The query must contain an explicit list of column names (you cannot use *).

·         The query cannot reference a view, derived table, temporary table, or table variable.

·         The query cannot reference large object types such as Text, NText, and Image columns.

·         The query cannot contain a subquery, outer join, or self join.

·         The query cannot use the DISTINCT, COMPUTE, COMPUTE BY, or INSERT keywords.

·         The query cannot use many aggregate functions including AVG, COUNT(*), MAX, and MIN.

 

This is not a complete list of query limitations. For the complete list, refer to the Creating a Query for Notification topic in the SQL Server 2005 Books Online or the MSDN website (msdn.Microsoft.com).

 

References: Link1, Link2, Link3

 

Hope this helps.

 

Regards,

Arun

 

 

 

---

Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.