So, you installed Audit Collection Services (ACS) and the collector isn't able to connect to the OperationsManagerAC database. You get a bunch of errors in your Operations Manager and Application event logs on your ACS Collector:
Event Type: Error Event Source: AdtServer Event Category: None Event ID: 4618 Date: 3/13/2008 Time: 12:23:30 AM User: N/A Computer: OMMS Description: Error occured on database connection: Status: 0x02200000 ODBC Error: 18456 ODBC State: 28000 Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Database: Register Connection: ComplianceTest
Event Type: Error Event Source: AdtServer Event Category: None Event ID: 4618 Date: 3/12/2008 Time: 11:22:10 PM User: N/A Computer: OMMS Description: The description for Event ID ( 4618 ) in Source ( AdtServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 0x02200000, 18456, 28000, [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'., Register, ComplianceTest, -.
If the SQL Server which hosts your ACS database is set to logon using a domain account, there may be a couple extra steps you may need to take beyond installation to get things flowing properly.
Before I go any further, first be sure you have these tools:
KERBTRAY is part of the Resource Kit Tools. Download here. SETSPN can be downloaded here.
Logon to the SQL Server housing the ACS database. Open a CMD prompt and type the following command:
setspn -L <domain>\<account>
This is the account in which SQL Server logs in as:
We are looking for results like in the example. If no registered SPN's are returned, we need to register them. Do so by running the following commands:
setspn -A MSSQLSvc/<fqdn> <domain>\<account>
setspn -A MSSQLSvc/<fqdn>:<port> <domain>\<account>
If you run setspn -L <domain>\<account> again, you should see the result return register SPN's like in example 2.
Now, open up Active Directory Users and Computers and navigate to the user account that SQL Server log in as. Right-click > Properties > click Account tab. Check "Account is trusted for delegation" in the Account options list.
Now logon to a Domain Controller and startup KerbTray. You should see a green icon appear in the system tray.
Right-click the KerbTray and click Purge Tickets. Wait for the icon to turn yellow. Then, in CMD prompt, run the command:
net session /d
Restart the MSSQLSERVER service on the ACS SQL Server and the Audit Collection Collector Service on the ACS Collector. Soon after, new tables and views should be created in your ACS database.
I do not moderate this blog anymore. If you have a question regarding this post, send me a message.
I've tried this and it still didn't work. Wondering where the MSSQLSvc is from and maybe I need to setspn a different instance. Any help?
MSSQLSvc is the name of the SQL Service, which is running on the database server. If the database is hosted under a default instance, you do not need to specify the instance. If you have named the instance, or have multiple instances on the SQL Server, then you'll need to specify MSSQLSvc/FQDN:instancename in the SETSPN tool. You can read more on that here (http://msdn.microsoft.com/en-us/library/ms191153.aspx).
Is SQL running under a domain account or a System account? If it's running under a System account, then you do not need to set a SPN because LocalSystem will automatically add and remove the SPN when SQL service starts and stops.
Helped me tremendously. Thanks JA
I've tried your method, but without success.
Every day at 4 AM one 4618 and one 4620 event arises in the Operations Manager log. Nothing else, no other time in no other logs.
There was only one SPN, with the port 1433.
I've created the other one, without the port.
Both are still alive, I can check them with Setspn -L.
The delegation trust looks different in our domain, there's no 'Account is trusted for delegation' checkbox but there is a Delegation tab. On that tab I've checked the 'Trust this user for delegation to any service (Kerberos only)' radio button.
Then I've started KerbTray and purged the tickets. The icon went yellow like it should.
The 'net session* /d' command fails with syntax error.
So I've deleted the sessions with 'net session \\10.60.51.1 /delete' like it is suggested by 'net session /?'.
I've restarted the SQLServer (MSSQLSERVER) service and that one restarted the ACS Collector service as well.
I've run through these steps 3 times but still no success.
What do I do wrong?
Are you having problems with the above tables getting created? Is ACS functioning fine otherwise? Sounds like you might have something running at that same time every day causing your connection failures.
The ACS works just fine.
The tables and views are there, a dozen of all four types.
I couldn't find anything causing this failure at 4 AM.
I can ignore it, only it does not look nice. I mean every day the same warning.