SP3 -> now: error with xp_sendmail, remote query

  • 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

  • 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?

  • 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.

  • Check this KB from Microsoft.

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

  • 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