Thursday, April 26, 2012


SQL Query Order of Operations

To start out, I thought it would be good to look up the order in which SQL directives get executed as this will change the way I can optimize:
  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause
This order holds some very interesting pros/cons:

FROM Clause

Since this clause executes first, it is our first opportunity to narrow down possible record set sizes. This is why I put as many of my ON rules (for joins) as possible in this area as opposed to in the WHERE clause:


WHERE Clause

With the WHERE clause coming second, it becomes obvious why so many people get confused as to why their SELECT columns are not referencable in the WHERE clause. If you create a column in the SELECT directive:


ORDER BY Clause

It might confuse people that their calculated SELECT columns (see above) are not available in the WHERE clause, but they ARE available in the ORDER BY clause, but this makes perfect sense. Because the SELECT clause executed right before hand, everything from the SELECT should be available at the time of ORDER BY execution.
I am sure there are other implications based on the SQL clause order of operations, but these are the most obvious to me and can help people really figure out where to tweak their code.

Wednesday, April 18, 2012

Authentication using WCF and asp.net custom-membership-provider

http://aspguy.wordpress.com/2011/07/30/single-sign-on-with-wcf-and-asp-net-custom-membership-provider/
http://www.codewrecks.com/blog/index.php/2009/11/26/wcf-over-https-authentication-with-asp-net-membership/
http://stackoverflow.com/questions/2615316/using-asp-net-membership-provider-authentincation-in-a-wcf-service

Using the ASP.NET Membership Provider and Authentication Service from Windows Phone 7


Those of you who have been building ASP.NET applications for a while now are no doubt familiar with the provider model, which includes provider abstractions for membership (authentication), roles (authorization), profiles (user data), and session state. These providers make it incredibly easy to provide a secure framework for your application. In fact, with an ASP.NET application template right out of the box, you can have a fully functioning authenticated, secure website in minutes.
What a lot of people have less familiarity with is the notion of provider services. You can actually create a WCF service head that sits on top of the ASP.NET membership system. This allows client applications to authenticate against your ASP.NET website using exactly the same authentication scheme that your users use. This means that a user who has access to your website should also be able to have access to the client application seamlessly.
To see how this works, create yourself a new ASP.NET MVC app (you can do this with a regular ASP.NET app, but I just happened to use MVC). Before doing anything, run the app, go to the “Log On” area, register yourself as a user and then quit the app. If you’ve got SQL Express installed properly and everything else is in order, your app now knows who you are.
Next, add a new WCF service to this application (call it Authentication.svc). Delete the files Authentication.svc.cs and the IAuthentication.cs. Open up the Authentication.svc file and replace it’s contents entirely with the following:



<%@ ServiceHost Language="C#"
    Service="System.Web.ApplicationServices.AuthenticationService" %>
Note here that there’s no code-behind. All we’re doing is exposing a service that is already part of the ASP.NET framework at a specific endpoint. You’re not quite ready yet – we have to make this service ASP.NET compatible, so open up your Web.config file and make sure that your system.servicemodel section looks like this:


































<system.serviceModel>
 <services>
 <service name="System.Web.ApplicationServices.AuthenticationService"
behaviorConfiguration="AuthenticationServiceBehaviors">
 <endpoint contract="System.Web.ApplicationServices.AuthenticationService"
     binding="basicHttpBinding" />
 </service>
 <service name="Wp7AspNetMembership.HelloService"
     behaviorConfiguration="AuthenticationServiceBehaviors">
 <endpoint
     contract="Wp7AspNetMembership.IHelloService"
     binding="basicHttpBinding"/>
 </service>
 </services>
 <serviceHostingEnvironment aspNetCompatibilityEnabled="true"
     multipleSiteBindingsEnabled="true" />
 <behaviors>
 <serviceBehaviors>
 <behavior name="AuthenticationServiceBehaviors">
 <serviceMetadata httpGetEnabled="true" />
 </behavior>
 <behavior name="">
 <serviceMetadata httpGetEnabled="true" />
 <serviceDebug includeExceptionDetailInFaults="false" />
 </behavior>
 </serviceBehaviors>
 </behaviors>
 </system.serviceModel>
 
 <system.web.extensions>
 <scripting>
 <webServices>
 <authenticationService enabled="true" requireSSL="false"/>
 </webServices>
 </scripting>
 </system.web.extensions>
