April 29, 2003 at 3:52 am
Hi all,
I would like to run xp_cmdshell DIR command to get list of files on a remote machine. When I'm running script in QA:
INSERT INTO #temp EXEC master..xp_cmdshell N'dir \\remotemachine\D$\'
I get the 'acces denied' error.
My question is: What account is used while running this script. Is this a system account of the sqlserver machine or some sqlserver account or what?
I need to get list of .csv files of the remote machine and import them (using dts) into sqlserver database. How should I solve this issue.
TIA
With regards
Maciej Szymanski
With regards
Maciej Szymañski
April 29, 2003 at 5:31 am
Should be the account that runs SQL agent. Easiest fix is to get your admin to give the account access.
Andy
April 29, 2003 at 7:52 am
Andy, I guess I'm now confused. I know there are two service accounts, one for MSSQLSERVER, and one for SQLSERVERAGENT. Now I always thought if you run JOBs that execute xp_cmdshell that the xp_cmdshell commands are run under the MSSQLACCOUNT account, and when you execute an xp_cmdshell command via QA (which is what Szymana is asking) the MSSQLSERVER account is used. So basically the MSSQLSERVER account is always used. My testing seems to confirm this, although it was not an exhaustive test. Now I suppose if the MSSQLSERVICE and SQLSERVERAGENT accounts are the same account then your answer would not confuse me. How confused am I?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 29, 2003 at 2:21 pm
April 29, 2003 at 6:26 pm
Greg,
this is true if you have sysadmin rights for the box (which is the minimum required to execute xp_cmdshell unless addition GRANT statements are executed). However, if a user isn't a sysadmin, SQL Server will use the SQL Server Proxy Agent account, provided this is set.
Of course, if the statement is executed as a CmdExec job, it runs under the context of the SQLServerAgent service account provided the owner is a sysadmin. Otherwise, SQL Server Agent proxy account!
From BOL:
quote:
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.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply