Friday, April 20, 2012

SCCM 2012: “*** *** Unknown SQL Error!” entries in SCCM log files

I had some major issues with this last error on our new SCCM 2012 server week. I worked on it for two solid days before I cried uncle and finally called Microsoft. Turns out the issue was caused when one of our database guys moved my SCCM database to a separate drive for "performance tuning". Since it was such a tough nut for us to crack I thought I'd post about it. According to the person I spoke with at Microsoft the SCCM database owner should *ALWAYS* be the "sa" account. Moving databases in SQL Server can cause the database ownership to change from “sa” to the user account of the person performing the move. If this happens the SCCM database owner *must* be changed back to "sa". A symptom of this issue is a constant string of “*** *** Unknown SQL Error!” entries in the SCCM log files every time it tries to write to the database.

Also when you need to make database configuration changes its a good idea to log into the SCCM server using your SCCM service account (which you should have set up prior to installing SCCM) and make all changes under that account so that if ownership does change at least the ownership will be designated to the SCCM service account and not your personal user account. This will ensure that your user-specific domain account is not accidentally given ownership of some SCCM-critical database related item. Plus (while it may not work every time) there’s a good chance that even if the SCCM service account somehow gets ownership of the item you change there’s it *could* continue to function just fine (as this account should have been set up by you as a local admin on your  SCCM Server and also as a SysAdmin on your SQL Server).


Here's a couple of useful SQL queries to help troubleshoot this issue (run from SQL Server Management Studio):

-- Check Database ownership:
EXEC sp_helpdb

-- Change a Database owner to 'SA'
USE <myDatabaseName>
GO
EXEC sp_changedbowner 'sa'

6 comments:

  1. I also had to run - ALTER DATABASE SET TRUSTWORTHY ON

    ReplyDelete
  2. Ah, thanks for the contribution!

    ReplyDelete
  3. This sorted my problem after moving a DB from one drive to another.

    Thanks!

    ReplyDelete
  4. Thanks a lot - this solved my issue unable to connect to CCM Site when launching CCM Console .... Niraj Kapadia

    ReplyDelete
  5. WOW, putting you on bookmark..Wonderful information and tip

    ReplyDelete