CE10 and Setting up the Auditing Database

[agreen10]

I am getting the following error and looked at everything and can't figure out what the problem is and why i cannot get any data to be audited.


"Auditing information is not being written to the auditing database. Please verify that the auditing data source is properly configured on the CMS. "

Can someone help me please?

 

[Turkbear]

Hi,
Have you set up the auditing database ( it is not the same as the CMS repository), specified it correctly in the CMS service properties and made sure it runs under an account that has permission to access the data?

What database are you using for Auditing ( also which one for the CMS)?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[agreen10]

Okay, I followed the following steps:

I stoped the CMS and seledted the specify auditing data source. Now here is where I think I am confused. Should the datasource be the actual database that I am using. However, I created a blank Access DAtabase and an ODBC data source called auditdata pointing to the database. then I restarted the CMS.

Not writing properly. not sure what is wrong. Perhaps you step me through the process again.

 

[Turkbear]

Hi,
With an Access database ( or any file system based Database) you need to run the CMS service under an account that has full rights to the location of the .MDB file..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[agreen10]

Okay! here is what I just did, I changed the cms using the properties option after I stopped it and then created a database called CE10 on the SQLServer which is the local machine that Crystal Enterprise is running on. but I am still getting the same error.

 

[SBJG]

Not sure if this is of help, but I've had a similar problem on CE10 using SQL Server and found a pointer to the CE release notes which state that you can not use a trusted connection to the CE Auditing database - You have to use a db login account. Not sure how/if this applies to Access but who knows!

 

[agreen10]

Thanks very much. the CMS data source is set to CE10 which is on the same server. and the autiding data source is set to the database CE10. But when I go toa ccess that database using Access, I do not see the tables that are to be created.

 

[SBJG]

One clarification in terms of the release notes: It states that while a trusted connection will work for the CE10 cms database, it will not work for the CE10 auditing database - even if they are the same database! I know it sounds crazy, but that's what the release notes tell me! So, just because the data source / database works as a CMS database, it does not mean it will work as the auditing database.

Not sure how helpful all this is to you but it did solve my issue with setting up auditing in SQL Server.

 

[agreen10]

So what can you propose that I do. I am at a lost with this as it is my first time and I am totally confused. I tried providing another auditing data source and it still did not recognize it. Here is a question, the CMS data source--isn't that the data source of the database you are tryign to audit? That I guess is where my confusion is. If that is the case, then I can provide the mysql database information as the cms data source.

 

[SBJG]

No problem - We'll all been there, facing the confusion of CE and CR.. Hopefully, my description below is easy to follow:

The data source you specify is the name of the database / odbc dsn where you want to record all your auditing information into. It is not the name of the cms database that you want to audit.

It might be easier if I explain my set up. I have 2 databases set up on a SQL Server machine: One called "CE10" and another called "CE10_Auditing".

CE10 is my CMS database. CE10_Auditing is a separate database on the same SQL Server machine that I set up to record my auditing information.

I have 2 ODBC DSNs set up on the CMS Server, one pointing at the CE10 database on the SQL Server machine and the other pointing at the CE10_Auditing database on the same SQL Server machine. For simplicity's sake, each ODBC DSN name matches the name of the database it points to.

The ODBC DSN for the CMS database (CE10) is set up to use a Trusted Connection. The other ODBC DSN (for CE10_Auditing) had to be set up so it specifies a SQL Server user login account to use for the connection.

WIth the above set up, my CMS configuration screen has the CE10 ODBC DSN listed as the "CMS Data Source" and "CE10_Auditing" listed as the "Auditing Data Source".

I know the above is all SQL Server specific, but hopefully it may give you a few pointers.

 

[agreen10]

OKay! I am following your explanation as I find it very helpful. However, I went through to reset an ODBC to connect to my sql server called CE10_Auditing database however, there is a screen during the configuration which asked "change the default database to: and I left that box unchecked. Should I check that and then add a database there?

 

[SBJG]

1) Not sure if you've done this bit but first, create the database in SQL Server first.

2) Then create a login account in SQL Server that has rights to create tables etc in this database (e.g. called CE_USER).

3) Then when setting up your new ODBC DSN, at the first screen, point it at the SQL Server machine. On the second screen, select "SQL Server Authentication using a login ID and password specified by the user" and specify the SQL Server username and password you created in step 2 above (e.g. CE_USER).

4) Now on the third screen, change the default database to the name of the database you created in step 1 above.

Once you have the DSN set up, go to the Crystal Config Manager, stop CMS and when specifying the audit ODBC DSN, select the data source created above. It should ask you for SQL Server Login details. Uncheck Use Trusted COnnection and specify the username and password created in step 2 above. CLick the OPTIONS button and ensure the "Database" is set to the name of your new CE AUditing database.

That should be it. You will know it worked when you restart CMS and you see a table called CMS_AUDITTABLE appear in your CE auditing database.

 

[agreen10]

Now I am getting a new error. A cluster error saying it could not access the database. Any ideas!

 

[SBJG]

Let's start again. Uncheck the "Write server audit information to specified data source" in the CMS property screen. See if CMS will start for you now. If so, at least we can narrow down your error to the auditing db only!

 

[agreen10]

you are correct, it does not start.

 

[SBJG]

If it does not start even after you have disabled the auditing database, then it looks like you have a problem with the CMS database or the ODBC DSN that you have specified for CMS to use.

Check to confirm the ODBC DSN for this CMS database is ok and test the connection outside of Crystal. Also, just make sure the database hasn't just decided to shut down.

This is not related to auditing db I would think but maybe one of the changes I've suggested was applied to the wrong DSN or something.

 

[agreen10]

Okay, I will and let you know.

 

[agreen10]

I have the CMS started now with the auditing database disabled. I will try the other suggested steps above again and let you know what I get.