Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
14 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sql server xp_sendmail and xp_readmail aren't working.
Posted on 2004-09-28
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
-
8
-
5
- +3
27 Comments
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?
Also are you logged in as the service account when you are doing your testing?
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)
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)
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
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
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:fro
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:f
Rs("urn:schemas:httpmail:t
Rs.MoveNext
Loop
Rs.Close
Rec.Close
Set Rec = Nothing
Set Rs = Nothing
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:fro
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:f
Rs("urn:schemas:httpmail:t
Rs.MoveNext
Loop
Rs.Close
Rec.Close
Set Rec = Nothing
Set Rs = Nothing
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.
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.
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,
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,
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).
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).
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.
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.
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?
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?
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?
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.
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.
ygelman - does outlook work/connect to the mailbox when logged onto the server(locally) with the SQL service account?
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?
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?
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)
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)
I'm logging the services as an Administrator just for testing purposes - since it has all the permissions.
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?
Do you think I have a chance to make it working?
"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
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
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.
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.
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.
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.
Sorry - I haven't had time to check it - did you get it to work?
check out this URL for accessing any exchange box via HTTP/ADO
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/e2k3/e2k3/_exch2k_the_http_url_scheme.asp
The URLs in the following table are examples of common file: URLs for a user's mailbox folders (English).
Folder URL
Calendar http://server.example.com/exchange/UserAlias/Calendar
Contacts http://server.example.com/exchange/UserAlias/Contacts
Drafts http://server.example.com/exchange/UserAlias/Drafts
Inbox http://server.example.com/exchange/UserAlias/Inbox
Journal http://server.example.com/exchange/UserAlias/Journal
Notes http://server.example.com/exchange/UserAlias/Notes
Outbox http://server.example.com/exchange/UserAlias/Outbox
Sent Items http://server.example.com/exchange/UserAlias/Sent%20Items
Tasks http://server.example.com/exchange/UserAlias/Tasks
check out this URL for accessing any exchange box via HTTP/ADO
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/e2k3/e2k3/_exch2k_the_http_url_scheme.asp
The URLs in the following table are examples of common file: URLs for a user's mailbox folders (English).
Folder URL
Calendar http://server.example.com/exchange/UserAlias/Calendar
Contacts http://server.example.com/exchange/UserAlias/Contacts
Drafts http://server.example.com/exchange/UserAlias/Drafts
Inbox http://server.example.com/exchange/UserAlias/Inbox
Journal http://server.example.com/exchange/UserAlias/Journal
Notes http://server.example.com/exchange/UserAlias/Notes
Outbox http://server.example.com/exchange/UserAlias/Outbox
Sent Items http://server.example.com/exchange/UserAlias/Sent%20Items
Tasks http://server.example.com/exchange/UserAlias/Tasks
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud.
This article shows how to create one of these functions to write directly to Azure Table Storage.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
- Superb Internet
- IT Administration
- Ransomware
- Storage Software
- Databases
- Cloud Computing, Cloud Services, *Backups, *Online Backup Services, *Backup Software
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.