A transport-level error has occurred in local VPN connection, But not in RDP.

Question

A transport-level error has occurred in local VPN connection, But not in RDP.

C.K.Shaiju

SSCarpal Tunnel

Points: 4228
More actions
January 28, 2016 at 3:09 am

#308098

Hello Experts,
I am trying to alter a sp in a remote server connected from a local server (through VPN) and getting the following error. But the sp can be able to alter without any issue if I have connected to the server with Remote desktop.
--------------------------------------------------------------------------------------------------------------
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.)
--------------------------------------------------------------------------------------------------------------
Would you please help or share any thoughts?
Regards,
Shaiju CK
_____________________________________________
One ounce of practice is more important than tonnes of dreams

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 1

Hello,

I think the issue is here. I have a code as below in the stored procedure. If remove RAISERROR from the code, I am able to alter the sp.

From the below, 1 and 2 are NOT working. But 3 and 4 are working.

Do I need to do anything related to RAISERROR?

--1---NOT altering--

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)

SELECT @msg=ERROR_MESSAGE()

RAISERROR (@msg, 16, 1)

END CATCH

END

--2---NOT altering--

--END TRY

--BEGIN CATCH

--DECLARE @msg VARCHAR(MAX)

--SELECT @msg=ERROR_MESSAGE()

--RAISERROR (@msg, 16, 1)

--END CATCH

--END

--3--Altering--

--END TRY

--BEGIN CATCH

--DECLARE @msg VARCHAR(MAX)

--SELECT @msg=ERROR_MESSAGE()

--END CATCH

--END

--4---Altering--

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)

SELECT @msg=ERROR_MESSAGE()

END CATCH

END

_____________________________________________
One ounce of practice is more important than tonnes of dreams

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 2

Hello All,

Do I need to post this message somewhere else to get some clue?

~Shaiju

_____________________________________________
One ounce of practice is more important than tonnes of dreams

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 3

Helloooo.... Any comments please.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

tony28 SSCertifiable Points: 7112 More actions · Answer 4

I think that you have to write whole procedure. will be better for understanding why will be catch and when ..

And I am not sure if this is problem in procedure, because error like this I have after for example restarting sql server or someone kill my session and my query is still opened in Management studio....

try to check http://stackoverflow.com/questions/551282/semaphore-timeout-period

also one topic is here ... http://www.sqlservercentral.com/Forums/Topic1037809-146-1.aspx%5B/url%5D

try to more searching

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

So you log into your desktop and then log into a local server and try to alter a stored procedure on yet another server? If so and because it all works when you RDP into the final target, I'm thinking you're simply getting nailed by the Kerberos Double Hop "feature". It call it a feature instead of a problem because it's another layer of security that I'm glad I have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 6

Thanks for the response.

tony28 (2/26/2016)
I think that you have to write whole procedure. will be better for understanding why will be catch and when ..
And I am not sure if this is problem in procedure, because error like this I have after for example restarting sql server or someone kill my session and my query is still opened in Management studio....
try to check http://stackoverflow.com/questions/551282/semaphore-timeout-period
also one topic is here ... http://www.sqlservercentral.com/Forums/Topic1037809-146-1.aspx%5B/url%5D
try to more searching

Tony,

No, it is not an issue with procedure.

The moment I execute the proc without "RAISERROR (@msg, 16, 1)", it will work without any prob.

Also, if I connect server remotely, and execute the procedure, it will alter the sp without any issue.

Let

Jeff Moden (2/26/2016)
So you log into your desktop and then log into a local server and try to alter a stored procedure on yet another server? If so and because it all works when you RDP into the final target, I'm thinking you're simply getting nailed by the Kerberos Double Hop "feature". It call it a feature instead of a problem because it's another layer of security that I'm glad I have.

Correc Jeff, that is how I am trying to alter the sp.

But Is there any way to get out from this issue?

The developers who adds "RAISERROR" in the sp can't alter the sp. Also, we can't give Remote Desktop permission to all the developers.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 7

tony28 (2/26/2016)
I think that you have to write whole procedure. will be better for understanding why will be catch and when ..
And I am not sure if this is problem in procedure, because error like this I have after for example restarting sql server or someone kill my session and my query is still opened in Management studio....
try to check http://stackoverflow.com/questions/551282/semaphore-timeout-period
also one topic is here ... http://www.sqlservercentral.com/Forums/Topic1037809-146-1.aspx%5B/url%5D
try to more searching

Tony, please find the test sp below. 1st one is NOT working, but 2nd one is working.

--[1]-------------------------------------------------------------------------

ALTER PROCEDURE pTest

AS

BEGIN TRY

SELECT 1

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

SELECT @ErrorSeverity = 16, @ErrorState = 1, @msg=ERROR_MESSAGE()

RAISERROR (@msg, @ErrorSeverity, @ErrorState)

END CATCH

---------------------------------------------------------------------------

--[2]-------------------------------------------------------------------------

ALTER PROCEDURE pTest

AS

BEGIN TRY

SELECT 1

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

SELECT @ErrorSeverity = 16, @ErrorState = 1, @msg=ERROR_MESSAGE()

END CATCH

---------------------------------------------------------------------------

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

There's something else going on here other than just the double-hop. Using RAISERROR with a severity level of 16 should not prevent remote execution. The original "semaphore" message is a bit of an indication of a larger, more insidious problem that is going to require a deeper dive on your part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 9

Jeff Moden (3/11/2016)
There's something else going on here other than just the double-hop. Using RAISERROR with a severity level of 16 should not prevent remote execution. The original "semaphore" message is a bit of an indication of a larger, more insidious problem that is going to require a deeper dive on your part.

Jeff,

I was just copied that severity line of code from the original sp. I tried with different severity also. even I removed that line itself and tried.

It just don't alter if the RAISERROR command is there.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 10

Any thoughts please?

_____________________________________________
One ounce of practice is more important than tonnes of dreams

kek01 SSC Rookie Points: 27 More actions · Answer 11

Your network may have a filter in place that is blocking that command. Yes, even in a commment. I have the exact thing happening. If I switch to a local database raiserror works fine either in a comment or uncommented. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/145df575-0b0a-4aaa-a0c2-56ef50dacd7c/raiserror-wont-compile-in-sproc?forum=transactsql

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

kek01 (12/31/2016)
Your network may have a filter in place that is blocking that command. Yes, even in a commment. I have the exact thing happening. If I switch to a local database raiserror works fine either in a comment or uncommented. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/145df575-0b0a-4aaa-a0c2-56ef50dacd7c/raiserror-wont-compile-in-sproc?forum=transactsql

Hadn't thought of that. That could be it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ez4321 SSC Enthusiast Points: 158 More actions · Answer 13

Thank you so much! I wasted an hour trying to comment out everything that could be offensive - your comment that even comments could be a problem was a great clue and the overall idea that running the ALTER PROCEDURE on the local machine by RDP'ing there may work, has saved my project! In my case I wasn't using RAISERROR. I suspect the culprit was working with xp_cmdshell or sp_configure. I can now ALTER my proc successfully and test.