VMware Workspace ONE Access External Database

Creating SQL Database

In this step we will create a Microsoft SQL database and configure a service account with access. This database will be used by the VMware Workspace ONE Access appliance. An external database is the recommended option and a necessity if there is a need for a highly available solution. For a PoC, an internal database can be a good option especially if you would like to quickly showcase the product.

VMware provides an SQL script that can be used to create the database and although you can also utilize a local SQL account, this example uses the Windows authentication option. Always confirm to your organization’s security policies on account management. For this purpose, AD account svc_vmwarewoadb has been configured with no password expiration.

The generic SQL script to be used is:

/*
Values within angle brackets (< >) are example values. When replacing the example value,
remove the angle brackets. The database name is case sensitive, and the name must be one word with no spaces. 
Make sure you enter the database name the same in all instances.
*/


CREATE DATABASE <saasdb>
COLLATE Latin1_General_CS_AS;
ALTER DATABASE <saasdb> SET READ_COMMITTED_SNAPSHOT ON;
GO

IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name=N'<domain\username>')
BEGIN
CREATE LOGIN [<domain\username>] FROM WINDOWS;
END
GO

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

CREATE USER [<domain\username>] FOR LOGIN [<domain\username>] 
WITH DEFAULT_SCHEMA=saas;
GO

CREATE SCHEMA saas AUTHORIZATION "<domain\username>"
GRANT ALL ON DATABASE::<saasdb> TO "<domain\username>";
GO

ALTER ROLE db_owner ADD MEMBER "<domain\username>";
GO

The following values will need to be replaced:

• <saasdb> – ie vmwarewoadb
• <domain\username> – ie showme\svc_vmwarewoadb

Below is an example of a modified script:

/*
Values within angle brackets (< >) are example values. When replacing the example value,
remove the angle brackets. The database name is case sensitive, and the name must be one word with no spaces. 
Make sure you enter the database name the same in all instances.
*/


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

IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name=N'showme\svc_vmwarewoadb')
BEGIN
CREATE LOGIN [showme\svc_vmwarewoadb] FROM WINDOWS;
END
GO

USE vmwarewoadb; 
IF EXISTS (SELECT * FROM sys.database_principals WHERE name=N'showme\svc_vmwarewoadb')
DROP USER [showme\svc_vmwarewoadb]
GO

CREATE USER [showme\svc_vmwarewoadb] FOR LOGIN [showme\svc_vmwarewoadb] 
WITH DEFAULT_SCHEMA=saas;
GO

CREATE SCHEMA saas AUTHORIZATION "showme\svc_vmwarewoadb"
GRANT ALL ON DATABASE::vmwarewoadb TO "showme\svc_vmwarewoadb";
GO

ALTER ROLE db_owner ADD MEMBER "showme\svc_vmwarewoadb";
GO

From within SQL Server Management Studio, click on New Query, paste the contents of the updated script and click Execute. This will create the database.

Validate Creation of Database

You can run the following SQL script to verify the database was created properly, remember to replace the values with your account and database names.

 execute as user = 'showme\svc_vmwarewoadb' 


/* Check if user is db owner. Return true */
SELECT IS_ROLEMEMBER('db_owner') as isRoleMember 
 
/* Make sure user is not sysadmin. Should return false */
SELECT IS_SRVROLEMEMBER('sysadmin')  as isSysAdmin
 
/* check if saas schema exists, should be not null */
SELECT SCHEMA_ID('saas') as schemaId
 
/* check schema owner, should be user provided to installer */
SELECT SCHEMA_OWNER FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='saas'
 
/* check if saas is user default schema, should return saas */
SELECT SCHEMA_NAME() as SchemaName
 
/* check db collation, should return Latin1_General_CS_AS */
SELECT DATABASEPROPERTYEX('vmwarewoadb', 'Collation') AS Collation
 
/* check if read committed snapshot is on, should return true */
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='vmwarewoadb'

Increase SQL Auto Growth

Additionally, you will need to configure database Auto Growth to 128MB. You can use the following script, again replace the database name with your own name.

ALTER DATABASE vmwarewoadb
        MODIFY FILE ( NAME = N'vmwarewoadb', FILEGROWTH = 128MB )
GO

References

Configure the Microsoft SQL Database with Windows Authentication Mode
Confirm Microsoft SQL Database Is Correctly Configured
Change SQL Server Database Auto Growth Settings

Series Navigation

<< VMware Workspace ONE Access OverviewWorkspace ONE Access Deployment (OVA appliance) >>