What’s going on in this Web.config file is pretty interesting. First, we’re exposing Authentication.svc and HelloService.svc (not yet created) over HTTP, with metadata allowed, with ASP.NET compatibility enabled. We’ve also used the system.web.extensions element to indicate that the authentication service is being enabled. In a production environment, you would set requireSSL to true.
At this point, you should be able to hit the URL /Authentication.svc and see the standard metadata page. Now let’s create HelloService.svc by creating a standard WCF service. Change the interface so it only has a single method called HelloWorld() that returns a string. The following is the implementation of HelloService.svc.cs:

























using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Web;
using System.ServiceModel.Activation;
 
namespace MvcApplication1
{
 [AspNetCompatibilityRequirements(
    RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
 public class HelloService : IHelloService
 {
  public string HelloWorld()
  {
   if (HttpContext.Current.User.Identity.IsAuthenticated)
     return HttpContext.Current.User.Identity.Name;
   else
     return "Unauthenticated Person";
  }
 }
}
This is a pretty simple service. Just pretend that instead of returning the name of the authenticated user, we’re actually performing some vital business function that requires a valid user context.
Now, we can get to the good stuff : the WP7 application :) In this WP7 application we’re going to create a login form, submit user credentials over the wire, get validation results and, if the user was authenticated, we’re going to call the HelloWorld() method with a validated, secure context – all of this with an incredibly small amount of work on the part of the WP7 client.
Add a new WP7 application (just the stock one, not the list view) to the solution, I called mine TestMembershipClient but you can pick whatever you like. Add service references to Authentication.svc and to HelloWorld.svc (I named the reference namespaces MvcWebAppReference for authentication and AspNetMvcRealReference for the service that simulates doing real work).
On your main page, drop a couple text block labels, two text boxes, and a submit button. When you run the app at this point, it should look like this screenshot:
WP7 Login Form
WP7 Login Form
Now rig up a click handler for the submit button with code that looks like this:





































private void LoginButton_Click(object sender, RoutedEventArgs e)
{
 MvcWebAppReference.AuthenticationServiceClient authService =
   new MvcWebAppReference.AuthenticationServiceClient();
 cc = new CookieContainer();
 authService.CookieContainer = cc;
 authService.LoginCompleted +=
   new EventHandler<MvcWebAppReference.LoginCompletedEventArgs>(
 authService_LoginCompleted);
 authService.LoginAsync(UsernameBox.Text, PasswordBox.Text, "", true);
}
 
void authService_LoginCompleted(object sender,
 MvcWebAppReference.LoginCompletedEventArgs e)
{
 if (e.Error != null)
 {
 MessageBox.Show("Login failed, you Jackwagon.");
 }
 else
 {
   AspNetMvcRealReference.HelloServiceClient helloService =
    new AspNetMvcRealReference.HelloServiceClient();
   helloService.CookieContainer = cc;
   helloService.HelloWorldCompleted +=
   new EventHandler<AspNetMvcRealReference.HelloWorldCompletedEventArgs>(
     helloService_HelloWorldCompleted);
   helloService.HelloWorldAsync();
 }
}
 
void helloService_HelloWorldCompleted(object sender,
 AspNetMvcRealReference.HelloWorldCompletedEventArgs e)
{
 MessageBox.Show("You're logged in, results from svc: " + e.Result);
}
Most of this is pretty straightforward. When the user clicks the submit button, it calls the LoginAsync method on the membership service (remember that all service calls in Silverlight are asynchronous). When we get the results of that call, we either tell the user that their login has failed, or we invoke the HelloWorld method (also asynchronously). When the hello world method comes back from the server, we display the results of the execution in a message box that looks like the one in the screenshot below:
Results of Calling a Secured Service from WP7
Results of Calling a Secured Service from WP7
One thing to take very careful note of is the CookieContainer. Because we’re using two different proxies: 1 to talk to the authentication service and 1 to talk to the hello world service, we have to ensure that these two proxies are using the same cookie container so that the auth cookie can be used by subsequent method calls on other services. You can do this for an unlimited number of services that are on the same DNS domain, so long as they all share the same cookie container. To enable the use of cookie containers in WCF services in Silverlight (it’s disabled by default), you have to set the enableHttpCookieContainer property to true. in the binding element in the ServiceReferences.ClientConfig file.
On the surface you might not think this is all that big of a deal. You might also think this is difficult but, keep in mind that I provided a pretty detailed walkthrough. This whole thing only took me about 15 minutes to set up from start to finish once I’d figured out how the CookieContainer thing worked. So why bother with this?
Consider this: If you already have an ASP.NET application that is using the membership provider, role provider, and profile provider you can quickly, easily, and securely expose services to a mobile (WP7) client that allow that client to have secured, remote access to services exposed by that site. In short, any user of your existing web application can use their existing credentials to log in from their WP7 device and access any services you decide to make available.
ASP.NET provider services, coupled with WP7 and the fact that Silverlight has access to WCF client proxy generation, means you can very easily prep your site for a rich WP7 experience.

How to Write Case Statement in SQL


Select  StudentId,
case
when StudentExpiryDate<getdate() then
StudentName + ' [Expired]'
else
StudentName
end
StudentName from ms_survey_corporate where StudentId=2000

Saturday, March 31, 2012

How to check if Internet Connection Available through Dot Net Code

Checking whether an Internet connection is available isn’t always as easy as it sounds.
Admittedly, there is a Windows API call that can check whether a connection exists, but it’s extremely fragile and returns incorrect results if the machine has never had Internet Explorer configured correctly. Oops.

The best method is to actually make a Web request and see whether it works. If it does, you’ve got your connection. The following neat code snippet does exactly that. Just call IsConnectionAvailable and check the return value:

Public Function IsConnectionAvailable() As Boolean
' Returns True if connection is available
' Replace www.yoursite.com with a site that
' is guaranteed to be online - perhaps your
' corporate site, or microsoft.com
Dim objUrl As New System.Uri("http://www.yoursite.com/")
' Setup WebRequest
Dim objWebReq As System.Net.WebRequest
objWebReq = System.Net.WebRequest.Create(objUrl)
Dim objResp As System.Net.WebResponse
Try
' Attempt to get response and return True
objResp = objWebReq.GetResponse
objResp.Close()
objWebReq = Nothing
Return True
Catch ex As Exception
' Error, exit and return False
objResp.Close()
objWebReq = Nothing
Return False
End Try

Here’s how you might use this function in your application:

If IsConnectionAvailable() = True Then
MessageBox.Show("You are online!")
End If

Friday, January 27, 2012

Encrypt or Decrypt Connection Strings in web.config


Introduction

In this article I will explain how to encrypt or decrypt connection Strings in web.config file using asp.net.

Description

In Previous posts I explained lot of articles regarding Asp.net, Gridview, SQL Server, Ajax, JavaScript etc. In many of articles I used connection Strings section in web.config file to store database connection. The connection Strings section contains sensitive information of database connections including username and password of database. Is it secured to store the sensitive information of database connections in plain text files called web.config and machine.config files?
 
If we are using applications in our internal servers with security then it’s ok if we deploy our applications in shared host environment then we have chance to arise security problems to avoid these problems asp.net 2.0 provided built in protected configuration model functionality to encrypt or decrypt few sections of web.config file those are
 
RSAProtectedConfigurationProvider: This is default provider and uses the RSA public key encryption algorithm to encrypt and decrypt data.
 
DataProtectionConfgurationProvider: This provider uses windows data protection application programming interface to encrypt and decrypt the data.
 
The encrypting and decrypting of connection strings in web.config file will do by using aspnet_regiis.exe command line tool and code behind.

First Method

First we will do encryption and decryption using aspnet_regiis.exe command line tool in file system website
 
To implement encryption and decryption first create one new website using visual studio. 
 
After that open web.config file in application and add sample db connection in connectionStringssection like this 
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=RahulMittal;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings >
After add dbconnection in connectionString check the below steps to encrypt or decrypt the connection string in web.config.
 
1. 1) Go to Start >> All Programs >> Microsoft visual studio 2008 >> Visual Studio Tools >> Visual Studio 2008 Command Prompt (Note: if you’re using windows 7 right click on command prompt and select Run as administrator)
 
