The strangest SQL Server Agent problem you will ever meet!

  • Article

I came across this issue recently and I found it to be logged nowhere. It was a nightmare to troubleshoot and thus I would like its resolution to stay publicly available - because frankly there is 0% chance anyone will be able to solve it on his own. I was lucky to be helped by a very talented SQL Server escalation engineer, otherwise I might be still wondering how to solve it.

Without further ado:

Symptoms

A SQL Server Agent job fails to get executed automatically and gives an exception. The stack exception looks like this:

System.IO.FileNotFoundException: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQLAGENT90.EXE   at System.Diagnostics.FileVersionInfo.GetVersionInfo(String fileName)   at System.Configuration.ClientConfigPaths.SetNamesAndVersion(String applicationFilename, Assembly exeAssembly, Boolean isHttp)   at System.Configuration.ClientConfigPaths..ctor(String exePath, Boolean includeUserConfig)   at System.Configuration.ClientConfigPaths.GetPaths(String exePath, Boolean includeUserConfig)   at System.Configuration.ClientConfigurationHost.get_ConfigPaths()   at System.Configuration.ClientConfigurationHost.RequireCompleteInit(IInternalConfigRecord record)   at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRunt

Root Cause

Recent changes on how impersonation is done on .Net applications

Resolution

Create this registry key at the SQL Server:

1. Click Start , click Run , type regedit, and then click OK

2. Locate and then click one of the following registry subkeys, depending on the version of Windows that is running on your computer

 If your computer is running a 32-bit version of Windows, click the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet
Explorer\MAIN\FeatureControl\FEATURE_IGNORE_ZONES_INITIALIZATION_FAILURE_KB945701

Programs that are 32-bit and run on a 64-bit version of Windows operate in WOW64 mode.

These programs access registry keys and values that are stored in a different registry subkey.

If you are running 32-bit programs on a 64-bit version of Windows, click the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Internet
Explorer\MAIN\FeatureControl\FEATURE_IGNORE_ZONES_INITIALIZATION_FAILURE_KB945701

Note: If the FEATURE_IGNORE_ZONES_INITIALIZATION_FAILURE_KB945701 subkey does not exist, you must manually create it

3.  Right-click FEATURE_IGNORE_ZONES_INITIALIZATION_FAILURE_KB945701, point to New , and then click DWORD Value

4.  Type SQLAGENT90.exe to name the new registry entry, and then press ENTER

5.  Right-click SQLAGENT90.exe , and then click Modify 

6.  In the Value data box, type 1 and then click OK

7.  Exit Registry Editor

8. Restart the computer