| Author |
Topic |
|
BobRoberts
Posting Yak Master
109 Posts |
 Posted - 2009-06-08 : 13:12:14
|
| I'm using SQL Server 2005 Express. I'm on an XP laptop, and have logged in as "sa". I turned on the xp_cmdshell with the following:--------------------EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1GORECONFIGUREGO-----------------------I am using this statement to try to delete a file:--------------------------------EXEC master.dbo.xp_cmdshell 'DEL C:\TEST_DIR\TESTFILE.DAT' -----------------------I've found that if the file was created with a COPY within SQL using the xp_cmdshell, it deletes the file.--------------------------------EXEC master.dbo.xp_cmdshell 'COPY C:\TEST_DIR\TESTFILE_ORI.DAT C:\TEST_DIR\TESTFILE.DAT' ---------------------------------However, if the file was created in some other way, SQL echoes back "Access Denied", and refuses to delete/rename the file. It's very important that this delete take place regardless of who executes it the SQL command. In other words, if someone is running this stored procedure, then the file should get deleted every time. Also, this stored procedure will eventually be run on a Windows 2003 Server, so it's important it function there as well. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2009-06-08 : 13:40:20
|
quote:
Originally posted by tkizer
Permissions aren't being set correctly on the file.
I'm not sure what you specifically mean by that. Be aware that this file is created by an external process beyond the user's control. It is simply THERE, "as is". Also, the file has absolutely no trouble being deleted by opening up a DOS command shell. It is only within SQL that it refuses to get deleted, by echoing back "Access Denied". If the file is created in SQL, then SQL can delete it. To me, this behavior is DYSFUNCTIONAL - SQL cannot function in a minimal fashion to get the job done, which requires deleting or renaming a file which happened to be created by another process. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-06-08 : 15:17:32
|
| Bob,did you parents really name you Robert Roberts ?One thing that may not be intuitive is that as Tara implied, xp_cmdshell commands are issued under the security context of the sql server service account as opposed to the credentials of the person (or job owner) issuing the command. If the person (or job owner) issuing the command is not a sysadmin then sql server will use Server Proxy Account as the security context the command is issued under. So the appropiate account requires the appropriate privileges to network resources you are trying to access.Be One with the OptimizerTG |
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2009-06-10 : 09:43:56
|
quote:
Originally posted by TG
Bob,did you parents really name you Robert Roberts ?One thing that may not be intuitive is that as Tara implied, xp_cmdshell commands are issued under the security context of the sql server service account as opposed to the credentials of the person (or job owner) issuing the command. If the person (or job owner) issuing the command is not a sysadmin then sql server will use Server Proxy Account as the security context the command is issued under. So the appropiate account requires the appropriate privileges to network resources you are trying to access.Be One with the OptimizerTG
There's only one user on my laptop - and I'm it. Also, I'm logged onto SQL with a username of "sa". So if it's not so already, how can I tell SQL that "sa" is a "sysadmin". I don't know what a "Server Proxy Account" is - but I'll try to research it. Is there a way of granting the "Server Proxy Account" full priviledges? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2009-06-11 : 11:32:51
|
quote:
Originally posted by tkizer
I'm not referring to the sa account. I was referring to the SQL Server service account. Go to the Services applet in Control Panel..Admin Tools to check it out.
I assume you mean click the properties of the SQL service. I looked in the Login tab, which seems to be the only relevant tab, and the "This account" radio button is checked. Directly to the right, it says, "NT AUTHORITY\NetworkService", and below, in the password, it says, "***************". But I'm not sure how this setting relates to my problem, unless you're saying I should change "This account" to "sa", and the password associated with it. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-11 : 11:51:50
|
| Bob -- easiest way to get to the services is as Tara said thru control panel or click on start -> run and enter services.msc the service will be there.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-11 : 12:08:11
|
| Bob,SA is a SQL login, it really has nothing to do with Windows security. The "NT AUTHORITY\NetworkService" is related to windows security. If that is the context within which SQL is running then you need to grant that account more privilege in windows or create a new account (like SqlService or something) grant that account windows admin (or appropriate) security level and have the SQL Server service run under that account. |
|
|
|
|
xp_cmdshell Will Not Delete/Rename File