We had a primary site for SCCM 2012 SP1 running fine. I then installed CAS for SCCM on another server. The prerequisites required me to change the collation on the existing server. I did this and ran a rebuild database command at the same time. The SCCM database for the site dropped, along with a reporting database and temp reporting database. I had to re-add some users to the instance and marry up with the reattached databases (the ones that had dropped). I am now getting a SQL Server error as follows:
Error: 18456, Severity: 14, State: 5.
2013-11-20 16:28:44.73 Logon
Login failed for user 'DOMAIN\MACHINENAME$'. Reason: Could not find a login matching the name provided. [CLIENT: ]
SCCM user auto discovery isn't working as new users in AD are not being written into the database, probably due to this. I have recreated the local machine user and checked NT AUTHORITY\SYSTEM, NT AUTHORITY\NETWORK SERVICE, and DOMAIN\MACHINENAME$ users match between the databases and the server and the SIDs all match. To do this, I used:
select * from sys.database_principals select * from sys.server_principals
All connections are local and using windows authentication. The owner of the SCCM database is 'sa' and the SID matches. TCP/IP and named pipes are enabled, the ports for connection are static. The 3 users above are set as sysadmin on the server.