question

SD-8446 avatar image
1 Vote"
SD-8446 asked saldana-msft edited

<*><*> CANT CONNECT TO SQL, RETURNING ERROR <*><*>

Current Branch version 2006
Site was working just fine before the holidays.
Came back and admin console would not connect.

Look at SMSProv.log and can see the following entries:
Unknown SQL Error! SMS Provider 29/12/2020 16:06:57 16084 (0x3ED4)
~~ Unknown SQL Error! ThreadID : 16084 , DbError: 50000 , Sev: 16~~ SMS Provider 29/12/2020 16:06:57 16084 (0x3ED4)
~~..\sspobjectquery.cpp(5028) : Error waiting for query to return.~ SQL Error:
Unknown SQL Error!~~ SMS Provider 29/12/2020 16:06:57 16084 (0x3ED4)
~~Error waiting for query to return. ** Unknown SQL Error!~~* SMS Provider 29/12/2020 16:06:57 16084 (0x3ED4)
Results returned : 0 of -1 SMS Provider 29/12/2020 16:06:57 16084 (0x3ED4)
CSspClassManager::SQL query failed and returned quickly so sleeping for 20 seconds... SMS Provider 29/12/2020 16:06:57 16084 (0x3ED4)

A quick google gives this page and although it isn't exactly the same, it does state the the DB owner should be SA, which it is not.
https://docs.microsoft.com/en-us/troubleshoot/mem/configmgr/cannot-create-software-update-package

What could have caused this error? and how to resolve?

mem-cm-site-deployment
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SD-8446 avatar image
1 Vote"
SD-8446 answered SimonRenMSFT-3639 commented

We resolved the issue by resetting SQL. I will update when I learn more from the DBA.
Thank you to everyone for their help.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thank you very much for your feedback and sharing. We're glad that the question is solved now. It may help others who have similar issue. If you have any questions in future, we warmly welcome you to post in Microsoft Q&A forum again.

Have a nice day!

Best Regards,
Simon

1 Vote 1 ·
SD-8446 avatar image
1 Vote"
SD-8446 answered SD-8446 commented

Well, we had a look and the SQL is OK.
What I can see is that when I try a query in WMI on the Primary Site server, it fails as per the attached screenshot.

53238-wmi-error-2.png



wmi-error-2.png (189.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So when you run the query in WMI, what does the smsprov.log say? same error as above?

if so then what exactly does SQL Server see?

1 Vote 1 ·

Hi Garth,

Thank you for replying.

There is no entry in the smsprov.log for the WMI query that I ran from WBEMTEST.
I also re-installed the SMS provider and installed the SMS provider on another server, with the same result.
I have attached an excerpt from the smsprov.log

53239-prov.txt


1 Vote 1 ·
prov.txt (3.9 KiB)
SD-8446 avatar image
1 Vote"
SD-8446 answered

Hi, quick update.

Server permissions on SQL are correct. We are attempting to re-install the SMS Provider on the site server.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SD-8446 avatar image
1 Vote"
SD-8446 answered SimonRenMSFT-3639 commented

Hi Simon, currently waiting for the DBA to get back from holiday :)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for your reply. Looking forward to your good news.

Best regards,
Simon

1 Vote 1 ·
SimonRenMSFT-3639 avatar image
1 Vote"
SimonRenMSFT-3639 answered

Hi,

Thanks for your reply.

1.The user account that runs the Configuration Manager console requires permission to access the site database by using the SMS provider. Meanwhile, an administrative user who uses a remote Configuration Manager console also requires Remote Activation DCOM permissions on the site server computer or the computer that hosts an instance of the SMS provider. Please also help check the SmsAdminUI.log and Smsprov.log to see if there is any further information.

2.Have the permissions in SQL Server changed in some form or fashion. For example, do you have a DBA meddling with things?

Best regards,
Simon


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SD-8446 avatar image
1 Vote"
SD-8446 answered

Thank you for the answer Garth, I will point the SQL DBA to this question and see what they come back with.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GarthJones-MVP avatar image
1 Vote"
GarthJones-MVP answered

Yes the Site Server Account MUST be SA and administrator on SQL. The error is saying that there is a problem with SQL so, you need to look at that.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PaulvanWijck-5508 avatar image
0 Votes"
PaulvanWijck-5508 answered

give computeraccount or service account more permissions in SQL: sa + dbowner SCCM database, and then reinstall SMS provider with same name;
worked for me

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.