Internal Activation - calls stored procs in other DBs

Hi all

I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".

I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?

Thanks

Ian

You are hitting the 'Extending database impersonation under EXECUTE AS context' issue. I have a series of posts in my blog tackling this problem:

http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx

The first link is posted today and is an actual example on how to call a procedure in another database under activation.

|||

Thanks for the information - it's just what I was looking for.

Ian

|||

One more question....

Is it essential that the owner of the other DB is the same as the owner of the activated stored proc?

I don't seem to be able to get this to work if they are different.

Thanks

Ian

|||You can use any user with receive permission on the queue.|||I think you must grant AUTHENTICATE permission on the 'other' DB to the user from the EXECUTE AS clause of the CREATE/ALTER procedure. If the EXECUTE AS is OWNER, then to the owner of the activated procedure)

intermittent locks

We are experiencing problems intermittently.

Users report problems of various types including timeout messages. We investigate and find a user has acquired a lock which is blocking other users.

We contact the user and they have usually completed their activity and are not always aware of any problem despite them owning a lock.

When the user logs out of the application the lock clears and the system returns to normal.

Indexes have been rebuilt, auto update statistics is on.

Does anyone have any suggestions? :cool:The first thing I'd do is start watching for locks to determine how often they occur, and ask the users if they know of any activity that causes the problems associated with locking/blocking (that may give you clues about what you need to watch).

Once you understand what you are looking for, run a trace using SQL Profiler at the same time as a Performance Monitor trace watching for locking/blocking. The PerfMon trace will show you when the problem occurs, the Profiler trace will show you what caused the problem.

When you understand the cause of the problem, you can then look at changing the application to avoid the problem.

-PatP|||We are trying to gather more information from the users to track this down.

Anecdotally users believe that they have finished their activity and are simply still logged on or are running searches.

We haven't needed to kill a session, the user simply logs off.

It's almost as though the lock has been taken but not released when the activity has finished.

Does this sound likely/possible? If so any ideas what could be causing it?|||Does this sound likely/possible? If so any ideas what could be causing it?Yes, it sounds rather likely.

I'd suspect that the problem is something that the code is doing "behind the curtains" that the user is completely unaware of, but is still causing havok. Until you can compare the two traces (or provide LOTS of additional insight into your application and server configuration), we can only guess.

-PatP

intermittent failures using web synchronisation

HI

We are using HTTPS anonymous merge subscriptions....

Sometimes when trying to synchonise, we will get the following error messages returned to the subscriber....

The upload message to be sent to Publisher '**thewebserver**' is being generated
The merge process is using Exchange ID '0F65CFCB-AF17-47DC-8D98-493A44C243E0' for this web synchronization session.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent received the following error status and message from the Internet Information Services (IIS) server during Web synchronization: [401 :'Unauthorized']. When troubleshooting, ensure that the Web synchronization settings for the subscription are correct, and increase the internet timeout setting at the Subscriber and the connection timeout at the Web server.

If I then go to a web brower, put in the HTTPS address, it brings up the logon dialog - I put in the admin username and password to confirm the connection and that's fine.

We try and synchronise again, and this time it works - it's as though I have 'woken' it up again and it's happy to play.

Is increasing the timeouts as suggested by the error message the way to go ? If so, where does one set the 'internet timeout setting at the subscriber', and the 'connection timeout at the webserver' ?

thanks

I dont think it is the timeout that is causing this. But anyways,

To increase the timeout on the Client (Merge Agent) use the Merge agent parameter: -InternetTimeout

To increase the timeout on the webserver, use IIS-->WebSites-->DefaultWebSite-->Properties-->ConnectionTimeout

From what you describe, it appears to me that the -InternetLogin may not have permissions to load the replisapi.dll once the process goes away? Would that be the case? This is just a hunch.

Under what context is the IIS running? (specifically the w3wp.exe process)

Is your InternetLogin and the login running the IIS (w3wp) the same?

You could also try other ways so that you can specify the w3wp.exe to never exit out after 20 minutes (I think that is the default one)

|||I had this happen just yesterday in my test environment. I added a new subscriber laptop to the mix and I kept on getting this error. It turns out that I had to visit the replisapi.dll via Internet Explorer and install the SSL certificate. (I am using Self SSL on the test environment) Once I installed the cert the error went away.sql

intermittent failures using web synchronisation

HI

We are using HTTPS anonymous merge subscriptions....

Sometimes when trying to synchonise, we will get the following error messages returned to the subscriber....

The upload message to be sent to Publisher '**thewebserver**' is being generated
The merge process is using Exchange ID '0F65CFCB-AF17-47DC-8D98-493A44C243E0' for this web synchronization session.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent could not connect to the URL 'https://**thewebserver**/client/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
The Merge Agent received the following error status and message from the Internet Information Services (IIS) server during Web synchronization: [401 :'Unauthorized']. When troubleshooting, ensure that the Web synchronization settings for the subscription are correct, and increase the internet timeout setting at the Subscriber and the connection timeout at the Web server.

If I then go to a web brower, put in the HTTPS address, it brings up the logon dialog - I put in the admin username and password to confirm the connection and that's fine.

We try and synchronise again, and this time it works - it's as though I have 'woken' it up again and it's happy to play.

Is increasing the timeouts as suggested by the error message the way to go ? If so, where does one set the 'internet timeout setting at the subscriber', and the 'connection timeout at the webserver' ?

thanks

I dont think it is the timeout that is causing this. But anyways,

To increase the timeout on the Client (Merge Agent) use the Merge agent parameter: -InternetTimeout

To increase the timeout on the webserver, use IIS-->WebSites-->DefaultWebSite-->Properties-->ConnectionTimeout

From what you describe, it appears to me that the -InternetLogin may not have permissions to load the replisapi.dll once the process goes away? Would that be the case? This is just a hunch.

Under what context is the IIS running? (specifically the w3wp.exe process)

Is your InternetLogin and the login running the IIS (w3wp) the same?

You could also try other ways so that you can specify the w3wp.exe to never exit out after 20 minutes (I think that is the default one)

|||I had this happen just yesterday in my test environment. I added a new subscriber laptop to the mix and I kept on getting this error. It turns out that I had to visit the replisapi.dll via Internet Explorer and install the SSL certificate. (I am using Self SSL on the test environment) Once I installed the cert the error went away.

Intermittant Connection Errors

I have a Merge-Pull subscription setup between two SQL 2005 databases. While watching the status messages display for the subscription, I am constantly seeing the following message being displayed:

"The merge process could not connect to the Publisher '{server}:{database}'. Check to ensure that the server is running."

It will display for anywhere between 15 seconds to a few minutes, then it will display the standard "Waiting 60 seconds..." message which is what I'm used to seeing when all is working well.

There doesn't seem to be any network issues that I can identify. I ran a quick script to run ping continuously against the publisher server and verified that the server was constantly "pingable", even when the above message was displayed (which, by the way, was being displayed by using SSMS while connected to the publisher).

Does anyone know what would cause this message to appear all the time - for extended amounts of time, with occasional glimpses of a non-error status?

It's a wild guess but failing to login / authenticate ?|||I thought of that but am not sure that it applies. The reason being that I can leave the subscription/synchronization status window open in SSMS and it will toggle back and forth (seemingly randomly over time) between the message I mentioned above and some type of valid message.|||What about Timeouts (Connection or Query)? I have seen similar issues in Web Sync. If the amount of data changed since the last sync operation then I have experienced Timeouts while the sync is initialized. Not exactly as you describe but another parameter worth checking.

Rab
|||

I'm not sure what the issue was but it seems to have resolved itself - at least for the time being. If it happens again, I'll be looking for more answers :-)

Thanks for everyone's help.

|||I have this problem as well. It works sometimes and then fails with this error message which leads me to believe it isn't a login problem. I'm still new to replication so I could be wrong, but I think once I get this message, replication is broken and requires someone to intervene to reinitialize. Has anyone figured out what is going on?

Intermittant Connection Errors

I have a Merge-Pull subscription setup between two SQL 2005 databases. While watching the status messages display for the subscription, I am constantly seeing the following message being displayed:

"The merge process could not connect to the Publisher '{server}:{database}'. Check to ensure that the server is running."

It will display for anywhere between 15 seconds to a few minutes, then it will display the standard "Waiting 60 seconds..." message which is what I'm used to seeing when all is working well.

There doesn't seem to be any network issues that I can identify. I ran a quick script to run ping continuously against the publisher server and verified that the server was constantly "pingable", even when the above message was displayed (which, by the way, was being displayed by using SSMS while connected to the publisher).

Does anyone know what would cause this message to appear all the time - for extended amounts of time, with occasional glimpses of a non-error status?

It's a wild guess but failing to login / authenticate ?|||I thought of that but am not sure that it applies. The reason being that I can leave the subscription/synchronization status window open in SSMS and it will toggle back and forth (seemingly randomly over time) between the message I mentioned above and some type of valid message.|||What about Timeouts (Connection or Query)? I have seen similar issues in Web Sync. If the amount of data changed since the last sync operation then I have experienced Timeouts while the sync is initialized. Not exactly as you describe but another parameter worth checking.

Rab|||

I'm not sure what the issue was but it seems to have resolved itself - at least for the time being. If it happens again, I'll be looking for more answers :-)

Thanks for everyone's help.

|||I have this problem as well. It works sometimes and then fails with this error message which leads me to believe it isn't a login problem. I'm still new to replication so I could be wrong, but I think once I get this message, replication is broken and requires someone to intervene to reinitialize. Has anyone figured out what is going on?sql

Integrity Check set in Maintenance Plan failed....

I'm trying to troubleshoot why this job failed. Welcome any suggestion
DURATION: 0 hours, 0 minutes, 49 second
STATUS: Faile
MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1)Make sure that you have defined a report file for the maint job and check for error messages in the
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Janice" <anonymous@.discussions.microsoft.com> wrote in message
news:EDF91295-5A27-43C3-AA1C-402F35FB629F@.microsoft.com...
> I'm trying to troubleshoot why this job failed. Welcome any suggestions
>
> DURATION: 0 hours, 0 minutes, 49 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run
was step 1 (Step 1).
>|||Thanks TIBOR - I viewed the report and discovered that db needs to be in single user mode.
-- Tibor Karaszi wrote: --
Make sure that you have defined a report file for the maint job and check for error messages in the
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Janice" <anonymous@.discussions.microsoft.com> wrote in message
news:EDF91295-5A27-43C3-AA1C-402F35FB629F@.microsoft.com...
> I'm trying to troubleshoot why this job failed. Welcome any suggestions
>> DURATION: 0 hours, 0 minutes, 49 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run
was step 1 (Step 1).
>|||The maint plan will try to set the db in single user, but that will fail if there are users in the
database. Also, some of the system databases cannot be set in single user mode.
I suggest you remove the option "attempt to repair minor problems" from the integrity checks. If you
do run into problems, you want to be there and contemplate over what you want to do to fix it.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Janice" <anonymous@.discussions.microsoft.com> wrote in message
news:8FF8A86A-F50A-4EB8-BF6D-058B99EF2455@.microsoft.com...
> Thanks TIBOR - I viewed the report and discovered that db needs to be in single user mode.
> -- Tibor Karaszi wrote: --
> Make sure that you have defined a report file for the maint job and check for error messages
in the
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Janice" <anonymous@.discussions.microsoft.com> wrote in message
> news:EDF91295-5A27-43C3-AA1C-402F35FB629F@.microsoft.com...
> > I'm trying to troubleshoot why this job failed. Welcome any suggestions
> >> DURATION: 0 hours, 0 minutes, 49 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step
to run
> was step 1 (Step 1).
> >