SQL Agent job fails with xXx subsystem failed to load

We had a SQLAgent job that runs cmdexec fail. Looking at the job history, the following error was present "The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.". Looking in the SQLAgent log the below errors were reported at startup [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found) After a little research we found that the subsystems (a collection of DLLs) loaded by SQLAgent are stored in a table named "dbo.syssubsystems" in msdb. The entries in dbo.syssubsystems pointed to a location that did not exist. A search for one of the DLLs quickly revealed the actual location of the "missing" DLLs, in our case same drive, but a different folder. Once we had the true location, we updated dbo.syssubsystems.subsystem_dll to reflect to correct location (see script below). After restarting SQLAgent the previously logged errors were not present & the job was able to complete successfully. /* Allow updates to system tables */ sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE /* Update to correct path */ UPDATE msdb.dbo.syssubsystems SET subsystem_dll = REPLACE(subsystem_dll,'C:\Program Files\Microsoft SQL Server\MSSQL.1','C:\Program Files\Microsoft SQL Server\MSSQL.2') FROM msdb.dbo.syssubsystems WHERE subsystem_dll LIKE 'C:\Program Files\Microsoft SQL Server\MSSQL.1%' /* Dis-allow updates to system tables */ sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE /* Verify update */ SELECT * FROM msdb.dbo.syssubsystems

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.

Connecting to a Remote SSIS Server with Access Is Denied Error

If you are getting the dreaded “Connect to SSIS Service on machine failed: Access is denied” error when you connect as follows: For Windows 2003 Server (SP1)

• If the user running under non-admin account it needs to be added to Distributed COM Users group
• Run %windir%\system32\Com\comexp.msc to launch Component Services
• Expand Component Services\Computers\My Computer\DCOM Config
• Right click on MsDtsServer node and choose properties
• In MsDtsServer Properties dialog go to Security page
• Configure your settings as described bellow
• Restart SSIS Service

In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog. “Launch Permissions” dialog allows you to configure SSIS server access per user/group. In the bottom of the dialog you can select:

• Local & Remote Launch permissions if you allow to a user/group to start service locally and remotely (they will need both).

• Local & Remote Activation permissions if you allow to a user/group to connect to SSIS server locally and remotely (they will need both).

• Read permissions on Change Configuration

• And also, add those users to “Distributed COM Users” group.

If the users continue to experience errors, there is final set of permissions to check on the DCOM application, controlled by the Access Limits on the "COM Security" tab.

Access Permissions

Launch Permissions

Microsoft Best Practices for SQL

Recently we had a Microsoft SQL resource onsite to troubleshoot issues we were having with adding a node to a cluster. While he was onsite we did an assesment of our cluster configurations & came up with several best practices for clustered nodes running SQL.

MSCS Specific

Heartbeat NIC: HEARTBEAT should be 1st in Network Priority (Cluster Admin > Properties > Network Priority). Heartbeat NIC: Private Heartbeat address should not be registered in DNS (NIC Properties > Advanced TCP/IP Settings > DNS > "Register this connections' addresses in DNS" checkbox). Heartbeat NIC: NetBIOS over TCP/IP should be disabled (NIC Properties > Advanced TCP/IP Settings > WINS > NetBIOS Setting > "Disable NetBIOS over TCP/IP" checkbox).

All SQL

File/Print Sharing: Change maximize data throughput to Network Application (NIC Properties > File & Printer Sharing for Microsoft > Properties > "Maximize data throughput for network applications" radio button). Processor Scheduling: Adjust for best performance to Programs (My Computer > Properties > Advanced > Performance > Settings > Advanced > Processor Scheduling > "Programs" radio button). Memory Usage: Adjust for best performance to Programs (My Computer > Properties > Advanced > Performance > Settings > Advanced > Memory Usage > "Programs" radio button).