2. 2) after open command prompt type the following command aspnet_regiis.exe -pef "connectionStrings" "C:\VisualStudio2008\Authorization"
 
Here –pef indicates that the application is built as File System website. Second argumentconnectionStrings indicates that name of the configuration section needs to be encrypted. The Third argument is the physical path of the folder where the web.config file is located.
 
3. 3) after enter the command click enters if everything goes well we will get success message like “Encrypting configuration section… Succeeded!”
 
Now open your application and check connectionStrings in web.config file that would be like this
<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>ZNUbIEnOwlZzC8qbzHj5F2GS9gLYSkWCIgCJGkrgZAX8A+8oEIssyohhxUKvAubD3jizFc5IjbLGt7HNXhoFhXNTUPYz2y6tdKJDVgDmtCgVf8Z2C990zoMRBJG+VXhmgnlo1vtHYhGx8x/bBzE1prT1+xDpep98vHF22d+LrVI=</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>tODWlPD0Q/B/mP14GQ/5tUxcjmhHcy9a0oPunV5osNrMQRztgi2h5V6sxJOEh+NC+G9gQNkv1huXf1s7eoZRRLy5/LDtLXzzqMUOqLSlJUs9igChvi33c9XG4rwGF15Tpn4N34bpQBt94n0rpSkQ18V9HCPzii+UO64PlA+ykDeQhc9aQr4gO3mCfUzmY2S9gsXzRbzdq0oCWBDvx8UkX2uDxaysVHC9Fo7u6IrlpU0+hOdK95Y3/A==</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>
Here we don’t want to write any code to decrypt the encrypted connectionString in our application because .NET automatically decrypts it. If we want to use the connection string just call it like normal way
string strconnection = ConfigurationManager.AppSettings["dbconnection"].ToString();
Now if we want to decrypt connectionStrings section in web.config use the following commandaspnet_regiis.exe -pdf "connectionStrings" "C:\VisualStudio2008\Authorization"
 
