7

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.

Any ideas?

Improve this question
5
  • 1
    Could the DOMAIN\MACHINENAME$ have been affected by the change in collation? What was the previous collation, and what is the collation now? Was anything else changed aside from the collation?
    – Hannah Vernon ♦
    Commented Nov 22, 2013 at 15:57
  • Unfortunately I don't remember what it was previously. I don't think it was the collation change that broke it, I think it was the rebuilddatabase command that broke it. I think it rebuilt the system databases from the ground up with new collation, I guess resetting security on the way. Problem is, I have no idea what security has changed to cause this problem or how to troubleshoot it. Collation is now SQL_Latin1_General_CP1_CI_AS. Thanks
    – Simkill
    Commented Nov 22, 2013 at 16:03
  • Collation was a long shot, admittedly, but since you mentioned it I thought I might eliminate that. I assume you don't have a backup of the old system.mdf - that is where the security accounts would be.
    – Hannah Vernon ♦
    Commented Nov 22, 2013 at 16:05
  • We run a nightly backup of the SQL. I had asked on the SCCM website whether a SQL restore would fix the problem and return the primary site to a state prior to becoming a child site, but no-one got back to me so I thought I would pursue a possible easy fix via permissions, but that doesn't look like it's going to happen either. I assume system.mdf is on the backup as all the databases on that sql are updated, but I'd have to check.
    – Simkill
    Commented Nov 22, 2013 at 16:34
  • This link could prove useful in resolving it.
    – Raymond
    Commented Jan 9, 2014 at 15:26

1 Answer 1

Reset to default
1

When you say you did a database rebuild command, do you mean you rebuilt the system databases for sql server & changed the default collation? Doing this means you've effectively created a new database server. I found these instructions for moving the SCCM db from one server to another. Looks like they're using a db backup/restore & you've attached the db, but everything else should be the same. There is a step in the ConfigMgr setup that will fix up the database permissions for you, have you tried that?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.