Nocount breaks BAM

I ran into another issue today that was caused by sql user options turning nocount on.   There was a failure in configure Bam Tools

This was in evidence in the configuration log.

[4:02:34 PM Info BAMTools] Updating TDDS_Destinations connection string with server sqlserver and database BAMPrimaryImport for destination Business Monitoring.

followed by this:

[4:02:34 PM Error Configuration Framework]Feature: [BAM Tools] Failed to configure with error message [<Exception Message="Error configuring BAM Tools" Source="BAMTools" HelpID="idsErrorConfiguringTools"><Exception Message="Error updating TDDS_Destinations connection string." Source="Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper" HelpID="error_UpdateTDDS"/></Exception>]

This was occurring while attempting to configure bam tools.

It turns out that user options had been configured on sql to set nocount on.   You can check this value by using sp_configure 'user options' on sql.   The default is 0.  

I had previously seen another error with BAM when trying to publish a tracking profile, which was also caused by this setting.

Failed to apply the tracking profile.
(Microsoft.BizTalk.Bam.TrackingProfileEditor.MainForm)
------------------------------
For help, click: <https://go.microsoft.com/fwlink/?LinkId=37935>
===================================
Error applying tracking profile for messaging.
(Microsoft.BizTalk.Bam.TrackingCompiler.MessagingDeployer)

Moral of the story.  Check User options and verify that nocount is not on.