After command execute we will get message like “Decrypting configuration section… Succeeded!”

Now check your connctionStrings section in your web.config file you will see decrypted connection string.
 
Till now we learned how to encrypt and decrypt connectionStrings section in File system website. If I want to encrypt connection string in IIS based site like i.e. Deployed website for that we need to use the following commands
 
Encrypt connectionStrings in web.config of IIS based site
 
aspnet_regiis.exe -pe "connectionStrings" -app "/SampleWebSite"
Here –pe indicates that the application is built as IIS based site. Second argument connectionStrings is the name of configuration section needs to be encrypted. The Third argument -app indicates virtual directory and last argument is the name of virtual directory where application is deployed.
 
Decrypt connectionStrings in web.config of IIS based site
 
aspnet_regiis.exe -pd "connectionStrings" -app "/SampleWebSite"
Till now we learned how to encrypt and decrypt connectionStrings section in web.config file using aspnet_regiis.exe command line tool now I will explain code behind method to encrypt and decrypt the connection string section in web.config.
 
Second Method: In second method I will use RSAProtectedConfigurationProvider and DataProtectionConfgurationProvider to encrypt and decrypt connectionStrings in web.config using asp.net.
 
First open Default.aspx page and write the following code
<html xmlns="http://www.w3.org/1999/xhtml">
<head  runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1"  runat="server">
<div>
<asp:Button id="btnEncrypt" runat="server" Text="Encrypt" onclick="btnEncrypt_Click" />
<asp:Button ID="btnDecrypt" runat="server" Text="Decrypt" onclick="btnDecrypt_Click" />
</div>
</form>
</body>
</html>
After that open code behind page and add the following namespace references
using System;
using System.Configuration;
using System.Web.Configuration;
After add namespaces write the following code in code behind

