Encrypt Data using Extended Stored Procedure

Extended Store procedures are DLLs that an instance of sql server can load and run dynamically. You can create your own extended stored procedure for external routine using programming language such as C or You can use already created one. In this article I have use xp_md5.dll to explain data encryption using hash5 algorithm. I got this DLL from http://www.cr0.net:8040/code/crypto/md5/ .

1. Extract this DLL to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn (or appropriate place)
2. Create Extended Stored procedure called xp_md5 in the master data base. Right click on Extended Stored Procedure and select New Extended Store Procedure. Enter xp_md5 for name and select full path to dll.( C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\ xp_md5.dll)

Or
EXEC sp_addextendedproc 'xp_md5', 'xp_md5.dll'
3. Create a user-defined function for each database in which you plan to use the MD5 procedure

CREATE FUNCTION [dbo].[fn_md5] (@data TEXT)
RETURNS CHAR(32) AS
BEGIN
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
RETURN @hash
END

Use user define function (fn_md5)
SELECT dbo.fn_md5('Lakshman')

Out Put

47a502ffc3c3b43a4e4856fa88290bdd

Or if you want store return value in variable.
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 'Lakshman', -1, @hash OUTPUT
PRINT @hash
Result will same.

Paint Ball Tournament (interblocks)- 27 June 2007

Before start the game

After finish 1st round

Form Authentication with SQL Membership provider

This is mainly concerned about form authentication with SQL Server membership provider. Forms authentication with SQL Server is most applicable in situations where users of your application are not part of your Windows domain, and as a result, they do not have Active Directory accounts.

In Here , I'm trying to explain how to create a login page using the new membership Login control, configure your Web application to use forms authentication, create the user store database, grant database access to your Web application account, configure ASP.NET membership settings, and set password complexity rules.

You create a simple Web application with a index page and a login page. The login page allows existing users to login and new users to register by creating new accounts.

1. Open Visual Studio 2005 and create new web application delete or rename default.aspx as index.aspx(index page has been used as home page in this example). Add new page named as Login.aspx.

2. Add Login control from the toolbox.

In this control contains displays user name and password fields and a Remember me next time check box. If the user selects this check box, a persistent authentication cookie is created and the user's browser stores it on the user's hard disk.

3. Configure web application for Form authentication.
Select web.config file on Solution explorer


<authentication mode="Forms"/><forms timeout="30" protection="All" defaulturl="index.aspx" loginurl="login.aspx" name=".ASPXAUTH""/></authentication"/>

Note: If just put authentication mode as Forms and omit other element then It will load defualt setting form Mechine.Config.comments file . That defauls setting are given below.


<forms defaulturl="default.aspx" loginurl="login.aspx" name=".ASPXAUTH" />
<credentials passwordformat="SHA1" ></forms/>

4. Add element under element in the Web.Config file. This will allow to all authenticated users to access you web site.

<authorization/><deny users="?"/><allow users="*"></authorization/>

In here, "?" indicates unauthenticated users and "*" indicates all users.

Create a data base to store user details.

You can create your SQL Server user store manually by using Aspnet_regsql.exe from the command line. Alternatively, you can run Aspnet_regsql.exe in Wizard mode or use the ASP.NET Web Site Configuration tool available on the Website menu in Visual Studio .NET 2005. In this example , manual method is used by me.

Use aspnet_regsql to create membership by using Visual studio 2005 Command prompt.
Start>All Program>Microsoft Visul Studio 2005>Visul Studio Tools>Visul studio 2005 command prompt

run Aspnet_regsql.exe with following parameters -S -A m -E

systex

aspnet_reg.exe -S (local) -A m -E

-S -- specified the server name
-E -- specified windows authentication to connecte SQL server.
-A m -- specified Add membership features only

for all command run aspnet_reg.exe /?

if you want create membership in your own sql data base run aspnet_regsql.exe with following parameters.


aspnet_reg.exe -S (local) -d -A m -U -P


to see the result of go to SQL server Open data base if you created on the existing batabase Membership related tables can see on that otherwise new database has been created on sql server named aspnetdb.

Within the Aspnetdb database, the following tables are created if they do not exist:

aspnet_Applications
aspnet_Membership
aspnet_SchemaVersions
aspnet_Users

5. Grant Database Access to Your Web Application Account

Your Web application process account requires access to the Aspnetdb database(or your own databse). If you run your application under Microsoft Internet Information Services (IIS) 6.0 on Windows Server 2003, the NT AUTHORITY\Network Service account is used by default to run Web applications.


6. Configure Membership setting on Web.Config

In the Web.config file, add a connection string similar to the following to point to your membership database.

<connectionStrings > <add name="MyConStr" connectionString="Initial Catalog=aspnetdb;data source=localhost;Integrated Security=SSPI;" /> </connectionStrings>



In this example, MyConStr is the name you will use for this connection. Set the connectionString attribute to the membership database you created earlier.

Add <Membership/> to config file after then <authorization/> element .

<membership defaultProvider="MySqlMembershipProvider" > <providers> <clear/> <add name="MySqlMembershipProvider" connectionStringName="MyConStr" applicationName="MyAppName" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> </providers> </membership>

check the connectionStringName attribute to the same name ("MyConStr") you have assigned in the above connectionStrings section. Also set the applicationName attribute to a unique value representing the application. User details are organized by application name within the membership database.

Note You must set the defaultProvider attribute of the <membership> element to point to the provider definition. The login controls use this attribute to determine which property to use by default. However, these controls also expose a MembershipProvider property that lets you use non-default providers with the controls. While you can also set the MembershipProvider property of the login controls, it is good practice to set the defaultProvider attribute.