Cannot enable Database Mail XP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Cannot enable Database Mail XP

Author

Topic

Kristen
Test

22859 Posts

Posted - 2013-11-22 : 03:46:20

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"

Looking in the error logs I can clearly see:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
(so it was already on)
Configuration option 'Database Mail XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
(so it was already on too)
FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
(which is what I believe is logged when doing RECONFIGURE)
and then that is followed by the above error.

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?

Any ideas?

Thanks

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 06:11:20

first run this


EXEC sp_configure 'Database Mail XPs'
GO

this will return 1 for run_value. if not you need to configure it as below


EXEC sp_configure 'Database Mail XPs',1
GO
RECONFIGURE WITH OVERRIDE
GO

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-11-22 : 06:19:35

Thanks Visakh. Already set, as best as I can tell:


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 :(

Kristen
Test

22859 Posts

Posted - 2013-11-25 : 10:16:41

I have found an answer. UNsetting the Database Mail XPs Config flag and re-setting it seems to have cured the problem, i.e.


EXEC sp_configure 'Database Mail XPs', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Only downside with this is that the first step, un-setting the flag, took 7 minutes to run and all users connected to any [or so it seems] database on that server lost their connection. Anyone following may want to run that in scheduled downtime in case that may 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.

However, after re-setting the flag DB Mail is now working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources