SQL Server Configuration Manager

On one of our SQL Servers, I was planning to make some changes to network configurations and I was welcomed with below error message when I try to open SQL config manager. FYI…This is a SQL Server 2016 Instance running on windows 2016 machine.

cannot connect to wmi provider. you do not have permission or the server is unreachable. note that you can only manage sql server 2005 and later servers with sql server configuration manager.invalid class [0x80041010]

This error mesage is pretty misleading IMHO at a high level, I am a local admin on the box and I am trying to manage a SQL 2016 Instance 🙂

Before proceeding to the final resolution, I would like you folks to read these statements from microsoft documentation.

“The Managed Object Format (MOF) compiler parses a file containing MOF statements and adds the classes and class instances defined in the file to the WMI repository. MOF files are usually automatically compiled during the installation of the systems with which they are provided, but you can also compile MOF files by using this tool.”

“To make the contents of a MOF file effective (by placing them in the CIM Repository), the file must be compiled. MOF files are usually automatically compiled during the installation of the systems with which they are provided, but you can also compile MOF files by using the MOF Compiler (Mofcomp.exe). The MOF Compiler is available in the %Windir%\System32\wbem directory. You must specify the MOF file as the parameter of the MOF Compiler.”

References:
https://docs.microsoft.com/en-us/windows/win32/wmisdk/compiling-mof-files
https://docs.microsoft.com/en-us/windows/win32/wmisdk/mofcomp?redirectedfrom=MSDN

In my case, For whatever reason, SQL Server installer coudn’t compile and register .mof files correctly. I am not sure if this happened during some patching or when SQL was installed initially on this box, I have no idea.

Resolution:
Navigate to shared features folder from cmd (as admin) and run below command:
mofcomp sqlmgmproviderxpsp2up.mof

In my case since it’s SQL 2016, the path for “sqlmgmproviderxpsp2up.mof ” file is
C:\Program Files (x86)\Microsoft SQL Server\130\Shared.
For SQL 2019, that would be:
C:\Program Files (x86)\Microsoft SQL Server\150\Shared.

Anyways…once the mof file got parsed successfully, I was able to open and manage my SQL Instance without any issues using SQL Server Config manager.

Things we should be Aware of in SQL Server DENALI Configuration Manager, A Small simple change which might make a huge difference!

Before proceeding any further, let me make it very clear….for any changes to me made on any of the SQL Server Services you’ve to make use of SQL Server Configuration Manager, not our typical windows services from administrative tools.

I’ve seen in the past(still seeing few folks doing the same even now!!) where people going to “RUN” and entering “services.msc” for changing SQL Server Service accounts, Start Up type etc….Hope you are not doing the same!

Config Manager in SQL Server 2008R2:

Okay, Let’s jump into Config Manager in SQL Server 2008R2, when you choose properties of SQL Server Engine we’ll be getting what is shown in the screenshot below.

This is the place where we’ve to change the Service accounts and pwd’s. You can change the Start Mode(Automatic/Manual/Disabled) in the Service Tab. For adding any start up parameter, you’ve to go to your advanced Tab as shown below.

Configuration Manager in DENALI:

See the screenshot below and if you see carefully, you’ll notice a new tab for startup parameters!

Basically, DENALI has 2 new tabs, one for Brand new fabulous feature “AlwaysOn HA” and the other for “Starup Parameters”. Let’s see what’s present in my AlwaysON tab…

Well, I’ve Installed this Instance of DENALI on my WINDOWS 7 x64 Client OS which is a stand alone, hence unfortunately, I can’t show you much about this setting here 😦 (I’m planning to Install DENALI on my LAB Cluster pretty soon to explore and learn this brand new HADR feature of DENALI…yayyyyyy)

Let’s move on to Starup Parameters Tab, You can see the brand new interface as shown below.

IMHO, this makes all of our(DBA’s) lives much easy dealing with those annoying semi-colons “;” and Spaces where we should be very very cautious upto SQL Server 2008R2. A single space will change the entire behavior of your startup parameter(s).

Let me show you how we can add a trace flag as a starup parameter in DENALI. Let’s try to enable Trace Flag 3226 as a start up parameter to disable all the successful backup events being logged in our error logs.( See here if you want to know how you can enable a trace flag globally instead of making it a startup parameter). All you need to do is Just typing “-T 3226” and click Add ->Apply->Ok (Service restart is required to reflect the changes) as shown in the below screenshot.

If you want to Update any parameter, all you need to do is Just click on the parameter which you want to change and make changes and click on Update Button. ( The Add button will change to Update button once you select any parameter as shown below).

In the same manner you can remove any Startup parameters which you’ve added.

This greatly reduces pain in our Butt dealing with Spaces and Semicolons( I’m sure that almost everybody agree with me in this matter, especially who’ve already faced an issue and wasted time(like me when i was a Novice in SQL) figuring out what’s wrong with the parameter provided and banging head to your desk, OMG! why this SQL Server is not starting up as expected 😦 )

Hope this post helps!