Archives for July 2010

VMware ESX 4.1 released!

With leaks starting to grow about next release of ESX(i), v4.1 is now out the door! It is a big upgrade and include a lot of new goodies for people. Below is a small sampling of the changes in 4.1:

VMware now made it obvious ESX is dying and everyone should be migrating to ESXi. The next major release of vSphere will be ESXi only.

VMware Converter vSphere client plug-in appears to be going away in future releases (standalone will remain supported)

VUM in future releases will no longer patch or scan Windows and Linux VMs. VUM now seems to be destined to just manage VMware tools and ESX host software. VUM in 4.1 does not scan or patch Windows 7 / Server 2008 R2 VMs.

vSphere web access is going away in the next release.

ESXi installs can now be scripted for rapid mass deployment.

– Boot from SAN supported for ESX and ESXi using iSCSI, FCoE, and Fibre Channel.

– New Hardware Acceleration with vStorage APIs for Array Integration (VAAI) allows leading-edge storage arrays to provide hardware assistance for certain disk operations. 3PAR put out a press release stating their support for VAAI.

– Storage I/O control provides I/O QoS capabilities. 3PAR is supporting SIOC. Short blog by VMware here with a nice chart.

iSCSI hardware offloads for 10Gb broadcom NICs.

– Network I/O control – Traffic management controls to allow flexible partitioning of physical NIC bandwidth. Great blog on it here.

VMware HA now supports Microsoft clusters

– Memory compression – slower that regular RAM but faster than disk.

– Up to 8x performance increase for vMotion

ESX and ESXi Active Directory Integration for privileged user authentication

USB device pass-through – vMotion compatible

– Windows Server 2008 R2 support for vCenter server

NO support for SQL Server 2008 R2 (Bummer!!!)

vCenter server is now 64-bit only.

To read the full list of new features, check out What’s New. Compatibility matrix is here. Check out the ESXi 4.1 release notes for known bugs and gotchas. You can download a full bundle of the 4.1 documentation here.

Microsoft System Center Management Pack Catalog Refresh

Several months ago when Microsoft moved their System Center management packs to their “PinPoint” site, it was a horrible mess. Trying to search or find the right MP was an exercise in futility. In fact, it’s beyond me how MS released that interface to the public. Clearly they didn’t consult any customers in the design as it was practically useless.

A few days ago MS gave the site a big face lift, and now it’s a pretty decent site. Release dates are finally shown, and you can easily filter by System Center product and vendors. This is what PinPoint should have been at its first release. I will no longer be cursing at Microsoft when I’m trying to find the latest MP.

You can check out the now usable interface here.

How strong is your SSL? Sniff and find out!

Today a colleague of mine asked me if I really thought one could tell what cipher strength is used during SSL transactions. I said sure! Piece of cake if you know what to look for. Just like in the movie Matrix, if you stare at the cipher text long enough you visualize the meaning and a little voice in your head tells you “AES-256…..” or “ECDHE….” or “They are out to get you…..”

Well ok not really..but you can easily find out and here’s how. When a client like IE connects to a web server that supports SSL there’s a handshake message called ClientHello. In this message the client bares all and lists all of the cipher suites that it supports. See the example below for an example ClientHello message…and look very carefully.

If you are used to seeing ClientHello messages, two things should appear somewhat odd in the image above. I captured it during an IE8/Windows 7 handshake with an IIS 7.5 web server. First, the client supports TLS 1.2. This is unusual, and takes manual intervention to enable in IE8. Second, the order of the cipher suites is non-standard as well. I didn’t like the default order that Microsoft bakes into Windows so I put the AES-256/SHA-256 cipher suites at the top of the list. Nothing but the best!

Now how about the server end? Astonishingly there’s a server message called ServerHello. Servers are busy beasts, so all it returns is the strongest cipher suite that it has in common with the client. In this case I also enabled TLS 1.2 on the server end, so it picked AES-256 with SHA-256.

From there on out the SSL handshake continues and eventually an encrypted session is built up and all comms go secure. You can apply the same sniffing techniques to deconstructing SQL SSL sessions as well. Network Monitor 3.4 seems better than WireShark at decoding more types of SSL connections, such as those used by SQL.

Enable SQL SSL with low-privileged service account

One of the neat security features with SQL 2005 and later is the ability to use a SSL certificate to encrypt off-host SQL server communications over port 1433. Encrypting communications between your SQL server and your remote applications is strongly recommended. Do you really want credit card data, personal information or sensitive data traversing the network in clear text? Probably not. Yes you could use IPsec between your SQL server and applications, but that’s not for the faint of heart. But once you know the trick to making SSL work with SQL, it’s a no brainer.

First, your computer needs a server authentication certificate with an allowed enhanced key usage of For simplicity the domains I manage have an auto-enroll policy that doles out machine certificates to all computers. If you don’t know how to create a machine certificate and don’t have auto-enrollment enabled, I’ll leave that as an exercise for the reader to explore. Assuming you have a valid server authentication certificate, we can continue.

SQL best practices urge that the SQL database engine, and agent service, run under a non-privileged service accounts. Should your SQL server get compromised, it can help limit the damage to the underlying operating system. But if you pick a machine certificate within SQL Configuration manager to use, SQL will fail to start.

The failure message, on Windows Server 2008 R2 with SQL Server 2008 R2 is: Event ID 26014, source MSSQLSERVER. Content of the error is:

Unable to load user-specified certificate [Cert Hash(sha1) “A3B….913C”]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.

Hmm…why is that? Well the answer is simple, and the fix is simple as well. The non-privileged service account is attempting to read the private key for the selected certificate and is unable to do so. The solution is to enable the SQL database engine service account read access to the private machine certificate key.

How do you do that? Glad you asked!

1. Open a blank MMC and add the Certificates snap-in. Chose the Computer account.

2. Open the Personal Certificates store and you need to see one certificate with the FQDN of your SQL server. If not, then no SSL for you!

3. Right click on the certificate and select All Tasks, Manage Private Keys.

4. Click Add and locate the SQL database engine service account. Change the permissions to Read only.

5. Click OK.

6. Open the SQL Server Configuration Manager and expand SQL Server Network Configuration. Open the properties of Protocols for MSSQLSERVER.

7. Change Force Encryption to Yes. Click on the Certificate tab and from the drop down select the certificate that is listed. It should be the same one you changed the permissions on from the previous step.

8. Close all windows and restart the SQL service. With any luck the SQL services will start.

9. To verify SQL is using the PKI certificate, check the Windows Application log for event ID 26013. It should read something like:

The certificate [Cert Hash(sha1) “A3B2…7913C”] was successfully loaded for encryption.

And there you go! To recap, we assigned an existing machine certificate to the SQL service, changed the permissions on the private key, and restarted the SQL service. Pretty easy once you know the permissions trick.

If you are really paranoid, whip out Network Monitor 3.4 (not Wireshark) and capture a trace during a connection attempt to the SQL server. Under the protocol column you should see a TLS session between the SQL server and your application server.

I noticed that it appears the SQL client that comes with SQL Server 2008 R2 does NOT attempt to negotiate a TLS 1.2 session, like IIS 7.5 and IE8 on Windows 7 does as mentioned in my prior blog.

SQL 2008 R2: Generate trusted TDE certificate

As previously mentioned in my blog about SQL TDE (Transparent Data Encryption), the example script I gave just used a SQL self-signed certificate to encrypt the database. While this is fine for a demo, you should only used trusted certificates in a production environment.

Getting a trusted certificated inserted into SQL 2008 R2 is easier than it sounds and took quite a bit of digging. BUT, there is a way and it’s not too terrible. Plus, this method can be used with commercial certificate authorities or an internal CA of your choice. It does not rely on a Microsoft CA, but works perfectly fine with one.

1. Download OpenSSL 1.x and install it. Do not use v0.9.x releases as they won’t work.

2. Open a command prompt and type: openssl.exe genrsa 2048 > private.key

3. Type: openssl.exe req -new -key private.key > certificate.csr

4. You will be prompted with a series of questions. Input data as you see fit, but pay attention to the “Common Name”. This will be the subject of the certificate.

5. Open the certificate.csr file and submit it to your favorite certificate authority. It could be a commercial or internal CA. Save the resulting certificate as a DER (not BASE64) file, let’s say certificate.cer.

6. Type: openssl rsa -in private.key –outform PVKpvk-strong -out private.pvk
-You will be prompted to type a password to protect the private key. Remember the password.

7. Open SQL Management studio and create a new query. Cut and paste the following query, adjusting the paths, filenames and password from step #6 you used. You can change “My_New_Certificate” to any name you wish. Probably best to use the common name you input during the certificate request.

FROM FILE = ‘D:certificate.cer
WITH PRIVATE KEY (FILE = ‘D:private.pvk‘,

8. Press the Execute button and you should get “command(s) completed successfully.” If not, triple check your paths, filenames, and password. The error messages are not helpful if you get it wrong.

9. To verify the certificate was actually installed and to view all the other certificates, in SQL Server Studio execute this query:

use master
select * from sys.certificates

And there you have it! You can now refer to back to my TDE blog post and change the sample script to use this new trusted certificate instead of the self-signed “RMSServerCert“. You should backup the two certificates you imported into SQL and delete all copies on the local hard disk. If you ever need to restore your encrypted database you MUST have these two certificate files…no data!

Happy encrypting!

SQL 2008 R2: Transparent Data Encryption (TDE) Example

One of the new features in SQL Server 2008/R2 is Transparent Database Encryption, or TDE. TDE lets you encrypt any database, without having to change your application. This means you can fully encrypt databases and log files for SharePoint, RMS, or anything else you wish. For the ultimate in security you can use Microsoft Exensible Key Management (EKM) to use a hardware security module (HSM) to store the private keys off-host.

The Thales nShield Connect is an excellent example of a FIPS 140-2 Level 3 and Common Criteria EAL4+ certified HSM that is compatible with SQL 2008 EKM and TDE. (Try saying that ten times fast!) Their SQL 2008 EKM brochure is here.

But for mere mortals that want to encrypt a database or two and don’t want to spend $50,000 or more for a HSM, the script below shows you how to encrypt the three Active Directory Rights Mangement Services (AD RMS) databases. Just change the database names, and you are good to go. There’s nothing unique here about RMS; just an example I worked on today. The certificate used in this example is only a self-signed, so for better security I’d recommend you use a trusted certificate. I’ll save how to do that for a future post here!

Just run the script in SQL Studio, and you should be good to go. Of course database encryption adds additional overhead, and you can kiss database compression good bye as well. So be careful what you encrypt and monitor database performance.

/* Configures SQL 2008/R2 transparent database encryption.
Version 1.1, 7 July 2010 */

/* Configures master database encryption key and certificate */

USE master;

/* Encrypts RMS Configuration Database */

USE DRMS_Config_RMS_Root_contoso_net_443
ALTER DATABASE DRMS_Config_RMS_Root_contoso_net_443

/* Encrypts RMS Directory Services Database */

USE DRMS_DirectoryServices_RMS_Root_contoso_net_443
ALTER DATABASE DRMS_DirectoryServices_RMS_Root_contoso_net_443

/* Encrypts RMS Logging Database */

USE DRMS_Logging_RMS_Root_contoso_net_443
ALTER DATABASE DRMS_Logging_RMS_Root_contoso_net_443

SQL 2008 R2: Temp Database Configuration (Part 6)

One of the last things I do during a SQL installation is configure the temp databases. Temp databases are very important to some applications, as they are used as a scratch or buffer space. Other applications may not use them hardly at all, so it really depends on your environment.

One rule of thumb I use for VMware environments is one TempDB for every vCPU presented to your SQL server. Since many virtualized SQL environments will have multiple processors, you want multiple TempDB files for SQL to use.

Using SQL studio, you can run the script below to automatically add a second TempDB file, and also expand the default TempDB. Of course, adjust the size and growth parameters to fit your situation.

If you want 8GB of TempDB space and have two vCPUs, then change the existing TempDB to 4GB and create a second that is also 4GB. You want the TempDBs all of equal size since SQL weights their usage based on their size.

USE master;
NAME = tempdev2,
FILENAME = ‘T:Microsoft SQL ServerMSSQLDatatempdb2.mdf‘,
SIZE = 2048MB,

( NAME = tempdev,
SIZE = 2048MB,

NAME = templog,
SIZE = 512MB,

SQL 2008 R2: Windows Firewall (Part 5)

One of the best things Microsoft did with Windows Server 2008 and later is the built-in firewall. Unlike previous OS releases where the firewall was pretty much a joke, Microsoft started from scratch and came up with a very robust two-way firewall. SQL is one of the prime targets for hackers as databases can contain a plethora of juicy data like credit card numbers, social security numbers, and other personal data.

As part of my standard SQL 2008 R2 installation I run a script which only allows inbound SQL requests from specific remote IP addresses. Requests from any other machine in the world will be dropped. Depending on what SQL services and features you are using, it is likely the script will need some tweaking. But the script below opens the basic SQL port (1433) and SQL browser port (1434). Reporting services, analysis services, etc. will need unique rules to allow them to function. Be sure to change the path to point to where your SQL binaries are installed.

@echo off
:: Configures Windows Server 2008/R2 firewall for SQL 2008 R2.
:: Version 1.1, 5 July, 2010
:: Requires one argument, the IP address of the remote 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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnsqlservr.exe” description=”Allows inbound Microsoft SQL connections.” remoteip=%1

netsh advfirewall firewall add rule name=”SQL Server Browser (TCP-in)” dir=in action=allow protocol=TCP Profile=domain localport=1434 program=”D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnBinnsqlservr.exe” description=”Allows inbound Microsoft SQL browser connections.” remoteip=%1

Exit /B

Echo Please specify IP address.

PXE Booting a Wyse zero client, Part 2

Yes, time for part 2 of the Wyse PXE booting series. Part 1 described the general concept and why you might want to PXE boot your Wyse zero client instead of having embedded flash for the ThinOS. The remainder of this post will cover the technical details on how to configure your DHCP and TFTP server to enable the Wyse device to PXE boot. These instructions assume you are using Windows Server 2008 R2. It will work with previous operating systems, but some of the steps may be different.

My environment is configured as follows:
DHCP/TFTP server:
Wyse PXE boot strap file: VL10_PXE (get from your Wyse rep)
IIS Configuration folder for Wyse file: inetpubwwwrootConfig-A

1. Install the DHCP server role and configure a scope during the install process. Be sure to use a valid IP range and subnet mask.

2. In the DHCP console, right click on IPv4 and click on Set Predefined Options. You need to configure two options, 161 and 162. I used the following parameters:

Name: Wyse File Server
Data type: String
Code: 161
Description: Wyse File Server

Name: Wyse File Path
Data type: String
Code: 162
Description: Wyse File Path

3. Right click on Server Options and select Configure Options. Now the values you input will depend on your environment. But, you must configure 66, 67, 161 and 162 for it to be successful. Examples are shown below:

67: VL10_PXE
161: http: //
162: /Config-A

4. Install IIS with minimal features: Static content, HTTP logging, Request filtering, IIS Management Console.

5. Add a MIME type to IIS for the .ini extension and use a MIME type of text/plain.

6. Create a directory under your wwwroot directory called Config-A. Make a sub-folder called Wyse, and under that a folder called wnos (i.e. wwwrootConfig-Awysewnos).

7. Copy your wnos.ini file to the wnos directory. This ini file is one you create per the Wyse configuration guides and is unique to your environment. If everything is working you can open IE and browse to http: //localhost/Config-A/wyse/wnos/wnos.ini and you should get a download prompt.

8. Install Windows Deployment Services (WDS) but only select the Transport Server feature.

9. Create a directory D:WDSWyse and copy your VL10_PXE and VL10_wnos files to it. You can only obtain these files from your Wyse rep.

10. Navigate to HKLMSystemCurrentControlSetservicesWDDServerProvidersWDSTFTP. Open the ReadFilter key and add * to the bottom of the list. Change the RootFolder path to D:WDSWyse.

11. Start or restart the WDS service. Now if you turn on your Wyse zero client, it should pick up a DHCP address, download ThinOS, then get your config file. If something doesn’t work, I’ve found Wireshark to be extremely helpful.

SQL 2008 R2: Auditing and moving system databases (Part 3)

Welcome to Part 3 of my SQL 2008 R2 installation and lockdown series. The script below must be run in SQL Studio and is step #2 in my series introduction. It turns on SQL auditing and pipes it to the Windows security event log, renames the SA account, and then points the SQL system databases and log files to a custom location. Note this doesn’t actually MOVE the databases and log files, so that’s where my PowerShell script in the final step comes into play.

/* Execute within SQL Studio */
/* Lockdown queries for SQL Server 2008/R2 Version 1.4, 3 July 2010 */
/* Configure Auditing */

/* Create a SQL Server Audit Object that writes the audit results to the Windows Security Log every one second. If the write fails, the instance continues running without stopping. */


/* Create a Server Audit Specification object for the server audit. This object include three audit action groups related to server principal changes. */


/* By default, both the audit and audit specification are created in the disabled state. We need to enable them before using them to record actions. */


/* Rename SA Account */

ALTER LOGIN sa WITH NAME = [Hollywood];

/* Move system databases */

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = ‘K:Microsoft SQL ServerMSSQLDataModel.mdf‘)

ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = ‘L:Microsoft SQL ServerMSSQLDataLogsModellog.ldf‘)

ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata, FILENAME = ‘K:Microsoft SQL ServerMSSQLDatamsdbdata.mdf‘)

ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog, FILENAME = ‘L:Microsoft SQL ServerMSSQLDataLogsmsdblog.ldf‘)