change configuration notification process

I need to implement an automated change configuration notification process
for all my current SQL server configurations. I don't know if SQL-DMO is the
way to go here, but I certainly do not know enough about it to be proficient.
Any web links and info would be greatly appreciated.
All SQL boxes are of the 2000 variety.
SQLcat,
Could you define your requirements more clearly? What do you mean by
"automated change configuration notification process". What changes do you
need to monitor? Schema, security, data, etc?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"SQLcat" wrote:

> I need to implement an automated change configuration notification process
> for all my current SQL server configurations. I don't know if SQL-DMO is the
> way to go here, but I certainly do not know enough about it to be proficient.
> Any web links and info would be greatly appreciated.
> All SQL boxes are of the 2000 variety.
|||sorry...I'm interested in ensuring the server side configuration settings
remain the same across the board and that if anyone other than myself has
made a change, I'd be notified...perhaps this would fall under security?
"Mark Allison" wrote:
[vbcol=seagreen]
> SQLcat,
> Could you define your requirements more clearly? What do you mean by
> "automated change configuration notification process". What changes do you
> need to monitor? Schema, security, data, etc?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
>
> "SQLcat" wrote:
|||in thinking about this a little further, I guess if I had the results of
sp_configure for each SQL server in my environment, I could compare the
results daily to see if any server side changes occurred....while it would
be nice to determine who would've changed what, the aforementioned will be a
good start....how could I get something like sp_configure to give me server
side results for all my SQL boxes without having to go to each box and run it
in QA?
"Mark Allison" wrote:
[vbcol=seagreen]
> SQLcat,
> Could you define your requirements more clearly? What do you mean by
> "automated change configuration notification process". What changes do you
> need to monitor? Schema, security, data, etc?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
>
> "SQLcat" wrote:

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.

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 KEYTOFILE='C:\ServiceBroker\PrivateKeyMasterB.pvk'

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

CreateCertificate EndPointCertificateC

WITH Subject ='C.Server.Local',

START_DATE ='06/01/2006',

EXPIRY_DATE ='01/01/2008'

ACTIVE FOR BEGIN_DIALOG =ON;

BACKUPCERTIFICATE EndPointCertificateC

TOFILE='C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

CreateCertificate EndPointCertificateC

FromFILE='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.