Post reply

XP_CMDSHELL Results Question

  • January 6, 2004 at 8:21 am

    #177391

    I am using the following script to attempt to obtain a file list from a mapped drive. (The drive is mapped from a UNIX Box)

     

    Drop Table Reuseable.dbo.tblDir

    Go

    Create Table Reuseable.dbo.tblDir ( dirtext nvarchar(255))

    Go

    Insert Into Reuseable.dbo.tblDir exec master..xp_cmdshell 'dir p:\'

    Go

    Select * From Reuseable.dbo.tblDir

     

    RESULT

    Access denied

    NULL

     

    What am I doing Wrong??

  • January 6, 2004 at 8:33 am

    #488799

    Is P:\ mapped for the computer you're working on, or for the SQL Server in question?

    xp_cmdshell works as if you were running a command prompt from the SQL Server itself.

     SJTerrill

  • January 6, 2004 at 8:43 am

    #488800

    Also make sure that the login you use to execute the command has proper permission to create table, drop table ... and also read whatever is in P:\ dir.

     

    mom

  • January 6, 2004 at 8:48 am

    #488801

     

    First, just simply try

    exec master..xp_cmdshell 'dir p:\'

    in Q/A with you logged into Q/A under the security context that the mapping to P: allows.

    In other words, if the Unix box allowed the mapping of P: using USER1 PW1, then you would need to have a SQL login of USER1 PW1, then try to query drive P:.

    or, Add to the Unix side a user identical to what SQL Server is using when executing xp_cmdshell.

    One other thought, SQL might be using the SQL Server Service login context when executing xp_cmdshell. Check that also.

    Hope this helps.



    Once you understand the BITs, all the pieces come together

  • January 6, 2004 at 9:06 am

    #488805

    how would one know if it using SQL Service login or my own?

  • January 6, 2004 at 9:09 am

    #488807

    From BOL

    When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 6, 2004 at 9:23 am

    #488813

    Also never use mapped drives.  They are yet another stray thread you have to keep track of.




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • January 6, 2004 at 10:22 am

    #488823

    Instead use the UNC for the share that you have mapped the drive to (\\server\sharename)




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

Viewing 8 posts - 1 through 7 (of 7 total)

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