Solved

How do I find and/or grant permissions to a SQL encryption key for a specific user?

Posted on 2014-03-20
5
1,046 Views
Last Modified: 2014-03-26
First, I am working with a database I did not setup let alone encrypt.  I'm just in charge of creating some stored procs and getting them accessible to a SQL account user.

I can run the stored procs within SSMS but when I try to reach them through a web service I have created to serve up the data I get this error...

"Cannot find the symmetric key 'SKEY_DataEncryption', because it does not exist or you do not have permission."

How do I find the key? (Do I even need to find it...?)
How do I grant the necessary permissions to my web service designated user account within SQL Server 2008?

Thanks!
0
Comment
Question by:Bruce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39943250
GRANT permission:

http://technet.microsoft.com/en-us/library/ms179887(v=sql.100).aspx
<<
GRANT CONTROL
ON CERTIFICATE :: certificate_name
TO username
>>

Find permissions at server level:

-- server logins and their server level permissions:
            SELECT SP1.[name] AS 'Login', 'Role: ' + SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission'
            FROM sys.server_principals SP1
              JOIN sys.server_role_members SRM ON SP1.principal_id = SRM.member_principal_id
              JOIN sys.server_principals SP2 ON SRM.role_principal_id = SP2.principal_id
            UNION
            SELECT distinct SP.[name] AS 'Login' , 'Permissions: ' + SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission'  FROM sys.server_principals SP  
            JOIN sys.server_permissions SPerm  ON SP.principal_id = SPerm.grantee_principal_id  
            ORDER BY [Login], [ServerPermission] desc;
0
 
LVL 1

Author Comment

by:Bruce
ID: 39943367
I ran the GRANT command and received a success message but am still getting the same error

"Cannot find the symmetric key 'SKEY_DataEncryption', because it does not exist or you do not have permission."
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39943398
" but am still getting the same error"

When/Where are you getting the error and what SQL Login are you using when you get the error? Same like you just granted permissions as per above?
0
 
LVL 1

Assisted Solution

by:Bruce
Bruce earned 0 total points
ID: 39945349
I was able to get this working by granting control to both the certificate and the symmetric key.  Not sure why but it works now...

GRANT CONTROL
ON CERTIFICATE :: CERT_KeyAccess
TO AgentApplication

GRANT CONTROL
ON SYMMETRIC KEY :: SKEY_DataEncryption
TO AgentApplication
0
 
LVL 1

Author Closing Comment

by:Bruce
ID: 39955478
I added an additional SQL statement that completed the explanation of the solution to the issue I was asking about...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

687 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