Showing posts with label service. Show all posts
Showing posts with label service. Show all posts

Friday, March 30, 2012

More problems with activation

I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

Activated task run under impersonated security context, similar to using the EXECUTE AS clause. Let me cross-post a reply from Remus:

<remus>
The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).
</remus>

While the above could solve your problem, it may be worthwhile trying to ask yourself, why is the service located in Basket_ODS instead of Intelligence_ODS.

Hope that helps,
Rushi

|||

I actually have a sample showing how to do this: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

HTH,
~ Remus

|||

Thanks Rushi,

As usual you do a great job with follow up. The activated stored procedure copies data from the Basket_ODS database to the Intelligence_ODS database. If I move the proc to the Basket_ODS database, I ran into the same issue when it tried to copy over.

I did read the information on BOL and your earlier post on this. I guess I'll have to work my way through signing the stored procedure. The trust database option really won't be a player for me I think. I did set up trust tonight and it worked though. I have to do another run in the morning but it was incredibly slower than the copy to the same database.

I'll try to worth through the signing tomorrow. That seems the way to go for me. We are trying to operate in least-trust mode. The DBAs start getting white hair when I ask for things like database trust.

Thank you so much for your help and your contribution to the community.

Gary

|||

Thank's Remus. Wow that's a great sample. I wish I found that earlier today. Thanks again for the great information.

Gary

|||

I created the certificates as noted in your scripts. The user associated with the certificate in my system is BrokerUser2. I do have a question though.

why can't I do this?

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BrokerUser2'

)

when I try this it says BrokerUser2 does not exist or I do not have access to it. I can do an sp_helpuser 'BrokerUser2' and see his rights though.

It did appear to work if I did this:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4, EXECUTE AS OWNER)

I did create the ODS.ProcessOrderQueue stored procedure [With Execute As 'BrokerUser2'].

|||

The user executing the ALTER QUEUE statement must have IMPERSONATE permission over 'BrokerUser2'.

HTH,
~ Remus

More on Runaway Log Files

