Solved

sql server xp_sendmail and xp_readmail aren't working.

Posted on 2004-09-28
27
2,003 Views
Last Modified: 2008-01-09
Hello forum.

Once again I have MS mistery to deal with.

I'm trying to user xp_sendmail adn xp_readmail to send exit status or to trigger execution of DTS packages.

Did all the necessary steps - created the SQLSERVER domain user, exchange e-mail box for the user, reconfigured sql server and sql server agent processes to start as Administrator account, granted all the necessary privileges to send and read the e-mails as SQLSERVER user to Administrator account.

So now I can. Open MS Outlook and receive the SQLSERVER e-mails with outlook. Start MS SQL Server Enterprise console get to Support Services of the server, bring up SQLMail properties and successfully start/stop with set MAPI profile.....

But if open the SQL query analyzer - and run xp_sendmail  I would be getting the error :
    "xp_sendmail: failed with mail error 0x80004005"

If I try to run xp_findnextmsg  I still get :
    "xp_findnextmsg: failed with mail error 0x80004005"

To be honest I already deployed other package which successfully sends out smtp messages, but since I have to receive messages I'm still trying to make SQL Mail work....


Here is the question - do we have any substitute to xp_readmail... I there is none what should I do to make MS thing work.


Thanks a lot in advance.

Yuri.
0
Comment
Question by:ygelman
  • 10
  • 8
  • 5
  • +3
27 Comments
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12175727
Does the exchange e-mail box for the user have send on the behalf of permission?
Also are you logged in as the service account when you are doing your testing?
0
 
LVL 2

Expert Comment

by:doswell
ID: 12176283
Log onto the SQL Server machine as the service account SQL is running under,
verify that outlook opens and the account is setup fine.
Open enterprise manager and verify the profile is set correctly.

If you do this remotely and under a different user it may not setup correctly since enterprise manager grabs hte mapi profiles of the machine it is run from. The best way to ensure it gets setup without problems is to do it logged in as the SQL account on the SQL server.
(It just isn't clear to me if that is what you did from your explanation)
0
 
LVL 4

Expert Comment

by:roshkm
ID: 12179673
How to Use CDO for Microsoft Windows NT Server (CDONTS) for sending email from SQL Server:

http://support.microsoft.com/view/tn.asp?kb=312839

May be the site will be of some help by manipulating it to recv email.

Regards,
RKM
0
 
LVL 2

Accepted Solution

by:
stockcowboy earned 250 total points
ID: 12179801
SQL mail can be a pain - another way is to use CDO/ADO in exchange.
you've already got sending email.

you can read email like this from an vb script


    Dim Rec As ADODB.Record
    Dim Rs As ADODB.Recordset
   
    Set Rec = New ADODB.Record
    Set Rs = New ADODB.Recordset
    Rec.Open "http://fileserver3/public/abuse-aol/"
    strQ = " SELECT ""urn:schemas:httpmail:fromemail"", ""DAV:href"", ""urn:schemas:httpmail:textdescription"", ""DAV:displayname"" "
    strQ = strQ & " FROM ""http://fileserver3/public/abuse-aol/"" "
    strQ = strQ & " WHERE ""DAV:isfolder"" = False "
    Rs.Open strQ, Rec.ActiveConnection
   
    Do While Not Rs.EOF
       
        Rs("DAV:href").Value                                                'URL OF EMAIL
            Rs("urn:schemas:httpmail:fromemail").Value                  'FROM ADDRESS
            Rs("urn:schemas:httpmail:textdescription").Value      'MESSAGE BODY
         
        Rs.MoveNext
    Loop

    Rs.Close
    Rec.Close

    Set Rec = Nothing
    Set Rs = Nothing
0
 
LVL 5

Assisted Solution

by:MichaelSFuller
MichaelSFuller earned 150 total points
ID: 12181771
Exchange 2000 introduces a different security structure, Access Control
List (ACL), because it is completely integrated into the Microsoft
Windows 2000 Active Directory. This new ACL method requires permission
for "Send As" to exist both at the Exchange 2000 Information Store level
as well as the Active Directory level. In this case, the SQL Server
service account does not have "Send As" rights at the Active Directory
level, therefore xp_sendmail fails on the submit of a message.
WORKAROUND
To make sure that the Active Directory account the MSSQLServer service
is logging in as has "Send As" rights on the Exchange 2000 mailbox
object in the Active Directory, follow these steps:
Open the Active Directory Users and Computers Snap-In.
Locate the user object directly associated with the Exchange 2000
mailbox and select it.
Right-click to get the properties for this account or mailbox object.
Click the Security tab. (If the Security tab is not showing, select
Advanced Features from the View menu; this will show the Security tab.)
In the Security dialog box, click Advanced.
In the Access Control Settings for XXX dialog box, click ADD.
Locate, and then select the Active Directory account that the
MSSQLServer service is logging in as.
Make sure that the Allow Onto list box is set to This object and all
child objects.
In the Permission Entry for XXX dialog box, scroll down in the
Permission pane, and under the Allow column, select Send As.
Press OK three (3) times to apply the change on all the open dialog
boxes.
This change may take some time to replicate through the Active
Directory.
0
 
LVL 1

Author Comment

by:ygelman
ID: 12182669
As I told the account under which SQLServer service running is domain Administrator account.
Yes I already gave "Send As" permissions to Administrator. It is still not working.

As for the solution to use CDO/ADO in Exchange how do I open recordset if my server name is "ExchangeSFO"?

Thanks,
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12182696
In Active directory or exchange?
0
 
LVL 1

Author Comment

by:ygelman
ID: 12182755
In AD
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12182808
1.) Stop and start SQL Agent on effected server
2.) Test with xp_sendmail (we have found that stopping and starting sql
agent by itself usualy does not work), if still broken, go to step 3.
3.) Issue xp_stopmail from query analyzer
4.) Issue xp_startmail from query analyzer
5.) Test with xp_sendmail once again (we have always received a good mail
delivery with no errors at this point).
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12182891
0
 
LVL 1

Author Comment

by:ygelman
ID: 12182950
Stockcowboy,

I'd like to ask you more about CDO/ADO solution you proposed.
When I open recordset to access particular exchange server - what is the url I have to specify to get to specific mailbox?

Let's say that server name is "SFOExchange" and email box is "SQLSERVER" what the path will look like
Rec.Open "http://SFOExchange/...?

Thanks in advance.
0
 
LVL 1

Author Comment

by:ygelman
ID: 12183044
Michael
I went trhough your list,
1. Stopped and started SQL Agent.
2. Tested xp_sendmail again. Result is the same - "xp_sendmail: failed with mail error 0x80004005"
3. Executed xp_stopmail. Went through.
4. Execution of xp_startmail thrown an error "xp_startmail: failed with mail error 0x8004011d". Though before throwing error output was "SQL Mail session started."
5. Obviously execution of xp_sendmail again gave an error "xp_sendmail: failed with mail error 0x80004005"

What do you think?
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12183222
Who are you logged on as in query analyzer? Is Outlook open? Also is there more then one profile on the system for the mail client?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:ygelman
ID: 12183323
I logged as master.
Outlook is closed.
There is one profile  on the system for the mail client.

0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12183386
You need to be logged on as the service account with sa rights.

The only other time I have seen this issue is when the mailbox permissions are not correct.
If all is correct try creating a new account, and follow exactly what I said before. Make sure both SQL Server and the Agent are running under that security context.

Then log onto the SQL server from QA using that account, not a different one.


0
 
LVL 2

Expert Comment

by:doswell
ID: 12190334
ygelman - does outlook work/connect to the mailbox when logged onto the server(locally) with the SQL service account?
0
 
LVL 1

Author Comment

by:ygelman
ID: 12194729
Yes it does.
Actually I log in to the machine is domain Administrator account and then open Outlook I can read the all the e-mails that I send to specific e-mail box.

Any ideas?

0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12196502
Why is your service account logging in as the Administrator account?
0
 
LVL 2

Expert Comment

by:doswell
ID: 12201692
yes, it's best security practice to set SQL server up with it's own domain account.
You can then make that user a local admin on the server if you wish.
Then give that SQL Service Account access to the mailbox you require to use, and setup outlook accordingly.

I've found with exchange this is easier, since the domain admin is typically actually denied various bits of access within exchange (ie accessing other mailboxes by default)
0
 
LVL 1

Author Comment

by:ygelman
ID: 12202369
I'm logging the services as an Administrator just for testing purposes - since it has all the permissions.

0
 
LVL 2

Expert Comment

by:doswell
ID: 12202638
It's best to test with how the setup will be in the end.
0
 
LVL 1

Author Comment

by:ygelman
ID: 12202837
Guys - right now I can't make it working. Once it will be working under one account I will make it work under others.

Do you think I have a chance to make it working?
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 12203354
"Guys - right now I can't make it working. Once it will be working under one account I will make it work under others."
Doesn't make sinces--start with the account you're going to use in the end.  Using an administrator account can make things work that might not normally work due to permissions....


Here is a link on proper SQL Mail configuration--please doublecheck your settings:

http://support.microsoft.com/default.aspx?scid=kb;en-us;263556&Product=sql2k


Also, this expands more on how to use CDOSYS:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839

Brett
0
 
LVL 2

Expert Comment

by:doswell
ID: 12203723
A member of the Domain Admin group within an exchange environment will have some permissions DENIED IE opening another users mailboxes. This is a security thing.
If you're doing testing on setting something up, doing it within an account that has FULL permissions is the worst way to do it. What will it accomplish when you switch to using a normal user and encounter permission problems?? You just end up wasting time testing something.

Why not spend time and have it work under the appropriate account.
0
 
LVL 1

Author Comment

by:ygelman
ID: 12203737
Guys - sorry for confusing you with a lot of details.

Here is the thing I'm trying to achieve - I already can send e-mails, it is not a problem. I need to RECEIVE EMAILS. I have to be able to send the e-mail for executing SQL server task.

That is a reason why I started to use xp_sendmail at all..

If you thing the same task can be acheived by something else. Be my guest I'm really opened for propositions.


Sorry for confusion - English is my second language.

Thanks,
Yuri.
0
 
LVL 2

Expert Comment

by:stockcowboy
ID: 12204843
0
 
LVL 1

Author Comment

by:ygelman
ID: 12204935
Cool - thank you everybody.

Let's try to do it other way....
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now