Checking SQL & SSIS Versions with Powershell

I'm currently in the process of applying SP3 + CU5 to all our SQL2005 Instances. I wanted to be able to quickly check whether the install was successful (we're experimenting with using SCCM to push to our standalone Instances).

Checking SQL was easy enough, just using @@version in a multi-query was enough. However SSIS is a little tougher, you can't check it via T-SQL and I don't have the patience to login to every machine and check it that way.

The solution I came up with uses SERVERPROPERTY('ProductVersion') to check the SQL build & [system.diagnostics.fileversioninfo]::GetVersionInfo() to check the Product version on the SSIS executable.

It returns a table with the Instance, Version, SQL & SSIS Builds and Notes (for example whether SSIS is installed & if installed do the SQL & SSIS Builds match). The code is here.

More fun with the February 2009 SQL Security Patch

On one of our clustered instances, we had to leave the services running for the February 2009 SQL Security Patch install to initiate successfully. We did not investigate the root cause since it was in the middle of a very busy period of work & we unfortunately didn't have the time. After applying the patch, we started getting complaints from users that their application was unable to login to the DB. Looking at the SQL error logs we saw the following errors...

Login failed for user 'Fake_User'. The user is not associated with a trusted SQL Server connection. [CLIENT: 127.0.0.1]
Error: 18452, Severity: 14, State: 1.

The login in question is a SQL login not Windows so this made no sense to us, on parsing the SQL error logs we noticed this (the instance had been running in MIXED mode)...

Authentication mode is WINDOWS-ONLY.

We changed the instance back to MIXED (SQL Server and Windows Authentication) mode & that resolved the issue. Thought I'd share this since it made for an interesting few minutes.

July 2008/Febuary 2009 SQL Security Patch – Known Issues and Resolutions

After going through the last few SQL Patches, I thought it would be usefull to compile a list of the top errors encountered and their fixes.
Error # in Summary.txt: 29506 Description: MSP Error: 29506 SQL Server Setup failed to modify security permissions on file xxx.xxx for user xxx. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
Fix: See the following MS Article KB916766. Notes: check on the file level, look for oddly named or abnormal modified dates as a starting place. Security must be granted at the individual file level.

Error # in Summary.txt: 11032 Description: Unable to install Windows Installer MSP file
Fix: A folder named FTData is not in the expected path, a search of the patch install log for "ERROR: FTE: InstallFTERef: Fail to create FTERef file:" (no quotes) will reveal the expected path. Create the folder in this pattern “F:\MSSQL.1\MSSQL\FTData” changing the path to match your install & what shows in the patch install log, when install complete copy all files to the existing FTData folder (for example) “F:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData” and delete the path\folder you created.

Error #: N/A Description: User has lost custom permissions to SSIS after patching. i.e. “Connect to SSIS Service on machine failed: Access is denied”
Fix: Patch hjorks the custom perms we have setup for SSIS. Recreate the perms according to the following post Connecting to a Remote SSIS Server with Access Is Denied Error.

Error #: N/A Description: Patch installs & reports success, but, one or more nodes in the cluster are un-patched (i.e. still on previous build)
Fix: The problem occurs when the "Windows Installer" service is set to manual & is not running. Start the service & re-run the patch on the un-patched node(s). You should be prompted to reboot, if not, reboot manually. After reboot check @@version on the node(s) in question.