Using xp_cmdshell and Zip Genius 6
-
Consider this command:
EXECUTE xp_cmdshell 'c:\ftpfiles\zg\zg.exe -add "C:\FTPFiles\test\butthead.zip" +"C:\FTPFILES\ZG\*.*" C9 R1'
There are no errors, just the "output" is NULL
Take the exact same command above (within the single-quotes) and run it within a Run window on the server and it will work nicely:
C:\PROGRA~2>c:\ftpfiles\zg\zg.exe -add "C:\FTPFiles\test\butthead.zip" +"C:\FTPFI
LES\test\*.txt" C9 R1
C:\PROGRA~2>
* * Important note * *
This runs pefectly on our production folder, and the secutiy settings/sharing permissions are the same in both environments
Has anyone run into this before?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
when you run xp_cmdshell, you are not running under your own credentials....
prove it to yourself by running this, which will show you what NT name you are running cmdshell from.
DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))
insert into @Results (TheOutput)
exec master..xp_cmdshell 'whoami' --nt authority\system for example
insert into @Results (TheOutput)
exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.
select * from @Results
this is a common security misconception/"gotcha". The problem is that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives and folders,xp_cmdshell, xp_cmdshell plus bcp with a "trusted" connection, sp_OA type functions etc.
it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL WILL pass your credentials to a linked server, but anything else is using an account you did not intuitively expect it to use.
SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:
or if the above was blank, the account in services:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
[/quote]Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks so much for such a detailed response, unfortunately that's not the issue here. Setting up a valid proxy account from AD on each of my DB servers is something I do pretty much from the get-go...on dozens of servers.
I can run all kinds of commands successfully with the shell, producing no issues that I am aware of which all yield output results, however when running the example command using zip genius...the output is NULL and nothing happens. Copy and paste the exact same command in the command prompt and it works.
I know it would appear like an issue with security but I don't believe it's the case here.
Could it be an issue with the fact this is a 64-bit OS and the zip genius is 32-bit software? Again, it DOES run just fine as a stand-alone app, or through the command window, but doesn't work when using the command shell. I've had issues with SQL system procedures before like 'sendmail' that won't allow you to send a true PAGE from TSQL because the API calls it uses don't work on a 64-bit OS. But who knows if that's the case here...
Any other ideas?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
i don't think it's 32 bit vs 64;
it might be that your zipgenus app is returning an error at teh command line that you are not seeing...
could you try this and see if there are any results?:
DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))
declare @sql varchar(4000)
SET @sql ='c:\ftpfiles\zg\zg.exe -add "C:\FTPFiles\test\butthead.zip" +"C:\FTPFILES\ZG\*.*" C9 R1'
insert into @Results (TheOutput)
exec master..xp_cmdshell @sql --capture any messages returned in our Results Table
select * from @Results
I've been using 7zip command line from xp_cmdshell, which is also 32 bit, and my commands a very similar to yours, and that's been working fine for me :
declare @command varchar(2000)
--zip one file
SET @command =
'"E:\Installs\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!
+ '7za.exe"' --the exe: i'm using the command line utility.
+ ' a ' --the Add command: add to zip file:
+ '"C:\Data\' --path for zip
+ 'myZipFile.zip"' --zip file name, note the dbl quotes for long file names!
+ ' ' --whitespace between zip file and file to add
+ '"E:\Installs\' --path for the files to add
+ 'XP-Vista_VirtualDesktops_Setup.msi"' --the file
+ ' -y' --suppress any dialogs by answering yes to any and all prompts
print @command
"E:\Installs\7zip_CommandLine_7za465\7za.exe" a "C:\Data\myZipFile.zip" "E:\Installs\XP-Vista_VirtualDesktops_Setup.msi" -y
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here are the results:
(1 row(s) affected)
ID TheOutput
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 NULL
(1 row(s) affected)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
I'm having the same issue returnning NULL...
Has this been resolved?
-
MyDoggieJessie (1/6/2011)
Consider this command:EXECUTE xp_cmdshell 'c:\ftpfiles\zg\zg.exe -add "C:\FTPFiles\test\butthead.zip" +"C:\FTPFILES\ZG\*.*" C9 R1'
There are no errors, just the "output" is NULL
Take the exact same command above (within the single-quotes) and run it within a Run window on the server and it will work nicely:
C:\PROGRA~2>c:\ftpfiles\zg\zg.exe -add "C:\FTPFiles\test\butthead.zip" +"C:\FTPFI
LES\test\*.txt" C9 R1
C:\PROGRA~2>
* * Important note * *
This runs pefectly on our production folder, and the secutiy settings/sharing permissions are the same in both environments
Has anyone run into this before?
If the "output" that you're talking about is what's returned as OUTPUT from xp_CmdShell, there's nothing returned at the DOS prompt so why would you expect xp_CmdShell to return anything but a NULL?
Just to verify... when you run this from xp_CmdShell, ZG.exe is producing the file as you want, yes?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Another possible problem is the "C:" drive.
When YOU run it from a command window, the command is referring to whatever machine you're running the command from, presumably, your desktop.
When you run the command through xp_CmdShell, the "C:" drive is the C: drive of the SERVER... not your desktop. This is why it's a good idea to get into the habit of using UNC paths to files instead of drive letter notation. It's just too easy to get confused.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Analyst2006 (12/27/2013)
I'm having the same issue returnning NULL...Has this been resolved?
See my post immediately above. That may be your problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply