Encryption of password using MS SQL Server-2008

While developing a webpage/ application, generally we have to develop login panel to verify authenticity of the user. In any such panel we need password. Now main concern is to store the password in the database.

There are so many ways to encrypt the data. We usually find that any encryption method comes with its decryption as well. But using those processes we will make our passwords insecure. Recently I found a function in SQL Server-2008 that is developed for encrypting the password. For checking the password, it has an another method the compares the plane text password with the encrypted password.

For encrypting the password, the method is

PWDENCRYPT('plane_text')

and for comparing the encrypted string with plane string is

PWDCOPMARE('plane_text', encrypted_text)


first function encrypt the plane_text into nvarchar type string and second function returns 1 if both string is same else returns 0.

One more thing PWDENCRYPT('plane_text') method returns different values i.e two different encryption of same string results in different encrypted strings. This implies that every time string is encrypted with different keys and that key is also part of the encrypted string.

Example:

SELECT PWDENCRYPT('plane_text') AS EncrptedString

output: 0x010052FFF61F36AB06A3A2DEA6A1C736FF0B7C8C646F6883C3EB


SELECT PWDCOMPARE('plane_text', 0x010052FFF61F36AB06A3A2DEA6A1C736FF0B7C8C646F6883C3EB) AS isSameString

output: 1


Note: When you will run first query, it's output will not be the same as mine as discussed above. But second query will return 1 as both are same string.

Getting System Information Using IP Address

We usually find the system support people maintaining the IT inventory manually. They visit to each machine and note down the related information like Model No, Serial No, Operation System, Processor, HDD capacity, RAM etc. Manual work comes with some human errors. So taking all point into the consideration I recently develop an application that fetches above metioned information only after passing the IP address of the machine. For that you will be needed admin username and password.

For this purpose I have taken help of WMI (Window Management Instrumentation) . WMI is the infrastructure and management data and operation on windows based OS.

The Win32_OperatingSystem WMI class represents a Windows-based operating system installed on a computer.

Using Win32_OperatingSystem, we can get various information like

Computer Name,

Description of Operating System,

Free Physical Memory,

Serial No.

and many more... for complete list visit http://msdn.microsoft.com/en-us/library/aa394239.aspx

The Win32_LogicalDisk WMI class represents a data source that resolves to an actual local storage device on a computer system running Windows.

Using this class I have just calculated the total HDD capacity.

For more details visit http://msdn.microsoft.com/en-us/library/aa394173%28v=vs.85%29.aspx

The Win32_ComputerSystem WMI class represents a computer system running Windows.

Using this class I have been able to get Total Physical Memory.

For more details visit http://msdn.microsoft.com/en-us/library/aa394102%28v=vs.85%29.aspx

The Win32_Processor WMI class represents a device that can interpret a sequence of instructions on a computer running on a Windows operating system.

Processor info can be collected using this class.

For more visit http://msdn.microsoft.com/en-us/library/aa394373%28v=vs.85%29.aspx

MAC address info is put under Win32_NetworkAdaperConfiguration class. Using Win32_ComputerSytemProduct class we can get Model No and Serial of the system.

I have created a class named SystemInfo to get above mentioned information. Here is the code...

using System;

using System.Management;

public class SystemInfo

