Configuring Applications to connect to SQL Servers on Non Standard SQL Ports

Following MS Best Practice, we always try to change the default Port used by our SQL Servers. Middleware and front end servers which connect to the SQL Servers can normally use the SQL Browser (UDP 1434) to find the port used by an instance. If you happen to have disabled the SQL Server Browser service or maybe have a firewall in between the SQL server and client, then the port that a SQL Instance resides on can be defined along with a suitable alias.

To do this use the 'SQL Server Client Network Utility' by going to Start > Run > cliconfg.exe and choose the 'Alias' tab. To add a new association of server alias and port click 'Add' and enter the relevant details. I shall never have to Google this again now :o)

Get SQL Database File Size Information

I had a query from a colleague this morning regarding the size of a database on one of our SQL Server 2005 clusters. He wanted to know whether it had run out of space, as the database is not configured to grow automatically.

I queried the 'sys.database_files' view on the database in question in order to determine it's current size, only to find what I thought to be a mismatch between what values were being returned in the size, max_size and growth fields and those returned from the SSMS GUI. Upon reading the MSDN article for sys.database_files, it appears that the values that are returned don't represent the actual file size but the file size in 8KB pages. Therefore I've concocted a SELECT statement which can be executed against a database and will return what I think is the more pertinent values in their expected form. 

SELECT name as [SQL Logical Name], physical_name as [File Path],
size * 8/1024 as [Current Size (MB)],
max_size * 8/1024 as [Maximum Size(MB)],
growth * 8/1024 as [Growth Increment (MB)]
FROM sys.database_files

As someone who dabbles with SQL (usually when something's went wrong), I find the above T-SQL very useful for assessing database sizes.

Getting to grips with VMware's PowerCLI

Yesterday I downloaded VMware's PowerCLI installer and started playing with some of the supplied cmdlets. The motivation behind it was to find a simple way of listing all of the current snapshots on our army of VMs. With the best will in the world, we find that the odd snapshot is not removed once we're finished a change and therefore can be in place for weeks before being re-discovered and removed.

The first step in using the PowerCLI cmdlets is to connect to the vCentre or Virtual Centre server and that's done using Connect-VIServer . If the optional arguments are supplied you'll be promped with a login dialog.

Once connected, you then have access to run the cmdlet's that are allowed as per your level of permissions in Virtual Centre. In my case I looked at the Get-Snapshot cmdlet, which accepts a VM name as an argument and returns details of any snapshots associated with the supplied VM name.

Using some basic PowerShell I was able to get a tabular list of all VMs with snapshots, showing the name of the VM, the date that the snapshot was created, the snapshot's name and description. Very handy for quickly finding those forgotten snapshots.

Get-VM * | Get-Snapshot | Select VM, Created, Name, Description

Using PowerShell with VMware looks to make those painful admin tasks easier but there was one 'gotcha' that i noticed. When finished using PowerShell, use the Disconnect-VIServer or your session will stay open on the VC Server.

More MDM SP1 Info.. Proxy and Work Exceptions

As part of the Mobile Device Manager work that i'm involved with I've been looking at Mobile Device GPOs. I've started reading about the Network Connection settings. There's an interesting article over at the enterprisemobile with regards to the Proxy/Work network configuration. I've found it very helpful and ended up following it for our implementation.

Virgin's Advanced Network Error Search

In a semi hungover state, I tried browsing over to Facebook but accidentally entered www.face. Instead of receiving the traditional address not found error page, I got the following;

Contrary to the rumours this is not a modification to your machine or browser but instead the way in which Virgin are handling DNS requests for addresses that are not found (NXDOMAIN). I've not decided whether it's good or bad at the moment but would rather it be an Opt-In service rather than Opt-Out. El-Reg covered this back in August and there's a lengthly discussion on cable forum regarding this - link

MDM SP1 SCW Manifests

Before deploying servers into the production environment at work, we run the Security Configuration Wizard to harden the Operating System. Hardening involves such things as disabling unnecessary Services, adjusting authentication methods and setting Windows Firewall Exception in an effort to reduce the overall attach surface of the Operating System. While Server 2003 SP1 includes a number of default templates, additional templates are available for download on the Internet.

As part of the MDM deployment that I'm involved with, I've been looking to harden the servers before they go live. The MDM Dev Team at Microsoft were kind enough to create manifests and bundle them in the MDM SP1 Server Resource Kit. I've yet to try them but I expect them to function as the others do. The MDM SP1 Resource Kit can be downloaded here.

64bit VM on a 32bit OS with VMware Workstation

I'm in the process of deploying MS SCMDM SP1 and have decided to first install the various components (MDM Gateway, Enrolment and Management Servers) in a isolated local VMware Environment before doing the live install. I stumbled upon a problem with running the 64bit Windows 2003 OS on my 32bit Windows XP laptop OS. Basically VMware Workstation was complaining that the CPU on the laptop was not compatible with the 64bit OS that i was trying to run on the VM.

There was two steps to resolve this;

• Enable Intel Virtualisation Technology in my laptop's BIOS
• Power off the laptop and then power on (a hot restart was not enough for this BIOS option to be recognised.)

There's also a handy CPU tester from VMware which will check your Host CPU for 32/64bit compatibility. It can be found here

Sysprep Answer File Location in VMware ESX 3.5/VC 2.5

I was looking for the file location of the Sysprep answer files in Virtual Centre 2.5 used with ESX 3.5 when creating a new Customisation Specification for a X64 VM Template but couldn't find it. I realise Customisation Specifications can be accessed in VC via Edit > "Customisation Specifications..." but really wanted to know where and how they were stored.

Not being able to find anything on net, I asked a colleague. He pointed out that they reside in a table in the VC SQL DB called 'VPX_CUSTOMIZATION_SPEC'.

Relocating SQL Server System Database Files

I have built a fair few SQL Servers over the last few years and always look to relocate the System DBs from their default location in the Install folder. The following MS KB article explains how to move the TempDB, MSDB, Model and Master on SQL Server 7, 2000 & 2005. Article is here

SCOM SNMP Monitor Issues

While creating a test SNMP Probe which produced alerts, we encountered issues around how to match the returned SNMP value for a Cisco Device's CPU OID.

To return the SNMP value you can use;

"/DataItem/SnmpVarBinds/SnmpVarBind[1]/Value" without the quotes.

The URL for the article is here

Also we found issues when using expression matching on numeric values returned via SNMP. this is due to the wizard creating the rules to expect String datatypes rather than Integer. The article can be found here.

I'm in the process of trying to configure Kerberos Constrained Delegation in ISA 2006 for a Web Server which will use Client Certificate Authentication.

I found this article over at Technet which i thought was very useful.

WSUS 3.0 SP1 Clients Being Overwritten

We've recently deployed WSUS 3.0 SP1 at work and found an interesting issue regarding our Virtual Machines. It appears that the VM's were overwriting eachother in the WSUS console.

We did a little reading and found this by Stephen Farrar. It appears that the Sysprep process that we used to commission our VMs on ESX does not strip out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\SusClientId] entry in the Registry and therefore all of our VMs have the same ID.

