Archives for February 2010

SQL 2008 Security and vCenter 4

This past weekend I worked on automating our SQL installs and scripting security lockdowns. During that effort I found the new policy evaluation tool in SQL 2008. This built-in tool allows you to run specific checks against your SQL 2008 installation to report on best practices. During my evaluation everything passed with a green light except “Public not granted server permissions“. Since I had no clue what that meant, a little googling come up with this blog.

After implementing both fixes described in the blog, vCenter died and was unable to connect to my SQL instance. Security is great, but not when it kills your missions critical applications. After some experimentation, I was able to find a combination of the lockdown commands that added some security yet allowed vCenter to operate.

If you read the blog I linked to above, you will see that I was able to execute all of the commands except the one denying public connections over TCP. Since vCenter uses TCP to connect to SQL, it makes sense that I might need to allow public connections over TCP. I was surprised that I was able to deny public the view role to any database and vCenter worked fine, but preventing the TCP connection was bad juju.

DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] to public
DENY CONNECT ON ENDPOINT::[TSQL Local Machine] to public

Unfortunately even after these lockdowns the SQL policy evaluation tool still failed me on “Public not granted server permissions.” Only when I locked down everything including TCP did I get a green check. Now how much actual security these commands provides, I have no idea. But given that vCenter still works, I figure the less attack surface area the better.

I also found this great blog with lots of SQL scripts for really putting the screws on SQL and hardening it even further. From some quick testing I did with vCenter 4.0, you can safely execute the server wide scripts with one modification: Enable the SQL Agent XPs on line 61. The database lockdown ran fine as-is on my vCenter server database.

Don’t take my word for it that these lockdowns won’t adversely affect your vCenter. So test, test, test!!!

Scripting SQL Server Firewall Rules

On servers that are running Windows server 2008 or later, you can take advantage of the built-in two-way firewall. SQL server firewall rules can be pretty easily created through the GUI, but it can be a bit tedious. So I wrote a little batch file that asks you for an IP address and then opens the SQL port such that it only accepts connections from that IP address.

You can of course extend this to any other program just by modifying the switches, protocols, IPs, etc. It will error out if you don’t supply an IP address, so that you don’t get a meaningless rule. A new rule is created each time you run the doesn’t update an existing rule. That is possible with the netsh command though.

@echo off
:: Configures Windows Server 2008/R2 firewall for SQL.
:: Requires a single argument, the IP address of the remote application server that requires SQL access.
:: Usage: SQL-Firewall.cmd

if [%1]==[] ; GOTO :ERROR
Echo Configuring Windows Advanced Firewall for SQL to listen on IP %1
netsh advfirewall firewall add rule name="SQL Server (TCP-in)" dir=in action=allow protocol=TCP Profile=domain localport=1433 program="D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" description="Allows inbound Microsoft SQL connections." remoteip=%1
Exit /B
Echo Please specify IP address.

Install vCenter and vSphere Client via command line

I stumbled upon this technical note from VMware on how to install vCenter and the vSphere client silently via the command line. Unfortunately the syntax is pretty convoluted, unlike the SQL 2008 unattended method. The vCenter command line install document can be found here.

Just to give you a flavor of the complexity, here’s a working command to silenty install the vSphere client to a custom directory. This only required one VMware switch…vCenter could require a dozen or more. Needless to say, this is not for the faint of heart. It’s probably quicker and easier to click through the GUI.

start /wait Z:vpxVMware-viclient.exe /q /s /w /L1033 /v” /qr INSTALLDIR=”D:Program Files (x86)VMwareInfrastructure”

Automate vCenter/VUM Database Creation Process

After installing and re-installing VMware vCenter and VUM many times (mostly for testing) all of the manual database creation steps got old pretty fast. So I put some effort into creating a T-SQL script for SQL 2008 that automates most of the tasks.

The T-SQL script below does the following:

1. Adds an existing domain service account for vCenter to SQL. Change the value in the SET command.
2. Grants this account db_owner rights on msdb. Required for vCenter/VUM installation.
3. Creates separate vCenter and VUM databases. Feel free to change their sizes.
4. Sets the ownership of the new databases to the service account.

It’s up to the installer to remove the db_owner rights on the MSDB database after you get done installing vCenter and VUM. I apologize for the line wrapping and weird spacing. It might take a bit of futzing to get it all looking pretty. Of course you will need to tweak the paths and any other information as required.

