Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating certificates Expand / Collapse
Author
Message
Posted Wednesday, December 9, 2009 2:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 5:39 AM
Points: 4, Visits: 10
I am trying to do the following:-
Create a certificate
Backup a certificate to a file
send the file to a third party
Have them create a certificate from it.

I get the following error:-
Msg 15468, Level 16, State 1, Line 1
An error occurred during the generation of the certificate.

When I do this on different servers within our domain it works fine. I can encrypt data and not decrypt. But where i created the certificate I can do both. As soon as I try to do it on an outside server it fails.

Any ideas?

thanks
Post #831237
Posted Wednesday, December 9, 2009 7:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Can you post the code you are using?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #831373
Posted Wednesday, December 9, 2009 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 5:39 AM
Points: 4, Visits: 10
on my server:

CREATE CERTIFICATE Test2009
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Protect North West',
EXPIRY_DATE = '01/01/2010';


BACKUP CERTIFICATE Test2009 TO FILE = 'Test2009.txt'


on third party server
CREATE CERTIFICATE Test2009
FROM FILE = 'Test2009.txt' --using full path

this last piece of code errors on third party server but works on a different server within my domian
Post #831442
Posted Wednesday, December 9, 2009 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
I believe you need to use a private key file to migrate the certificate that way. Here's a blog post I wrote about using certificates to sign stored procedures where I found that moving a certificate from one database to another user database required a private key file be created when backing up and restoring to the new database. Here's the basic syntax:

/*
Create the Certificate
*/
CREATE CERTIFICATE cert_access_other_db
ENCRYPTION BY PASSWORD = 'c3rtPa$$word'
WITH subject = 'Access Other DB'
GO

/*
Backup the certificate being sure to use a Private Key
*/
BACKUP CERTIFICATE cert_access_other_db TO FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk' ,
ENCRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
DECRYPTION BY PASSWORD = 'c3rtPa$$word');
GO

/*
Create the certificate in the new database from the file
*/
CREATE CERTIFICATE cert_access_other_db FROM FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk',
/*The password used to create the private key*/
DECRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
ENCRYPTION BY PASSWORD = 'D3cryptKeyPa$$word');
GO





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #831455
Posted Wednesday, December 9, 2009 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 5:39 AM
Points: 4, Visits: 10
Thankls a lot for that Jack. I will let you know how I get on.
Post #831467
Posted Thursday, December 10, 2009 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 5:39 AM
Points: 4, Visits: 10
I still get the same error on the third party's server
Post #832325
Posted Thursday, December 10, 2009 11:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Unfortunately that's all the help I can offer at this point. May be able to try it out later though.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #832435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse