Fatal error 682 and SqlDependency

  • Thread starter Thread starter Efran Cobisi
  • Start date Start date
E

Efran Cobisi

Hi,

In the web application my company is developing, we are trying to use the
SqlServer 2005 Notification Services feature to get notified about changes
in recordsets we query. Since our application server is asp.net, we
partially use its embedded support for that feature; in short, when our
application receives a command about retrieving some data, our retrieval
algorithm first checks the cache to see if it is already there. Otherwise it
retrieves the requested data from SqlServer and puts it in the
aforementioned cache, ready for subsequent requests. This cache gets
invalidated through SqlServer 2005 Notification Services, transparently
handled for us by the .Net framework class named SqlDependency.
Everything seems working fine but, after some tests, we have found that
medium sized queries (> 1Kb or so) makes the notification system crash,
giving us an infamous error at the client side (web server machine):

Warning: Fatal error 682 occurred at Feb 20 2006 10:09AM. Note the error and
time, and contact your system administrator.

On the server side (sql server machine), the event log for SqlServer shows
the following:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9644
Date: 20/02/2006
Time: 10.28.59
User: N/A
Computer: ---------
Description:
An error occurred in the service broker message dispatcher, Error: 15517
State: 1.

SqlServer machine software details:

Windows Server 2003 SE + SP1
SQL Server 2005 SE

Web server machine software details:

Windows Server 2003 SE + SP1
Microsoft .Net Framework 2.0

Is this a bug of SqlServer 2005? How can we solve this?

Thank you for your help.

--
Efran Cobisi
QBGroup Spa

(e-mail address removed)
To directly reply to me please remove all of the "nospam." occurences from
my email address.
 
Error 15517 is: Cannot execute as the database principal because the
principal "%.*ls" does not exist, this type of principal cannot be
impersonated, or you do not have permission.

From my experience this error typically happens when 'dbo' maps to a login
that is a Windows account that makes no sense on the current database host
machine. Usually this happens when a database is created on a machine by a
local administrator and then gets moved (detach/attach or backup/restore) to
another machine. 'dbo' will be the original local administrator on the first
machine, an account that cannot be resolved by the new machine. The fix is
easy, change the database ownership to an account that exists on the new
machine, for example 'sa':

ALTER AUTHORIZATION ON DATABASE::[...] TO [sa];

However, this error is unrelated to the size of the query notification, is
just related to the database the notification is fired from. To identify
which database potentially causes this problem, run this query:
select name,suser_sname(owner_sid) as owner from sys.databases

The ones that will have a NULL on the 'owner' column in the resultset are
going to be the ones that trigger the error 15517.

If it turns out I guessed wrong and this is not the issue, you will have to
look into the sys.transmission_queue on the database, where you should find
the undelivered notifications. These have the in the transmission_status
column the error message coresponding to the error 15517, with the proper
inserted user name on the '%.*ls' position. If this is not 'dbo', then the
problem will probably turn out to be orphaned users as in
http://support.microsoft.com/default.aspx?scid=kb;EN-US;274188

When this error happens in the server messages for one query notification
subscription will be delayed indefinitely in the transmissions_queue. I'm
not sure I understand why the client has to crash when this happens...

--
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
 
[forcing the SSB NG in]

Remus Rusanu said:
Error 15517 is: Cannot execute as the database principal because the
principal "%.*ls" does not exist, this type of principal cannot be
impersonated, or you do not have permission.

From my experience this error typically happens when 'dbo' maps to a login
that is a Windows account that makes no sense on the current database host
machine. Usually this happens when a database is created on a machine by a
local administrator and then gets moved (detach/attach or backup/restore)
to another machine. 'dbo' will be the original local administrator on the
first machine, an account that cannot be resolved by the new machine. The
fix is easy, change the database ownership to an account that exists on
the new machine, for example 'sa':

ALTER AUTHORIZATION ON DATABASE::[...] TO [sa];

However, this error is unrelated to the size of the query notification, is
just related to the database the notification is fired from. To identify
which database potentially causes this problem, run this query:
select name,suser_sname(owner_sid) as owner from sys.databases

The ones that will have a NULL on the 'owner' column in the resultset are
going to be the ones that trigger the error 15517.

If it turns out I guessed wrong and this is not the issue, you will have
to look into the sys.transmission_queue on the database, where you should
find the undelivered notifications. These have the in the
transmission_status column the error message coresponding to the error
15517, with the proper inserted user name on the '%.*ls' position. If this
is not 'dbo', then the problem will probably turn out to be orphaned users
as in http://support.microsoft.com/default.aspx?scid=kb;EN-US;274188

When this error happens in the server messages for one query notification
subscription will be delayed indefinitely in the transmissions_queue. I'm
not sure I understand why the client has to crash when this happens...

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


Efran Cobisi said:
Hi,

In the web application my company is developing, we are trying to use the
SqlServer 2005 Notification Services feature to get notified about
changes
in recordsets we query. Since our application server is asp.net, we
partially use its embedded support for that feature; in short, when our
application receives a command about retrieving some data, our retrieval
algorithm first checks the cache to see if it is already there. Otherwise
it
retrieves the requested data from SqlServer and puts it in the
aforementioned cache, ready for subsequent requests. This cache gets
invalidated through SqlServer 2005 Notification Services, transparently
handled for us by the .Net framework class named SqlDependency.
Everything seems working fine but, after some tests, we have found that
medium sized queries (> 1Kb or so) makes the notification system crash,
giving us an infamous error at the client side (web server machine):

Warning: Fatal error 682 occurred at Feb 20 2006 10:09AM. Note the error
and
time, and contact your system administrator.

On the server side (sql server machine), the event log for SqlServer
shows
the following:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9644
Date: 20/02/2006
Time: 10.28.59
User: N/A
Computer: ---------
Description:
An error occurred in the service broker message dispatcher, Error: 15517
State: 1.

SqlServer machine software details:

Windows Server 2003 SE + SP1
SQL Server 2005 SE

Web server machine software details:

Windows Server 2003 SE + SP1
Microsoft .Net Framework 2.0

Is this a bug of SqlServer 2005? How can we solve this?

Thank you for your help.

--
Efran Cobisi
QBGroup Spa

(e-mail address removed)
To directly reply to me please remove all of the "nospam." occurences
from
my email address.
Click to expand...
Click to expand...
 
Thank you for your help.
Actually, the orphaned user was dbo, so I had to execute sp_changedbowner
for the 'sa' account; now all works as it should.

--
Efran Cobisi, MCP
QBGroup Spa

Remus Rusanu said:
Error 15517 is: Cannot execute as the database principal because the
principal "%.*ls" does not exist, this type of principal cannot be
impersonated, or you do not have permission.

From my experience this error typically happens when 'dbo' maps to a login
that is a Windows account that makes no sense on the current database host
machine. Usually this happens when a database is created on a machine by a
local administrator and then gets moved (detach/attach or backup/restore)
to another machine. 'dbo' will be the original local administrator on the
first machine, an account that cannot be resolved by the new machine. The
fix is easy, change the database ownership to an account that exists on
the new machine, for example 'sa':

ALTER AUTHORIZATION ON DATABASE::[...] TO [sa];

However, this error is unrelated to the size of the query notification, is
just related to the database the notification is fired from. To identify
which database potentially causes this problem, run this query:
select name,suser_sname(owner_sid) as owner from sys.databases

The ones that will have a NULL on the 'owner' column in the resultset are
going to be the ones that trigger the error 15517.

If it turns out I guessed wrong and this is not the issue, you will have
to look into the sys.transmission_queue on the database, where you should
find the undelivered notifications. These have the in the
transmission_status column the error message coresponding to the error
15517, with the proper inserted user name on the '%.*ls' position. If this
is not 'dbo', then the problem will probably turn out to be orphaned users
as in http://support.microsoft.com/default.aspx?scid=kb;EN-US;274188

When this error happens in the server messages for one query notification
subscription will be delayed indefinitely in the transmissions_queue. I'm
not sure I understand why the client has to crash when this happens...

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


Efran Cobisi said:
Hi,

In the web application my company is developing, we are trying to use the
SqlServer 2005 Notification Services feature to get notified about
changes
in recordsets we query. Since our application server is asp.net, we
partially use its embedded support for that feature; in short, when our
application receives a command about retrieving some data, our retrieval
algorithm first checks the cache to see if it is already there. Otherwise
it
retrieves the requested data from SqlServer and puts it in the
aforementioned cache, ready for subsequent requests. This cache gets
invalidated through SqlServer 2005 Notification Services, transparently
handled for us by the .Net framework class named SqlDependency.
Everything seems working fine but, after some tests, we have found that
medium sized queries (> 1Kb or so) makes the notification system crash,
giving us an infamous error at the client side (web server machine):

Warning: Fatal error 682 occurred at Feb 20 2006 10:09AM. Note the error
and
time, and contact your system administrator.

On the server side (sql server machine), the event log for SqlServer
shows
the following:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9644
Date: 20/02/2006
Time: 10.28.59
User: N/A
Computer: ---------
Description:
An error occurred in the service broker message dispatcher, Error: 15517
State: 1.

SqlServer machine software details:

Windows Server 2003 SE + SP1
SQL Server 2005 SE

Web server machine software details:

Windows Server 2003 SE + SP1
Microsoft .Net Framework 2.0

Is this a bug of SqlServer 2005? How can we solve this?

Thank you for your help.

--
Efran Cobisi
QBGroup Spa

(e-mail address removed)
To directly reply to me please remove all of the "nospam." occurences
from
my email address.
Click to expand...
Click to expand...
 
Back
Top