Secuirty Issues With Xp_cmdshell
Dec 18, 2007
Greetings,
I have created a user ReportUser and given few permissions.
Also given execute permission on xp_cmdshell extended stored procedure.
Now when i am trying to rename a file using xp_cmdshell ,I am getting a error message access denied.
execute xp_cmdshell 'rename C: t.txt gg.txt '
Error Message :Access denied
Please let me know why i am getting the above error.
Do i have to give any more permission to the User to rename a file.
View 21 Replies
ADVERTISEMENT
Oct 27, 2004
Hi Folks,
Have a scenario where we have to audit all our databases and servers for changes in security accross the servers.
We have a central montioring server where we pull SQL metadata at regular intervals. In this instance we are looking to have before and after snapshots of the SQL system tables.
For the logins this is fine as there is a last updated field in the syslogins table. We can tell when a new user has been added , remmoved or the login has changed ie the login as been added to sysadmin fixed server role... etc Perfect !!!
What Im trying to work out now is how I can do this for object level permsisions. Have looked at sysprotects but no joy. If a user or a role has been granted select or update on a table... How can I tell based on before and after snap shots of the system tables what permissons have changed and whom have they changed for...
Help ......
View 2 Replies
View Related
Nov 16, 2007
Here is my situation: I am creating a database driven ASP.Net web application that will be used over the internet. My ASP.Net application connects to my SQL server 2005 database/server by using a SQL server login. I am using the DPAPI API to encrypt my connection stings with a hidden entropy value for extra security. I am using the SQL login for obvious reasons, as my users will not have a windows login.
What I am trying to do: I want to limit this SQL login account to be able to just run/execute stored procedures and NOT access the tables or views directly. In my ASP.Net application I am using the MS applications data block, and I am using stored procedures for every single database access action. There is no inline SQL being executed from my web application.
What I have tried so far:
I created a new schema and made the above SQL login account owner of this schema. I then granted "Execute" permissions to the SQL login and DENY permissions to all other permissions.
View 4 Replies
View Related
May 15, 2001
Hello,
I am trying to create a directory containing the date and then copy all the files in the current directory to it.
If I run the T-SQL script found below within Query Analyser it works fine (directory is created and files are copied in to it).
But if I run it as SQL task within DTS, only the directory is created. The files are not copied in to it!?!
I thought it maybe a permissions problem, but the SQL Server/Agent account is a local administrator and has sysadmin role with SQL Server.
I would be grateful of any assistance in this problem.
Thanks in advance,
Chris.
SQL:
declare @directoryname varchar(200)
declare @doscmd varchar(255)
select @directoryname = 'e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char))
select @doscmd = 'mkdir ' + @directoryname
exec master..xp_cmdshell @doscmd
select @doscmd = 'copy e:Audit_table_archive*.* e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char)) + '*.*'
exec master..xp_cmdshell @doscmd
View 1 Replies
View Related
Jul 9, 2001
does anyone know how to execute a FTP command through sql server 7? I am creation a table and need to ftp the result set. I have it currently creation the table, turning it into a CSV txt file and placing it in a directory. I have to then manually ftp the txt file. I want to automate this process but I cant get the FTP command to execute throught the xp_cmdshell. It will work at the DOS prompt though so I know the syntax is correct. Any sugetsions would be appreciated.
-Nathan
View 2 Replies
View Related
Aug 10, 2001
hi everybody
My requirement is, I have to transfer database backup files from one server to another server to take tape backup. Generally in my backup folder there will be 4days backups. I want to schedule a job so that after database backed up, today’s backup file only copied to the other server. I wrote following code, upto xp_cmdshell every thing is working fine.
But xp_cmdshell statement is giving error. I have to use variable value with sp_cmdshell. Please give me the solution for this.
declare @year1 as varchar(4),@month1 as varchar(2),@day1 varchar(2),@filename varchar(40)
set @year1=ltrim(str(year(getdate())))
if month(getdate())<10
set @month1='0'+ltrim(str(month(getdate())))
else
set @month1=ltrim(str(month(getdate())))
if day(getdate())-1 <10
set @day1='0'+ltrim(str(day(getdate())-1))
else
set @day1=ltrim(str(day(getdate())-1))
set @filename='EMPTest_db_'+ @year1+@month1+@day1+'*.bak'
xp_cmdshell @a
thanks
Keerthi
View 1 Replies
View Related
Aug 17, 2001
I am trying to run a Visual Basic Script using the xp_cmdshell stored procedure. When I try to run the file, the MS Script Debugger application is started.
I run the script like this: "xp_cmdshell 'D:ScriptFilesSpaceMail.vbs'"
I can run it from the DOS prompt successfully, but not from Query Analyzer. Does anyone have any suggestions on how to prevent the MS Script Debugger from running?
Chris
View 1 Replies
View Related
Sep 7, 2001
Hi,
I am trying to run bcp with xp_cmdshell inside a trigger. Whenver I update table the server is hanging. It creates the file in specified location but of ZERO size and I cannot delete it unless I stop SQL server service.
The smae code runs from a stored procedure without any problem.
Can you pl tell me if there are any LIMITATIONS with xp_cmdshell and Trigger.
Thanks
sekhar
View 1 Replies
View Related
Apr 14, 2000
Hi!!!
Can someone help me?
How can I get the result of following execution in some stored procedure, and work with it:
exec xp_cmdshell 'dir c:'
Thanx in advance
Laert
View 1 Replies
View Related
Nov 1, 2000
Hi,
while using XP_cmdshell for renaming a file , can I concatenate the current date to the file?
Ramam
View 1 Replies
View Related
Nov 28, 2000
Hi,
What is best way to compare two files and get the most recent one using xp_cmdshell?
Thanks
TT
View 1 Replies
View Related
Nov 28, 2000
Hi,
I am trying to execute this command.
use master
exec xp_cmdshell..'dir estserverest_dataInventory_Files*.txt /b'
I am getting the following error..
output
------------------------------------------------------------------------------Logon failure: unknown user name or bad password.
I am logged on as 'sa' for that server.
Any suggestion on how to resolve this?
Thanks in advance.
View 1 Replies
View Related
Mar 8, 2000
Hai ,
I beleive this is simple question but I have problem using this command
I was trying to delete contents of a temp folder thru TSQL.
Correct me if wrong
For example
xp_cmdshell " del c:emp*.*"
This is leading me to prompt 'Are you sure to delete Y/N'.
How do I write a command to say Y.
I want to schedule this every week. Is there any other way to do this task
like using AT command ,etc.
Thank you in advance
Surya
View 1 Replies
View Related
Mar 1, 2000
Hi !
I am trying to use xp_cmdshell
---
xp_cmdshell 'dir abcc$'
---
where abc is host name
and C$ is shared name
I get the following error message
"Logon failure: unknown user name or bad password."
I execute the same command at the dos prompt , I get the directory listing
Any suggestions??
Thanks.
KMM
View 2 Replies
View Related
Jul 6, 2000
Hi Guys,
i am trying to run an .exe file from queryanalyzer for my testing.I am using the following command
exec master..xp_Cmdshell 'C:extEXEproject1.exe -SCHEETAH -Uvijay -Pbell'
CHEETAH is my server name
User :vijay
pwd:bell
the problem is its running forever in QueryAnalyzer,when i tried to run the
same thing from dosprompt its executing fine.
Please help me in this
thanks for any help
View 1 Replies
View Related
Jun 6, 2003
SQL 7.0
I have given a non sa user permissions to run xp_cmdshell via his NT logon. When he runs it and does a "dir" of the server it works. When he trys to do a "dir" of his own machine it comes up with "Logon failure unknown user name or bad password".
SQL Agent is running under a domain admin account. I have placed SQLAgentCmdExec in the servers local administrators group. I have de-selected the option from SQLAgent propertiesJob System which restricts non sa users executing CmdExec stuff. I did attempt to Reset Proxy Account and Reset Proxy Password. When I clicked on these it just gave me message that the account and password had successfully been reset without asking me for username, password or domain.
View 5 Replies
View Related
Sep 24, 2003
Hello, friends,
This is probably not a question for DBA forum, but may be someone knows the answer.
In the stored procedure I'm using xp_cmdshell command to copy file from one dir to another.
How I can receive any indication what the process succeded?
xp_cmdshell return 1 or 0 if the stored procedure was invoked,
I would like to receive an indication that the file was copied.
View 2 Replies
View Related
Aug 22, 2001
How to create directory and file using xp_cmdshell..?
Thanks,
Harish
View 1 Replies
View Related
May 12, 1999
I tried to use the external procddure - xp_cmdshell - to copy a file from a local drive to a network drive, but failed. Yet the attempt succeeded if I tried to copy a file from one place to another in local drive.
It seems that SQL Server 6.5/7.0 did not recognize any network drive.
I greatly appreciate your help/assistance and/or any hint(s). Thanks a lot
View 1 Replies
View Related
Nov 2, 1998
Hi there!
Is it possible to supply variable for the xp_cmdshell parameter?
here's an example:
declare @@okay char(20)
select @@okay = "dir c:mssqlinn"
exec master..xp_cmdshell @@okay
but this doesn't work. is there any workaround on this? instead
of writing a new extended sp to cater for this.
thanks in advance.
dion
View 6 Replies
View Related
Apr 13, 2005
I was able to run DTS using xp_cmdshell. In my dts I have 3 global variables which I would like to pass. Can I pass a variable values using xp_cmdshell?
xp_cmdshell 'DTSRUN /S ServerName /U user/P pass /N "DTS_Name" '
View 3 Replies
View Related
Jun 15, 2004
Hi,
I need to do FTP using a FTP script...here is the syntax that i am using
ftp -s:c: empftpcmd.txt > c: emplog.txt
when i run it thru the command line, log.txt file has the following
==================================================
ftp> Connected to 9.999.99.999.
open 9.999.99.999
220-YYYY1 XXX FTP CS V1R2 at XXXXXX.XXXXXX.XXX.COM, 16:55:07 on 2004-06-15.
220 Connection will close if idle for more than 5 minutes.
User (9.999.99.999:(none)):
331 Send password please.
230 USERNAME is logged on. Working directory is "USERNAME.".
ftp> Invalid command.
ftp>
ascii
200 Representation type is Ascii NonPrint
ftp> get 'XXX.YYY.ZZZ.ACCOUNT' c: empXXX.YYY.ZZZ.ACCOUNT.txt
200 Port request OK.
125 Sending data set XXX.YYY.ZZZ.ACCOUNT FIXrecfm 22
250 Transfer completed successfully.
ftp: 1172544 bytes received in 3.31Seconds 353.82Kbytes/sec.
ftp> quit
221 Quit command received. Goodbye.
=================================================
but when i run it from inside a query...using
declare @cmd varchar(1000)
select @cmd = 'ftp -s:c: empftpcmd.txt > c: emplog.txt'
exec master..xp_cmdshell @cmd
the log.txt file has
=================================================
User (3.172.28.153:(none)): open 3.172.28.153
Invalid command.
ascii
get XXX.YYY.ZZZ.ACCOUNT' c: empXXX.YYY.ZZZ.ACCOUNT.txt
quit
=================================================
why is this differnece? what can i do so that i get full details in the log file.
please help.
thanks
rohit
View 9 Replies
View Related
Apr 27, 2007
Where should i read up on using
dos commands in conjunction with the xp_cmdshell extended stored procedure..
I need to manipulate file names....
move copy take off ... parts of the filename and replace... them...
etc... any good sites or topics... so that I can learn how to do this...
thanks...
jonathan
View 13 Replies
View Related
Oct 4, 2007
Hi All,
I've recently installed SQL Server 2005 and now trying to enable xp_cmdshell through SQL Server 2005 Surface Area Configuration. However, I am getting the following error:
User does not have permission to perform this action.
You do not have permission to run the RECONFIGURE statement.
I am logged in as "sa".
How can I make it work?
Thanks.
View 4 Replies
View Related
Feb 25, 2004
how to enable xp_cmdshell
View 2 Replies
View Related
Mar 10, 2004
Basic problem:
When xp_cmdshell interacts with FTP it doesn't log the FTP return codes.
History:
I found Nigel Rivett's T-SQL FTP scripts http://www.nigelrivett.net/ and concatenated them together with some modifications so I could have a process that actually errors when an FTP fails.
The process FTP's a file, disconnects, and then reconnects to do a dir and then select from the returned listing to see if the file name exists.
I wanted to review the initial log from the put and check for a valid FTP return code of 250 that says "Transfer completed successfully"
Log Snippets:
A normal FTP log shows:
ftp> put foo 'mainframe.node.structure.foo'
200 Port request OK.
125 Storing data set MAINFRAME.NODE.STRUCTURE.FOO
250 Transfer completed successfully.
ftp: 7972888 bytes sent in 99.63Seconds 80.03Kbytes/sec.
ftp> quit
221 Quit command received. Goodbye.
xp_cmdshell log shows (with FTP debug on) shows:
7 put foo 'mainframe.node.structure.foo'
8 PORT 10,99,11,16,7,30
9 ---> STOR MAINFRAME.NODE.STRUCTURE.FOO
10 ---> quit
11 QUIT
12 (null)
As you can see, the return codes and some other messages are missing.
I've messed around with various options and can't seem to get any return codes in the log at all.
Any ideas?
View 1 Replies
View Related
Apr 1, 2004
I need to kill some user session that i get from below query output
xp_cmdshell 'netstat -an | find /i "est"'
output: -
TCP 0.0.0.47:1099 0.0.0.39:139 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.104:2961 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.104:3012 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.39:2915 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.39:3106 ESTABLISHED
TCP 0.0.0.47:2007 0.0.0.26:1487 ESTABLISHED
TCP 0.0.0.47:2007 0.0.0.79:1139 ESTABLISHED
do we have any command to kill the connected session to my sql except kill spid.
ex: i want to kill the session of IP 0.0.0.79 from my sql server.
thanks in advance
View 3 Replies
View Related
May 31, 2004
Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??
Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??
thanks
View 1 Replies
View Related
May 6, 2008
Hi
Why am I not able to use a table created via CTE with xp_cmdshell to dump it to a text file?
cPRLDetails AS
(
SELECT 1,2,3
FROM [dbo].[VmyVIew] vsp
)
EXEC master..xp_cmdshell 'bcp "Select * FROM cPRLDetails ORDER BY PartExt05" queryout "p: argetfile.txt" -c -T'
I get the following error
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'cPRLDetails'.
Thanks
View 3 Replies
View Related
Oct 16, 2007
In SQL Server 2005, once in a while, the xp_cmdshell gives the
following error.
"An error occurred during the execution of xp_cmdshell. A call to
'CreateProcess' failed with error code: '5'. [SQLSTATE 42000] (Error
15121). The step failed.".
Does Looping has a limitation on the number of times it can call
xp_cmdshell.
It works good whenever we call it individually.
Thanks in Advance
RKNAIR
View 8 Replies
View Related
Dec 18, 2006
Hi!I have a batch file (.bat) that I need to execute inside my storedprocedure (SQL 2000).I am trying to call that batch file using xp_cmdshell command. I amprobably not using it the right way but I am trying to pass a path ofthe batch file into xp_cmdshell and it doesn't recognizes it as acommand.Can you anybody help me with the syntax?T.
View 2 Replies
View Related
Oct 9, 2007
Hi,
Please could someone give me a pointer on how to resolve this no doubt basic problem I am having. I am new to creating DTS and the security is making go around in circles.
I am executing a DTS package using the xp_cmdshell in a stored procedure. This stored procedure is executed from a web application. So when the stored procedure is executed the connection to SQL Server database is not a WINDOWS account and is not an admin role but instead a database user set up with a public role. When I go to execute this stored procedure it fails with the error:
xp_cmdshell failed to execute because current security context is not sysadmin and proxy acount is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.
My stored procedure looks as follows:
CREATE PROC UP_ExecuteDTS
@GlobalVar1 varchar(255), --Global Variable 1
@PkgName varchar(255),
@Server varchar(255) = '(local)',
@ServerUser varchar(255) = Null,
@ServerPWD varchar(255) = Null
AS
declare @cmd nvarchar(4000)
set @cmd = 'DTSRun /S "'+ @Server + '" /N "' + @PkgName + '" ' --/G "{1B29D7A1-B27A-4355-8B02-C58221A6A0BF}" '
set @cmd = @cmd + '/A "FileName":"8"="' + @GlobalVar1 + '" ' + '/W "0" /E '
exec master.dbo.xp_cmdshell @cmd
GO
Is there a way to allow the database user calling the stored procedure to execute the DTS?
I was thinking of adding a proxy account but am not too sure how to do this as it is not a Windows account. Does the window account have to exist etc? I was thinking of the inserting the lines below before exec the xp_cmdshell
--Code to authenticate User to execute DTS
SET @ServerUser = IsNull(@ServerUser, '')
IF @ServerUser = 'SQLConnectionUser'
BEGIN
EXEC sp_xp_cmdshell_proxy_account @ServerUser, @ServerPWD
END
I would be most grateful if anyone could help.
Thanks
Tracey
View 5 Replies
View Related
Feb 23, 2008
Hi,
I am trying ti unzip a backup file throguh xp_cmdshell. I am getting this error. It works when I tried manually unzipped.
NULL
C:WINDOWSsystem32>"C:Program Files (x86)WINZIPwzunzip.exe" -ybc -s0Ff$1tE
WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
NULL
ERROR: missing name of Zip file
Program is terminating!
NULL
My batchfile: "C:Program Files (x86)WINZIPwzunzip.exe" -ybc -s0Ff$1tE E:OffsiteBackupsGLPArchival20080201.zip E:RestoreDatabaseVEENATest_unzipbackup
Mt stored procedure :
CREATE PROCEDURE USP_UNZIP
@ZIPFILE VARCHAR(2000),
@BAKPATH VARCHAR(1000)
AS
DECLARE @SQLSTATEMENT VARCHAR(2000)
SET @SQLSTATEMENT =''
SET @SQLSTATEMENT = 'C:UNZIP.BAT "'+@ZIPFILE+'" "'+@BAKPATH+'"'
PRINT 'SQL STATEMENT'
PRINT '-------------'
PRINT @SQLSTATEMENT
PRINT 'MESSAGE'
PRINT '-------'
EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT
Thanks,
-G
View 1 Replies
View Related
