Re: Permission to view system tables in master database

Dave (Dave@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
That is a very useful web site at http://www.sommarskog.se/

However, one thing I am not clear on; why do you back up the private key
and Laurentiu drops it? What is the risk of dropping it like he did?


Erland Sommarskog's example:

BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')


Laurentiu Cristofor's example:

alter certificate certSignCreatePrincipal remove private key;
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';

If I do it in one way, and Laurentiu another, Laurentiu is likely to be
right! (Unless it's the usage of EXECUTE AS, where we are known to
disagree. :-)

I will have to admit that I was not aware of the REMOVE PRIVATE KEY
clause. I used what I was able to get to work. Dropping the private key
seems to be a better idea. However, as I understand it, it calls for a
different order of things. To wit, I first create the certificate and the
login in master, export the cert, and then move to the target database
where I sign the procedure. As I need the privte key to sign, I do need
the private key at this point.

I should definitely spend some time reworking the example - I only
need to find that time. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: The signature of the public key is invalid
    ... I know that I've seen a suggestion that you should remove the private key ... off to compose a script that did that last night, but I could not get it ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Asymetric Encryption - What am I doing wrong?
    ... 'Save the public key information from the RSAParameters structure. ... There is NO access from the Internet directly TO the SQL server. ... The SQL server actually does the work of processing orders, ... server has the private key so that it can unencrypt the data to use it. ...
    (microsoft.public.dotnet.security)