{

ConnectionOptions options = new ConnectionOptions();

public string ipAddress { get; set; }

ManagementScope scope;

ObjectQuery ObjQuery;

ManagementObjectSearcher MgtObjSearcher;

ManagementObjectCollection MgtObjCollection;

public SystemInfo()

{

options.Username = "admin_username";

options.Password = "admin_password";

}

//get ComputerName, OS, HDD Size and RAM, Processor....

public string[] getSystemBasicInfo()

{

string[] strSysInfo = new string[5];

ManagementScope scope = new ManagementScope("\\\\" + ipAddress + "\\root\\cimv2", options);

try

{

scope.Connect();

ObjQuery = new SelectQuery("SELECT * FROM Win32_OperatingSystem");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

foreach (var m in MgtObjCollection)

{

strSysInfo[0] = m["csname"].ToString(); //Computer Name

strSysInfo[1] = m["Caption"].ToString(); //OS Description

}

//HDD size in GB...

ObjQuery = new SelectQuery("Select * from Win32_LogicalDisk");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

double DiskSize = 0;

foreach (var m in MgtObjCollection)

{

DiskSize += Math.Round(Convert.ToDouble(m["Size"]) / (1024 * 1024 * 1024), 2);

}

strSysInfo[2] = DiskSize.ToString();

//RAM...

ObjQuery = new SelectQuery("Select TotalPhysicalMemory from Win32_ComputerSystem");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

foreach (var m in MgtObjCollection)

{

strSysInfo[3] = Math.Round(Convert.ToDouble(m["TotalPhysicalMemory"]) / (1024 * 1024 * 1024), 3).ToString();

}

//Processor...

ObjQuery = new SelectQuery("Select * from Win32_Processor");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

string sCpuInfo = String.Empty;

foreach (var m in MgtObjCollection)

{

strSysInfo[4] = m["Name"].ToString().Trim();

}

}

catch(Exception exp)

{

Utility util = new Utility();

strSysInfo = new string[5] { util.getErrorMessage(exp), util.getErrorMessage(exp), "0.0", "0.0", util.getErrorMessage(exp) };

}

return strSysInfo;

}

//get MAC Address...

public string getMACAddress()

{

ManagementScope scope = new ManagementScope("\\\\" + ipAddress + "\\root\\cimv2", options);

scope.Connect();

ObjQuery = new SelectQuery("Select * from Win32_NetworkAdapterConfiguration");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

string strMACAddress = "";

foreach (var m in MgtObjCollection)

{

if (m["macaddress"] != null)

{

if (Convert.ToBoolean(m["IPEnabled"]))

{

strMACAddress = m["macaddress"].ToString();

strMACAddress = strMACAddress.Replace(":", "-");

break;

}

}

}

return strMACAddress;

}

//get Serial No....

public string getSerialNo()

{

ManagementScope scope = new ManagementScope("\\\\" + ipAddress + "\\root\\cimv2", options);

scope.Connect();

ObjQuery = new SelectQuery("Select * from Win32_ComputerSystemProduct");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

string strSerialNo = "";

foreach (var m in MgtObjCollection)

{

strSerialNo= m["IdentifyingNumber"].ToString();

}

return strSerialNo;

}

//get Model No....

public string getModelNo()

{

ManagementScope scope = new ManagementScope("\\\\" + ipAddress + "\\root\\cimv2", options);

scope.Connect();

ObjQuery = new SelectQuery("Select * from Win32_ComputerSystemProduct");

MgtObjSearcher = new ManagementObjectSearcher(scope, ObjQuery);

MgtObjCollection = MgtObjSearcher.Get();

string strModelNo = "";

foreach (var m in MgtObjCollection)

{

strModelNo = m["Name"].ToString();

}

return strModelNo;

}

}

Opening window without menu and address bar

In the recent project I had to open the page without browser's menu bar, toolbar and also without address bar. So I tried following to achieve my goal...

Before the default page I put a html page and on its body onload called a user defined wininit() method which first opens the default page and after that closes the contained page.

wininit() method first opens the default page (the page we are willing to open) and again opens a blank page with target= _self and then closes it.

Here is our wininit() method...

function wininit()

{

var win = window.open("index.aspx", "HomeOpener", "status=0, toolbar=0, menubar=0, resizable=1, width=" + screen.availWidth + ", height = " + screen.availHeight + '"');

win.moveTo(0, 0);

window.open('', '_self', '');

window.close();

}

Here index.aspx is our default page.

Notice that we are opening a blank page before calling the window.close() method. It is done all because it could produce a warning message for closing the window.

Also notice that new window is open as maximized as its width and height is set as screen height and width.

Put the wininit() method in head block and call it on body onload.

Note: It works fine with IE and Google Chrome but in Mozilla Firefox it opens the default window as per our desire but does not close the container page.

Remember Me option in login page

There is a popular option in login page i.e Remember me. If we check Remember me, it remembers login-id and password after logging out from the website. Also when Remember me is not checked, it work as a normal control... means it does not remember the login-id or password after exit the website.

When Remember me is checked, it simply saves the login credentials (of course valid one) into the browser in form of cookie.

So after successful login, credentials is written into cookie. Also after logout, cookie is not destroyed and is added to the http response. It is tracked at the login page and login-id and password is set.

After checking the credential call this method...

public void WriteCredentials() {

HttpCookie cookie = new HttpCookie("Login_Credentials");

if (chkRememberMe.Checked)
{
cookie.Values.Add("user_name", txtUserName.Text);
cookie.Values.Add("user_password", txtPassword.Text);

cookie.Expires.AddDays(30); //Cookie will expire after 30 days.
}
else
{
cookie.Values.Add("user_name", string.Empty);
cookie.Values.Add("user_password", string.Empty);

cookie.Expires.AddMinutes(2); //Cookie with empty credential strings.
}
Response.Cookies.Add(cookie); //Finally cookie is added to the http response.
}


Add following code-snips in Sign-out function...

if (Response.Cookies["Login_Credentials"] != null) {
HttpCookie cookie = Request.Cookies.Get("Login_Credentials");

cookie.Expires = DateTime.Now.AddDays(30);

Response.Cookies.Add(cookie);
}

Now after singing out, again cookie is added to the response. It will be tracked in login page at Page Load...

if(!IsPostPack)
{
//Remember me...
if (Request.Cookies["Login_Credentials"] != null)
{
HttpCookie cookie = Request.Cookies.Get("Login_Credentials");
txtUserName.Text = cookie.Values["user_name"];

//Notice that how the value of password is set.
txtPassword.Attributes.Add("value", cookie.Values["user_password"]);

if (txtUserName.Text != "")
chkRememberMe.Checked = true;
else
chkRememberMe.Checked = false;
}
}

This code is again a tested one.

Drawing graph in MS Excel using C#

Platform: ASP.NET-2.0
Database: MS SQL Server-2005
AdventureWork (Download)

In this article we will try to learn drawing graph in MS Excel.

For this article, we have used Customer table of Northwind database. Here we are counting companies in various countries.

First we populate the GridView. This will give you an idea about the data only. There is nothing to do with drawing the graph.

On clicking btnExport, all functionality of drawing graph is written.

DatatTable that has the resultant table of the query using which we have to draw the graph, first it is being written on the Excel file and on the basis of the data written on the excel, graph is plotted. Data written in the excel file and the graph plotted into it is interrelated. At the end don’t forget the go through the function ReleaseObject(). Also there will be needed a .Net reference of Microsoft.Office.Interop.Excel

Excel.Application xlApp;

Excel.Workbook xlWorkBook;

Excel.Worksheet xlWorkSheet;

object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();

xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

DataTable dt = new DataTable();

dt = PopulateCompanyCount(0);

xlWorkSheet.Cells[1, 1] = "";

xlWorkSheet.Cells[1, 2] = "Count";

for (int i = 0; i <>

{

for (int j = 0; j <>

{

xlWorkSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();

}

}

chartRange = xlWorkSheet.get_Range("A1", str);

chartPage.SetSourceData(chartRange, misValue);

chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

string strPath = @"C:\Documents and Settings\awadheshs\My Documents\";

string strFileName = "CompanyCount";

xlWorkBook.SaveAs(strPath + strFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Close(true, misValue, misValue);

xlApp.Quit();

releaseObject(xlWorkSheet);

releaseObject(xlWorkBook);

releaseObject(xlApp);

lblAlert.Text = "Excel file created...
" +

"Created on: " + DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss") +

"
Collect the file " + strFileName + " at location " + strPath;

//This function will be used as it is….

private void releaseObject(object obj)

{

try

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

obj = null;

}

catch (Exception ex)

{

obj = null;

lblAlert.Text = "Exception Occured while releasing object " + ex.ToString();

}

finally

{

GC.Collect();

}

}

Click on download to get the complete code

Exporting GridView to MS Excel using C#

Platform: ASP.NET-2.0
Database: MS Sql Server-2005
AdventureWork (download)

Main aim of this article is to export ASP.NET-2.0 GridView data into MS Excel. Additionally this article will focus on proper report formatting as well.

We have taken data from Customer table of default database Northwind.

Before going into export to excel functionality, first we have to fill the GridView.

We assume GridView is filled and its matter of exporting it into Excel. (Refer code attached with this article. AdventureWork).

There is button with ID btnExport on its Click event; we have to write the functionality of exporting. First set AllowPaging property of GridView to false so that whole data can be exported into single Excel file.

grdCustomerDetails.AllowPaging = false;

And then fill the GridView. At this time formatting GridView can be done so that it might reflect into Excel file.

grdCustomerDetails.ForeColor = Color.Black;

grdCustomerDetails.Style.Add("font-family", "Verdana");

grdCustomerDetails.Style.Add("font-size", "10px");

Here are few lines of codes that are self explanatory…

Response.Clear();

Response.AddHeader("content-disposition", "attachment; filename=Customer_Detailed_Report.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd-ms.xls";

StringWriter stringWrite = new StringWriter();

HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

While exporting we have to take care of report header as well. So for this purpose take a label control with visibility false. It will help in rendering the report header information with its text property.

lblExcelHeader.Visible = true;

string strReportHeader = "Employee Detailed Report
" +

"Report Generated on: " + DateTime.Now.ToString("dd-MMM-yyyy") + "
" +

"" +

"Note: This is a demo report";

lblExcelHeader.Text = strReportHeader;

lblExcelHeader.RenderControl(htmlWrite);

lblExcelHeader.Visible = false;

grdCustomerDetails.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();