DECLARE @login_name NVARCHAR(50)
SET @login_name=’contososvc-020-VCTR01′
EXEC(‘CREATE LOGIN [‘+@login_name + ‘]FROM WINDOWS’)


EXEC sp_grantdbaccess @login_name
EXEC sp_addrolemember db_owner, @login_name

USE master
create database “vCenter Server”
( name = ‘vCenter Server’,
filename = ‘K:DataMSSQLvCenter_server.mdf’,
size = 5000MB,
filegrowth = 250MB )

log on
( name = ‘vCenter Server log’,
filename = ‘L:LogsvCenter_server.ldf’,
size = 200MB,
filegrowth = 20MB )

COLLATE SQL_Latin1_General_CP1_CI_AS;

create database “vCenter Update Manager”
( name = ‘vCenter Update Manager’,
filename = ‘K:DatavCenter_Update_Manager.mdf’,
size = 250MB,
filegrowth = 25MB )

log on
( name = ‘vCenter Update Manger log’,
filename = ‘L:LogsvCenter_Update_Manager.ldf’,
size = 25MB,
filegrowth = 2MB )

COLLATE SQL_Latin1_General_CP1_CI_AS;

EXEC(‘ALTER AUTHORIZATION ON DATABASE::”vCenter server” TO [‘+@login_name + ‘]’)

EXEC(‘ALTER AUTHORIZATION ON DATABASE::”vCenter Update Manager” TO [‘+@login_name + ‘]’)


Create 32-Bit system ODBC DSN with Powershell

This Powershell script creates a 32-bit system DSN on a 64-bit server. I got tired of manually creating DSNs for my VMware vCenter installations, so I automated the process. The only argument you need to pass to the script is the FQDN or IP address of your SQL server. If you don’t pass any arguments, the script will abort with an error message.

I apologize for the line wraps, but I think its fairly obvious where the breaks are. You can change the $DSNName to be any name that you wish. $DBName is the name of the database on the SQL server. The script assumes the SQL 2008 native client is installed. If you use a different client you will need to modify the script a bit.

## Creates a 32-bit System DSN on 64-bit OS.

$DSNName = “vCenter Server Update Manager”
$DBName = “vCenter Update Manager”

If($args[0] -eq $NULL) { echo “Must specify FQDN or IP of SQL server.”; Exit}



md $HKLMPath1 -ErrorAction silentlycontinue

set-itemproperty -path $HKLMPath1 -name Driver -value “C:WINDOWSSysWOW64sqlncli10.dll”

set-itemproperty -path $HKLMPath1 -name Description -value $DSNName
set-itemproperty -path $HKLMPath1 -name Server -value $args[0]
set-itemproperty -path $HKLMPath1 -name LastUser -value “”
set-itemproperty -path $HKLMPath1 -name Trusted_Connection -value “Yes”
set-itemproperty -path $HKLMPath1 -name Database -value $DBName

## This is required to allow the ODBC connection to show up in the ODBC Administrator application.

md $HKLMPath2 -ErrorAction silentlycontinue

set-itemproperty -path $HKLMPath2 -name “$DSNName” -value “SQL Server Native Client 10.0”

Revisiting your backup licensing model

I’m in the process of doing a market survey for various enterprise backup products. During my investigation, I’ve found that picking the right backup licensing model can save you a large amount of money. How?

Companies like IBM and Symantec offer different licensing models for the same product. Specifically, you can go the typical a la Carte model where you add up the number of servers, application agents, tape libraries, options, etc. and purchase a boatload of line items to exactly match your environment. Depending on your vendor, the boat you end up with may be large and complex or not so large.

Another model is a capacity based model. In this model you license the quantity of storage you use in TB increments, and depending on your vendor, you may need to add additional licenses for the number of servers or unique agents. I call this capacity + other license model a hybrid approach since it’s not strictly based on capacity.

During my research I discovered that Symantec NetBackup 7 offers both the traditional a la Carte model, and a pure capacity based model. IBM Tivoli storage manager offers the agent model, and a hybrid capacity model. Depending on your specific environment, these two different capacity based models may significantly alter the bottom line cost. The pricing tier for both companies is dependent on the number of processors or cores in each server.

Let’s take a concrete example to make this clearer. Here’s a sample environment:

10TB usable space on a SAN disk array, 3TB of actual data
LAN-Free Backups required
Fibre Channel tape library
Virtual Tape Library
All servers are dual socket, quad core
20 Total physical servers
— 3 SharePoint
— 2 Exchange
— 2 Active Directory
— 2 SQL 2008
— All remaining are member servers

In the pure capacity based model, such as NetBackup 7, you would need a single 3TB license. Allowing for future growth, maybe add another TB, for a total of 4TB. Throw in yearly maintenance and you are done. Two line items on your PO. You could add/remove servers, libraries, add a VTL, virtualize, or do whatever you want for no additional cost, assuming you stay within your capacity license.

If your backup product goes the hybrid route, like Tivoli 6, then your shopping cart would look something like this:

–Capacity license (1TB x3)
–SharePoint License (x3)
–Server license (x20)
–Master server license (x1)

Depending on your specific situation, one model may be significantly more or less expensive than the other. Other products like Veeam Backup, are licensed on a per-socket basis and have no capacity other limitations. The bottom line is thoroughly understand ALL licensing models that your backup vendors are offering and get quotes for both approaches. You may be shocked at the cost difference.

I was quite surprised that NetBackup 7 and Tivoli 6 capacity licenses are based on used disk space, not total presented disk space. This means you only pay to backup your actual data usage, and not for the unused capacity on your disks or SAN. Bottom line, calculate the total amount of data in a full system backup and that’s your minimum licensing requirement for the capacity model.

Banish the evil VMware ESX VM snapshots

The other week I was telling a co-worker that in most cases using VM snapshots in ESX was evil and should be avoided in all but exceptional circumstances. He gave me this look like I grew a third eye, my ears fell off, and my skin turned green.

I then conveyed to him that using VM snapshots can be very bad. From a security perspective, they can let you ‘go back in time’ and possibly hide tracks of snooping around a system. If you revert an Active Directory server to a prior state, you could end up with very confused and screwed Active Directory server or forest. You could also run out of snapshot disk space, or suffer large performance hits when you delete the snapshot. Features like DRS, vMotion, and backups are also impacted.

After this short conversation, I guess my third eye disappeared, my ears grew back, and my complexion returned to my normal pale white…just like on an infomercial. Today I stumbled on a GREAT, and very lengthy, article on the evils of VM snapshots and their potentially significant performance impacts. Check out Erik Zandboer’s extremely thorough blog post.

I also found very interesting is a comment left by a reader that purports to be a former VMware support professional. According to him most VM performance problems were linked to snapshots.

While I won’t deny snapshots have their use in VERY limited cases, I fear that people over use them and don’t realize the problems they create. We have alarms set in vCenter to alert on snapshots, and have a written policy that snapshots not exceed six hours and can only be used in exceptional circumstances.

3PAR InForm OS Upgrade – Easy as pie!

If you’ve been following my blog, you know that last year our project acquired a 3PAR T400 Fibre Channel array. One of the features which really sold us on 3PAR was its ease of use and the ability for our technical staff to perform all aspects of maintenance including major upgrades without any on-site professional services.

In the last few weeks 3PAR released a major software upgrade for their arrays which enables a boatload of new features, including enhanced thin provisioning, autonomic provisioning, and RAID-MP. Today I performed the upgrade to Inform OS 2.3.1, and it went without a hitch and was quite easy. Unlike high-end arrays from Hitachi, EMC, and IBM, the upgrade process is not complex and took no more than an hour of time.

Basic upgrade process was as follows:

1. Upgrade the service processor (a Linux-based management appliance that does NOT sit in the data path) to the latest OS. Insert a CD, run a couple of menu commands, then come back 20 minutes later.

2. Perform a health check of the array to ensure it can do an online update. Again, just a couple of menu options and review the output.

3. Execute the controller upgrades by executing a menu command, then come back 20 minutes later after the rolling (and non-disruptive) upgrade completes.

4. Perform another health check to verify the upgrade went as planned. Run a final command to upgrade the disk cage and disk drive firmware. Again, non-disruptive.

5. Upgrade the Windows-based management tools to the latest version and call it a day!

The entire process requires no downtime for attached hosts, and I/Os continue to be serviced throughout the entire process. This was our first major upgrade, and I must say the ease of use really paid off.

VMware Workstation 7.0.1 Released

A couple of days ago VMware released Workstation 7.0.1. No earth shattering updates, but now officially supports Server 2008 R2 and Vista SP2 as host and guest operating systems. You can download it here and read the release notes here.