[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Error VB.net

Hi
Following code is working FINE at my computer. im login as Db_Owner

When im runing at some collegue machine, Following error
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 vB.net
 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)
as


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
CLOSE SYMMETRIC KEY TestSymmetricKey

Open in new window

========================================================
Calling Code
========================================================
VB.net code
connection 1= New SqlConnection(connetionString)
connection1.open()
co1.Connection =connection
co1.CommandText="TestKey"
co1.CommandType = CommandType.StoredProcedure
co1.parameters.Add("@KeyNumber", sqlDBType.varchar).value = KeyValue1
reader=co1ExecuteReader()

Open in new window

Error
The key is not encrypted using the specified decryptor


Could some one show me where is the mistake or any advice highly appriciate.
Thanks
0
ukerandi
Asked:
ukerandi
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
ste5anSenior DeveloperCommented:
Can you please rephrase your question and descibe, what you're trying to do?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Nice work aikimark.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you really needed to run the 'CREATE SYMMETRIC KEY' statement, right?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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