diagnosing Service Broker problems - help
in [Programming SQL]
|
From: Mark on 9 Jan 2007 19:05 Thanks Remus. It didn't occur to me to check the security log from the sender for cred fails and it wasn't clear (to me anyway) from the Profiler exchange which authentication was failing. I think it's the back authentication to the connectee that was failing - which, as it turns out is emminently explainable given my situation (password expired at new year). The authentication fails in the event log on the sender didn't have any information about the context in which the authentication was happening, but I was able to match up the times and the originating host. I'll get that sorted out when I get back in the office. Thanks again for your help. -Mark "Remus Rusanu [MSFT]" wrote: > I just wanted to ensure you're no looking at an application event from SQL > when it reports the succesfull login/logout of the users. What you describe > is the right stuff. > Keep in mind that SSB runs two authentications (once in each direction, both > conector and conectee authenticate the other), so you might see a success in > one direction, followed by a failure on the other. > > HTH, > ~ Remus > > > "Mark" <mmodrall(a)nospam.nospam> wrote in message > news:03B1F350-AE84-418C-92B1-F8159F8591CE(a)microsoft.com... > > Hi Remus... > > > > Thanks for the tip on the domain controller. On the other point, please > > pardon me if I'm being a little thick. The entries in the Security log > > are > > credential checks by an application (Sql Server) in response to an action > > (posting a message), but they're not in the Application log - am I missing > > a > > subtlety here? The Security title for the message was "Successful Audit." > > > > I'm out of the office today, so I'll only be able to poke at this > > intermittently, but thanks for your help. > > > > -Mark > > > > > > "Remus Rusanu [MSFT]" wrote: > > > >> 1) Could not obtain information about Windows NT group/user > >> '<domain>\mmodrall', error code 0x5 > >> This is because the service account for SQL Server cannot contact the > >> Active > >> Directory. Easiest fix is to change the database owner a SQL account: > >> ALTEr AUTHORIZATION ON DATABASE::[dbname] TO [sa]; > >> > >> 2) Security Event viewer section and all the things llisted there are > >> successful login entries - no failures (which is what makes the Sql > >> Server > >> Profiler message seem odd). > >> What you see are not login events but application events: in this case an > >> application (MS SQL Server) reported an ordinary event that contains in > >> its > >> description something about a login. This is not the same thing as the > >> true > >> system logon events. You are probably not auditing logon events. You need > >> to > >> turn on the system logon audit (start Administrative Tools\Local Security > >> Policy or run 'secpol.msc', select Security Settings\Local Policies\Audit > >> Policy in the tree, select Audit logon events in the list and enable both > >> Success and Failure). After that the Event Viewer will show the system > >> logon > >> events in the 'Security' tab. > >> The Kerberos troubleshooting guide link I sent you describe this in > >> greater > >> detail. > >> > >> -- > >> This posting is provided "AS IS" with no warranties, and confers no > >> rights. > >> > >> HTH, > >> ~ Remus Rusanu > >> > >> SQL Service Broker > >> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx > >> > >> > >> "Mark" <mmodrall(a)nospam.nospam> wrote in message > >> news:6FD3F503-EFD0-49C4-84A6-77E22431463D(a)microsoft.com... > >> > Hi Remus... > >> > > >> > I started by looking at the Security Event viewer section and all the > >> > things > >> > listed there are successful login entries - no failures (which is what > >> > makes > >> > the Sql Server Profiler message seem odd). I see entries in the event > >> > log > >> > for <domain>\<host>$ from the caller and they are always successful. > >> > > >> > Roger pointed me at your blog, so I've been reading through that and > >> > trying > >> > some things there to further the diagnosis. Specifically, I was trying > >> > the > >> > activation procedure on your page to put errors in the event log. I > >> > had > >> > different problems with that, but I think those are related to using > >> > windows > >> > authentication. Your script had the activation procedure EXECUTE AS > >> > OWNER > >> > and I have the db set up for mixed mode authentication and the owner is > >> > an > >> > NTLM account. When it tries to run the activation procedure, it logs > >> > an > >> > Application error "Could not obtain information about Windows NT > >> > group/user > >> > '<domain>\mmodrall', error code 0x5" (I'm the dbo on the objects in > >> > question > >> > and I used windows authentication when I created them). I'm guessing > >> > that > >> > trying to run as me when I'm not connected is causing that problem. I > >> > tried > >> > changing the procedure to EXECUTE AS CALLER but that ALTER statement > >> > was > >> > kicked out as a syntax error (can't use CALLER when setting perms on an > >> > activation procedure I guess?). Then I tried leaving the EXECUTE AS > >> > off > >> > entirely, but I was back to it failing authentication with my user > >> > name; I > >> > guess EXECUTE AS OWNER is the default. > >> > > >> > Anyway, so I'm still stuck with no way of figuring out why the messages > >> > are > >> > failing. Event log says the login was fine, Sql Server Profiler says > >> > the > >> > login failed (but won't say what login failed). > >> > > >> > As I noted to Roger, when I first set up the "Hello World" example, I > >> > hadn't > >> > granted SEND on the remote svc and saw similar external behavior (the > >> > post > >> > succeeds but messages just don't show up) but in that case Profiler > >> > gave a > >> > different error message (one related to permissions not > >> > authentication). > >> > > >> > I'll read the reference you've included here to see if there are any > >> > clues. > >> > > >> > Thanks > >> > -mark > >> > > >> > "Remus Rusanu [MSFT]" wrote: > >> > > >> >> 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 > >> >> > > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > > > |