How to Use Windows Authentication to Access SQL Server Through a ASP.NET Web Application

This sample application demonstrates how to use Windows authentication for Web-based intranet applications to access a SQL Server database using ASP.NET. Most of the intranet Web applications prefer to use Windows authentication because they are going to used by internal users.

We need to configure the following four different areas to access Windows integrated security:

1. SQL Server
2. IIS Web Server
3. ASP.Net web application
4. ConnectionString 

SQL Server be running on same IIS machine. If both are on different machines, we should go for analternative security model such as Forms authentication, which is not covered in this article. The access users must be in the same domain where the Web server is running.

Configuring SQL Server

To configure SQL Server for Windows integrated security:

1. From the Windows Start menu, choose Microsoft SQL Server, and then choose Enterprise Manager.
2. Open the node for the server and expand the node for the database you want to give users permissions for.
3. Right-click the Users node and choose New Database User.
4. In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK. Alternatively, configure the SQL Server to allow all domain users to access the database.

Configuring IIS

You need to configure your application in IIS to turn off anonymous access and turn on Windows authentication. To configure IIS for Windows integrated security:

1. In Windows, open the Internet Information Services administration tool.
2. Open the node for your server, and then open nodes until you find the node for your application, typically under Default Web Site.
3. Right-click your application and choose Properties.
4. In the Directory Security tab, click Edit.
5. In the Authentication Methods dialog box, clear the Anonymous Access box and make sure Integrated Windows authentication is checked.
6. Click OK to close all the dialog boxes.

Configuring the ASP.NET Web Application

In the application configuration file (Web.config), you establish the authentication mode that your application uses and establish that the application will impersonate the user's credentials—that is, that it will run as that user. To configure Web.config to allow Windows integrated security:

Open the Web.config file for your application and add the following elements to it:

<authentication mode="Windows"/>

<identity impersonate="true"/>

The <authentication> element might already be there. 

Creating Connection Strings

When you create a connection string to access SQL Server, you must include attributes that tell SQL Server that you are using Windows integrated security. To configure connection strings for Windows integrated security:

In any connection string for SQL Server, include the Trusted_Connection=Yes attribute and remove the username and password attributes. The following shows a typical connection string configured for Windows integrated security:

<"data source=Sql01;initial catalog=Northwind;

integrated security=SSPI;persist security info=False;

Trusted_Connection=Yes."/>

Sample C# code for connecting SQL server from ASP.Net application using windows authentication:

private void DataBind()

{

DataSet dataSet = new DataSet();

SqlConnection sqlConnection = new SqlConnection("data source=ServerName; initial catalog=Northwind; integrated security=SSPI; persist security info=False; Trusted_Connection=Yes");

sqlConnection.Open();

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT * FROM Employees", sqlConnection);

dataSet = new DataSet();

sqlDataAdapter.Fill(dataSet, "Employees");

GridView1.DataSource = dataSet.Tables["Employees"].DefaultView;

GridView1.DataBind();

}

Important settings in the web.config file are as follows:

<system.web>

<authentication mode = "Windows"/>

<identity impersonate="true"/>

<authorization>

<allow users = "*"/>

</authorization>

</system.web>

FileUpload Control Validation

With FileUpload control you can receive files from your web application users. Often, you don't want to receive all file types, but only specific extensions (e.g. images only) depending of your application requirements. Unfortunately, FileUpload control still have not some Filter property like Open and Save File dialogs in .NET Windows Forms to limit file types. Because of that, you need to write some additional code to be sure that user will upload regular file type.

Let say you have two web controls on web form, on FileUpload Control to select a file and one button control

Because in ASP.NET we have client and server side, validation could be done on client and on server. You need to have server side validation because of security reasons, but you also need client side validation because it is faster and more user friendly. Let see how to implement both types, for example our web application will allow only upload of .xls or .xml files.

FileUpload Control client side validation

You can use CustomValidator to implement FileUpload validation on client side. Possible implementation could be with code like this:

<asp:FileUpload ID="fuData" runat="server" />

&nbsp;<asp:Button ID="btnUpload" runat="server" Text="Upload" />

<br />

<asp:CustomValidator ID="CustomValidator1" runat="server"

 ClientValidationFunction="ValidateFileUpload" ErrorMessage="Please select valid .xls or .xml file"></asp:CustomValidator>

<script language="javascript" type="text/javascript">

    function ValidateFileUpload(Source, args) {

        var fuData = document.getElementById('<%= fuData.ClientID %>');

        var FileUploadPath = fuData.value;

        if (FileUploadPath == '') {

            // There is no file selected

            args.IsValid = false;

        }

        else {

            var Extension = FileUploadPath.substring(FileUploadPath.lastIndexOf('.') + 1).toLowerCase();

            if (Extension == "xls" || Extension == "xml") {

                args.IsValid = true; // Valid file type

            }

            else {

                args.IsValid = false; // Not valid file type

            }

        }

    }

</script> 

If visitor tries to upload wrong file type or FileUpload have empty value, CustomValidator will return an error message.

FileUpload Control server side validation

Because of security reasons, you need to validate FileUpload control on server side too. You can use the same idea like for client side validation. In this case, just use OnServerValidate event, like in code bellow:

 protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
{
    // Get file name
    string UploadFileName = fuData.PostedFile.FileName;

    if(UploadFileName == "")
    {
      // There is no file selected
      args.IsValid = false;
    }
    else
    {
        string Extension = UploadFileName.Substring(UploadFileName.LastIndexOf('.') + 1).toLower();

        if (Extension == "xls" || Extension == "xml")
        {
          args.IsValid = true; // Valid file type
        }
        else
        {
          args.IsValid = false; // Not valid file type
        }
    }
}



Now is your FileUpload Control protected on client and server side. I hope you found this tutorial helpful

Refresh an Update Panel using JavaScript

I have noticed a lot of discussion regarding methods to refresh an UpdatePanel via client script. This is very easy on the server side, of course. You can just call UpdatePanel.Update(). However, on the client side, the most common solutions I have been seeing just don’t feel right.

Many will advise you to use a hidden button control inside the UpdatePanel, manipulated via button.click(), to trigger a partial postback of the UpdatePanel. While it does work, I never have been able to get past the inelegant solution for this problem.

To find a better solution, we’ll need a demonstration UpdatePanel to experiment with:

<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div id="Container">
<asp:UpdatePanel runat="server" ID="UpdatePanel1" 
    OnLoad="UpdatePanel1_Load">
  <ContentTemplate>
    <asp:Label runat="server" ID="Label1" />
  </ContentTemplate>
</asp:UpdatePanel>

protected void UpdatePanel1_Load(object sender, EventArgs e)
{
  Label1.Text = DateTime.Now.ToString();
}


That’s a slightly modified version of the standard UpdatePanel DateTime 


hat the UpdatePanel’s OnLoad event is handled in code-behind. 

Anytime UpdatePanel1 is loaded or reloaded in a postback, Label1 will b
example. Instead of the more commonly used Button_Click trigger, notice 
te 
updated to reflect the current date and time.

Luckily, there’s an easy method for triggering a postback targeted at the 
UpdatePanel: __doPostBack(). 

As long as the event target of a __doPostBack() call is an async trigger of 
an UpdatePanel, the ASP.NET AJAX framework will intercept the postback and 
fire a partial postback instead. For purposes of demonstration, I’m going to 
add that to the OnClick event of the container div:






<div id="Container" onclick="__doPostBack('UpdatePanel1', '');">



Now, clicking anywhere in the UpdatePanel will trigger a partial postback, 
targeting the UpdatePanel. Since partial postbacks follow the full page lifecycle
this will fire UpdatePanel1_Load and update the Label’s text.


A word on _doPostBack
You may have noticed that there is also an ASP.NET AJAX specific method of 
the PageRequestManager named _doPostBack. While it works much like
__doPostBack, as long as the ASP.NET AJAX client framework is 
present, you should not call it directly. 
The original __doPostBack method performs identically, but is more robust 
since it gracefully degrades to full postbacks when the ASP.NET AJAX framework 
isn’t available. It’s also unlikely that __doPostBack will disappear in future 
versions of ASP.NET, while it’s less assured that the ASP.NET AJAX framework 
will remain unchanged.

Set focus to textbox after postback

In my web application which is developed using ajax, I had a problem. My textbox lost its focus after a postback, so I supposed to solve this issue with the textbox. Then I came with a solution using javascript. This may be helpful to some one

Put this javascript on your updatepanel page. You may have to change controls id strings.

<script type="text/javascript">

  var postbackElement;

  Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(beginRequest);

  Sys.WebForms.PageRequestManager.getInstance().add_pageLoaded(pageLoaded);

 

  function beginRequest(sender, args) {

      postbackElement = args.get_postBackElement();

  }

        

  function pageLoaded(sender, args) 

    {

      var updatedPanels = args.get_panelsUpdated();

      if (typeof(postbackElement) === "undefined") 

     {

          return;

      }

      else if (postbackElement.id.toLowerCase().indexOf('button1') > -1) 

       {

          for (i=0; i < updatedPanels.length; i++) { 

              alert('here');         

              document.getElementById('<%= TextBox1.ClientID %>').focus();

          }

      }

 

  }

</script>

How to Merge GridView Rows (Cells with Equal Values)

There are a lot of methods in the Internet solving the problem of how to merge GridView rows if neighbour cells show equal values. My approach is not the first; however, I think, it is rather universal and very short - less than 20 lines of code.

The algorithm is simple: to bypass all the rows, starting from the second at the bottom, to the top. If a cell value is the same as a value in the previous (lower) row then to increase RowSpan and to make invisible the lower cell, and so forth.

The code that merges cells is very short:

 public class GridDecorator

    {

        public static void MergeRows(GridView gridView)

        {

            for (int rowIndex = gridView.Rows.Count - 2; rowIndex >= 0; rowIndex--)

            {

                GridViewRow row = gridView.Rows[rowIndex];

                GridViewRow previousRow = gridView.Rows[rowIndex + 1];

                for (int i = 0; i < row.Cells.Count; i++)

                {

                    if (row.Cells[i].Text == previousRow.Cells[i].Text)

                    {

                        row.Cells[i].RowSpan = previousRow.Cells[i].RowSpan < 2 ? 2 :       previousRow.Cells[i].RowSpan + 1;

                        previousRow.Cells[i].Visible = false;

                    }

                }

            }

        }

    }

The last action is to add an OnPreRender event handler for the GridView:

protected void gridView_PreRender(object sender, EventArgs e)

    {

        GridDecorator.MergeRows(gridView);

    }