Category: Generic Errors

Cannot connect to WMI provider-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.

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

This morning one of our developers contacted me regarding this error message he got when he tried to design a table from his SSMS.

1

Also, he reported that his SSMS goes to “Not Responding” state when he tries to edit rows using his SSMS. So…What’s happening?

He was trying to edit a table within a database which is running on SQL Server 2014 version using his SSMS 2012.  Now, all he needs is upgrading his client tools to 2014. Remember, you can manage/manipulate older versions of your SQL Servers using your 2014 SSMS, However be prepared to face these annoying issues if you are trying the other way.

Happy weekend folks!

 

The server principal already exists – Msg 15025, Level 16, State 2, Line 1

It’s friday and it’s dark and drizzling outside which usually makes me lazy..,:) But I got this one ticket from one of our customers requesting to create a Login which made me active :D.  Well, thought creating a login is just a basic routine security request, Went ahead and tried creating the login from SSMS. It failed…saying “The server principal already exists”1

Well, I thought the login already exists and verified from SSMS( I didn’t found one…Hmmm Interesting). Assuming might be a bug in SSMS, i tired using T-SQL, same message again…as you can see below.

2

Basically, I am not seeing the login(Server Principal) but SQL Server says it already has one!!!…To double check I queried sys.server_principals and also used sp_helprevlogin to see if it can show something useful to me.  But I had no luck again as you can see below…they are not giving me any useful results when I am querying using Login Name.

3

Now..It’s time to play with SIDs(SUSER_SID is super useful in this scenario). I queried SUSER_SID passing the login name which I am trying to create, Interestingly it returned a row as you can see below…

4

Right away I queried sys.server_principals again, but with SID(Use the SID which came from the above query) this time as opposed to Login Name. Results are shown below

5

Tadaaaaa…..There you go! It returned a different login name which already exsits on the SQL Server with the same SID of the new login which I’m trying to create. 

So, I contacted customer saying ‘ Hey, Login A is conflicting with your login, do you happen to know what Login A is?’ I got a response, that was her Old Windows user account(her Lastname got changed recently)

Now all I’ve to do is drop her old login(Remove DB mapping as needed) and create new login, fix DB mapping and permissions. Hope this helps…Happy Friday folks 🙂

RESTORE DATABASE is terminating abnormally.Msg 905, Level 21, State 1, Line 1.

Recently one of my friends(Rookie DBA, Just started his career as a Jr DBA) called me and mentioned about this error he encountered while he was trying to restore a database(2008) from prod to Non-Prod Environment and was wondering how to make it work.

“RESTORE DATABASE is terminating abnormally.Msg 905, Level 21, State 1, Line 1.

Database ‘TstDB’ cannot be started in this edition of SQL Server because it contains a partition function ‘TstPF1’. Only Enterprise edition of SQL Server supports partitioning.”

Well, as you can see, the message thrown by SQL Server is pretty much self explanatory. The database is using Partioning which is Enterprise only Feature and hence you can’t restore this database to standard edition or Express edition. The solution is to drop all the partition schemes and partition functions prior to taking a backup and use that backup to restore on any Non-Enterprise edition(Except Developer Edition) instance! That’s the reason you should be very careful if you are using enterprise only features in any of your production databases. For example TDE is an Enterprise only feature, CDC is an enterprise only feature. Having any of these features turned ON on your database will make the DB restore process to Non-Enterprise editions(Except Developer Edition) little painful.

So, Is there anyway to retrive all the Enterprise Only features enabled on any given database? Yes, you can query the DMV “sys.dm_db_persisted_sku_features”

Query:
SELECT feature_name FROM sys.dm_db_persisted_sku_features;
GO

For example, I’ve a test database which has Data Compression and CDC enabled and the result is as shown below…

3

Btw, For looping through all the databases to identify enterprise only features enabled, you can write a simple loop or simply make use of “sp_MSforeachdb”.

Cheers! Happy weekend 🙂