ACS, SQL Server, and Windows authentication

In the course of installing and configuring Audit Collection Services (ACS) I had to troubleshoot a permissions issue connecting to the database.

Specifically, the ADTServer application service running on the collector was attempting to connect to the ACS database on a separate system.  The service runs as the local network service on the collector which means that it uses the computer account to perform network authentication: DOMAIN\SYSTEM$.  This account was added to SQL Server and granted dbo privileges by the ACS installation.  However, every time the service attempted to connect to the database, authentication for the account was rejected with the below events.

I went in a lot of different directions but ended up looking at the local system policies and finally found one: Access this computer from the network.  By default, this setting is set to Administrators, Backup Operators, Power Users, Users, and Everyone.  On the database server, it was set to Administrators and Backup Operators only.  Thus in order to connect to a database on this system, a user must be an administrator (or Backup Operator) on this system.  By seemingly reducing the access to this system it is actually less secure because it requires database users to be administrators. 

Leaving this setting at its default setting does not pose a security risk, it simply allows users to be authenticated and access resources that they are authorized to use; in this case, SQL Server is controlling authorization.  Note that authentication and authorization are two distinctly different things.  The setting identified above is limiting authentication not authorization and this ultimately decreases the effective security of this system.

Event Type:      Error
Event Source:   AdtServer
Event Category:            None
Event ID:          4618
Date:                1/8/2008
Time:                9:25:10 AM
User:                N/A
Computer:         Collector
Description:
Error occured on database connection:
 Status: 0×02200000
 ODBC Error:    18452
 ODBC State:    28000
 Message:         [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ”. The user is not associated with a trusted SQL Server connection.
 Database:        Register
 Connection:     ComplianceTest
 Statement:       -
 

Event Type:      Error
Event Source:   MSSQL$ACS
Event Category:            (4)
Event ID:          17806
Date:                1/8/2008
Time:                9:25:10 AM
User:                N/A
Computer:         DATABASE
Description:
SSPI handshake failed with error code 0×8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.32.9.69]
 

Event Type:      Failure Audit
Event Source:   MSSQL$ACS
Event Category:            (4)
Event ID:          18452
Date:                1/8/2008
Time:                9:25:10 AM
User:                N/A
Computer:         DATABASE
Description:
Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.32.9.69]

Ops Manager 07 DNS Management Pack

I finally found a fix for the problems that I’ve experienced with the DNS Management Pack in Operations Manager 2007; actually it’s more of a work-around: add the Data Reader account to the Operations Manager Administrators group and all is well.  Note that this should be the group designated during installation as the Operations Manager Administrators; the setup program adds this group to the Operations Manager Administrators role.

The problem occurs when the DNS Management Pack is added to Ops Manager 07 and generates the following alert: Data Warehouse failed to deploy reports for a management pack to SQL Reporting Services Server.  Additionally, an error is logged in the Operations Manager event log (a sample of which is copied below).

I’ve experienced this problem both in the lab and in multiple production environments.  This problem does not affect any other management packs and I have no idea what it is uniquely doing to cause this issue.  I do think it’s an installation only issue and if you remove the account from that group after the MP has been successfully deployed to all agents, everything will work fine – I haven’t actually tested taking it back out though.

             Event Type:        Error

Event Source:    OpsMgr SDK Service

Event Category:  None

Event ID:              26319

Date:                     12/20/2007

Time:                     2:50:34 PM

User:                     N/A

Computer:          MOM

Description:

An exception was thrown while processing GetRelationshipTypesByCriteria for session id uuid:2856e71b-a9a4-4518-ba41-151504c5e7d1;id=19.

 Exception Message: The creator of this fault did not specify a Reason.

 Full Exception: System.ServiceModel.FaultException`1[Microsoft.EnterpriseManagement.Common.UnauthorizedAccessMonitoringException]: The creator of this fault did not specify a Reason. (Fault Detail is equal to Microsoft.EnterpriseManagement.Common.UnauthorizedAccessMonitoringException: The user DOMAIN\scomreader does not have sufficient permission to perform the operation.).