Geek asks and answers

Data Encryption in a SQL Server Column

Is there any algorithm on sql server for data encryption. I have a password field that I need to encrypt, but I have to encrypt the data after passing it in a stored procedure. This means that I cannot transfer 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
+5
source share
3 answers

This is a very quick example. It works with an asymmetric key . Hope this helps.

First: 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 always declare a variable that you want to decrypt or encrypt

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

Data decryption

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

Data encryption

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))
+4

, Pinal Dave . , SQL Server, , . , , .

+3

I would suggest salted sha1 or md5

CONVERT(VARCHAR(32), HashBytes('MD5', 'email@dot.com'), 2)

Although hashing is not technically encrypted.

-1
source

More articles:


All Articles