vCAC usp_SelectAgent SQL Errors

The home lab got a vCAC (now renamed vRealize Automation) refresh to version 6.1 recently and although I’d posted a guide to installing vCAC 6 earlier, I found myself having a few errors with my vCAC 6.1 deployment.  The only difference in my environment was the version of Windows I used for the IaaS components.  Instead of using server 2008R2 as I did with 6.0, I used Server 2012 R2 for vCAC 6.1 since it was now supported.

I found that once I added my vCenter server as an endpoint to vCAC, I could see my clusters as I would have expected, but they didn’t show any resources.  When I went to add a reservation, I couldn’t see how much RAM was available or any of the datastores that I could use.

If you look in the vCAC logs, you’d find errors every two minutes refering to “Error executing query usp_SelectAgent” like the one below.

After a bit of playing around I found Event ID 4874 in the Windows event log on the IaaS Server.

This was the smoking gun.  That error led me to a VMware KB Article that explained the issue.  http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2038943

What I found was that my SQL Server component registry security settings needed to be modified in order for IaaS to query the database correctly.  After changing the below settings, everything worked again.  I hope this helps someone else having similar issues.