November 24, 2013 at 10:10 am
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)
November 25, 2013 at 2:03 am
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
November 25, 2013 at 2:55 am
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
[font="Courier New"]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.[/font]
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
November 25, 2013 at 2:58 am
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
November 25, 2013 at 3:38 am
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.
November 25, 2013 at 3:57 am
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
November 25, 2013 at 6:23 am
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
November 25, 2013 at 7:23 am
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.
November 25, 2013 at 8:19 am
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.
November 25, 2013 at 8:40 am
Kristen-173977 (11/25/2013)
Thanks, you've saved me days of messing around looking for answers.
Glad you've got it solved!
June 9, 2015 at 5:29 am
Try below script
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
June 23, 2016 at 2:37 am
This works but I faced another issue:
No global profile is configured. Specify a profile name in the @profile_name parameter.
June 23, 2016 at 2:57 pm
durga.palepu (6/23/2016)
This works but I faced another issue:No global profile is configured. Specify a profile name in the @profile_name parameter.
just a simple internet search came up with the solution for this:
http://blog.sql-assistance.com/index.php/no-global-profile-is-configured
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply