CODEWITHSUNDEEP
sql-serverencryptionsql-insert
FelipeFonsecabh
FelipeFonsecabh

Reputation: 187

Can't insert encrypted data?

I'm trying to insert encrypted data into my SQL Server table. First step, I created a master key, a certificate and a private key:

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'PASSWORD@123'
GO

CREATE CERTIFICATE ElipseCert
ENCRYPTION BY PASSWORD = 'SENHA@123'
WITH SUBJECT = 'Certificado Senha Usuario'
GO

CREATE SYMMETRIC KEY KeyElipse
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ElipseCert 
GO

After I tried to insert data:

OPEN SYMMETRIC KEY KeyElipse
DECRYPTION BY CERTIFICATE ElipseCert

DECLARE @GUID UNIQUEIDENTIFIER = (SELECT KEY_GUID('KeyElipse'))
INSERT INTO Usuario VALUES ('FONSECA', ENCRYPTBYKEY(@GUID, 'Abcd1234'))
GO

SELECT * FROM Usuario

CLOSE SYMMETRIC KEY KeyElipse

But when I executed the code returns me:

The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key.

What's wrong?

Thanks a lot!

Upvotes: 0

Views: 69

Answers (1)

Ilyes
Ilyes

Reputation: 14928

The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key

So provide the password, replace this code

OPEN SYMMETRIC KEY KeyElipse
DECRYPTION BY CERTIFICATE ElipseCert

with this one

OPEN SYMMETRIC KEY KeyElipse
DECRYPTION BY CERTIFICATE ElipseCert WITH PASSWORD = 'SENHA@123';

Upvotes: 1

Related Questions