[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Cannot Decrypt Using Certificate

I have an encryption certificate set up for my DB.  All the fields i'm trying to encrypt are varbinary(128).  I inserted a few rows using the certificate to encrypt.  The data appears to be encrypted.  here is a sample of what on column/row looks like:

0x8F3CBF38BB459DB15DE114024C0A4F3EB04E9E63B9C2250AA2792D39686DB9158B0C11A3C3D3784A90B5CA0AEF0213CF11C902B1862D221D3082BB812746EA8DAAA6A14519A0CE76230863B604C443B1984D51267C638AB169D6EBFA7326AA6B97829DF036C7A48E37EF79B57857D5594387A39C953A49599F4F5C77712F0F5E

That is my credit card number field

However, when i attempt to decrypt it i get NULL.  What am i doing wrong? Below is the query i'm using to decrypt
Select	CONVERT(VARCHAR(50),DecryptByCert(Cert_ID('InfinityCertificate'),CreditCardNumber)),
		CONVERT(VARCHAR(50),DecryptByCert(Cert_ID('InfinityCertificate'),ExpMonth)),
		CONVERT(VARCHAR(50),DecryptByCert(Cert_ID('InfinityCertificate'),ExpYear)) 
from	ReservationRequest

Open in new window

0
davidcahan
Asked:
davidcahan
1 Solution
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
make sure that the user that is doing the commands has VIEW DEFINITION on the certificate and CONTROL permissions on the certificate.

GRANT CONTROL ON CERTIFICATE :: InfinityCertificate
    TO [username in database]
GRANT VIEW DEFINITION ON CERTIFICATE :: InfinityCertificate
    TO [username in database]
0
 
davidcahanAuthor Commented:
good point....i will need that tibit when i go to production as my DB User will not be set to DB_OWNER.  However, to fix i had to force regenerate the SMK.  everything worked as expected after that.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now