XenApp/XenDesktop 7.x database permissions

Procedure to configure XenDesktop database in a secure environment.

XenDesktop wizard is made to automatically connect to the SQL Server and create the database. That is actually very helpful in a lab or small environments but it does not work in large and secure deployments. Simply because you don’t have full access to the SQL server and the rights to perform database creation tasks that require extended privileges. In this post, we will review how to create XenDesktop 7.x database in a secure environment.

Requirements

• Windows authentication is required for connections between the Controller and the SQL Server database. SQL Authentication is not supported.
• Dedicated service account is recommended to connect to SQL.
• SQL Server is supported up to 2014 SP1.
  • http://docs.citrix.com/en-us/xenapp-and-xendesktop/7-5/cds-system-requirements-75.html
• Mirroring is only possible with the full version of SQL Server.
• SQL collation of the database must be ending with 100_CI_AS_KS.
• SQL server collation can be different from the XD database collation.
• Each XenDesktop service gains access to the database through the local controller’s machine account.The services gain access to the database server through their machine account logon (names of the form ‘DOMAIN\MACHINE$’). These logons do not need to be members of any server-level roles.
  • http://support.citrix.com/article/CTX127998

Permissions

Citrix provides the privileges required to perform XD tasks:

As you can see, for an initial configuration you need to be db_creator, db_owner and securityadmin. Your DBA friends won’t like you if you ask securityadmin privileges on the production SQL servers. The solution to this problem is to delegate the creation of the database and the configuration to the DBA guys.

Setup

At the screen below, enter the database server location, and the name of the database.

You can try to connect to the SQL server but you won’t be able to connect. In a secure environment, your account does not have the permissions to connect to the SQL server and especially does not have the SecurityAdmin privileges required.

Then select Generate database script.

SQL Scripts are generated in the C:\TEMP folder.

Configuration

Now contact your DBA team and ask them to create a new database with the following configuration:

• Name: XD7_DB
• Owner: your admin account
• Size: Primary – 400MB and Logs – 100MB
• The recovery model is set to Simple. For use as a mirrored database, this must be changed to Full.
• Collation: Latin1_General_100_CI_AS_KS
• Permissions:
  • DBOwner for the service account you are using to connect to the DB
  • DBOwner for your admin account (optional)
• Run the SQL script in SQLCMD mode in Microsoft SQL Management Studio

Note: The database must be created before running the script. 

 

Create database

To be performed by DBA guys.

Name: XD7_DB
Owner: CITRIXGURU\Administator
Primary: 400 MB
Logs: 100MB

Collation: Latin1_General_100_CI_AS_KS
Recovery mode: Simple (Full, if mirrored)

Database is created but is empty.

Run previously generated SQL Script

DBA team also needs to run the script previously created by the XenDesktop installer.

The script must be executed in SQLCMD mode with an account account having securityadmin or systemadmin permissions on the SQL server.

The script is showing errors if you are not in the SQLCMD mode:

You can put SQL Management Studio into SQLCMD mode by going to the “Query” menu and selecting SQLCMD mode.

The database is now containing XenDesktop tables.

Permissions have been updated with the XD controller computer account:

• CITRIXGURU\CDC01$ added (no role membership)

XD controller computer account permissions

Add permissions

The SQL administrators also need to add your service account in the database permissions.

Create the account, login name: CITRIXGURU\svc_sql_ctx
Default database: XD7_DB

Login name and user name: CITRIXGURU\svc_sql_ctx

The account is now in the users of the dabatase.

Select DB_owner schema.

The database is now ready.

At the following screen you can select Yes.

Enter alternative credentials.

 

Select OK and XenDesktop installer should continue the installation without errors.

Join a new controller to the XD farm

Select Connect this Delivery Controller to an existing site.

Enter the address of the primary controller: cdc01.citrixguru.lab

Select No.

Then select Generate scripts.

Same as before, as the DBA guys to run the script.

The script will automatically add the secondary controller in the permissions.

Next, press OK and enter the service account credentials.

Desktop Studio will process the request.

Install is successful.

You can verify that the secondary controller is present by clicking on Controllers in configuration.