I had to stop the RS service on our server. It's creating 30 megabyte log
files, once every minute. I can still serve up reports evidently with the
ReportServer service stopped in Control Panel, but I doubt I can publish
reports.
If I start the service, the log files immediately start to pile up.
Any ideas? Any way to just stop logging?
JeffYou should contact PSS for a fix for this issue. Restarting the service
should make the problem go away but it could come back at anytime.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:e$H8GRsjEHA.156@.TK2MSFTNGP10.phx.gbl...
> I had to stop the RS service on our server. It's creating 30 megabyte log
> files, once every minute. I can still serve up reports evidently with the
> ReportServer service stopped in Control Panel, but I doubt I can publish
> reports.
> If I start the service, the log files immediately start to pile up.
> Any ideas? Any way to just stop logging?
> Jeff
>|||Hi!
Nice to hear, as we have the same problem.
Would you be so kind to tell me what PSS is and how to contact them?
Armin Mueller
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:erEFxDtjEHA.596@.TK2MSFTNGP11.phx.gbl...
> You should contact PSS for a fix for this issue. Restarting the service
> should make the problem go away but it could come back at anytime.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:e$H8GRsjEHA.156@.TK2MSFTNGP10.phx.gbl...
> > I had to stop the RS service on our server. It's creating 30 megabyte
log
> > files, once every minute. I can still serve up reports evidently with
the
> > ReportServer service stopped in Control Panel, but I doubt I can publish
> > reports.
> >
> > If I start the service, the log files immediately start to pile up.
> >
> > Any ideas? Any way to just stop logging?
> >
> > Jeff
> >
> >
>|||Microsoft Product Support Services. See
http://support.microsoft.com/default.aspx?pr=cntactms&style=home. We are
working on the fix and should have a KB article number by the end of this
week.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Armin Mueller" <armin.mueller@.cosmicos.de> wrote in message
news:%23zEQTD$kEHA.3432@.TK2MSFTNGP14.phx.gbl...
> Hi!
> Nice to hear, as we have the same problem.
> Would you be so kind to tell me what PSS is and how to contact them?
> Armin Mueller
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:erEFxDtjEHA.596@.TK2MSFTNGP11.phx.gbl...
>> You should contact PSS for a fix for this issue. Restarting the service
>> should make the problem go away but it could come back at anytime.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
>> news:e$H8GRsjEHA.156@.TK2MSFTNGP10.phx.gbl...
>> > I had to stop the RS service on our server. It's creating 30 megabyte
> log
>> > files, once every minute. I can still serve up reports evidently with
> the
>> > ReportServer service stopped in Control Panel, but I doubt I can
>> > publish
>> > reports.
>> >
>> > If I start the service, the log files immediately start to pile up.
>> >
>> > Any ideas? Any way to just stop logging?
>> >
>> > Jeff
>> >
>> >
>>
>|||Can you tell me if there's been any progress on the hotfix and/or the KB
article related to this issue?
Thanks,
Shawn
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ONXc$UFlEHA.536@.TK2MSFTNGP11.phx.gbl...
> Microsoft Product Support Services. See
> http://support.microsoft.com/default.aspx?pr=cntactms&style=home. We are
> working on the fix and should have a KB article number by the end of this
> week.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Armin Mueller" <armin.mueller@.cosmicos.de> wrote in message
> news:%23zEQTD$kEHA.3432@.TK2MSFTNGP14.phx.gbl...
> > Hi!
> >
> > Nice to hear, as we have the same problem.
> >
> > Would you be so kind to tell me what PSS is and how to contact them?
> >
> > Armin Mueller
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:erEFxDtjEHA.596@.TK2MSFTNGP11.phx.gbl...
> >> You should contact PSS for a fix for this issue. Restarting the
service
> >> should make the problem go away but it could come back at anytime.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> >> news:e$H8GRsjEHA.156@.TK2MSFTNGP10.phx.gbl...
> >> > I had to stop the RS service on our server. It's creating 30 megabyte
> > log
> >> > files, once every minute. I can still serve up reports evidently with
> > the
> >> > ReportServer service stopped in Control Panel, but I doubt I can
> >> > publish
> >> > reports.
> >> >
> >> > If I start the service, the log files immediately start to pile up.
> >> >
> >> > Any ideas? Any way to just stop logging?
> >> >
> >> > Jeff
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yes, the hotfix is now available although the KB article is not finished.
Ask product support for the fix associated with KB 885286.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shawn Hansen" <shansen@.lrs.com> wrote in message
news:%23oPKgdomEHA.1712@.TK2MSFTNGP09.phx.gbl...
> Can you tell me if there's been any progress on the hotfix and/or the KB
> article related to this issue?
> Thanks,
> Shawn
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ONXc$UFlEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Microsoft Product Support Services. See
>> http://support.microsoft.com/default.aspx?pr=cntactms&style=home. We are
>> working on the fix and should have a KB article number by the end of this
>> week.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Armin Mueller" <armin.mueller@.cosmicos.de> wrote in message
>> news:%23zEQTD$kEHA.3432@.TK2MSFTNGP14.phx.gbl...
>> > Hi!
>> >
>> > Nice to hear, as we have the same problem.
>> >
>> > Would you be so kind to tell me what PSS is and how to contact them?
>> >
>> > Armin Mueller
>> >
>> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
>> > news:erEFxDtjEHA.596@.TK2MSFTNGP11.phx.gbl...
>> >> You should contact PSS for a fix for this issue. Restarting the
> service
>> >> should make the problem go away but it could come back at anytime.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >>
>> >> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
>> >> news:e$H8GRsjEHA.156@.TK2MSFTNGP10.phx.gbl...
>> >> > I had to stop the RS service on our server. It's creating 30
>> >> > megabyte
>> > log
>> >> > files, once every minute. I can still serve up reports evidently
>> >> > with
>> > the
>> >> > ReportServer service stopped in Control Panel, but I doubt I can
>> >> > publish
>> >> > reports.
>> >> >
>> >> > If I start the service, the log files immediately start to pile up.
>> >> >
>> >> > Any ideas? Any way to just stop logging?
>> >> >
>> >> > Jeff
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Monday, March 19, 2012

Monitoring service broker queues through a .NET process

Is there a way for a .NET application to receive a notification when a service broker queue has been updated with a new message? I tried using SqlDependency on an SB queue but I got an "invalid" error in my notification handler.

Such a notification would be much better than having to poll the queue every N seconds.

Thanks

Issue a WAITFOR(RECEIVE.. ) on the queue with no timeout? Make sure you set the CommandTimeout on the SqlCommand to infinite.|||I will try that. Thanks very much

Monitoring service broker

Hi!
We have next problem in production.
We have one target that accepts messages from many initiators. Some time target can not get message from some initiator. It may be due hardware problem, for example technitian switch off router. In general service broker succeed to receive all messages when hardware problem was solved (if it takes less than retantion time on initiator side). I would like to build some job that would look at queue of target and could say (or trigger) that from the initiator A for some period of time target did not get any message or did not succeed to close conversation or it constantly have some connectivity problem (like duplicated message).

Any idea?

There are various traces you can use that indicate potential problems, like Message Drop, Message Classify with the subclass Delayed or Connection with the subclass Closing (unless is a ordinary timeout close due to lack of activity). Using the sp_trace_... procedure(s) some automation can be built around them.

An easier solution is to monitor the sys.transmission_queue on all parties involved. If messages are building up, is an indication of a problem that needs to be investigated.

Monitoring Queue status

Hi,

I'm new to the service broker service. All I want to do is to monitor the queue status. If the queue is disabled, send me an email alert.

Can you let me know what's the best way to accomplish it?

Thanks,

Jia

if ((select is_receive_enabled from sys.service_queues where name = 'PublisherQueue ') = 0)

begin

execute msdb.dbo.sp_send_dbmail 'MyDBMail', 'me@.abc.com', @.subject = 'queue is down'

end

|||

You could use SQL Trace Events to do this for you. Here are the steps... you can lookup Books Online for exact syntax on what statements you need to use to achieve this:

1. Create a Service that will get notified whenever your queue gets disabled. Make sure that the service implements the PostEventNotificationContract (the actual name is a big URL I haven't memorized... look at sys.service_message_types).

2. Write a service program (as a stored proc) for the above service that accepts event notification messages from the service queue, parses the required information and sends e-mail via DBMail.

3. Create an event notification for QUEUE_DISABLED_EVENT for your user queue.

When your user queue gets disabled, the event service will receive an event message. Activation will launch your service program which will send you an e-mail.

|||Thanks Rushi!

Wednesday, March 7, 2012

Monitor service account logins

Hello,
I've been asked to create a method of alerting if the account that the
MSSQLSERVER service runs under is used to attempt to log into the
database from another workstation. Here's what I've thought of so far:
OPTION 1: Monitoring for all login attempts. I don't want to do this as
we get about 200,000 logins an hour ( a seperate issue).
OPTION 2: I could do it via a server side trace but I'm not sure how I
could get an alert out of this without reading the trace file every
couple of minutes.
OPTION 3: I could run a query every minute that queries the
sysprocesses table but this leaves the possibility of sessions that
last under a minute not being picked up.
Can anyone think of a more elegant way of doing this? I'm leaning
towards option 2.
Cheers
DaveHello lightning-dave,

> I've been asked to create a method of alerting if the account that the
> MSSQLSERVER service runs under is used to attempt to log into the
> database from another workstation. Here's what I've thought of so far:
> OPTION 2: I could do it via a server side trace but I'm not sure how I
> could get an alert out of this without reading the trace file every
> couple of minutes.
If you are running SQL 2005, you might want to give this [0] a read and
see
if it useful in this case. Instead of having the event write to an output
table, you could have send an email, alert, whatever.
http://www.sqljunkies.com/WebLog/kt...ifications.aspx
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for the link. Unfortunately we're still on SQL2K - should have
mentioned that on my first message.
Kent Tegels wrote:
> Hello lightning-dave,
>
> If you are running SQL 2005, you might want to give this [0] a read an
d see
> if it useful in this case. Instead of having the event write to an output
> table, you could have send an email, alert, whatever.
> http://www.sqljunkies.com/WebLog/kt...ifications.aspx
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/

Monday, February 20, 2012

MOM 2005 Reporting

I'm trying to install the Mom Reporting service, My MOM 2005 (W2k3 w/sp1)
server also hosts the sql database (sql2000 w/sp3). While running the
install for the reporting services I get this error.
Failed to create data source for data warehouse, check to make sure you can
access the SQL services reporting server. (This is also hosted locally on
the mom server). Error code 2147467259.
Any one have any clues? I am NOT installing the report services to the same
folder as the mom2005 folder per microsoft. Any suggestions or guidance is
greatly appreciated.
Vid
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Check your system requirements! 2003 SP1 is not mentioned, only 2003 RTM.
I had to:
1. uninstall SP1.
2. uninstall MOM web console
3. uninstall SRS
4. uninstall Application Services (IIS, ASP, etc.)
5. reinstall Application Services
6. reinstall MOM web console
7. reinstall SRS
8. reinstall MOM reporting services
Early indications are that this seems to have corrected the problem. Your
mileage may vary.
"dave@.xrxdc.com" wrote:
> I'm trying to install the Mom Reporting service, My MOM 2005 (W2k3 w/sp1)
> server also hosts the sql database (sql2000 w/sp3). While running the
> install for the reporting services I get this error.
> Failed to create data source for data warehouse, check to make sure you can
> access the SQL services reporting server. (This is also hosted locally on
> the mom server). Error code 2147467259.
> Any one have any clues? I am NOT installing the report services to the same
> folder as the mom2005 folder per microsoft. Any suggestions or guidance is
> greatly appreciated.
> Vid
> --== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
>|||Were you able to successfully install the MOM database itself? MOM is rather
picky when it comes to the order in which things are installed...
(1) Install MOM database after installing SQL 2000 w/SP3a
(2) Install MOM application
(3) Install SQL Reporting
As with previous reply - your mileage will vary.
"JerryW" wrote:
> Check your system requirements! 2003 SP1 is not mentioned, only 2003 RTM.
> I had to:
> 1. uninstall SP1.
> 2. uninstall MOM web console
> 3. uninstall SRS
> 4. uninstall Application Services (IIS, ASP, etc.)
> 5. reinstall Application Services
> 6. reinstall MOM web console
> 7. reinstall SRS
> 8. reinstall MOM reporting services
> Early indications are that this seems to have corrected the problem. Your
> mileage may vary.
>
> --
> "dave@.xrxdc.com" wrote:
> > I'm trying to install the Mom Reporting service, My MOM 2005 (W2k3 w/sp1)
> > server also hosts the sql database (sql2000 w/sp3). While running the
> > install for the reporting services I get this error.
> > Failed to create data source for data warehouse, check to make sure you can
> > access the SQL services reporting server. (This is also hosted locally on
> > the mom server). Error code 2147467259.
> >
> > Any one have any clues? I am NOT installing the report services to the same
> > folder as the mom2005 folder per microsoft. Any suggestions or guidance is
> > greatly appreciated.
> >
> > Vid
> >
> > --== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
> > http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
> > --= East and West-Coast Server Farms - Total Privacy via Encryption =--
> >