Post reply

DTS Package only runs as sa

  • August 24, 2004 at 9:07 am

    #59309

    Hi-

    I'm running into problems similar to this article:

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=128785

    But, my problem is a little different and need some serious help.

    I cannot get myself setup as a proxy user and as a result, have to give a generic SQL Server account SysAdmin rights - ugh!

    I posted more detail about my problems on the original thread you see above.  Please help  - B

  • August 24, 2004 at 10:15 am

    #520359

    In the properties for SQLAgent, on the Job System tab is a place you can specify the proxy account. Setup an account that can connect to SQL Server and run the job and then put that account in the proxy setup.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 24, 2004 at 11:13 am

    #520372

    Thanks Steve.

    However, I've tried that to no avail - here's what I've noticed.

    1) The proxy setup exists under the "Job System" tab of SQL Manager.  But, I'm not using a job.  I'm calling a DTS package from a SProc using xp_cmdshell.  Does the Job System tab apply truly only to jobs ?

    2) I've also tried using xp_sqlagent_proxy_account to no avail.

    In all cases I've tried using my Windows Domain account as the proxy.  BTW, I'm the dbo and sysAdmin on the database - very strange that nothing works.

    Note that if I run tests using the normal SQL User with sysAdmin rights - all works fine.  If I remove the rights - the execution fails.

    Any more help is greatly appreciated - I must be missing something.

    - B

  • August 25, 2004 at 8:12 am

    #520516

    Hi,

    The proxy user applies to DTS also.  You need to change the owner of the job that executes the DTS package to a non-sysadmin user.  The proxy user only executes the job if the owner is NOT a sysadmin.  I'd set up the proxy user as a domain account (sysadmin privs).

    Mary

  • August 25, 2004 at 8:20 am

    #520519

    Thanks Mary-

    However - my biggest question relates exactly to what you've just said - "Jobs".

    I'm not running any Jobs at all.

    I simply:

    1) call a Stored Procedure which

    2) runs xp_cmdshell with a dtsrun command to import a file

    All the "Proxy stuff" seems to relate only to "Jobs" but I do not use jobs.

    How can I specify a proxy user for something other than a job?

    - B

  • August 25, 2004 at 8:32 am

    #520524

    Here's more detail.

    - DotNet ADO code call an "ImportFile" Stored Procedure using an SQL User called USER_Generic.

    - The Stored Procedure executes this code:

    dtsrun /F C:\DTS\ImportAFile.dts ...

    Now:

    1) USER_Generic has been granted permission to execute xp_cmdshell

    2) If

         USER_Generic is a sysAdmin - the file is imported.

         USER_Generic is NOT a sysAdmin - import fails.

    I am the DBO of the database and have sysAdmin rights.

    If I make myself (a Windows Domain account) a "Proxy" using the Enterprise Manager proxy tab or make myself a proxy using xp_sqlagent_proxy_account - the import still fails if USER_Generic is NOT a sysAdmin.

    Again - I'm not running a job - just executing xp_cmdshell really.

    The whole reason I'm using an SQL Server account named USER_Generic is so that:

    1) Connection pooling works properly

    2) The user only has rights to execute certain Stored Procedures.  No direct table access at all.

    But if I need to make the user a sysAdmin just to import a file then this is all ridiculous.

  • August 25, 2004 at 8:51 am

    #520535

    BTW-

    Here's the error message I get when the user is not a sysAdmin.  Regardless of my Domain user account being set as a proxy or not.

    Msg 50001, Level 1, State 50001

    xpsql.cpp: Error 87 from GetProxyAccount on line 604

    Do I need to restart the SQLAgent service or something.

    It's crazy that it's this difficult to get a file into a Database with proper security.

  • August 25, 2004 at 11:21 am

    #520578

    I found this article which helped a bit but still did not solve the problem.

    http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

    All is the same up to this point in the article:

    <To verify that authorization of xp_cmdshell was the problem, I logged in as the user that was experiencing the problem and tried the previous script. What I got was:

    Msg 50001, Level 1, State 50001xpsql.cpp: Error 997 from GetProxyAccount on line 604

    I checked that the proxy account was set up correctly with this script:

    -- retrieve the proxy account to check that it's correct.xp_sqlagent_proxy_account N'GET'go>
    From here, my system has the checkbox turned off and all appears in order but now I get this error:
    Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
    I give up for now - this is total BS and a waste of my time.

  • August 25, 2004 at 3:30 pm

    #520619

    This is probably to little to late, but did you look up xp_cmdshell on BOL?  It's pretty clear about permissions.  If you want to give a non-sysadmin rights to xp_cmdshell you can.  If the user you set up is not a sysadmin the command will be executed under the proxy account (which you can see with xp_sqlagent_proxy_account).  So you would have to give your proxy account the ability to run dos commands (using Windows security).

    All this seems pretty hard, and it's probably even trickier than BOL would lead you to believe. 

    However, here's another method you can use:

    Schedule the DTS package (once a year, say).  Now that you have a scheduled job; go in and disable it.  Now, when you want to run the DTS package simply run the "sp_start_job" proc.  Using this method you can do everything within SQL Server, and you don't have to go to the command line at all.

     

     

    Signature is NULL

  • August 25, 2004 at 3:35 pm

    #520620

    Thanks Calvin - pretty slick idea.

    And in fact I may end up doing just that if I cannot get this to work.

    It's just frustrating that my "Proxy user" is in fact me - a dbo, sysAdmin, and local machine admin as well and cannot get this %^#'n thing to work.

    I'd assume I can run DOS commands since I'm the local admin but maybe I'm missing something here.

    Regardless, thanks for the very slick suggestion.

    Take care - B

  • August 27, 2004 at 8:54 am

    #520898

    Well I got it working using a proxy user.

    And most importantly NOT having my generic SQL user(who calls the SProcs) set up as a sysAdmin.

    The formerly mentioned Novick article and this were the most help:

    http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/defaul..microsoft.com:80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1&NoWebContent=1

    Making the proxy user a local machine admin seemed to do the trick.

    Hope this helps someone else out there.

    - B

  • August 27, 2004 at 12:16 pm

    #520937

    Interesting article...gosh that is complex.

    good job!

    Signature is NULL

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

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