SQL ServerTVVideos
1K
0

Encriptando datos en SQL paso a paso

En muchas ocasiones como programadores nos toca encriptar información sensible de nuestras bases de datos.

Para poder hacer esto en SQL Server o Azure SQL tenemos distintas alternativas que nos proporciona el propio motor.

En el siguiente video tutorial te voy a mostrar paso a paso algunas de estas opciones que podes utilizar.

Aca te dejo el código de ejemplo que he utilizado

--- Demos: Encriptando datos en MSSQL
USE AdventureWorks2019 
GO
----------------------------------------------------------------
--Using a Function to Encrypt By Passphrase
-----------------------------------------------------------------
CREATE TABLE #SecretInfo
(Secret1 varbinary(8000) NOT NULL,
 Secret2 varchar(8000) null,
 Secret3_int varbinary(8000) null)
 
GO
-- USAMOS LA FUNCION EncryptByPassPhrase PARA INSERTAR VALORES CIFRADOS
INSERT #SecretInfo
(Secret1,Secret2,Secret3_int)
SELECT 
EncryptByPassPhrase(
'Clave',
'Varbinary'),
EncryptByPassPhrase(
'Clave',
'Varchar'),
EncryptByPassPhrase(
'Clave','100')
-- HACEMOS UN SELECT COMUN Y VEMOS QUE EL VALOR QUEDO CIFRADO
SELECT *
FROM #SecretInfo
-- HACEMOS UN SELECT USANDO DecryptByPassPhrase PARA MOSTRAR LA INFORMACION DESCIFRADA
SELECT CAST(DecryptByPassPhrase(
'Clave',Secret1) as varchar(50)) as secret1,
CAST(DecryptByPassPhrase(
'Clave',
Secret2) as varchar(50)) as secret2,
cast(cast(DecryptByPassPhrase('Clave',Secret3_int) as varchar(50)) as int)  
as secret3
from #SecretInfo 

drop table #SecretInfo
-------------------------------------------------------------------------------------
-------------------------------  CERTIFICADOS ---------------------------------------
-------------------------------------------------------------------------------------
--Creating a Database Certificate
CREATE CERTIFICATE cert_triggerdb
ENCRYPTION BY PASSWORD = 'AA5FA6AC'
WITH SUBJECT = 'Certificado Triggerdb ',
START_DATE = '7/15/2005', EXPIRY_DATE = '10/15/2026'
--Viewing Certificates in the Database
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM sys.certificates
--Backing Up and Restoring a Certificate
BACKUP CERTIFICATE cert_triggerdb
TO FILE = 'c:\temp\cert_triggerdb.BAK'
WITH PRIVATE KEY ( FILE = 'c:\temp\certTriggerdbPK.BAK' ,
ENCRYPTION BY PASSWORD = '3439F6A',
DECRYPTION BY PASSWORD = 'AA5FA6AC' )
--Managing a Certificate’s Private Key
drop table IF EXISTS #PasswordHint

CREATE TABLE #PasswordHint
(CustomerID int NOT NULL PRIMARY KEY,
 Username varchar(300) NOT NULL,
 userPassword   varbinary(1000) NOT NULL)
GO
--Using Certificate Encryption and Decryption
INSERT #PasswordHint
(CustomerID, Username, userPassword)
VALUES
(1, 'Triggerdb',
EncryptByCert(Cert_ID('cert_triggerdb'), 'Clave1'))

Select * from #PasswordHint

SELECT CAST(userPassword as varchar(200)) Password
FROM #PasswordHint
WHERE CustomerID = 1

SELECT CAST(DecryptByCert(Cert_ID('cert_triggerdb'),
userpassword, N'AA5FA6AC') 
as varchar(200)) Password
FROM #PasswordHint
WHERE CustomerID = 1

-- BORRAMOS CERTIFICADO
DROP CERTIFICATE cert_triggerdb
GO

-- CREAMOS CERTIFICADO POR MEDIO DE BACKUP
CREATE CERTIFICATE cert_triggerdb
FROM FILE = 'c:\temp\cert_triggerdb.BAK'
WITH PRIVATE KEY (FILE = 'c:\temp\certtriggerdbPK.BAK',
DECRYPTION BY PASSWORD = '3439F6A',
ENCRYPTION BY PASSWORD = 'AA5FA6AC')
GO

Select * from #PasswordHint

SELECT CAST(DecryptByCert(Cert_ID('cert_triggerdb'),
userpassword, N'AA5FA6AC') 
as varchar(200)) Password
FROM #PasswordHint
WHERE CustomerID = 1

DROP CERTIFICATE cert_triggerdb
GO

----------------------------------------------------------------------------------------
------------------------------- CLAVES ASIMETRICAS -------------------------------------
----------------------------------------------------------------------------------------
--Creating an Asymmetric Key
DROP ASYMMETRIC KEY asy_TRIGGERDB

CREATE ASYMMETRIC KEY asy_TRIGGERDB
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'EEB0B4DD'
--Viewing Asymmetric Keys in the Current Database
SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys
--Encrypting and Decrypting Data using an Asymmetric Key
drop table IF EXISTS #PasswordHint

CREATE TABLE #PasswordHint
(CustomerID int NOT NULL PRIMARY KEY,
 Username varchar(300) NOT NULL,
 userPassword   varbinary(1000) NOT NULL)
GO

INSERT #PasswordHint
(CustomerID, Username, userPassword)
VALUES
(1, 'Triggerdb',
 EncryptByAsymKey(AsymKey_ID('asy_TRIGGERDB'),
'Clave del usuario'))

SELECT * FROM #PasswordHint

-- 

SELECT CAST(DecryptByAsymKey
( AsymKey_ID('asy_triggerdb'),

userPassword,
N'EEB0B4DD') as varchar(100)) BankRoutingNBR
FROM #PasswordHint

You must be logged in to post a comment.