Access Encrypted Data using Linked Server

• SEKHAR ANANTH

  SSC Enthusiast

  Points: 103

  More actions

  November 16, 2011 at 9:15 am

  #148827

  Hi,

  I have two SQL SERVER 2005 servers namely SERVER 1 & SERVER 2.

  Using "linked server", the data from second server can be accessed from the first. I have a stored procedure that reads data from the first and inserts/updates information in the second. The user credential configured has minimum access privileges to execute the stored procedure and access linked server. The same user credential is created in SERVER 2.

  However, the data in the second server is encrypted using a symmetric key. Since the user has minimum access privileges, the certificate, symmetric key and database master key are not visible. What are the minimum security privileges required to access these? If the stored procedure is executed with the user SA, it works but shows the following error if executed with the minimum access privilege:

  "Cannot find the certificate [CERTIFICATE NAME], because it does not exist or you do not have permission"

  It would be great if anyone can point in the right direction.

  Thanks.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  November 16, 2011 at 10:24 am

  #1408735

  Duplicate post. Please post answers here

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question