After the System Center 2012 ConfigMgr SQL Site database is moved, you cannot create a Software Update package or application

Applies to: Microsoft System Center 2012 Configuration Manager

Symptoms


After you move the System Center 2012 Configuration Manager SQL Site Database to a different drive, and then you try to create a Software Update group, Software Update package, or application, the operation fails, and log entries that resemble the following are logged in the SMSProv.log file:

*** *** Unknown SQL Error! SMS Provider 14-03-2012 07:56:47 2016 (0x07E0) 
*~*~*** Unknown SQL Error! ThreadID : 2016 , DbError: 50000 , Sev: 16~*~* SMS Provider 14-03-2012 07:56:47 2016 (0x07E0)
*** [24000][0][Microsoft][SQL Server Native Client 10.0]Invalid cursor state SMS Provider 14-03-2012 07:56:48 2016 (0x07E0)
*~*~[24000][0][Microsoft][SQL Server Native Client 10.0]Invalid cursor state *** Unknown SQL Error! ThreadID : 2016 ,
DbError: 0 , Sev: 0~*~* SMS Provider 14-03-2012 07:56:48 2016 (0x07E0)
SQL Server Profiler provides the following additional details:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65539. The server may be
running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.
Run the query again, or check documentation to see how to solve the assembly trust issues. For more
information about this error:

System.IO.FileLoadException: Could not load file or assembly 'cryptoutility, Version=5.0.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35' or one of its dependencies. An error relating to security occurred.
(Exception from HRESULT: 0x8013150A) System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity,
Assembly locationHint,
StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity,
StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity,
StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

Cause


This problem may occur if the SQL Site database MDF and LDF files are moved to a different drive. For example, this problem may occur if the Configuration Manager Site Database is created on C:\Program files\MSSQL server\data, and then the MDF and LDF files are moved to D:\CM2012DBto save space.

Note This is a supported SQL Server operation. For more information, go to the following Microsoft Knowledge Base article:

This problem occurs because the SQL TRUSTWORTHY property of the SQL Site Database that is set to ON by default is reset to OFF when you detach and reattach the database. When the database is not configured to have the property set to ON, <ConfigMgr_Install>\bin\x64\CryptoUtility.dll fails to load into SQL Server, and you receive the "invalid cursor state'" error message that is mentioned in the "Symptoms" section.

Resolution


To resolve this problem, follow these steps:

  1. Manually reset the property to ON by running the following command against your CM database:

    ALTER DATABASE <ConfigMgr DB>
    SET TRUSTWORTHY ON
  2. Make sure that the database that was moved is owned by the SA account.
  3. Make sure that the Isolation Level value is set to READ_COMMITTED_SNAPSHOT. To check this value, run the following command:

    DBCC USEROPTIONS 
  4. If the Isolation Level value is set to anything other than READ COMMITTED SNAPSHOT, run following commands in the given order:


    ALTER DATABASE <ConfigMgr DB>
    SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE <ConfigMgr DB>
    SET READ_COMMITTED_SNAPSHOT ON
Note You may have to change the SQL Server database to Single User mode before you run the commands in step 4. For more information about how to detach and attach a database in SQL Server, see the following TechNet topic: