XP_CMDSHELL Results Question

Question

Post reply

XP_CMDSHELL Results Question

mrlpgmr

SSC Enthusiast

Points: 179
More actions
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??

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

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

SJTerrill SSCrazy Points: 2938 More actions · Answer 1

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

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 2

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

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

mrlpgmr SSC Enthusiast Points: 179 More actions · Answer 4

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

Keith Henry Hall of Fame Points: 3759 More actions · Answer 6

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.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 7

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.