Encrypt the data in a column in SQL Server

Question

Is there any algorithm exist in the sql server to encrypt the data. I have a password field which i need to encrypt but i have to encrypt the data once its being passed in the stored procedure. It means i cannot pass the encrypted data from my c# code.

CREATE PROC saveData(@p1 VARCHAR(50),
                 @p2 VARCHAR(50))
AS
BEGIN
  DECLARE @encryptedP1 VARCHAR(50)

  SET @encryptedP1=dbo.fnEncrypt(@p1)  --ENCRYPTING THE DATA WITH AN INBUILT FUNCTION

  INSERT INTO table1
              (column1,
               column2)
  VALUES     (@encryptedP1,
              @p2)
END 

Answer 1

This is a very fast example. It works with Asymmetric Key. I hope this can help.

First off: Create your asymmetric Key using this code:

---

USE [yourDB]

GO

CREATE ASYMMETRIC KEY ClaveAsym

WITH ALGORITHM = RSA_2048 ---->Take a look in this type of algorithms

ENCRYPTION BY PASSWORD = 'yourKey'

GO

Remember this, you must to declare a variable always that you want to decrypt or encrypt

DECLARE @KEYID INT
SET @KEYID = AsymKey_ID('ClaveAsym')

---

Decrypting data

SELECT columnA, CAST(DecryptByAsymKey(@keyid, columnUser, N'yourKey') AS VARCHAR(100)),
        CONVERT(VARCHAR(MAX), DECRYPTBYASYMKEY(@keyId, columnPass, N'yourKey'))
FROM yourTable

---

Encrypting Data

DECLARE @User VARCHAR(MAX), @pass VARCHAR(MAX)

SET @User = 'yourUser'

sET @pass = 'yourPass'

DECLARE @KEYID INT SET @KEYID = AsymKey_ID('ClaveAsym') 

INSERT INTO yourTable( User, passw) VALUES EncryptByAsymKey (@keyid, @User ), EncryptByAsymKey (@keyid, @pass))

Answer 2

As usual, good old Pinal Dave has some useful information on this. It's a little involved but if you must do it in SQL Server then rather than in client side code then it should do the job for you. You might want to keep an eye out for performance issues though as it's quite a CPU intensive process.