wllp
Enthusiast
Enthusiast

vRA 7: Error executing query usp_SelectAgentCapabilities

Hi,

I am having the following errors in the vRA 7

  • CollectedDataImportService: Ignoring exception: Error executing query usp_SelectManagementEndpoint Inner Exception: Error executing query usp_SelectEntityProperties
  • DataBaseStatsService: ignoring exception: Error executing query usp_SelectAgent Inner Exception: Error executing query usp_SelectAgentCapabilities

I have followed the solutions that are found online

  1. Make sure that DTC is running - On both IaaS and IaaS SQL servers
  2. Uninstall and re-install DTC - On both IaaS and IaaS SQL servers
  3. Restart the VMs (IaaS and IaaS SQL)

However, I am still getting the same error. In the IaaS Server logs, this error was captured:

The MSDTC transaction manager was unable to pull the transaction from the source transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers. (Exception from HRESULT: 0x8004D02B)

I have downloaded the DTCPing and both the servers are able to communicate with each other. Firewall has been turned off on both servers.

These servers are not joined to domain and all access are configured on the DNS server.

Appreciate any help on this issue. Thanks!

11 Replies
GrantOrchardVMw
Commander
Commander

As I read the subject of your post I was thinking "this sounds like an MSDTC issue".

On reading the detail, the issue may be the lack of domain join. I don't believe vRA is supported to be installed within a workgroup.

Grant http://grantorchard.com
0 Kudos
admin
Immortal
Immortal

I have a vRA 7.1 new installation that is exhibiting these same characteristics, except all windows servers are joined to the same domain.  We have turned off all firewalls on IAAS and SQL server.  DTCping works fine.  We are getting these same errors as the OP in the IAAS Server Logs.

Any Suggestions?

0 Kudos
wllp
Enthusiast
Enthusiast

Hi Kraacks,

Check the Local DTC in both servers (iaas and iaas-sql). This is what I have configured on both servers

local-dtc.png

0 Kudos
Damien33
Contributor
Contributor

Hello,

I have the same problem. Any news ?

br,

Damien

0 Kudos
wllp
Enthusiast
Enthusiast

Check the Local DTC for both the IAAS and SQL Servers

0 Kudos
jstander
Enthusiast
Enthusiast

Did anyone ever get this resolved?

I am running into the same problems with DTC working but unable to sync my vCenter endpoint.

0 Kudos
mvr007
Contributor
Contributor

This is related to MSDTC on SQL server and IAAS Manager. You have to ensure that the MSDTC Settings are configured as listed below.

Restart the Manager service, vSphere Agent service and run the data collection.

This is a pre-requisite for all IAAS components. You can also refer to the following KB article.

Various tasks fail while calling stored procedures with errors: "Error executing query usp_<SP Na...

Screen Shot 2017-07-22 at 12.32.32 AM.png

0 Kudos
daphnissov
Immortal
Immortal

jstander​, can you post the resolution you found for your issue so others can benefit from it?

0 Kudos
mvr007
Contributor
Contributor

I found the problem with MSTDC settings on SQL server. The problem is fixed after changing the MSDTC settings. I have also restarted the IaaS Manager vSphere Proxy agent.

0 Kudos
mvr007
Contributor
Contributor

The main difference is the servers which are not part of domain as per post by wllp Sep 9, 2016 1:44 AM."These servers are not joined to domain and all access are configured on the DNS server"

This is something which needs to be verified. I have not seen any VRA setup with workgroup.' Authentication and access is minimum criteria of the vRA stack. I don't think it will work with WORKGROUP. You need to join the machines to the AD Domain and service account must have local admin access to all the IaaS servers along with SQL for DB.

0 Kudos
jstander
Enthusiast
Enthusiast

My situation might be a bit different but I was able to get it resolved with a workaround..

We could create an endpoint, and can see the compute resource but could not see any allocated CPU, memory or storage for the cluster.

We reviewed all settings of MSDTC with SQL and all Iaas Windows server, and it is configured correctly.

We could successfully connect with DTCping and DTC tester from IaaS server to SQL server and reverse.

The environment is not what I would call standard Smiley Happy

A DNS zone is defined and is used as the server FQDN, (not the domain name).  For example   server.siteA.primarydomain.com

The domain the Windows servers are connected too is different.  For example domain.primarydomain.com

What I initially thought was that there is a firewall between the vRA environment and Domain Controllers. It's possible that the NETBOIS name lookup is blocked by firewall, because normal DNS short/long/nslookup worked but the NETBIOS lookup for the Domain Controllers fail.

I was able to fix the MSDTC problem by performing the following steps:

     1. Added the IP address and NETBIOS names for all our servers and the Domain controllers to each of the servers LMhosts file

     2. Changed MSDTC security settings to “no authentication required”

     3. Change the account access to MSDTC through the following article

          https://social.technet.microsoft.com/Forums/windowsserver/en-US/eb8835d9-5f5b-4df2-8c68-4a61d5e44d6b...

     4.  ADD domain name and DOMAIN SEARCH PATHS correctly to your VMware appliances. This will help with NETBIOS fixes…

Nr 1 can probably be fixed with NETBIOS firewall ports opened and/or having NETBIOS enabled and correctly configured on the network.