August 25, 2009 at 7:18 am
I have been trying to set up our initial SQL Server 2008 box and am running into issues with database mail.
I can configure database mail without issue but when I try to send a test email I get this error:
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.
The thing is, I have already enabled 'Database Mail XPs' with these commands:
sp_configure 'show advanced options',1
go
RECONFIGURE
go
sp_configure 'Database Mail XPs',1
go
RECONFIGURE
go
Print 'Script Complete'
Running sp_configure shows that 'Database Mail XPs' run_value and config_value are indeed 1.
I have tried creating the profile again, restarted services, and restarted the server to no avail.
I always used the same scripts (above and below) to enable and configure Database Mail on all of our 2005 SQL Servers without an issue. I have also even tried just using the wizard to create the profiles. Not that I think the profile has anything to do with it given the error thrown.
declare @varDisplayName nvarchar(128)
declare @varSMTPServer nvarchar(128)
------- UPDATE THE FOLLOWING DATA -------
set @varSMTPServer = 'mail.mydomain.com'
set @varDisplayName = 'SQL Service (SQL04)'
------- END UPDATING SECTION -------
declare @varAcctName nvarchar(128)
declare @varAccountDescription nvarchar(256)
declare @varProfileName nvarchar(128)
declare @varProfileDescription nvarchar(256)
declare @varProfileSequence int
declare @varEmailAddress nvarchar(128)
declare @varIsDefault bit
set @varAcctName = 'SQLService'
set @varAccountDescription = 'Default mail account for e-mail.'
set @varProfileName = 'SQLService'
set @varProfileDescription = 'Default mail account for e-mail.'
set @varProfileSequence = 1
set @varEmailAddress = 'SQLService@MyDomain.com'
set @varIsDefault = 1
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @varAcctName,
@description = @varAccountDescription,
@email_address = @varEmailAddress,
@display_name = @varDisplayName,
@mailserver_name = @varSMTPServer;
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @varProfileName,
@description = @varProfileName;
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @varProfileName,
@account_name = @varAcctName,
@sequence_number = @varProfileSequence ;
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public',
@profile_name = @varProfileName,
@is_default = @varIsDefault;
Any advice would be greatly appreciated.
August 25, 2009 at 9:29 pm
Have you looked over the profile and accounts through SSMS after running that script? Are you able to send a test email through the GUI and only having problems with the SP?
When I get to the office tomorrow, I will try to post the script I use for 2008 to enable the SP's and send a test email.
Steve
August 26, 2009 at 7:17 am
I have tried it via the GUI as well and get the same error.
August 26, 2009 at 8:35 am
This is the code that I use- certainly you'll need to update to your smtp server and recipient address for the test email.
/* FIND AND REPLACE
my.smtp.com with the smtp server email will send from
to verify that the stored proecures and database mail was set up successfully.
*/
/*****
This script will set up database mail with a default profile and send a test email to verify
that it is successfully working so that alerts can be effectively set up.
*****/
/****** Enable DB Mail stored procedures ******/
USE [master]
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
/****** Create Database Mail Objects ******/
USE msdb
GO
DECLARE @test_email_recipient VARCHAR(255)
SET @test_email_recipient = 'myemail@test.com'
DECLARE @ProfileName VARCHAR(255),
@ProfileDescription VARCHAR(255),
@AccountName VARCHAR(255),
@AccountDescription VARCHAR(255),
@SMTPAddress VARCHAR(255),
@EmailAddress VARCHAR(128),
@ReplyToAddress VARCHAR(255),
@DisplayUser VARCHAR(128)
SET @ProfileName ='SQL_DBA_Profile';
SET @ProfileDescription ='Default DB Mail profile created by the SQL DBA team.';
SET @AccountName ='SQL Server DBA Account';
SET @AccountDescription ='SQL DBA Default Account to send mail.'
SET @SMTPAddress ='my.smtp.com';
SET @EmailAddress =LTRIM(RTRIM(REPLACE(@@servername, '\','_')))+'@mydomain.com';
SET @ReplyToAddress ='myemail@test.com';
SET @DisplayUser ='SQLServer';
/****** Clean up Profile and Account if it already exists ******/
IF EXISTS
(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
p.name = @ProfileName AND
a.name = @AccountName
)
BEGIN
PRINT 'Deleting Profile Account'
EXECUTE sysmail_delete_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName
END
IF EXISTS
(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName
)
BEGIN
PRINT 'Deleting Profile.'
EXECUTE sysmail_delete_profile_sp
@profile_name = @ProfileName
END
IF EXISTS
(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName
)
BEGIN
PRINT 'Deleting Account.'
EXECUTE sysmail_delete_account_sp
@account_name = @AccountName
END
/****** End Cleanup ******/
/****** Create the Accounts and Profile ******/
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@description = @AccountDescription,
@email_address = @EmailAddress,
@replyto_address = @ReplyToAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName,
@description = @ProfileDescription
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;
/****** Send a Test Email *****/
EXECUTE msdb.dbo.sp_send_dbmail
@recipients=@test_email_recipient,
@body= 'Test email following DB mail implementation',
@subject = 'Test email following DB mail implementation',
@profile_name = 'SQL_DBA_Profile'
Hope that helps you potentially clean up your script or make changes.
At first glance, yours looks correct, but my guess there is something subtle that isn't working.
Best of luck,
Steve
August 27, 2009 at 3:17 pm
Tried your script and received the same result. It is just like the server isn't recognizing the flag is enabled for "Database Mail XPs"
August 27, 2009 at 9:02 pm
I would open a case with MS at this point.
September 3, 2009 at 9:17 am
Just as a follow up to close out this. I had uninstalled and reinstalled SQL2008 SP1 which gave me the same result. Finally I just uninstalled and reinstalled SQL Server 2008 and all is working fine this time.
Thanks for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply