Managing Certificates
External links:
General Commands
Most common command is to build PFX-file from PEM files:
openssl pkcs12 -export -out certificate.pfx -inkey privkey.pem -in fullchain.pem -certfile cert.pem
WS1 Access always has to be signed with corporate or trusted certificates. If Access is clustered, sign the load-balanced name with external trusted certificate and the 3 nodes - with certs from corporate CA.
- Go to WS1 Access web console
- On the top, click the Appliance Settings tab,
- On the left, click the VA Configuration node.
- On the right, click Manage Configuration. You will be redirected to a separate portal
- Login as admin account
- On the left, click Install TLS Certificates.
- On the right, in the upper box, delete the certificate and key that are currently displayed.
- Paste in the new PEM certificate and RSA private key. Paste every certificate in the chain: server + intermediate + root. Click Save.
❗️The order of certificates is important! First server, then intermediate, then root.
Certificate Requests
######### The cert request in idm01.domain.local.inf file
[Version]
Signature= "$Windows NT$"
[NewRequest]
Subject = "CN=idm01.domain.local,OU=IT,O=Horn_n_hooves,L=Moscow,S=Moscow,C=RU"
KeySpec = 1
KeyLength = 2048
Exportable = TRUE
MachineKeySet = TRUE
UseExistingKeySet = FALSE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
KeyUsage = 0xa0
FriendlyName = "vdm" ; needed for Horizon Connection Server only!
[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1
[RequestAttributes]
CertificateTemplate = WebServerExportable2008
[Extensions]
; If your client operating system is Windows Server 2008, Windows Server 2008 R2, Windows Vista, or Windows 7
; SANs can be included in the Extensions section by using the following text format.Note 2.5.29.17 is the OID for a SAN extension.
2.5.29.17 = "{text}"
_continue_ = "dns=idm01.domain.local&dns=idm01&dns=idm02.domain.local&dns=idm02&dns=idm03.domain.local&dns=idm03&dns=idm.domain.local"
##################################################################################
Bat script to submit certificate request:
set srvname=idm01.domain.local
cd C:\temp
Certreq -New -f %srvname%.inf %srvname%.req
Certreq -submit %srvname%.req %srvname%.crt
Certreq -accept %srvname%.crt
Certutil -exportpfx -p 12345 %srvname% "%srvname%.pfx"
In order to copy and paste the private key from PFX certificate for vIDM, you need a decrypted version of the key. Use OpenSSL to obtain this key:
openssl pkcs12 -in idm01.domain.local.pfx -nocerts -out idm01.domain.local_encrypted.key
openssl rsa -in idm01.domain.local_encrypted.key -out idm01.domain.local_decrypted.key
Open idm01.domain.local_decrypted.key with a text editor and copy the key from there.
After inserting certificates, click OK to restart WS1 Access web service.
SQL Preparation
Links
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:
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;