SqlServerAgent cann''t see mail profile only created with Outlook
-
Interesting problem... SQL server2000 service, SQL Server Agent Service and Windows2003 Server run on the same account dom\user. I created MAPI profile through MS Exchange successfully, but SQL Mail and SQLServerAgent cann't see it. When I created mail profile with Outlook (pop3/smtp ), SQLSERVERQGENT saw it. Maybe this account doesn't have necessary rights on the system? After installation of sql server I've changed its account to dom\user and gaved it following permissions:
• Act as part of the operating system. • Increase quotas(On windows2003 it sounds different). • replace process level token. • Log on as a batch job. Any suggestions? Thanks in advance
-
From KB 840219
Enable computer and user accounts to be trusted for delegation
Impersonate a client after authentication
Lock pages in memory
Log on as a batch job
Log on as a service
Replace a process level token
I have taken the liberty to place these in alphabetical order, why can't BOL or KB?
Be sure that the login has sysadmin rights in SQL Server. And that when you configure you have logged into the SQL Server with the same domain account that the services use.
BOL states that the login also needs local Administrators membership.
Andy
-
Thanks Andy for your trying, but the problem remains. The account has local admin rights, also I gave it the same rigths (at local policies) as the previous login (which I typed during sql server installation) had. I think the main point is during SQL SERVER installation system granted necessary rights to that previous login and there were not any problems. When we decided to change it into the new account dom\user1 we got that problem. All work fine with the new login except mail on SQL server. SQL Mail and SQLServerAgent can not see mail profiles craeted with the new login mailbox through the MS Exchange Server.
On different recourses it was said that all (SQL server, SQl server agent and windows) must work on the same login with above mentioned rights and all will work fine. But I think that there can be something else... Registries or else
-
For my configurations, I logged in as the service domain\user and configured SQL Agent with the Outlook profile.
When I normally log into the server I use another domain account and I found that I needed to create the profile again. Since I am also the Exchange Admin, this was not a problem, I simply used the Control Panel | Mail applet to create the same profile for the domain\user.
If your login does not have the permission to access the other mailbox, you will have to always login to SQL Server as the service account. I always use sa for EM and Admin tasks, not integrated, but I do not believe that this is required.
Remember that Outlook profiles are Not global for all user accounts on a machine, they reside with the login profile where they were created, so another profile on the same machine will not have access to the Outlook profile created while logged in as the service account.
The Outlook profile must be configured for the same domain\user that is assigned to the service. I also suggest that at least one email be sent and received using this account. This is one trick that Microsoft solved with the Outlook 2003 Welcome email when it's wizard creates this account.
Have you used the Test portions of SQL Agent to verify the configuration?
Andy
-
Or you could forget the hassle of setting up the profiles and use xp_smtp_sendmail from http://www.sqldev.net
--------------------
Colt 45 - the original point and click interface
-
Thanks Andy for your patient
Yes I'd created a new profile through Control Panel|Mail with the new domain user account.
You know, I tried to do the same on the second our test server and I've got the same problem again.
Do you have such possibility to test it on any server?
Maybe it will be a good step if I try to review all my steps what I'm doing in order to change services login accounts:
1. On local policies (of test server) I add the new domain\newuser account with the following permission:
Act as part of the OS
Bypass traverse checking
Generate security audits
Enable computer and user accounts to be trusted for delegation
Impersonate a client after authentication
Lock pages in memory
Log on as a batch job
Log on as a service
Replace a process level token
As you see I gave even not necessary permissions in order to copy the old login rights
2. Add domain\newuser to local admin group
3. Through EM change SQL server, SQLServerAgent login account to domain\newuser
4. Restart server
5. Login to server with domain\newuser
6. Add new profile through control panel|mail (exchange server mail account)
7. I go to EM to change mail profile and I don't see anything!!!
What I'm doing wrong? Have you any idea?
-
Just Type the name of the profile you saved in the Mail applet, I would start with SQL Mail, then test. Move on up to SQL Agent and because SQL Mail has the profile configured, you should see it in the combo box, if not Type it again, and test. Next create an Operator and use the Test to send an email.
When you installed Outlook, you did choose the CMO option, right? Without it SQL Mail cannot connect to Outlook profiles.
Andy
-
I don't see any mail profiles listed in the combo box. When I press test at SQL mail after typing mail profile name I get an error:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 18030: xp_test_mapi_profile: Either there is no default mail client or the current mail client can not fulfill the messaging request. PLease run MS Outlook and set it as default mail client.
When I go to SQL agent properties I see not activated (grey) mail session.
Previously I installed MS Outlook from MS Office Professional Edition 2003 SP1 by default properties. What did you mean when asked me about CMO option? I uninstalled this SP1 pack and on new installation didn't see any CMO option. I went through advanced customization option.
-
Isn't it hopeless situation? I begin to believe that it is...
-
See my earlier post. You would have everything setup and running by now
--------------------
Colt 45 - the original point and click interface -
Thank you Phill, it could be a solution of my problem. I have a couple days to fix it and if I don't find the correct answer to my problem within these days I'll follow your suggestion. So this topic is still open for yours suggestions
Hoewer as for administrator it's quite interesting issue
-
The main reason I like xp_smtp_sendmail is the simplicity. No fiddling with profiles, no restarting SQL Services to reconnect to Exchange. No security holes from running Outlook on the server.
Just register the extended stored procedure and then you can instantly run any of the many included emailing samples without configuring anything else.
To use it for alerts takes a few more steps. First you create a job to send an email message. You setup the job to use SQL Agent Tokens to capture the alert text. Then when you create an alert just set it to run that job.
You can also email out query results with a little bit of extra code. You can use either sp_makewebtask, or straight field concatenation, to generate the HTML text, then use this text as the body of the email.
--------------------
Colt 45 - the original point and click interface -
Manos(Guys)!!
I found the solution to this problem!!!
Just follow this steps:
1- In the enterprise manager, right-click on the server
2- Click on "properties"
3- Click on tab "Security"
4- And, finally, check the box "Allow cross-database ownership chaining"
Good Lucky!!
Gabriel.
-
I've got the same problem and still have no solution. We're running MS Exchange, SQL Server 2000 and Outlook all on the same server. The SQLServerAgent mail profile is grayed out. I tried "Allow cross-database ownership chaining" and this did not work.
Any other ideas? I need SQLServerAgent Mail to work because we are running Microsoft Great Plains and functionality depends on this working correctly.
thanks,
Jason
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply