Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Unhappy Unanswered: Error 7410 when doing a SELECT from a Link table in xp_sendmail

    Really would appreciate some advice on this problem. I have a cluster, CORPUSGA and a stand alone server CORPUSAPP22. CORPUSAPP22 has a linked server set up to CORPUSGA using 'sa' as the security context. Within a stored procedure I am running a SELECT to CORPUSGA.msdb.dbo.sysjobs.

    When this is run from Query Analyzer it is fine and runs under the 'dbo' security context, but when is is run under xp_sendmail that uses the SQL Agent's security context (a domain account called
    IntServiceGA) it fails with this message:

    ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER.

    For the following command:

    exec master.dbo.xp_sendmail
    @recipients = 'stelzner_eve@emc.com;dabas_ravi@emc.com;bergin_gi llian@emc.com;lynch_eoin@e
    mc.com;sherman_ nancy@emc.com',
    @message = @email,
    @query = @query_text,
    @no_header = 'true',
    @width=80,
    @subject = 'Job Failed on CORPUSGA ',
    @attach_results = 'true',
    @set_user = 'dbo'

    Where @query_text is something like "select * from CORPUSGA.msdb.dbo.sysjobs"

    The IntServiceGA account is identical on both servers and is in the local administrators group on both servers and is in the system administrators role within SQL Server, has access to the database and sysjobs has granted permissions to IntServiceGA (SELECT, INSERT,UPDATE,DELETE).

    When I run profiler against this, it tries to execute under IntServiceGA (an Windows authentication account) and then tries to SETUSER to sa . This is what it objects to the switching. I have tried to set up the IntServiceGA as the security context for the linked server too and this does not work, same error.

    Any help would be great,
    Thanks,
    Eve

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why do you need @set_user if SQLAgent service account is already in sysadmin server role?

  3. #3
    Join Date
    Oct 2002
    Posts
    4

    @set_user

    I have another stored procedure that uses this and it works with the
    @set_user on this one. However, whether I leave it in or take out the
    @set_user parameter it still fails. Any ideas?

    Thanks,
    Eve

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Try to run your xp_sendmail after doing SETUSER 'DOMAIN_NAME\IntServiceGA' and see what you get.

  5. #5
    Join Date
    Oct 2002
    Posts
    4

    Linked server select with xp_sendmail

    no go, using the SETUSER prior to the xp_sendmail still netted the
    same results? Do you know what generates this message- NT or SQL?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's NT. It just proved that your SQLAgent account is lacking some rights. Was mail profile setup using SQLAgent service account?

  7. #7
    Join Date
    Oct 2002
    Posts
    4

    SELECT failed from Linked Server with xp_sendmail

    Yes, the profile was set up on both servers from the
    corp\IntServiceGA account that is a local admin on
    the both servers. Are there additional policies that need
    to be put on for this account that are not in the default?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •