Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password = '45Gme*3^&fwu';

BACKUP MASTER KEY TO FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'

Create Certificate EndPointCertificateC

WITH Subject = 'C.Server.Local',

START_DATE = '06/01/2006',

EXPIRY_DATE = '01/01/2008'

ACTIVE FOR BEGIN_DIALOG = ON;

BACKUP CERTIFICATE EndPointCertificateC

TO FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

Create Certificate EndPointCertificateC

From FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY (

FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD = '45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.