SQLServerAgentMail error
-
I've setup SQL Mail and I'm trying to setup SQL Server Agent mail as well, but this does not work.
Both MSSQLServer and SQLServerAgent services run under the standard local Administrator account. A mailprofile and a postbox is created for this account, client is Outlook 2000, mailserver is Exchange 5.5. The mailaccount works fine, I can sent and receive mail.
When activating SQLMail, no problem. I can use xp_sendmail without a problem.
However, when trying to activate SQL Server Agent mail, after choosing the Exchange profile(the same 1 as for SQL Mail, there is only 1 profile) I receive a MAPI Logon failed:
Error 22022:SQL Server Agent error: MapiLogon Ex Failed due to MAPI error 273: MAPI Logon failed.
I can understand what this means:the Administrator account is not recognized as a legal login. Since both services run under this login, and this poses noprob for SQL Mail, it makes no sence to me. Obviously I am missing something, but I don't know what.
Can some1 enlighten me? I've googled the internet, but fount no similar problem mentioned and no suitable answer.
Greetz,
Hans Brouwer -
I've configured a pop3 mail account and recieve the same error when setting SQL Agent mail.
Read in some article, that the error is espected. I accepted the error and the setup of the mail, and when entering again the profile was setted ok. And notifications started to arrive.
-
Alas, tried it, but did not work. Question remains open.
Tnx for responding tho.
Greetz,
Hans Brouwer -
I don't know if this is the only way it can be done, but we do it this way and it works. The SQLServerAgent runs under its own user account which has an email box. This email profile is then set up on SQL Server, and once the profile is set up, you must go to three seperate places in SQL Server to assign that profile as the one to use.
If you're using Jobs, and it involves email, you should run it under the SQLServer Agent account.
Hope this helps.
-
Ronkyle,
Which 3 places are you referring to? I can think of SQLMail, SQLServerAgent, and no other. Do I miss something here?
Tnx for responding tho.
Greetz,
Hans Brouwer -
Go to SQL Server Properties (Configure), under the Server Settings tab there is a SQL Mail Portion.
-
I see what you mean. It is the same as properties under supportservices\sqlmail.
Tnx anyway.
Greetz,
Hans Brouwer -
Hans
We used to get the same issue. Usually you had to stop and start the MAPI service on the server to fix it. If you've just installed the mail stuff you need to stop and start the agent for it work sometimes.
WE got so fed up that I wrote a stored procedure and just added an extra step to any jobs etc. so that on failure or success it would send a mail using the stored procedure instead. The advantage being no outlook on your database server with all its nice security vulnerabilities.
If you're interested this is it.
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) = "SOMEONE@SOMEONE.COM",
@To varchar(100) ,
@cc varchar(300)= " ",
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@Attachment varchar(255) = " "
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
Comments are added to the stored procedure where necessary.
***********************************************************************/
AS
Declare @iMsg int
Declare @iBp int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'post.demon.co.uk'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Error handling.
IF @hr 0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
You can add extra bits for authentication etc. look on the the technet site under collaboration data objects for further info.
This version will only work on if SQL is intalled on windows server 2000 or 2003.
Dave
-
Tnx David, I'll be sure to look into your procedure. It's just that this problem nags me. I have written something like your sp on ad-hoc basis, though not as structured as yours, surely.
I indeed have tried restarting both the MAPIservice and SQLServerAgent. We even had the server rebooted, no dice. Istill would like to know what causes this and what solution(if any) is needed.
Tnx again
Greetz,
Hans Brouwer -
It has become more weird! I found an article with a possible solution, which simply comes down to accepting the (only) profile for the Microsoft Exchange profile, stopping and starting the SQLServerAgent service, after which it would work. It did not.
Now a new feature has arissen: the mailsession droplist, with which to set the mailprofile is gereyed out, and the TEST button as well! This means I cannot change the SQLServerMail settings anymore! Probably I can rectify this by changing the service under which the SWQLServerAgent runs, but still I wouild like to know what is causing this behaviour, and how to solve it.
TIA,
Greetz,
Hans Brouwer -
I had this problem and finally got thru it by adding the domain account used to start SQL Server and SQL Server Agent to the local administrators group, as well as configuring the mail profile in both profiles. After making the changes, all I did was stop and restart SQL Server Agent, and all was well.
-
doug Bak, thx for your idea of setting up mail in both profiles. Once i setup mail in the local admin account, I was able to configure SQL server Agent to send mail. Though the SQL server agent is still setup to run under a diff account.
-
Try starting the MSSQLServer and SQLAgentServices with the domain account and assign Local admin rights to that account on the box.
Log on to the server, with the account, you have started the services and configure the outlook profile there. Both SQL mail and Agentmail will work without any issues.
thanks
-
Just in case someone else runs into _this_ gem...
We too were getting MAPI Logon failed (error number 273).
It seems that some genius at Microsoft finds it necessary to truncate the pull-down menu (in Management\SQL Server Agent\Properties -> General[tab] -> Mail Session -> Mail profile:[pull-down list]) to 32 characters.
The default profile (at least for our installs) of Office 2000 Outlook is “Microsoft Outlook Internet Settings”. When I went to choose it from the list, all that showed was “Microsoft Outlook Internet Setti”. I didn't particularly pay much attention to the Microsoft pre-populated field, because how could it possibly be wrong, but SQL Server Agent Mail failed vigorously.
I manually typed the “ngs” into the field, and the test/Agent Mail worked!
Rage-on.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply