Notification Alert System crash after any unrelated error of OLE DB initialization by ellight1

Active

Type:	Bug
ID:	775018
Opened:	12/22/2012 7:32:38 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

We using log error notification service: SQL Server Agent -> Alerts
Correspondingly logging errors (RAISERROR WITH LOG;) sends message by e-mail to admins

If anywhere, any provider with external context run fails on initialization, then alert system will fall off. And only system/service restart restores it.

At the same time other mechanisms, such as providers, e-mail etc. continues normal function (though some of them doesn't immediately, it depends)

Links for settings instructions:
http://support.microsoft.com/kb/2647989
http://msdn.microsoft.com/en-us/library/ms186358.aspx
http://msdn.microsoft.com/en-us/library/ms189982(v=sql.100).aspx

The use of an external context run is necessary due to problem providers with memory leaks and crashes (Oracle provider for example)

Details (expand)

English

SQL Server 2008 SP3

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Server 2008 R2 Enterprise (SP1)

English

-- Setting up and convincing that our user doesn't have rights for DCOM MSDAINITIALIZE
-- (script error will show about this)
USE [msdb]
-- Of course email account and profile need to be configured:
-- Management -> Database Mail -> Configure Database Mail -> ... (account) ... (profile)
-- And in agent properties need to be configured e-mail profile:
-- SQL Server Agent -> Property -> Alert System -> Mail session
GO
-- Configuring alert for error severity level 18 for mail sending
EXEC msdb.dbo.sp_add_alert @name = N'Alert (Severity 18 Error)' -- Possibly this alert exists, in this case you need to reconfigure it
    ,@message_id = 0 -- Event Alert
    ,@enabled = 1
    ,@severity = 18 -- RAISERROR(,18,) WITH LOG;
    ,@include_event_description_in = 1 -- Email
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Alert (Severity 18 Error)'
    ,@operator_name = N'Tester' -- Specify configured operator: SQL Server Agent -> Operators -> New Operator ...
    ,@notification_method = 1 -- Email
GO
USE [master]
GO
RAISERROR('Before',18,1)WITH LOG; -- Message will be sent to email configured in <Tester> operator
GO
-- Using Active Directory Provider as a sample
-- Configuring provider for external launch, which will be started through the DCOM MSDAINITIALIZE
EXEC master.dbo.sp_MSset_oledb_prop N'ADsDSOObject', N'AllowInProcess', 0
GO
-- Corresponding provider will failed to start and a error will appear
SELECT Top(10) * FROM OPENROWSET('ADSDSOObject','adsdatasource';'Login';'PassWord','
SELECT displayName FROM    ''LDAP://DC=my, DC=local, DC=domain'' WHERE objectCategory=''person''')
GO
RAISERROR('After',18,1)WITH LOG; -- This is the end, till service restart emails will not be
GO

Only one email and fail of email notification system

-- Query result
Msg 50000, Level 18, State 1, Line 1
Before

Msg 17054, Level 16, State 1, Line 1
The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.

Msg 50000, Level 18, State 1, Line 1
After

Two received alert emails and continue of working of email notification system

-- Query result is the same

X64

0 attachments

Microsoft