Endpoint with id [XXXXX-XXXXX-XXXXX-XXXXX] is not found in SQL Server on IAAS endpoint
Recently, I was looking at a problem where user was unable to save an NSX endpoint
When we edit these endpoints and click on "Test Connection" , it does succeed.
But as soon as we click on save, we get below exception under /var/log/vmware/vcac/catalina.out
[UTC:2019-02-26 04:12:41,054 Local:2019-02-26 15:12:41,054] vcac: [component="cafe:iaas-proxy" priority="ERROR" thread="tomcat-http--3" tenant="nukescloud" context="Ge5uipgR" parent="Ge5uipgR" token="iVIMcVWX"] com.vmware.vcac.iaas.controller.endpointconfiguration.EndpointController.update:121 - Endpoint update failed: Endpoint with id [XXXXX-XXXXX-XXXXX-XXXXX] is not found in SQL Server on IAAS endpoint.
We definitely know that there is a problem w.r.t this endpoint on SQL database , but where was the question.
I did create an NSX endpoint in my lab , it creates updates both vPostgres DB for vRA and SQL db for IaaS
As first step, let's look into vRA's Postgres database
Login into postgres database
su - postgres /opt/vmware/vpostgres/current/bin/psql vcac
Enable expanded display
vcac=# \x Expanded display is on.
Then , review the contents of this table
vcac=# select * from public.epconf_endpoint;
This is how the output would look. You have two endpoints visible
One for the vCenter and the other for NSX.
id displayed in the above table is the NSXEndpointId what SQL refers in it's IaaS database
type_id is the type of endpoint you create
name and description are the descriptors you give while creating an endpoint
extension_data is the data it fetches from the endpoint w.r.t certificate thumbprint , username & password
created_date and last_updated are self explanatory
Now let's compare this with SQL's table which has this configuration
The table we are interested would be [dbname].[DynamicOps.VCNSModel].[VCNSEndpoints] as show in the below screenshot
As i stated earlier Id in vRA's Postgres database should be same as NSXEndpointId in IaaS database
In the environment where the failure was observed, NSXEndpointId was set to NULL
Now that we established a relationship by trying to understand how this works from DB perspective , it was easy to fix it on the problematic environment
All we had to do is to execute an update statement to replace the NULL value with it's appropriate ID captured from postgres database
update [vradistrib].[DynamicOps.VCNSModel].[VCNSEndpoints] set NSXEndpointId = '79fc5423-089b-4b4a-8a7a-b416f68e70bb' where Id = '3';
!! Hope this helps !!