xp_sqlagent_proxy_account

  • I'm trying to set up a proxy account on SQL 2000/Windows 2000 to allow developers to run Cmdexec and ActiveX on our test server. I want to use the account that runs SQL Server and SQL Server Agent. I'm getting this error: "Error executing extended stored procedure: Specified user can not login" -- even if I log directly into the SQL Server machine using the SQL Server service account. Any ideas?

  • Yikes, this is a tough one. Since the account that starts SQL Server should be a sysadmin, it shouldn't be that, but could try to give that account direct permissions to XP_CMDSHELL and see if it throws an error then as well. Another item I would try is to run Profiler and track the T-SQL statements being passed during the granted permissions. Sometimes, you can run it in Query ANalyzer and receive a better error to debug with.

    Brian Knight

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

  • I had a simular problem with running a DTS from the xp_cmdshell (using the proxy account)

    It was a bug with SP1 (Q302828). It may be worth setting the permissions on the temp directory as per the Q article to see if that fixes it.

    Steven

  • Even I had a similiar kind of problem. Granting the proxy account, read/write permissions on temp directory did help. As mentioned my

    Steven, it was a bug in SP1 that was fixed in SP2.

    Thanks,

    Chandra

  • Even I had a similiar kind of problem. Granting the proxy account, read/write permissions on temp directory did help. As mentioned my Steven, it was a bug in SP1 that was fixed in SP2.

    Thanks,

    Chandra

  • We're running SP2 so the permissions on the "temp" directory were OK (full access). I've tried running Profiler while executing xp_sqlagent_proxy_account and the message I get is: "'xp_sqlagent_proxy_account' was found in the text of this event.-- The text has been replaced with this comment for security reasons." I've tried this on all of our other SQL servers and get the same result. The account I want to use is a local admin on the SQL Servers and has the sysadmin role. Does it also need to be an admin in the domain?

  • It shouldn't need to be a domain admin and besides, you do NOT want developers having access to domain admin rights, period.

    A silly question, and you probably are, but are you using xp_sqlagent_proxy_account command setting the password. The example from BOL:

    
    
    EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
    N'NETDOMAIN', -- agent_domain_name
    N'ralph', -- agent_username
    N'RalphPwd', – agent password

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Yes, I even found the typo (comma after 'RalphPwd'). When I execute it with the "Get" option it seems to run, although I don't get any results. I've tried it with all combinations of upper/lower case and even a couple of different login accounts. I get the same error ("Error executing extended stored procedure: Specified user can not login") every time.

  • Does the user account have rights to logon as a service for that particular system (I'm reaching here)? I know it's a requirement for the MSSQLSERVER and SQLSERVERAGENT service accounts, I'm wondering if it is for the proxy account.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • I'm attempting to use the account that runs SQL Server and SQL Agent.

  • This is the "fix" (thanks to Microsoft support):

    In Security Settings/Local Policies/User Rights Assignment make sure the account you want to use as your proxy has these privileges:

    Act as part of the operating system

    Increase quotas

    Log on as a service

    Replace a process level token

    Log on as a batch job

    After setting those and a reboot, my problem was resolved (whew!).

  • Hi

    I confirm LSCHOLL's support fix, also, use the SQL*Agent GUI properties for managing the proxy where possible to aleviate some confusion in the process, and always re-connect you sessions before re-testing the config change.

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply