January 10, 2008 at 1:19 pm
We have an email notification system for SQL Server 2000 that used XPSMTP and standardized steps within each job to handle staff notification in the event of a job failure. In other words, when a failure occurs in a job, the step directs the flow to a standard job number which executes the stored procedures that send out email notifications.
I'm in the process of porting this functionality over to SQL Server 2005, but I'm running into some problems with configuring Database Mail and getting the SQL Agent service to successfully send an email in the event of a job failure.
We briefly considered switching over to SQL Agent's native notification in the event of a job failure now that it uses SMTP, but we have a need to send emails in different formats to different recipients for each failure event, and I didn't see a way that it would meet our requirement.
So, I converted the xpsmtp stored procedure calls in our job to now use Database Mail's stored procedures. I enabled Database Mail, ran a successful email test, and set up an account and private profile for the SQL Agent user. However, when I run a test of sending an email from within the job, I receive the following error message:
Executed as domain\SQLAgentUser. No global profile is configured. Specify a profile name in the @profile_name parameter. [SQLSTATE 42000] (Error 14636). The step failed.
I found these instructions, Using Database Mail with SQL Server Agent:
http://msdn2.microsoft.com/en-us/library/ms190606.aspx
So, I've got a couple questions. In the document I've linked, what does step 4, 'Set the profile as the default profile for the msdb database' mean? A database is not a principal, so how can you give it a profile? Do they mean set the profile as the default profile for the public role on msdb database? Am I not able to assign a private profile to the SQL Agent user and have that work instead?
I can get my process to work if I either create a default public profile, or by changing my executions of the sp_send_dbmail stored procedure to include the @profile_name in each job, but I don't consider either option to be ideal. For the default public profile, eventually I may want to give other users access to send email, and I may want to make sure they use their own profile, and NOT a default public profile. As for the second option, I don't want to have to add @profile_name to all of my jobs, plus, some of our users in the test environment can view the job details, but I don't necessarily want them to know the profile names which they could easily see in the job step command.
Does anyone have any suggestions that I may have missed? Arguments for a configuration I may have already rejected?
February 5, 2008 at 2:41 pm
Despite my testing, I could not get a private profile to work for the SQL Agent user, especially with the other security we have setup for that user. The only way I could get it to work was to set the default profile for the public role.
In the end I decided to create a profile and call it from sp_send_dbmail by using the @profile_name parameter. It meant modifying the send email steps in my jobs.
June 26, 2008 at 10:32 am
As soon as I got "1" when this query ran
Email started working:
SELECT TOP (1) pp.profile_id
FROM msdb.dbo.sysmail_principalprofile as pp
WHERE (pp.is_default = 1)
AND (dbo.get_principal_id(pp.principal_sid) = @database_user_id OR pp.principal_sid = 0x00)
ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
June 26, 2008 at 1:48 pm
Thanks for the query. Part of the issue is that the SQL Agent user I use is a sysadmin, and so I don't/haven't defined it as a database principal in msdb. Since I built some code to dynamically generate my error handling steps for the sql jobs, declaring the profile name doesn't seem that big of a deal anymore.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply