Decrypt column data in SQL server

CREATE FUNCTION [dbo].[Decrypt] ( @EncryptedString NVARCHAR(4000) )

RETURNS VARCHAR(4000) WITH ENCRYPTION AS

BEGIN

DECLARE @vClearString VARCHAR(4000)

DECLARE @vIdx INT

DECLARE @vBaseIncrement INT

SET @vIdx = 1

SET @vBaseIncrement = 128

SET @vClearString = ''''

WHILE @vIdx <= LEN(@EncryptedString)

BEGIN

SET @vClearString = @vClearString +

CHAR(UNICODE(SUBSTRING(@EncryptedString, @vIdx, 1)) -

@vBaseIncrement - @vIdx + 1)

SET @vIdx = @vIdx + 1

END

RETURN @vClearString

END

To encrypt check following...

http://binarymyth.blogspot.com/2010/06/encrypt-column-data-in-sql-server.html

Enterprise Library 2.0 Encrypted ConnectionString

Enterprise Library 2.0 connection string is not encrypted by default. So recompile "Microsoft.Practices.EnterpriseLibrary.Data" DLL with following change...

In Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabaseAssembler decrypt "connectionStringSettings.ConnectionString" as you wish before calling SqlDatabase

public Database Assemble(string name, ConnectionStringSettings connectionStringSettings, IConfigurationSource configurationSource)

{

string conn = YourDecryptionLogic(connectionStringSettings.ConnectionString)

return new SqlDatabase(conn);

}