diagnosing Service Broker problems - help
in [Programming SQL]
|
From: Mark on 8 Jan 2007 08:43 Hi... I've been experimenting with Service Broker "Hello, World" type examples using Express as the sender and a full instance of Sql Server 2005 on another. I had the whole pipeline working a couple of weeks ago but when I went to test it again today, I'm having some weird problems. I can't say "errors" exactly because one of the problems is that the error isn't raising an error. I have a stored procedure in Express that does the message queuing. The sproc puts the message queuing in a transaction. I call the Express sproc; I know I get in and run it because I have print statements there. I don't see any errors logged in the sql server logs on either side. The receiver event log has security events showing <NODE>\SQLEXPRESS$ logging in and logging off successfully, but nothing shows up in the receiver queue. And nothing is in sys.transmission_queue on the sender side. It just vanishes into space with no error being reported. I attached the Sql Sever Profiler to both sides and tracked all Broker and security audit events. On the sender side I get: Broker:Connection 2-Connected Audit Broker Login 2-Login Protocol Error Broker:Connection 4-Closing 'Connection handshake failed. An OS call failed: (8009030c) The logon attempt failed). State 67. Broker:Connection 5-Closed On the receiver side I get: Broker:Connection 6-Accept Broker:Connection 4-Closing '10054(An existing connection was forcibly closed...' Broker:Connection 5-Closed Looks like simple credential error, right? Except that the Event Viewer on the receiver side says that the Express login was successful and there are no errors in the Sql Server log. Where do I go from here? It's also troubling that this low level failure has no reflection on up the chain. @@ERROR is 0 after the post call; I have no way of knowing something went wrong. Any tips? thanks -Mark
From: Roger Wolter[MSFT] on 8 Jan 2007 10:39 Remus - one of the Service Broker devs has some good troubleshooting advice on his blog: http://blogs.msdn.com/remusrusanu/ Service Broker is asynchronous - that's the main value of using it - so a SEND command succeeds when the message is committed to a queue - in your case the sys.transmission_queue. The actual message send on the network could possibly happen days after the SEND command completes so there's no way for @@error to return an error on a SEND unless the message can't be put into any queue. If you need to know when your stored procedure completes that the action on the remote system has completed successfully then you should be using linked servers and distributed transactions. The reason you see two different security messages is probably because service broker has two levels of security. The TCP/IP connection security is enforced on the connection between systems and succeeds if the endpoints are configured correctly and dialog security is enforced for each dialog. If you have correct credentials for the connection endpoints but not for the dialogs, you will see the connection succeed and the dialog fail. -- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Mark" <mmodrall(a)nospam.nospam> wrote in message news:3DACEC33-61B9-4045-9328-38F301BA012B(a)microsoft.com... > Hi... > > I've been experimenting with Service Broker "Hello, World" type examples > using Express as the sender and a full instance of Sql Server 2005 on > another. I had the whole pipeline working a couple of weeks ago but when > I > went to test it again today, I'm having some weird problems. I can't say > "errors" exactly because one of the problems is that the error isn't > raising > an error. > > I have a stored procedure in Express that does the message queuing. The > sproc puts the message queuing in a transaction. I call the Express > sproc; I > know I get in and run it because I have print statements there. > > I don't see any errors logged in the sql server logs on either side. The > receiver event log has security events showing <NODE>\SQLEXPRESS$ logging > in > and logging off successfully, but nothing shows up in the receiver queue. > And nothing is in sys.transmission_queue on the sender side. It just > vanishes into space with no error being reported. > > I attached the Sql Sever Profiler to both sides and tracked all Broker and > security audit events. > > On the sender side I get: > Broker:Connection 2-Connected > Audit Broker Login 2-Login Protocol Error > Broker:Connection 4-Closing 'Connection handshake failed. An OS call > failed: (8009030c) The logon attempt failed). State 67. > Broker:Connection 5-Closed > > On the receiver side I get: > Broker:Connection 6-Accept > Broker:Connection 4-Closing '10054(An existing connection was forcibly > closed...' > Broker:Connection 5-Closed > > Looks like simple credential error, right? Except that the Event Viewer > on > the receiver side says that the Express login was successful and there are > no > errors in the Sql Server log. Where do I go from here? > > It's also troubling that this low level failure has no reflection on up > the > chain. @@ERROR is 0 after the post call; I have no way of knowing > something > went wrong. > > Any tips? > > thanks > -Mark > >
From: Mark on 8 Jan 2007 11:21 Hi Roger... Thanks for responding. I understand about the value of asynchronicity in SB; what perplexed me (and perplexes me on other errors I've seen) is that certain delivery failures end up with the message simply disappearing altogther - the SEND action finishes "successfully" (i.e. you got into sys.transmission_queue) so your transaction completes, something goes wrong in delivery, and you end up with nothing in sys.transmission, nothing at your destination, and no indication anywhere that something went wrong. That seems to violate the "guaranteed delivery" promise. From various books I read on it, my understanding was that delivery failures were supposed to result in the message stuck in sys.transmission_queue where operators could find it and (manually if necessary) recover it. I had a similar problem a while back when I hadn't granted SEND on the far side's service, but when I got Sql Server Profiler involved, it gave a different error message (something about not having permissions). That error lead me to what I needed to GRANT. This error is perplexing because the Security event log says the login was successful. Sql Server Profiler is saying it wasn't, but it doesn't even log any information about who/what login fails. And again, the message in question just disappears from both sides. I'll look at the remus site to see if they have any hints. Thanks -Mark "Roger Wolter[MSFT]" wrote: > Remus - one of the Service Broker devs has some good troubleshooting advice > on his blog: > http://blogs.msdn.com/remusrusanu/ > Service Broker is asynchronous - that's the main value of using it - so a > SEND command succeeds when the message is committed to a queue - in your > case the sys.transmission_queue. The actual message send on the network > could possibly happen days after the SEND command completes so there's no > way for @@error to return an error on a SEND unless the message can't be put > into any queue. If you need to know when your stored procedure completes > that the action on the remote system has completed successfully then you > should be using linked servers and distributed transactions. > > The reason you see two different security messages is probably because > service broker has two levels of security. The TCP/IP connection security > is enforced on the connection between systems and succeeds if the endpoints > are configured correctly and dialog security is enforced for each dialog. > If you have correct credentials for the connection endpoints but not for the > dialogs, you will see the connection succeed and the dialog fail. > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > Use of included script samples are subject to the terms specified at > http://www.microsoft.com/info/cpyright.htm > > "Mark" <mmodrall(a)nospam.nospam> wrote in message > news:3DACEC33-61B9-4045-9328-38F301BA012B(a)microsoft.com... > > Hi... > > > > I've been experimenting with Service Broker "Hello, World" type examples > > using Express as the sender and a full instance of Sql Server 2005 on > > another. I had the whole pipeline working a couple of weeks ago but when > > I > > went to test it again today, I'm having some weird problems. I can't say > > "errors" exactly because one of the problems is that the error isn't > > raising > > an error. > > > > I have a stored procedure in Express that does the message queuing. The > > sproc puts the message queuing in a transaction. I call the Express > > sproc; I > > know I get in and run it because I have print statements there. > > > > I don't see any errors logged in the sql server logs on either side. The > > receiver event log has security events showing <NODE>\SQLEXPRESS$ logging > > in > > and logging off successfully, but nothing shows up in the receiver queue. > > And nothing is in sys.transmission_queue on the sender side. It just > > vanishes into space with no error being reported. > > > > I attached the Sql Sever Profiler to both sides and tracked all Broker and > > security audit events. > > > > On the sender side I get: > > Broker:Connection 2-Connected > > Audit Broker Login 2-Login Protocol Error > > Broker:Connection 4-Closing 'Connection handshake failed. An OS call > > failed: (8009030c) The logon attempt failed). State 67. > > Broker:Connection 5-Closed > > > > On the receiver side I get: > > Broker:Connection 6-Accept > > Broker:Connection 4-Closing '10054(An existing connection was forcibly > > closed...' > > Broker:Connection 5-Closed > > > > Looks like simple credential error, right? Except that the Event Viewer > > on > > the receiver side says that the Express login was successful and there are > > no > > errors in the Sql Server log. Where do I go from here? > > > > It's also troubling that this low level failure has no reflection on up > > the > > chain. @@ERROR is 0 after the post call; I have no way of knowing > > something > > went wrong. > > > > Any tips? > > > > thanks > > -Mark > > > > > > >
From: Remus Rusanu [MSFT] on 8 Jan 2007 12:07 Hello Mark, About the system error, 0x8009030C is SEC_E_LOGON_DENIED, explained in MSDN as simply 'The Logon failed'. This is a Windows authentication issue, I would start by looking into the system Event Viewer's Security log to see if there are any related entries. The best troubleshooting guide I know on the issue is here: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx (altough is Kerberos, the methodoly aplies to NTLM as well) As for the message flow problem, is not clear to me what you're seeing. If the message 'vanishes' from sys.transmsission_queue it means either it was acknowledleged by the target (so the message was delivered), either your code explictly removes it (e.g. by issuing END DIALOG ... WITH CLEANUP) HTH, ~ Remus "Mark" <mmodrall(a)nospam.nospam> wrote in message news:3DACEC33-61B9-4045-9328-38F301BA012B(a)microsoft.com... > Hi... > > I've been experimenting with Service Broker "Hello, World" type examples > using Express as the sender and a full instance of Sql Server 2005 on > another. I had the whole pipeline working a couple of weeks ago but when > I > went to test it again today, I'm having some weird problems. I can't say > "errors" exactly because one of the problems is that the error isn't > raising > an error. > > I have a stored procedure in Express that does the message queuing. The > sproc puts the message queuing in a transaction. I call the Express > sproc; I > know I get in and run it because I have print statements there. > > I don't see any errors logged in the sql server logs on either side. The > receiver event log has security events showing <NODE>\SQLEXPRESS$ logging > in > and logging off successfully, but nothing shows up in the receiver queue. > And nothing is in sys.transmission_queue on the sender side. It just > vanishes into space with no error being reported. > > I attached the Sql Sever Profiler to both sides and tracked all Broker and > security audit events. > > On the sender side I get: > Broker:Connection 2-Connected > Audit Broker Login 2-Login Protocol Error > Broker:Connection 4-Closing 'Connection handshake failed. An OS call > failed: (8009030c) The logon attempt failed). State 67. > Broker:Connection 5-Closed > > On the receiver side I get: > Broker:Connection 6-Accept > Broker:Connection 4-Closing '10054(An existing connection was forcibly > closed...' > Broker:Connection 5-Closed > > Looks like simple credential error, right? Except that the Event Viewer > on > the receiver side says that the Express login was successful and there are > no > errors in the Sql Server log. Where do I go from here? > > It's also troubling that this low level failure has no reflection on up > the > chain. @@ERROR is 0 after the post call; I have no way of knowing > something > went wrong. > > Any tips? > > thanks > -Mark > >
From: Roger Wolter[MSFT] on 8 Jan 2007 13:02
As Remus said, messages don't just disappear. They stay somewhere until your application tells them to go away. They may not end up where you expected them to be but they don't just go away unless you receive them or clean them up. -- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Mark" <mmodrall(a)nospam.nospam> wrote in message news:2CC28926-2124-4CB8-B936-8968205B0F75(a)microsoft.com... > Hi Roger... > > Thanks for responding. > > I understand about the value of asynchronicity in SB; what perplexed me > (and > perplexes me on other errors I've seen) is that certain delivery failures > end > up with the message simply disappearing altogther - the SEND action > finishes > "successfully" (i.e. you got into sys.transmission_queue) so your > transaction > completes, something goes wrong in delivery, and you end up with nothing > in > sys.transmission, nothing at your destination, and no indication anywhere > that something went wrong. That seems to violate the "guaranteed > delivery" > promise. From various books I read on it, my understanding was that > delivery > failures were supposed to result in the message stuck in > sys.transmission_queue where operators could find it and (manually if > necessary) recover it. > > I had a similar problem a while back when I hadn't granted SEND on the far > side's service, but when I got Sql Server Profiler involved, it gave a > different error message (something about not having permissions). That > error > lead me to what I needed to GRANT. > > This error is perplexing because the Security event log says the login was > successful. Sql Server Profiler is saying it wasn't, but it doesn't even > log > any information about who/what login fails. And again, the message in > question just disappears from both sides. > > I'll look at the remus site to see if they have any hints. > > Thanks > -Mark > > > "Roger Wolter[MSFT]" wrote: > >> Remus - one of the Service Broker devs has some good troubleshooting >> advice >> on his blog: >> http://blogs.msdn.com/remusrusanu/ >> Service Broker is asynchronous - that's the main value of using it - so a >> SEND command succeeds when the message is committed to a queue - in your >> case the sys.transmission_queue. The actual message send on the network >> could possibly happen days after the SEND command completes so there's no >> way for @@error to return an error on a SEND unless the message can't be >> put >> into any queue. If you need to know when your stored procedure completes >> that the action on the remote system has completed successfully then you >> should be using linked servers and distributed transactions. >> >> The reason you see two different security messages is probably because >> service broker has two levels of security. The TCP/IP connection >> security >> is enforced on the connection between systems and succeeds if the >> endpoints >> are configured correctly and dialog security is enforced for each dialog. >> If you have correct credentials for the connection endpoints but not for >> the >> dialogs, you will see the connection succeed and the dialog fail. >> >> -- >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> Use of included script samples are subject to the terms specified at >> http://www.microsoft.com/info/cpyright.htm >> >> "Mark" <mmodrall(a)nospam.nospam> wrote in message >> news:3DACEC33-61B9-4045-9328-38F301BA012B(a)microsoft.com... >> > Hi... >> > >> > I've been experimenting with Service Broker "Hello, World" type >> > examples >> > using Express as the sender and a full instance of Sql Server 2005 on >> > another. I had the whole pipeline working a couple of weeks ago but >> > when >> > I >> > went to test it again today, I'm having some weird problems. I can't >> > say >> > "errors" exactly because one of the problems is that the error isn't >> > raising >> > an error. >> > >> > I have a stored procedure in Express that does the message queuing. >> > The >> > sproc puts the message queuing in a transaction. I call the Express >> > sproc; I >> > know I get in and run it because I have print statements there. >> > >> > I don't see any errors logged in the sql server logs on either side. >> > The >> > receiver event log has security events showing <NODE>\SQLEXPRESS$ >> > logging >> > in >> > and logging off successfully, but nothing shows up in the receiver >> > queue. >> > And nothing is in sys.transmission_queue on the sender side. It just >> > vanishes into space with no error being reported. >> > >> > I attached the Sql Sever Profiler to both sides and tracked all Broker >> > and >> > security audit events. >> > >> > On the sender side I get: >> > Broker:Connection 2-Connected >> > Audit Broker Login 2-Login Protocol Error >> > Broker:Connection 4-Closing 'Connection handshake failed. An OS call >> > failed: (8009030c) The logon attempt failed). State 67. >> > Broker:Connection 5-Closed >> > >> > On the receiver side I get: >> > Broker:Connection 6-Accept >> > Broker:Connection 4-Closing '10054(An existing connection was forcibly >> > closed...' >> > Broker:Connection 5-Closed >> > >> > Looks like simple credential error, right? Except that the Event >> > Viewer >> > on >> > the receiver side says that the Express login was successful and there >> > are >> > no >> > errors in the Sql Server log. Where do I go from here? >> > >> > It's also troubling that this low level failure has no reflection on up >> > the >> > chain. @@ERROR is 0 after the post call; I have no way of knowing >> > something >> > went wrong. >> > >> > Any tips? >> > >> > thanks >> > -Mark >> > >> > >> >> >> |