Posted on 2014-08-12
Last Modified: 2014-08-13
Following code is working FINE at my computer. im login as Db_Owner

When im runing at some collegue machine, Following error
The key is not encrypted using the specified decryptor

I gave grant to run, I created Group called Group1 and add the users and give the
access as Public DB_Datareader and DB_DataReader
and also gave Following
GRANT EXECUTE ON Table1 TO [Server01\Group1]; // Table name
GRANT EXECUTE ON TestKey TO [Server01\Group1]; //stored procedure name

Open in new window

Create Key code
 string TempKey = txtKeyNumber.Text.ToString();
                string strCreateSymmeticKey;
                strCreateSymmeticKey="CREATE SYMMETRIC KEY TestSymmetricKey";
                strCreateSymmeticKey=strCreateSymmeticKey+" "+"WITH ALGORITHM = AES_256";
                strCreateSymmeticKey = strCreateSymmeticKey + " " + "ENCRYPTION BY PASSWORD = '" + TempKey + "'";

                cmd.CommandText = strCreateSymmeticKey;

Open in new window

Sored Procedure
Create Procedure TestKey
@Empnumber varchar(7),
@KeyNumber nvarchar(100)

DECLARE @open nvarchar(4000)
SET @open = 'OPEN SYMMETRIC KEY TestSymmetricKey  DECRYPTION BY PASSWORD =' + quotename (@KeyNumber,'''')
EXEC sp_executesql @open

SELECT CONVERT(NVARCHAR(4000),DecryptByKey(salary)) FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY emp_num ORDER BY RecordDate DESC)'RowNum'  FROM Table1  )sub
WHERE RowNum = 1 and emp_num=@Empnumber

Open in new window

Calling Code
======================================================== code
connection 1= New SqlConnection(connetionString)
co1.Connection =connection
co1.CommandType = CommandType.StoredProcedure
co1.parameters.Add("@KeyNumber", sqlDBType.varchar).value = KeyValue1

Open in new window

The key is not encrypted using the specified decryptor

Could some one show me where is the mistake or any advice highly appriciate.
Question by:ukerandi
    LVL 44

    Accepted Solution

    Where do you run the 'CREATE SYMMETRIC KEY' statement?

    And also 'GRANT EXECUTE ON Table1 TO [Server01\Group1]' is wrong. You don't grant execution on tables but SELECT, INSERT, UPDATE and DELETE.
    LVL 32

    Expert Comment

    Can you please rephrase your question and descibe, what you're trying to do?
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    Nice work aikimark.
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    So, you really needed to run the 'CREATE SYMMETRIC KEY' statement, right?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    When the confidentiality and security of your data is a must, trust the highly encrypted cloud fax portfolio used by 12 million businesses worldwide, including nearly half of the Fortune 500.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now