After server patching you might find that the Integration Services service will fail to start; not just SQL 2005 SP1 but others too. This is described in a Microsoft knowledge base article. The problem is that applying those instructions is a manual and error prone process.
I whipped up a little PowerShell to do it in a more reliable way. This is only for if you experience this issue however.
# https://support.microsoft.com/en-us/kb/918644
$magicWords = '<generatePublisherEvidence enabled="false"/>'
$dllName = "C:\Windows\Microsoft.NET\Framework\v2.0.50727\MsCorWks.dll"
$dll = [System.Diagnostics.FileVersionInfo]::GetVersionInfo($dllName) # Works on PowerShell v1
# Confirming we have the minimum version of .NET installed
if (!($dll.FileMajorPart -ge 2 -and $dll.FileMinorPart -ge 0 -and $dll.FileBuildPart -ge 50727 -and $dll.FilePrivatePart -ge 876)) {
Write-Error "$dllName version must be 2.0.50727.876 or higher."
} else {
# Find the location of Integration Services, as far as I know only 2005 needs to be patched, which is MsDtsServer
$service = Get-WmiObject Win32_Service | Where { $_.Name -eq "MsDtsServer" }
$servicePath = (Split-Path $service.PathName.Replace("`"", ""))
Set-Location $servicePath
$files = Get-ChildItem . *.config
foreach ($file in $files) {
$okay = $false
# We should only update the files indicated in the KB article
switch ($file.Name) {
"msdtssrvr.exe.config" {
$okay = $true
}
"DTExec.exe.config" {
$okay = $true
}
"Dtshost.exe.config" {
$okay = $true
}
"DtsDebugHost.exe.config" {
$okay = $true
}
"DTUtil.exe.config" {
$okay = $true
}
"DTSWizard.exe.config" {
$okay = $true
}
default {
$okay = $false
}
}
if (!$okay) {
Write-Host "$($file.Name) does not need to be modified"
} else {
$content = Get-Content $file.Name | Out-String
if ($content -like "*$magicWords*") {
Write-Host "$($file.Name) is already up to date"
} else {
Write-Host "$($file.Name) will be updated"
# We might need to add some parent tags if they don't exist already. This could be done better with a proper XML parser.
if ($content -like "*<runtime>*") {
$content = $content.Replace("</runtime>", " $magicWords
</runtime>")
} else {
$content = $content.Replace("</configuration>", " <runtime>
$magicWords
</runtime>
</configuration>
")
}
# Back up to .bak files
$backupNumber = ""
While (Test-Path "$($file.Name).bak$backupNumber") {
$backupNumber = [int] $backupNumber + 1
}
Copy-Item $file.Name "$($file.Name).bak$backupNumber"
Set-Content $file.Name $content
}
}
}
}
This could do with a rollback script.