I've written a VBScript which first of all deletes the key, stops and starts the Automatic Updates service and then forces the client to re-register with the WSUS Server. This seems to resolve the issue.

Option Explicit

Dim strRegKey, WshShell, strServiceName
Set WshShell = WScript.CreateObject("WScript.Shell")

'Delete Automatic Update Client ID

strRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\" _
& "CurrentVersion\WindowsUpdate\SusClientId"
WshShell.RegDelete strRegKey

'Restart Automatic Update Windows Service

strServiceName = "wuauserv"
WshShell.Run "Net Stop " & strServiceName, 0, True
WshShell.Run "Net Start " & strServiceName, 0, True

'Force the Client to report to the WSUS Server
WshShell.Run "wuauclt.exe /detectnow /resetauthorization"

Custom HTTP Protocol Issue in ISA Server 2006

While trying to publish a CRL (Certificate Revocation List) Location through two sets of MS ISA 2006 Arrays, we had an issue with the HTTP Protocol in ISA proxying the request and therefore changing the Source IP of the request to the ISA rather than the original requesting server's IP.

To remedy this, we created a new HTTP Protocol in ISA and removed the Web Proxy filter from the new protocol. The new protocol was then used in the access rules.

This seemed to go some way to resolving the issue although we still had problems, as it appeared that the out of box HTTP Protocol was still being used and consequently the rule in the next set of ISAs in the route was not matching.

We stumbled across the link below which described our problem which involves creating a deny rule after the problematic access rule. This worked even though the guys commenting on the blog below state that they had problems with it on ISA 2006.

Check the MS ISA Team blog here

SCOM 2007 SCW Manifest

I'm in the process of implementing the Beta of SCOM 2007 R2 at work and had some SCW work to do on the servers that are going to run the SCOM Agents. I found this useful link which has the XML manifest for SCOM 2007, allowing easier configuration of the various SCOM 2007 components using the Security Configuration Wizard.

http://www.microsoft.com/downloads/details.aspx?FamilyID=244512ab-7419-4c93-8046-9c8e47e0f780&DisplayLang=en

SQL Server 2005 SPNs

When running SQL Server 2005 services under normal domain user accounts, there's some additional configuration that's needed to have SQL auto register it's SPN which is required for Kerberos authentication.

If SQL is unable to register an SPN upon startup you'll see "The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15." in the SQL Error logs. If the SPN can't be registered then the authentication will fall back to NTLM instead of Kerberos.


Step 3 in the following article describes the steps that i performed on the SQL Server service account to enable the self registration of the SPNs. - http://support.microsoft.com/kb/319723


The following TSQL can be ran from SSMS to determine the current authentication method (Kerberos or NTLM).

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

Also more info can be found here - http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx