After moving your Configuration Manager 2012 database via the Detach and Attach method, you might run into some SQL related issues. You might receive an error like this “The SMS Provider reported an error connecting to the ConfigMgr site database server. Verify that the SQL Server is online and that ConfigMgr site server computer account is an administrator on the ConfigMgr site database server.” while adding a Software Updates Group to your Configuration Manager site.
When trying to add a Software Update Group an error with SQLStatus 50000 and StatusCode 2147749889 was reported.
Also looking at the status messages of the SMS Policy Provider and the SMSProv.log in the logs folder of the Configuration Manager 2012 installation folder will show you errors.
In the SMS Policy Provider you see errors like below;
“Microsoft SQL Server reported SQL message 50000, severity 16: *** Unknown SQL Error!”
In the SMSProv.log you see the same error and the following;
“*~*~e:nts_sccm_releasesmssiteserversdk_providersmsprovsspconfigurationitem.cpp(2099) : CSspConfigurationItem: SQL_ERROR~ SQL Error: *** Unknown SQL Error!~*~*”
After checking the permissions of Site Database and searching for a solution on Google and Bing, taught me that you need to check some things at the SQL Site Database like described in KB 2709082. The first thing I saw that my Domain Admin was the owner of the database, so that needed to be changed.
To be sure I also updated the database by executing the three SQL update commands.
This procedure, described in KB 2709082 fixed the issue of not being able to create a Software Update Group. If you do not want to run into this issue, I recommend using the backup and restore procedure while moving a Site database to another location.