February 12, 2003 at 2:28 am
hi,
after applying SQL SP3 we have problems with jobs that use xp_sendmail to send query results (query linked server).
error message:
ODBC error 7410 (42000) Remote access not allowed for Windows NT user
activated by SETUSER
All of these jobs ran fine before SP3 was applied. I tested xp_sendmail - works fine. Tested the remote query - works fine.
As soon as the remote query is being called within xp_sendmail - it fails with the above error.
Has anybody come across the same problem?
Any idea how to solve this issue?
Greetings,
Kerstin
February 12, 2003 at 8:05 am
Are the account you used to test same as the account to run the job?
Can you try to run xp_sendmail with a local query?
What kind of linked server in remote? If it is SQL Server, Does it have SP3 installed?
February 12, 2003 at 8:20 am
The linked server is also SQL Server SP3. I have tested the remote query call under same account as job-owner of the job which is executing the xp_sendmail with the remote query call. I have tested xp_sendmail with a local query - it's working fine.
The job-owner is a user with sysadmin rights - and all accounts from local server are mapped to user with sysadmin rights on the linked server - to make sure. To make sure I have tested to run the job under all possible users - tried both NT-accounts as well as sql-accounts - no difference. I have tried different RPC login-mapping scenarios - it makes no difference either. And as I said - it has been working well before we applied SP3. We rely on these jobs heavily because it's the only way to use mail-notification as we cannot set up SQL-Mail on the linked server.
February 12, 2003 at 8:52 am
Check this KB from Microsoft.
http://support.microsoft.com/default.aspx?scid=kb;en-us;811031
February 13, 2003 at 1:15 am
Thank you for the link :
...
----------------------------------------
Method 1
Make the system administrator the owner of the job.
Method 2
Use mapped security context for the linked server and modify the job to run as OSQL.
To set the mapped security context for the linked server:
Right-click the linked server, and then click Properties.
Click the Security tab.
Select either of the following options.
Be made using the login's current security context
Be made using this security context
-----------------------------------------
As I said: the sysadmin is owner of the job & I have also tried 'Method 2' - without success. Problem still exists.
Anybody else any idea?
greetings,
Kerstin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply