July 29, 2008 at 9:42 pm
Hi guys,
We are facing some problems in Sqlserver 2005 standard edition,while opening the symmetric key (certificate decryption) on sa login.The key and certificate are created under normal user"xxxx".
The error message is:
Cannot find the symmetric key "xxxx", because it does not exist or you do not have permission.
The script we are using as follows :
OPEN SYMMETRIC KEY SKey_AD
DECRYPTION BY CERTIFICATE ActiveDirectoryInfo;
July 30, 2008 at 6:14 am
Have you put a "GO" statement to define the end of the procedure?
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
July 31, 2008 at 5:34 pm
I had the same problem when the database AD account accessing the database was different that the account that created it. I found the following and am in the process of testing it:
GRANT REFERENCES ON SYMMETRIC KEY :: ]
For example:
GRANT REFERENCES ON SYMMETRIC KEY :: sk_Admin TO [MyDomain\Jerry] ;
October 31, 2008 at 8:54 am
We tried...
GRANT REFERENCES ON SYMMETRIC KEY::[keyname] TO
It runs successfully, but it still does not give permissions to the user. There has to be something else we are missing.
October 31, 2008 at 9:17 am
wait just got it...
need to give access to the certificate first then the key.
do this first then give access to the symmetric key with the line above
GRANT CONTROL ON CERTIFICATE::Certificatename TO
December 27, 2009 at 5:14 am
hi,
i have tried as your said, but it is not working again permissions issue.
🙂
August 27, 2013 at 3:54 pm
Any resolve to this... I'm having the same issue.
This grant succeeds...
GRANT alter,control,references,take ownership, view definition ON SYMMETRIC KEY::[BTS_Key1] TO [johnm]
GO
grant alter,control,references,take ownership, view definition on certificate::BTS_CERT1 to johnm;
GO
When johnm does this in the same database, it fails...
OPEN SYMMETRIC KEY [dbo.BTS_Key1]
DECRYPTION BY CERTIFICATE [dbo.BTS_CERT1];
Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'dbo.BTS_Key1', because it does not exist or you do not have permission.
But, If I grant johnm SA fixed server role... it succeeds.
Troy.
#
August 27, 2013 at 4:11 pm
Doing this...
GRANT view definition ON SYMMETRIC KEY::[BTS_Key1] TO [johnm]
grant control on certificate::BTS_CERT1 to johnm
And removing the schema seems to be the key.
OPEN SYMMETRIC KEY [BTS_Key1]
DECRYPTION BY CERTIFICATE [BTS_CERT1];
Troy.
#
July 18, 2018 at 6:09 am
Hi there,
I am facing the same issue. I am working on setting up encryption/decryption mechanism for my organization. The code works fine with my login (i.e similar to SA) however the same code do not work for other login (which is used by online reports) . It gives the error.
use db1
OPEN SYMMETRIC KEY [xyz] OPEN SYMMETRIC KEY [xyz]
DECRYPTION BY CERTIFICATE cert_abc; DECRYPTION BY CERTIFICATE cert_abc;
Msg 15151, Level 16, State 1, Line 3Msg 15151, Level 16, State 1, Line 3
Cannot find the symmetric key 'xyz', because it does not exist or you do not have permission.Cannot find the symmetric key 'xyz', because it does not exist or you do not have permission.
I have used the solutions provided above, however it is still gives same error.
GRANT REFERENCES, CONTROL, VIEW DEFINITION ON CERTIFICATE::[cert_abc] TO [JohnDoe]
GRANT ALTER, REFERENCES, CONTROL, VIEW DEFINITION ON SYMMETRIC KEY::[xyz] TO [JohnDoe]
Can you please let me know if anything else required to be done ?
September 14, 2021 at 12:22 pm
You can use the below command to open the symmetric key.
--Grant View
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SQLSymmetricKey TO [UserName]
--Grant Control
GRANT CONTROL ON CERTIFICATE::SelfSignedCertificate TO [UserName]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply