Mar 032014
 

It turns out that in System Center Operations Manager 2012 R2 (SCOM 2012 R2), there is a pretty nasty, but well documented bug that causes corruption and the following error:

SCOM Specified cast is not valid

After clicking OK a few times, you can attempt to view the Distribution tab, but will find it is now empty:

SCOM Empty Distribution Tab

The root cause of this is likely due to the uninstall of a previously managed agent. If you are in a large shop with multiple admins, which agent was it that is currently causing the problem? Since I’m a SQL guy, I took to Transact-SQL to find the answer. Log into your OperationsManager database instance, and run the following SQL to find the bad agent:

SELECT BME.DisplayName,
       BME.IsDeleted,
       CHS.SecureStorageElementId,
       CHS.HealthServiceId,
       CHS.LastModified
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
  JOIN [OperationsManager].[dbo].[CredentialHealthService] AS CHS
    ON CMSS.SecureStorageElementId = CHS.SecureStorageElementId
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service

Objects with the IsDeleted column set to 1 are likely your problem agents.

So, how to clean this up? Well, there are a few options. One, if you are lucky you can re-approve the agent and after which you should be back to normal. Two, you can delete the offending row from the dbo.CredentialHealthService table. Lastly, and the option I’ll use is to call the same procedure that the SCOM Console uses in the GUI. To do that, run the following SQL:

DECLARE @SecureStorageElementId NVARCHAR(36);
DECLARE @HealthServiceIdList NVARCHAR(MAX);

SELECT @SecureStorageElementId = CMSS.SecureStorageElementId
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service
   /* or use CMSS.DisplayName, which is what the SCOM console shows */;

SELECT @HealthServiceIdList = COALESCE(@HealthServiceIdList + N',', N'') + CAST(CHS.HealthServiceId AS NVARCHAR(MAX))
  FROM [OperationsManager].[dbo].[CredentialHealthService] AS CHS
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
   AND BME.IsDeleted = 0
 WHERE CHS.SecureStorageElementId = @SecureStorageElementId;

PRINT N'exec dbo.p_SetApprovedHealthServicesForDistribution 
            @SecureStorageElementId=''' + @SecureStorageElementId + N''', 
            @HealthServiceIdList=N''' + @HealthServiceIdList + N''', 
            @ApprovedHealthServicesResult=2;';

The output of which, you are free to inspect – no changes are made – until you are comfortable with running it. When you are, simply copy and paste the output and execute against your OperationsManager database. The output is exactly what the SCOM console would do if you were making changes to the distribution list. Running the output of the above script is done at your own risk. Changing data in the SCOM OperationsManager database is not officially supported. Contact Microsoft support if you are in an emergency. Feel free to mention the above solution to your support engineer, however.

  14 Responses to “SCOM 2012 R2 – Specified Cast Is Not Valid Error – Run As Accounts”

  1. Thanks for this post, saved me a lot time troubleshooting. Greatly appreciated.

    Dan

  2. Just for anybody else, this is not an R2 specific bug… I have had this many times in SP1 also :)

  3. The quieries run and I see the “is deleted” is set to “1” on the 2 I just screwed up, but the query that runs the stored procedures does not do anything to get rid of them. I even changed {AND BME.IsDeleted = 1}. Am I missing something…is the waiting game now for grooming to happen? I mean I can just delete the rows, but I am not getting what this console method does to remove the issue?

  4. nevermind! I get it now! You gotta take the GUIDs that are output and punch them into the SP…duh

  5. Gene – If you run the entire SQL statement, it outputs the command necessary to run the stored procedure. Just copy the output of the PRINT statement, and paste it in a new query window to execute it.

  6. My Problem now is that the output results HealthServiceIDList reached MAX per the Declare statement at the top and I have more than MAX allows so stuff would be cut-off. I suppose you would go into the SP and fix whatever it has MAX set to be allowed.

  7. NVARCHAR(MAX) supports up to two gigabytes of data, so it should work for you. The stored procedure also accepts NVARCHAR(MAX). If you don’t have a need to see the output before running it, you can replace the PRINT statement with the actual EXEC statement, which will pass things along correctly. See below for an example.


    exec dbo.p_SetApprovedHealthServicesForDistribution
    @SecureStorageElementId = @SecureStorageElementId,
    @HealthServiceIdList = @HealthServiceIdList,
    @ApprovedHealthServicesResult=2;

  8. That got it. Thanks for your replies!! For the record; I did take the PRINT statement off, but I must have had something wrong in the exec pattern (putting on my dunce cap for the day). This is a much needed thing to have for any SCOM admin and I appreciate you posting it up.

  9. Phil, thanks for this post! I’m having a bit of a problem though. I can run the first query and get the list and I can identify the server that was deleted. However, when I run the second SQL query it successfully runs but there is no output. I change the CMSS.UserName and CMSS.Domain in the first query (that generates results) and I set it the same in the second query (I even removed it with no difference in output) but nothing is ever output from the second query. Is there something else that needs to be changed in the second query? Thanks!

  10. Jason, What happens when you run both queries together at the same time? (The whole set of SQL I provided is designed to be executed together.)

  11. Didn’t realize that, thanks. However, if I run them both together (just copied and pasted them both in the same query window and changed the username and domain) I get a list of all the servers (under the Results tab), just like I do when I run the first part/block all by itself. I don’t see any “PRINT” output like I would think under the Messages tab, it only says “(281 row(s) affected)”. In poking around some more it looks like @HealthServiceIdList might be the culprit, if I replace the final print with “PRINT @SecureStorageElementId;” it shows an ID under the Messages tab. However, if I replace the final print with “PRINT @HealthServiceIdList;” nothing is output, suggesting that the variable is null.

  12. The following should be executed as one query, changing the domain/user name that aligns to the RunAs account definition.


    DECLARE @SecureStorageElementId NVARCHAR(36);
    DECLARE @HealthServiceIdList NVARCHAR(MAX);

    SELECT @SecureStorageElementId = CMSS.SecureStorageElementId
    FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
    WHERE CMSS.UserName = N'scom_sqlm.service'
    AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service
    /* or use CMSS.DisplayName, which is what the SCOM console shows */;

    SELECT @HealthServiceIdList = COALESCE(@HealthServiceIdList + N',', N'') + CAST(CHS.HealthServiceId AS NVARCHAR(MAX))
    FROM [OperationsManager].[dbo].[CredentialHealthService] AS CHS
    JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
    AND BME.IsDeleted = 0
    WHERE CHS.SecureStorageElementId = @SecureStorageElementId;

    PRINT N'exec dbo.p_SetApprovedHealthServicesForDistribution
    @SecureStorageElementId=''' + @SecureStorageElementId + N''',
    @HealthServiceIdList=N''' + @HealthServiceIdList + N''',
    @ApprovedHealthServicesResult=2;';

  13. Thanks Phil, I figured it out. I had two problems. I was running exactly what you had, but in running some of the different queries by themselves I found that the following produced two results:


    SELECT CMSS.SecureStorageElementId
    FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
    WHERE CMSS.UserName = N'username'
    AND CMSS.Domain = N'DOMAIN'

    There was one account configured as an Action Account and another configured as a Windows Account and both had the same username. I changed one of them to have a different username and then running what you have finally produced some output.

    However, I ran into a problem with the PRINT statement not containing all of the servers, it would max out at 4000 characters. In doing a little research, it looks like that’s a limitation of the PRINT statement, which states:

    A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

    So, I ended up just replacing the PRINT part with the exec code snipped that you have above and that worked. There’s probably some work around for that, but in doing a little searching I didn’t really find anything “simple”. Thanks for your help and patience! This will be very handy now. :)

  14. Glad you got it working!

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php