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:
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:
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:
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
And, as expected (and hoped), the SQL Server Agent service now start successfully and keeps running:
Let me know if this helped you or if you have any questions.
Cheers!
Pieter
Pieter Vandenheede 
Thanks for the research! I had the same problem.
LikeLiked by 1 person
I have the same problem which still persists after upgrading ODBC drivers
LikeLike
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?
LikeLike
Same here.. After installing 2016-SP1 I have two instance but only one instance has failed with the error message other was running fine i have tried to change the Service accounts, Repair but nothing worked.. Your solution worked beautifully. (My Mistake: I just checked log only on event viewer).
LikeLiked by 1 person
thankx
LikeLiked by 1 person
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLiked 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.
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
hello,
have solution or not yet ,i have sems problem
LikeLike