Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot enable Database Mail XP Expand / Collapse
Author
Message
Posted Sunday, November 24, 2013 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 11:18 AM
Points: 6, Visits: 68
I'm trying to turn on Database Mail.

I set the flag in db_config, did the reconfigure, and for good measure stop/started SQL Service (and thereby SQL Agent service too)

If I try to send a test email I get

"SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online"

EXEC sp_configure 'Database Mail XPs'
GO

gives
name              minimum     maximum     config_value run_value   
----------------- ----------- ----------- ------------ -----------
Database Mail XPs 0 1 1 1

I also, latterly, set "SQL Mail XPs", but that hasn't made any difference either

From everything I have Googled I have not seen anything that says that this error message is caused by anything else (for example that the EMail Profile is not configured properly - I think it is, but it might not be of course!)

I haven't rebooted server, but that shouldn't be necessary should it?

My problem looks very similar to this post:
www.sqlservercentral.com/Forums/Topic776651-1550-1.aspx
in that instance the O/P reinstalled SQL Server to fix it. I'd like to avoid that if possible.

Thanks

@@VERSION =
Microsoft SQL Server 2008 (SP2) - 10.0.4067.0 (X64)

(Not the latest SP, I'm arranging for that to be installed in case that brings a solution, but that needs scheduled downtime which will be a while obtaining)
Post #1517100
Posted Monday, November 25, 2013 2:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 2,231, Visits: 2,668
Does the account you are using have enough permissions to use Database Mail? If possible, please try to send a mail using a sysadmin account.

Use (parts of) the code below to get configurion settings regarding Database Mail:
USE msdb ;
GO

-- show information about several Database Mail related settings
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' -- value 1 indicates enabled

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' --- check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp -- lists information about associations between Database Mail profiles and database principals

EXECUTE dbo.sysmail_help_status_sp ; --- check status of Database Mail

--EXECUTE dbo.sysmail_start_sp --- start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail' -- list the state of the mail or status queues


-- show a list of all mail-items send past week
SELECT
sysmail_allitems.mailitem_id
, sent_status
, recipients
, subject, body
, send_request_date
, send_request_user
, sent_date
, sysmail_allitems.last_mod_date
, sysmail_event_log.event_type
, sysmail_event_log.description
FROM msdb.dbo.sysmail_allitems
LEFT OUTER JOIN msdb.dbo.sysmail_event_log
ON sysmail_allitems.mailitem_id = sysmail_event_log.mailitem_id
where send_request_date > dateadd(dd, -7, getdate())
and sent_status = 'failed'
order by
send_request_date desc



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1517174
Posted Monday, November 25, 2013 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 11:18 AM
Points: 6, Visits: 68
HanShi (11/25/2013)
Does the account you are using have enough permissions to use Database Mail? If possible, please try to send a mail using a sysadmin account.


Thanks for your help.

My login is sysadmin, so unless I have done something goofy I should be OK on that.

is_broker_enable = 1 (in MSDB)

check members of the DatabaseMailUserRole
DbRole               MemberName                  MemberSID
-------------------- --------------------------- ------------------------------------------------------------------
DatabaseMailUserRole Certificate_UseDBMail_Login 0x010600000000000901000000F605E188C86F4F59B7AF1E2856BCC3A33C3B7AF7


I don't know how to interpret this data.

lists information about associations between Database Mail profiles and database principals
principal_id principal_name profile_id  profile_name is_default 
------------ -------------- ----------- ------------ ----------
2 guest 1 Default 1


check status of Database Mail

SQL Server blocked access to procedure 'dbo.sysmail_help_status_sp' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

EXECUTE dbo.sysmail_start_sp --- start Database Mail in a mail host database

Same error message

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail' -- list the state of the mail or status queues
queue_type length state    last_empty_rowset_time  last_activated_time
---------- ------ -------- ----------------------- -----------------------
mail 0 INACTIVE 2013-11-24 22:41:15.300 2013-11-24 22:41:15.300

Time here now (GetDate() ) is 2013-11-25 09:51:48.113. Colleague of mine rebooted the server last night, and re-tested DB Mail then, so that time probably reflects that action

-- show a list of all mail-items send past week

(0 row(s) affected) - No results displayed

Post #1517181
Posted Monday, November 25, 2013 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 11:18 AM
Points: 6, Visits: 68
P.S. Rechecked that Database Mail XPs is on, after the reboot. It seems to be:
name              minimum     maximum     config_value run_value
----------------- ----------- ----------- ------------ ---------
Database Mail XPs 0 1 1 1

Post #1517182
Posted Monday, November 25, 2013 3:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 2,231, Visits: 2,668
Open SSMS and navigate to the Management subtree.
Right click "Database Mail" and select "Configure Database Mail".
Click [next] on the (optional) welcome screen.
Make sure the option "Set up database mail by performing the following tasks" is set and click [next]
-- You should recieve the message/question "The Database Mail feature is not available. Would you like to enable this feature?"
Click [yes] in respons to above question.

This should enable the mail feature. You can test this by executing (in another query window) the code "exec sysmail_help_status_sp". This code shouldn't throw an error anymore.

Finish the Database Mail configuration wizard by entering the Profile and Account details.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1517187
Posted Monday, November 25, 2013 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 11:18 AM
Points: 6, Visits: 68
HanShi (11/25/2013)
Open SSMS and navigate to the Management subtree.
Right click "Database Mail" and select "Configure Database Mail".
Click [next] on the (optional) welcome screen.
Make sure the option "Set up database mail by performing the following tasks" is set and click [next]
-- You should recieve the message/question "The Database Mail feature is not available. Would you like to enable this feature?"
Click [yes] in respons to above question.


After the Welcome Screen I get the "New Profile" form. Although I have already set one up I set up a new TEST entry, and added a Mail account to the profile.

After that the "Configure System Parameters" page was displayed. This has the same values as if I use the "View / Change system parameters" on the initial menu page, I didn't adjust anything.

Then finally the "Summary" page was disabled.

There wasn't any prompt for "The Database Mail feature is not available. Would you like to enable this feature?"

sysmail_help_status_sp still gives same error message
Post #1517189
Posted Monday, November 25, 2013 6:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 2,231, Visits: 2,668
I'm sorry, i am running out of suggestions. Maybe someone else can shed some light?

What if you reset the Mail XP's?
EXEC sp_configure 'Database Mail XPs', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1517220
Posted Monday, November 25, 2013 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 11:18 AM
Points: 6, Visits: 68
Sorted!

I'm surprised I hadn't already tried that as its just the sort of cover-your-front,back-and-both-sides belt&braces that I normally go in for!

Reset back to zero and reconfigure took 7 minutes to complete. I have absolutely no idea what it was up to ... but after setting back to 1 and reconfigur'ing a test sp_send_dbmail worked straight away.

Off to check now that it is indeed really fixed, rather than just pretending to be fixed!

Thanks, you've saved me days of messing around looking for answers.
Post #1517252
Posted Monday, November 25, 2013 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 11:18 AM
Points: 6, Visits: 68
Follow up:

It turns out that the first step, un-setting the flag, took 7 minutes to run and a side effect was that all users connected to any [or so it seems] database on that server lost their connection.

For anyone else who tries this you may want to run that in scheduled downtime in case the effect I had will always be a side effect. Its just possible that it was the need to stop/start the Broker service and that that needed a lock on SQL Agent, so perhaps manually stopping SQL Agent first might speed up that step.
Post #1517286
Posted Monday, November 25, 2013 8:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 2,231, Visits: 2,668
Kristen-173977 (11/25/2013)

Thanks, you've saved me days of messing around looking for answers.

Glad you've got it solved!


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1517298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse