SQL SERVER – Unable to Start SQL Server Agent. Error – Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed

If someone tells me any situation, the first question which I always ask is “What is the error message?”. Now to get the right error message, it is important to look at the right log file. In this blog, we would learn about SQL Server Agent startup issue.

If SQL Server Agent service is not getting started, here are the logs I always look at:

  1. out file. This is generally located in the same folder where we have SQL Server ERRORLOG file. You can refer to below blog to find the location. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
  2. If there is nothing interesting over there or the file itself is missing, then I would look at Event logs (both application and system). To open it, we can use the shortcut.

Start > Run > EventVwr.msc

For this client, here are the messages in various logs.

  1. In SQLAgent.out, we found below messages.

2018-01-28 12:01:34 – ! [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2018-01-28 12:01:34 – ! [382] Logon to server ‘(local)’ failed (ConnConnectAndExecuteSQL)
2018-01-28 12:01:34 – ! [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2018-01-28 12:01:34 – ! [382] Logon to server ‘(local)’ failed (SendCryptoKeyToServer)
2018-01-28 12:01:34 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2018-01-28 12:01:34 – ! [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2018-01-28 12:01:34 – ! [382] Logon to server ‘(local)’ failed (DisableAgentXPs)
2018-01-28 12:01:34 – ? [098] SQLServerAgent terminated (normally)

From above, we could see some ODBC errors but not exact cause of the issue. So, as a next step, I opened event viewer and found below message at the same time when SQL Agent startup was attempted.

Log Name: Application
Source: SQLNCLI11.1
Event ID: 1
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: DBSRV1.SQLAuthority.com
Description: SQL Server Native Client 11.0: Unable to load sqlnclir11.rll due to either missing file or version mismatch. The application cannot continue.

WORKAROUND/SOLUTION

After seeing above mentioned message in application event log, I was convinced that there is some issue with SQL Server Native Client component. To fix this, we went to add/remove program and performed a repair of “SQL Server Native Client” as shown below.

SQL SERVER - Unable to Start SQL Server Agent. Error - Driver's SQLAllocHandle on SQL_HANDLE_ENV failed sqlagt-sqlncli-01-800x571

Once we repaired the component, SQL Server Agent was able to start.

Have you encountered such issues which are solved by messages in the event log?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Unable to Start Jobs – Error: Enumerate Target Servers Failed for Job
Next Post
SQL SERVER – Installation Wizard Hang Forever – Please Wait on Feature Selection Page

Related Posts

6 Comments. Leave new

  • This is exactly what we were looking for. Had spent lot of time around SQL. FInally got it. Thanks Pinal.

    Reply
  • I tried to repair SQL Server Native Client according to what mentioned above but failed and it is said that the installer has insufficient privileges to modify the file. Please help. Thanks!

    Reply
  • Hi Pinal,

    I am getting this error when i run the SSIS package from Integration Services Catalog (Using SSMS 17).

    Error: There was an error trying to establish an Open Database Connectivity(ODBC) connection with the database server. SqlState = IM004 Message = [Microsoft][ODBC Drive Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed.

    Can you please guide me what would be the cause for this issue?

    Thanks,
    AJ

    Reply
  • This was really helpful! Thanks

    Reply
  • Your posts always help me! I have having issues connecting to SQL Server from Tableau after installing 2019 with error “[Microsoft][ODBC Driver Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed”

    There were several posts giving solution but your post tells how to actually cofirm the driver is the problem. finding facts gives confidence in solution rather than guess work.

    Thank you I was able to confirm issue from event log and fix it just by simple repair of ODBC 17 SQL Server driver.

    Reply

Leave a Reply