vCloud Director 5.1 upgrade and missing sysadmin permissions

I`ve recently managed to crash my test lab. One of the rebuild tasks was to reinstall vCloud Director - the hard way: dropping the DB and recreating it. Next I was running the vCloud Director installation script (5.1.1), recreating the DB schema and so on. After a week I`ve decided to upgrade the vCD installation from 5.1.1 to 5.1.2.

After downloading the bin file (vmware-vcloud-director-5.1.2-1068441.bin) from VMware site and uploading it on my vCD cell , I`ve executed the bin and a long list of "Yes" ends up with an error:

Unable to update database statistics. Database user has insufficient privileges to update database statistics. To complete this step manually, run 'EXEC sp_updatestats' as the DBO or a member of the sysadmin fixed server role.

Next, I`ve been looking in the DB server (MSSQL) - logging in with vCD DB admin user (vcddbadmin) and checking if the rights are ok. And they were, since the user was dbowner. I`ve ran 'EXEC sp_updatestats' in query editor and ended up with another error:

Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15
User does not have permission to perform this action.

It was stored procedure time: vcddb01 -> Programmability ->Stored Procedures -> System Store Procedures and after a few pages of scrolling down: sys.sp_updatestats. I`ve opened the stored procedure (Context menu - Modify) and on line 15 there was a check to see if the current user was sysadmin. Which, obviously it was not.

Below, a small SQL script that will display if the current user is sysadmin or not:

if is_srvrolemember('sysadmin') = 1
print  ' It is sys admin'
else if is_srvrolemember('sysadmin') = 0
print  ' It is NOT sys admin'

To solve the problem, I`ve added sysadmin role to the vCloud Director DB admin user (vcddbadmin) and ran again the stored procedure:

This time it worked ok and updated database statistics. I`ve checked back the installation procedure for vCloud DB and I`ve taken a look at KB 2034540 to see if I`veforgotten anything while creating the user for vCD DB. But it seems I did not. The only reference is to db_owner role. Since I am not a DB guy, I`ll just leave it as is, happy to have solved my problem. 

Installing vCloud Director on CentOS and MS SQL Express - part 1

Before it all starts

In the past 3 months I have been involved in projects using vCloud Director. The necessity to have a test environment appeared naturally. So I started working in my home lab on deploying vCloud Director. I had the help of some other blogs (about which you will hear in the post below) but I have also done it different by using CentOS 6.3 and MS SQL 2012 Express (both not supported by VMware).

The following post presents installation of vCloud Director 5.1 on CentOS 6.3 and MS SQL 2012 Express and will have 3 parts:

• part 1 presents Configuration of MS SQL 2012 Express Database for vCloud Director installation
• part 2 presents Configuration of CentOS 6.3 for vCloud Director
• part 3 presents Installation of vCloud Director 5.1

Before starting anything make a little IP planning and check that you have a local DNS server and make sure that all infrastructure can be resolved properly (hosts, vcenter server, databases, vcloud cells and so on). I am using an AD (since I`ll  be testing LDAP integration ) integrated with MS DNS server. Other prerequisites: do not forget to deploy the vShield Manager appliance - each vCenter Server needs to have a vShield Manager. We will talk a bit later about basic configuration of vShield Manager.

 

Configuration of MS SQL 2012 Express Database for vCloud Director installation

First create the VM: 1 vCPU, 3 GB RAM (2GB recommended by MS), 25GB HDD thin provisioned (thin provisioning on SSD works great). Install Windows Server 2008  and VMware Tools. Configure the server (IP, hostname), if AD exists, join the server to the domain.

Next, download MS SQL 2012 Express from Microsoft site, the version that includes management studio (SQLEXPRWT_x64_ENU). Installing the DB is pretty straight forward - make sure to choose mixed mode authentication and configure sa user password. If you miss this step, it can be done after the install ( SQL Server Management Studio - Server properties - Security). During the configuration choose an instance name (VCDDB for example).

After SQL server is installed, open SQL Server Management Studio connect to VCDDB instance with sa user and add a user for vcloud director - vcddbadmin (Security - Logins - New)

Now it is time to create the DB: Database - New - vcddb01 (you can give it any name). Change the owner to user vcddbadmin.

Sizing the DB: VMware documentation offers the following parameters: data file (mdf) size = 100MB, filegrowth= 10% and for log file (ldf) size = 1MB, filegrowth= 10%. In lab environment these should suffice.

A very interesting post about vCloud director database can be found on Erik Bussink blog. Based on that post , I have decided to make the following configuration on my DB:

• data file size = 1024MB, growth = 512 MB, limit = 3072 MB
• log file size = 128 , growth = 128 MB, limit = 1024MB 

The actual limits are determined by both SSD space and VM size in my lab environment.

On Options page from database properties the proper collation sequence will be configured to Latin1_General_CS_AS. Default values for recovery model and compatibility level will be left unchanged ( Simple, SQL Server 2012 respectively)

The last action on the database is to prepare the DB. Again, I have used the information from Erik Bussink. Open query editor and execute the script below:

USE [vcddb01]
GO
ALTER DATABASE [vcddb01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [vcddb01] SET ALLOW_SNAPSHOT_ISOLATION ON;
EXEC sp_addextendedproperty @name = N'ALLOW_SNAPSHOT_ISOLATION', @value = 'ON';
ALTER DATABASE [vcddb01] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
EXEC sp_addextendedproperty @name = N'READ_COMMITTED_SNAPSHOT', @value = 'ON';
ALTER DATABASE [vcddb01] SET MULTI_USER;
GO

Form the original script I have removed the first line ALTER DATABASE [vcddb01] SET RECOVERY SIMPLE since the DB has default recovery model set as simple. Check that the extended properties were added: database Properties - Extended Properties.

To finish the installation, a couple of more things have to be done. First, configure MSSQL server to listen on TCP/IP. Using Sql Server Configuration Manager go to SQL Network Configuration and enable TCP/IP. In Properties tab chose the IP address and set the following parameters:Active YES,  Enabled YES, TCP Port 1433,  TCP Dynamic Port 0.

Then, restart the service and check that the server is listening on port 1433 (netstat -van).

Finally, configure Windows Firewall to allow incoming connections on TCP 143 and test that communication with the server on TCP 1433 is ok from another VM using the command telnet ip_addr_db 1433.