SQL Preparation

Attachments

Manual

Internal PostgreSQL

To login into the DB, get the PostgreSQL password: first login to console with SSH.


cat /usr/local/horizon/conf/db.pwd

Copy password, then login with it:


psql saas horizon

External MS SQL Preparation

❗️ Database schema name must be ‘saas’, cannot be changed. ❗️Collation must be ‘Latin1_General_CS_AS’, could be changed but change not recommended. ❗️The server role used to grant server-wide security privileges is set to public. The database role membership is db_owner.

Microsoft SQL Database Using Local SQL Server Authentication Mode for Workspace ONE Access (replace values in brackets < > ):


CREATE DATABASE saas
COLLATE Latin1_General_CS_AS;
ALTER DATABASE saas SET READ_COMMITTED_SNAPSHOT ON;
GO

BEGIN
CREATE LOGIN <loginusername> WITH PASSWORD = N'<password>';
END
GO

USE <saasdb>; 
IF EXISTS (SELECT * FROM sys.database_principals WHERE name=N'<loginusername>')
DROP USER [<loginusername>]
GO

CREATE USER [<loginusername>] FOR LOGIN [<loginusername>]
WITH DEFAULT_SCHEMA=saas;
GO

CREATE SCHEMA saas AUTHORIZATION <loginusername>
GRANT ALL ON DATABASE::saas TO <loginusername>;
GO

ALTER ROLE [db_owner] ADD MEMBER <loginusername>;
GO

JDBC URLs

SQL local user jdbc:sqlserver://<DB_VM_IP_ADDR>;DatabaseName=saas

jdbc:sqlserver://<DB_VM_IP_ADDR>\INSTANCE_NAME:PORT;DatabaseName=saas (you can remove the instance name if default)

AD domain user jdbc:jtds:sqlserver://<DB_VM_IP_ADDR>:1433/saas;integratedSecurity=true;domain=LAB.LOCAL;useNTLMv2=true

Multi-site, SQL Always On jdbc:sqlserver://;DatabaseName=saas;multiSubnetFailover=true

Troubleshooting Issues

Troubleshooting JDBC URL Wizard page

Back and continue buttons become greyed out and unclickable.

  • Use Web Admin tools of the Browser (Firefox, Chrome);
  • Right click and “Inspect Element” on the disabled button. Find the ID tag for “nextButton”;
  • Within this line of text there is a value = “is-disabled”. Remove “is-disabled” from the line by clicking and typing into the inspector;
  • Return to web page, button should start working. Click & proceed.

Database Locked Error

See resolution link

Check the result of the following DB query, If DB is locked, you should see an entry with the locked value set to 1 or TRUE.

	
SELECT * FROM DATABASECHANGELOGLOCK 

if locked=TRUE then run the below UPDATE statement to release/reset the DB lock.

USE [DBNAME]
GO 

UPDATE [saas].[DATABASECHANGELOGLOCK]
   SET [LOCKED] = 0 

 ,[LOCKGRANTED] =NULL 

 ,[LOCKEDBY] = NULL
 WHERE ID = 1 

GO

Restart horizon-workspace service on each node one after another. ❗️DO NOT restart all nodes of WS1 Access cluster simultaneously.

Important Queries

Update the Connector if there is more than 1 sync and Authentication


UPDATE “Connector” SET “isDirectorySyncEnabled”=‘false’ WHERE “host”=‘<auth_connector_hostname>’

Check if IDM certificate is present in the console from DB side


SELECT * from dbo.coreuser WHERE isactivedirectoryuser=0  
SELECT * from dbo.certificate WHERE certificatethumbprint like '%%'  
SELECT * from dbo.UserLink WHERE coreuserid = < username >
SELECT * from dbo.role WHERE roleid =3

List of UUID with Super Admin access


SELECT strUsername, strFirstName, StrLastName,strExternalId, stremail, uuid FROM slesdb.saas.Users U (nolock) INNER JOIN  
(SELECT * FROM Slesdb.saas.ACS_RuleSetAssociation (nolock)  
WHERE ruleSetId LIKE  
(SELECT id FROM slesdb.saas.ACS_RuleSet (nolock)  
WHERE name LIKE 'Super Admin)) as A  
On U.uuid = A.SubjectUUID
ORDER BY strUsername

Update email address of the user or admin


UPDATE sles.saas.Users
SET strEmail = 'value'  
WHERE username LIKE 'value'

Update the value of the Identity Provider with correct connector name


UPDATE slesdb.saas.IdentityProviders  
SET strDescription = (  
SELECT host FROM slesdb.saas.Connector WHERE id = (

SELECT idConnector FROM slesdb.saas.IdpJoinConn WHERE idIdentityProvider = (  
SELECT id FROM slesdb.saas.IdentityProviders WHERE strFriendlyName LIKE '%Workspace%')))

Update the attribute column to make it mandatory or non-mandatory


 SELECT id, * FROM slesdb.saas.userattributedefinition WHERE idorganization IS NOT NULL AND ownerUuid IS NULL AND strName LIKE '<attribute to be updated>

Then run:


UPDATE slesdb.saas.userattributedefinition SET bIsRequired = 0 WHERE id = <id identified above>

Connector Sync Validation


SELECT id, uuid, tenantID, host, domainJoined, createdDate, oAuth2ClientId, isDirectorySyncEnabled FROM saas.Connector;

SELECT idSyncProfile, directoryConfigId, syncConnectors FROM saas.DirectorySyncProfile

⭐️ FROM saas.Connector isDirectorySyncEnabled = 1 means connector is the connector set as Sync. If not, you can update:


UPDATE saas.connector SET isDirectorySyncEnabled=0 WHERE id=1;

⭐️ FROM saas.DirectorySyncProfile syncConnector = saas.Connector uuid value. You can update if needed as well:


SET isDirectorySyncEnabled=0 WHERE id=1;  
update saas.DirectorySyncProfile SET syncConnectors='["12345678-abcd-1234-1234-0123a678b78"]' WHERE idSyncProfile=1;