Sql-server – Why is script with “ xp_cmdshell ” in comment failing with a transport level error

sql serversql server 2014xp-cmdshell

When executing the following batch that is part of a large script:

...
GO
-----------------------------------------------------------------------------------------------
-- Removed the xp_cmdshell for deleting the backup file 
-----------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PR_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PR_MyProc]
GO
...

We get the following error on a remote SQL Server:

Msg 121, Level 20, State 0, Line 0 A transport-level error has
occurred when receiving results from the server. (provider: TCP
Provider, error: 0 – The semaphore timeout period has expired.)

When we remove "xp_cmdshell" from the comment, the batch succeeds. This error only occurs on some of our SQL servers. We are running the script remotely. The version of SQL Server that fails is:

Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (X64) (Build 9200: ) (Hypervisor)

On another server the same script succeeds. That server has this version:

Microsoft SQL Server 2014 – 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (X64) (Build 7601: Service Pack 1) (Hypervisor)

Both servers have xp_cmdshell enabled. The failing server is running Windows 2012 R2 and the server that succeeds is running Windows Server 2008 R2 SP1. If we run the script on the server locally it succeeds. If we run the script remotely from another virtual server, it fails. We also found that the script only fails if the comment has "(space) xp_cmdshell (space)".

Can anyone tell me why this script fails? And why does it fail on some servers and not others?

There is no antivirus running on the server. SQL Server Management Studio is connected, changing the comment to remove xp_cmdshell always succeeds and the original script always fails.

Best Answer

There is a connect item discussing (well discussing...) exactly this issue, so it appears to be a bug.

Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system

It appears that the string causes problems, not only in comments but also anywhere it's used as an exact string so even 

SELECT 'xp_cmdshell'

results in the same error according to the bug report.

So it appears that the exact string xp_cmdshell is indeed the problem, unfortunately the connect item is closed as "not reproducible".
That being said, I can't reproduce it (on a local connection) on my SQL 2014 (12.0.2000.8, Windows NT 6.3 (Build 9600: )) test system either, so the operating system is probably relevant. Executing the query from a local management studio vs over the network might also be relevant as noted in the connect item.

So you could either work around it by changing the exact text (as you did) or try to reopen the connect item or file a bug report through Microsoft Support.

Related Question