Prev: Formatting GetDate()
Next: Sending html-mail from sql server 2005
From: Mark on 8 Jan 2007 14:01
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
> >
> >
>
>
>
From: Remus Rusanu [MSFT] on 8 Jan 2007 14:43
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
>> >
>> >
>>
>>
>>


From: Roger Wolter[MSFT] on 8 Jan 2007 19:40
I run into the "can't obtain information..." error all the time on my laptop
because I set up the application while I'm connected to the network and then
try to run it at home when I can't get to the domain controller. As Remus
said. The fix for that is to make the dbo a SQL Server user (SA is good) so
that objects owned by dbo don't have to be authenticated through the domain
controller. This obviously may have nothing to do with your problem but in
my experience it's one of the most common issues that cause an application
that worked when I wrote it to stop working when I try to demo it.

--
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: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
>> >
>> >
>>
>>
>>


From: Mark on 9 Jan 2007 08:19
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
> >> >
> >> >
> >>
> >>
> >>
>
>
>
From: Remus Rusanu [MSFT] on 9 Jan 2007 08:58
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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Formatting GetDate()
Next: Sending html-mail from sql server 2005