How to manually remove a directory from vRealize Automation

Products

• VMware Aria Suite

Issue/Introduction

Removal of corrupted directory configuration from postgres database

Symptoms:

• Syncronizations fail for a specific directory
• Error in UI: "Failed to complete sync due to a problem with the service.  Exception Info: User is not authorized"
• Error found in connector.log / catalina.out / horizon.logs:

2018-10-04 17:31:35,128 ERROR (tomcat-http--13) [[email protected];<USERNAME>@VSPHERE.LOCAL;10.141.2.200] com.vmware.horizon.connector.admin.controller.DirectoryController - Could not add the Bind user - Could not bulk push Users, Groups. Received Response Code: 401 and Response body: {"errors":[{"code":"unauthorized","message":"User is not authorized.","parameters":null}]}

• Deleting the directory in the UI fails with "Internal Server Error"

Environment

VMware vRealize Automation 7.x

Resolution

To manually delete, then recreate a problematic directory, perform the steps below:

• Isolate idOrganization and domain values:

1. Run these three statements to identify ID and UUID of directory to be removed:
   1. su - postgres
   2. cd /
   3. psql -d vcac
   4. set schema = 'saas';
   5. \x
   6. Select * from "Organizations";
   7. Select * from "Domain";
   8. Select * from "DirectoryConfig";

In the below record note the id is 53 the directoryConfigID is 53 and the uuid 7b6cd22a-f7bb-4347-8ca6-70f75f1a6a0e for the Directory "Core.local" which is to be deleted.

Note:
Select * from "Domain";
-[ RECORD 2 ]-----+-------------------------------------
id                | 53
uuid              | 7b6cd22a-f7bb-4347-8ca6-70f75f1a6a0e
domainName        | corp.local
netBiosName       | CORP
domainControllers | []
directoryConfigId | 53
createdDate       | 2018-07-18 19:17:03.812+00
timestamp         | 1531941423812000000
resolved          | t

• Once the id and uuid are identified, substitute the values from previous queries to build the Delete statements below.

DELETE from "UserGroup" where "idGroup" in (select "id" from "Group" where "idOrganization" = '53' and "domain" = 'corp.local');
DELETE from "GroupParent" where "groupUuid" in (select "uuid" from "Group" where "idOrganization" = '53' and "domain"='corp.local');
DELETE from "Group" where "idOrganization" = '53' and "domain"='corp.local';
DELETE from "UserRole" where "idUser" in (select "idUser" from "Users" where "domain"='corp.local' and "strNotDeleted"='1' and "idOrganization"='53');
DELETE from "UserArtifacts" where "idUser" in (select "idUser" from "Users" where "domain"='corp.local' and "strNotDeleted"='1' and  "idOrganization"='53');
DELETE from "Users" where "domain"= 'corp.local' and "strNotDeleted"='1' and "idOrganization"='53';
DELETE from "Domain" where "id"='53' and uuid ='7b6cd22a-f7bb-4347-8ca6-70f75f1a6a0e';
DELETE from "DirectoryConfig" where "id" = '53' and "uuid" ='43248bc4-7c79-4844-b9cc-baecd3667c9d';
DELETE from "UserDomain" where "id"= '53';

• The Directory should now be removed.
• Recreate the directory and redirect users to an existing directory:
  • Change any users who are pointing to the removed directory and verify that their uuid is pointing to the new directory:
    • 155 is the ID if the new directory in the example below from the query: "Select * from "Domain";".
    • Run the query and note the new uuid. The userstoreUuid will now need to be updated to point to the new directory.

select * from "DirectoryConfig" where "id"=155;
-[ RECORD 1 ]-----------------------+------------------------------------------------------------------------------------
id | 155
uuid | 1ad9ff83-3183-4b79-9b5a-e369cc3cf6f0
tenantId | VSPHERE.LOCAL
host |
port |
useSsl | f
sslCertificate |
directorySearchAttribute | SAM_ACCOUNT_NAME
useSRV | t
baseDN | DC=corp,DC=local
bindDN | CN=svc_adaccess,OU=Users,DC=corp,DC=local
createdDate | 2018-10-06 00:51:09.402+05:30
timestamp | 1538767307988281408
name | corp.local

vcac=# select * from "UserStore";
-[ RECORD 4 ]------------+-----------------------------------------------
id | 155
uuid | e17526a1-4519-4b8a-8bb1-198c3e89c390
idOrganization | 2
name | Userstore_1ad9ff83-3183-4b79-9b5a-e369cc3cf6f0
createdDate | 2018-10-06 00:51:09.387+05:30
timestamp | 1538767302615233751
syncClientId | 410
userStoreNameUsedForAuth | f

vcac=# select * from "Users" where "domain"='corp.local' and "strUsername"='testuser';
-[ RECORD 1 ]------+-------------------------------------
strUsername | Testuser
idUser | 546
idOrganization | 2
idUserStatus | 1
strFirstName | Testuser
strLastName | Testuser
strEmail | [email protected]
strPhone | 7507559
strActivationCode |
idEncryptionMethod | 2
createdDate | 2018-07-19 01:31:27.564+05:30
strNotDeleted | 1
strExternalId | b7cc258b-abe1-4c63-9341-c6cc5a29130c
timestamp | 1538676872108798305
userStoreUuid | ce1f37c8-ed43-46cb-b18a-d9608affc415
userType | PROVISIONED
uuid | c72ad673-9af6-4928-85ed-95bb03146fd2
domain | corp.local

 

• Modify the users in which are pointing to the old userStoreUuid:

1. Update a single user:

update "Users" set "userStoreUuid"='e17526a1-4519-4b8a-8bb1-198c3e89c390' where "strUsername"='testuser' and "domain"='corp.local';

2. Update all other users

update "Users" set "userStoreUuid"='e17526a1-4519-4b8a-8bb1-198c3e89c390' where "userStoreUuid"='ce1f37c8-ed43-46cb-b18a-d9608affc415' and "domain"='corp.local';

Additional Information

Impact/Risks:
Ensure there is a valid database backup before execution of steps in this Knowledge article.