Cannot Decrypt Using Certificate

Posted on 2009-04-22
Last Modified: 2012-05-06
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:


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)),



from	ReservationRequest

Open in new window

Question by:davidcahan
    LVL 24

    Accepted Solution

    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]
        TO [username in database]

    Author Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now