C# code

string provider = "RSAProtectedConfigurationProvider";
string section = "connectionStrings";
protected void Page_Load(object sender, EventArgs e)
{
 
}
protected void btnEncrypt_Click(object sender, EventArgs e)
{
   Configuration confg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
   ConfigurationSection configSect = confg.GetSection(section);
   if (configSect != null)
   {
      configSect.SectionInformation.ProtectSection(provider);
      confg.Save();
   }
}
 
protected void btnDecrypt_Click(object sender, EventArgs e)
{
   Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
   ConfigurationSection configSect = config.GetSection(section);
   if (configSect.SectionInformation.IsProtected)
   {
      configSect.SectionInformation.UnprotectSection();
      config.Save();
   }
}

VB.NET

Imports System.Web.Configuration
Partial Class _Default
   Inherits System.Web.UI.Page
   Private provider As String = "RSAProtectedConfigurationProvider"
   Private section As String = "connectionStrings"
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
   End Sub
   Protected Sub btnEncrypt_Click(ByVal sender As Object, ByVal e As EventArgs)
      Dim confg As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath)
      Dim confgSect As ConfigurationSection = confg.GetSection(section)
      If confgSect IsNot Nothing Then
         confgSect.SectionInformation.ProtectSection(provider)
         confg.Save()
      End If
   End Sub
   Protected Sub btnDecrypt_Click(ByVal sender As Object, ByVal e As EventArgs)
      Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath)
      Dim confgSect As ConfigurationSection = config.GetSection(section)
      If confgSect.SectionInformation.IsProtected Then
         confgSect.SectionInformation.UnprotectSection()
         config.Save()
      End If
   End Sub
End Class
 
After that open web.config file in application and add sample db connection in connectionStrings section like this 
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=RahulMittal;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings >
Now run your application and check your web.config file after click on Encrypt button that would be like this 
<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>WagJ9DDjWTNc1nmYVNQXaQqXalQzXaiCHAOtUJvTWBRZiuT6UK1fBElM80PnL6dC5Umb8qvfHdkSMgoMW9CJzwOTZ0zTy17JBGZqRQmlfW2G9LacoWIil0UrxjhgmJmRXhwXHFpdGwEVl7AoQGVlJGabXuChutaTxmfGOoUbCr0=</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>qry5qnr3qxOgyoNPeP7OKEiHpr/PPTsaeQ2mYUsSK7cg4Kkl9uPO4RyUXgBIkgCTsjbObqLlyndcSBnYyek6bxG/IBL82G1R5J1ci8i1eyt8kIDqouzYOx5vtouErld4z1L+7WGf9Wg37QAH5RiiEfkCHndJJq3dTqjxnnXZSno6NgbxSXDfqzwE/eKDVhGV3oaTQSfjVmO8e5a9wvREYeeyasDhojx8J2mdy7/Q9rEIpv98RTiRxA==</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings> 
If we want to implement encryption and decryption with “DataProtectionConfigurationProvider” just replace “RSAProtectedConfigurationProvider” with “DataProtectionConfigurationProvider” and use same code.