SQL Server Agent 2016 does not start with Shared Memory disabled

If you need to install BizTalk Server 2016, there is a big chance you will also install SQL Server 2016. Not only does SQL Server 2016 support the AlwaysOn capabilities that BizTalk Server 2016 might need, it comes with a much-welcomed upgrade to the support life-cycle. At the moment of writing, the latest service pack of SQL Server 2016 is SP1, which offers support until Jul 13 2021.

Installing BizTalk Server properly will require you to make some changes to the SQL Server Configuration as well. Specifically, you might want/need to disable the Shared Memory Protocol.

Shared Memory Protocol

The SQL Server Shared Memory Protocol per the MSDN documentation around SQL protocols:

Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

Basically, in the case your application is not hosted on the same server as your SQL Database Engine, it makes zero sense to enable the protocol. Therefore, it is considered good practice to disable the protocol and only enable the Named Pipes and TCP/IP protocol:

2017-03-29 14_53_26-mRemoteNG - mgRemoteNG.xml - SQL PROD

The above screenshot gives an overview of how the protocol configuration at server side should look like. At Codit, we disable the protocol at server side, since it might pose somewhat of a security risk.

SQL Server Agent not running

While the above has been a good practice for many years and many different BizTalk versions and never gave us any issues, for SQL Server 2016 this meant that the SQL Server Agent (windows service) was stopping unexpectedly. Something that came to my attention while trying to configure the BizTalk SQL Jobs.

The below is a screenshot of the event log and the error logged:

2017-04-12 16_30_41-Clipboard

The error:

SQLServerAgent could not be started (reason: Unable to connect to server ‘SOMESERVER’; SQLServerAgent cannot start).

(Re)starting the SQL Agent service works fine, but after about a minute, we get the same issue and the service crashes.

After some google-Fu, I finally managed to find the culprit due to a message in the SQL Server log:

2017-03-27 13:33:18 – ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

And with some further research, it seemed the ODBC version that ships with SQL Server 2016, which is ODBC v13.0, contains a bug that will prevent the SQL Server Agent from running with Shared Memory disabled!

ODBC Drivers

To check the version of the ODBC drivers on your machine, there is actually a page on Microsoft Docs: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/check-the-odbc-sql-server-driver-version-windows

Mine said:

2017-04-12 16_47_22-Clipboard

Note: 2015.130.x = v13.0

We now need to upgrade ODBC to version v13.1. You can download ODBC v13.1 from the Microsoft website: https://www.microsoft.com/en-us/download/details.aspx?id=53339

After upgrading, the Drivers tab mentions v2015.131.x = v13.1

2017-04-13 09_09_25-Clipboard

And, as expected (and hoped), the SQL Server Agent service now start successfully and keeps running:

2017-04-13 09_10_18-Clipboard

Let me know if this helped you or if you have any questions.

Cheers!
Pieter

Advertisements

15 thoughts on “SQL Server Agent 2016 does not start with Shared Memory disabled

    • What version of SQL Server 2016 are you running? The problem apparently occurs after installing SQL Server 2016 SP1. The upgrade of the ODBC servers fixed the issue for me. What version of ODBC are you running now?

      Like

  1. I had the same issue but when i installed SP1 the version of the driver was 2017.140.1000.169
    The agent still didnt run so i used an alias and that solved the issue but now the jobs didnt fire so i had to enable shared memory again. Any ideas?
    Kal

    Like

    • That is interesting. Did SP1 update the ODBC drivers for you or were they already updated? I’m afraid I don’t have a solution for your issue. Are you sure SQL Agent keeps running? It might take a minute or so before it stops running.

      Like

      • I’m reinstalling SQL server so far I’ve done the base install and the version is 2015.130.1601.05.
        Then I’ll apply SP1 and check the version and finally applying CU7 and check the version. I think that version was updated by CU7.
        Kal

        Like

  2. SO it turns out if you install SSms 17.* it updates the Sql server driver version to 2017
    Please test it and see what you get but also change the default port and disable shared memory.
    Kal

    Liked by 1 person

    • That explains a lot. Thanks for posting this. I’m still getting the bug with version 2017.140.1000.169 that SSMS 17 installs. ODBC 13.1 also won’t install because it says my version is newer. What a pain.

      Like

      • And of course uninstalling the version that SSMS 17 installs (2017.140.1000.169) and installing 2015.131.4414.46 didn’t fix the problem either. I’m at a loss now.

        Like

      • Omega
        Did you apply any CU after SP1?
        Apply CU7 as I found a bug at least its a bug in my system with CU8 and database mail. It still doesn’t make sense why disabling shared memory stops the agent but I’ll test it again today and get back to you.
        Kal

        Like

      • Yes, I have CU8 installed. Thanks for all your troubleshooting so far, Kal. I’d also like to add I do have Named Pipes disabled as well which you said affects things too.

        Like

  3. Hey Everyone
    here is what i have tested so far:

    So here is what i have been able to find from my extensive testing in the past couple of days.
    SQL server agent doesnt run if you disable both Shared and Named Pipes protocol
    If you check in the log files i get the error:

    Date 4/12/2018 8:55:34 AM
    Log SQL Server Agent (Current – 4/12/2018 8:55:00 AM)

    Message
    [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
    Date 4/12/2018 8:55:34 AM
    Log SQL Server Agent (Current – 4/12/2018 8:55:00 AM)

    Message
    [298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]

    Solutions i have tried:

    1. Create an alias and place that in the connections section in the agent and that works given that you must first enable Agent XPs from the advanced options otherwise there is no way to enter the alias into the agent properties
    2. Enter the hostname into the registry as some other posts have suggested and that didnt work for me
    3. Enable the Named Pipes or the Share Memory protocols and those worked
    4. Update the ODBC driver to version 13.1 and this doesnt work if you have installed SSMS 17.* because that also updates the ODBC driver to version 2017.*

    The main question here is the Agent’s dependancy on Named Pipes and Shared Memory to connect locally to the SQL server engine? Can we edit something somewhere to make it connect using TCP/IP?
    I am not familiar with using Aliases hence is the reason i am not fond of going that route
    From my experience usually more applications use the Named Pipe protocol so we can disable the shared memory protocol

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s