Encrypt/Decrypt Data with SQL Server 2005 : Security with SQL Server 2005

Now a days security is very much concern for websites. We can use many security options. Like use an SSL, encrypt/decrypt with C# code and another option is to use the encryption/decryption in SQL Server 2005.

There is Great Functionality in SQL Server 2005 Which Comes Handy to make your web site Secure.I will Give You Some Magic Code to Encrypt and decrypt data in SQL server.To Encrypt/Decrypt in SQL Server you need One Key(Secured Key), which we can use. Here is the syntax for creating a key to Encrypt/Decrypt data in SQL Server.

Create Symmetric Key SecuredKey

With ALGORITHM=DESX

ENCRYPTION BY PASSWORD = 'Haresh';

Here “Haresh” is Password and “Secured Key “is Key Name To process data key generated above should be open first to Encrypt/Decrypt in SQL Server.

Open Symmetric Key SecuredKey

DECRYPTION by Password=N'Haresh';

After Opening key you can perform Encryption or decryption in SQL Server

Below In The Complete Code For Above Operations.

Declare @Encrypted varbinary(max)

Declare @Decryptrd varchar(100)

Open Symmetric Key SecuredKey

DECRYPTION by Password=N'Haresh';

set @Encrypted= EncryptByKey (Key_GUID('SecuredKey'),'Haresh Dhameliyua')

set @Decryptrd =DecryptByKey (@Encrypted)

Print @Encrypted

Print @Decryptrd

CLOSE SYMMETRIC KEY SecuredKey;

Please let me know if you have further questions regarding Encrypt/Decrypt in SQL Server.

Remove Invalid Characters Using C#,SQL or Javascript

To provide search your application some time you need to replace invalid Characters from value submitted by user to prevent SQL injection or some other resons.at this time you can do this in three defferent ways.

You can use one of the tree way for remove Special Characters from String as per you search requirement

(1)Javascript

Use Regular Expression For Replacing Invalid Charactervar invalidChars=document.getElementById("txtAllchars").value; document.getElementById("txtAllchars").value=invalidChars.replace(/[^a-zA-Z 0-9]+/,'');

(2).C#:

Yu can Use System.Text.RegularExpression Name spaceUsing RegEx Class You can aloow Only Valida Characters other Invalid Character will be removed

objProperty.ZipCode = Regex.Replace(txtZipCode.Text, "[^A-Za-z0-9]+", "");

(3).SQL.

In SQL There Is Not Direct method To Do this So You Need To Apply Following Code To perform task.

declare @strMain varchar(200) declare @charsToReplace varchar(10) set @charsToReplace ='^&%' set @strMain='Haresh%%^^&&' declare @i int set @i=len(@charsToReplace) while(@i>0) Begin declare @tmp char set @tmp= substring(@charsToReplace,@i,len(@charsToReplace)) set @strMain= replace(@strMain,@tmp,'') set @i=@i-1 End print @strMain

How to set Favicon in Mozilla and IE

How to set Favicon in Mozilla and IE Hi. As we Know in all web there is always Icon before site URL in addressbar. We need Favicon.ICO File and Put Following Code in Head Tag of Page link rel="shortcut icon" href="~/App_Themes/SkinFile/Images/favicon.gif" This Code Will Not Works For Some Versions of Internet Explorer. I Found Solution For That Just Pu Folowing Code You will Get Favicon in Both Mozill and IE link rel="shortcut icon" href="../App_Themes/SkinFile/Images/favicon.ico" type="image/vnd.microsoft.icon" Thanks.

Change ASP.NET Validator ErrorMessage Using Javascript

This Post helps You in Changing Validatiors Errormessage Dynamically One Upon in I was Developing Video Upload Module Using Teleric File Upload. I want that FileUpload input should not blank .as well Should Accept Only .FLV Files.As i can be able to useRequireFieldValidatior .So I Use CustomValidator . In CustomeValidator Using ClientValidationFunction="validateRadVideoUpload" Property U can write fileInputs[0].value.length <= 1 to validate for '"Required Filed". So what For it should Contain .FLV at the end?? It Is easy to check Using Regular Expression. Now Problem Is Here. How will you change ErrorMessage Of CustomValidator To "Required Valid File" If user Enter file with invalid Extension. I have To Use Javascript Right?? Here is The Solutions. var Validate_VideoclientId='<%= Validate_Video.ClientID %>'; var tmp_Validate_VideoclientId = document.all ? document.all[Validate_VideoclientId] : document.getElementById(Validate_VideoclientId); tmp_Validate_VideoclientId.errormessage = "Please select valid VideoFile"; This the way u can change ErrorMessage Property of ValidationControl Dynamically. Nees Morehelp?? Write me on This Blog..Thanks

Find Currently running query/process in MS SQL Server 2005

Hi Friends,

While managing a server, we might come across a point where we want to know which of our database is loading on the server.

Let's take a case. We have hosted many sites on a server with MS SQL Server 2005. Now the traffic on some sites suddenly increase or new code is uploaded to some of site. This causes the MS SQL Server 2005 and as well server to be on a hike of 100% CPU usage.

Now we want to investigate that which query or process of MS SQL Server 2005 causing to take the CPU on the 100%  of usage. I found many people that are searching for finding currently running query/process in MS SQL Server 2005. Here is a help for them to find out which queries are currently running in MS SQL Server 2005.

 

You can run this query.

SELECT * FROM MASTER..SYSPROCESSES

Other queries I found are

 

SELECT   st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

 

Now if you have the session id of your request then you can try with this query

SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st

Technorati Tags: MS SQL Server 2005,Currently running query in SQL Server 2005

WHERE r.session_id = <spid number>

Here <spid number> is your session id.