Issue with xp_cmdshell in SQL2005

Folks,

I would like to make you aware in my App, we use xp_cmdshell to get a list of file names (using dir into a temp table) from a specific directory and process these files one after another to insert into Database through the Job.

In SQL2000 xp_cmdshell was enabled by default so never had an issue, with us migrating to SQL2005 xp_cmdshell needs to be enabled through the Configuration manager (i know how we do that) as it is off by default which is a security concern as suggested by our DBA and hence not allowed. Also, xp_cmdshell could only be executed by SA user or other user using proxy account with appropraite rights.

I looked at the alternative susggested by some about using SQL Agent Proxies so only certain users can access it.

Can somebody walk me through the steps of making it work for my SQL 2005 certain users using proxy account. i have tried but in vain... i'm not so good with creating windows users and domain policies(stuff)

p.s. Note that we will not be allowed to have xp_cmdshell enabled on the server by the DBA. so the solution has to not enable the xp_cmdshell from the SQL Configuration manager.

i also tried using SQL-CLR method of creating stored procedure but since we may have to use EXTERNAL_ACCESS or UNSAFE type while creating assembly as we use kernel.dll etc. to access file system through code it cannto be SAFE. we are back to square one because of security concern.

i also tried using xplog70.dll to create assembly which is the file used under the wrapper xp_cmdshell but it suggested it needs a .NET assembly and the dll is of previous version or something...

Any other ideas are welcome.

please help... my deadline is fast approaching...

my email id : sunnyny2003@.hotmail.com

Thanks.

Sunny.

I know at least 2 ways to solve this problem.

1. Create a job with ActiveX script step. In it, you may use standard VBS statements like Dir() and so on, so it will be relatively easy to obtain list of files, create ADODB.Connection object and insert the entire list into your table. You have only to decide who will be the owner of the job and how you will start it from your T-SQL code (there is a system stored procedure for this, sp_start_job, but you may run into security problems here, too).

2. You may use virtually the same approach as in the previous solution, but this time code it directly in T-SQL. There is a bunch of stored procedures to work with COM objects - sp_OACreate, sp_OAMethod and so on. Look here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ff16a833-01fe-4877-8aa6-55b72603ec2e.htm
for further details.
The only difference here is that you need to use a Scripting.FileSystemObject in order to reach Dir functionality through COM.|||

A SQL Agent Proxy account won't give a non-sysadmin access to use xp_cmdshell as part of a job step. A SQL Agent Proxy Account will only allow a non-syadmin to use a job step with the type of operating system.

In order to do what you need to do following what appear to be the same guidelines that I lay out for my developers.

Use xp_dirtree 'C:\Folder\With\Files\', 1, 1 to get a list of files within the folder that you specify. The second parameter tells it how many levels deep to scan. 1 tells the procedure to only use the current level. The third parameter tells it to include files. If the third parameter is not included then only folders are shown.

Then use the BULK INSERT command to load the files. This can all be done without needing access to xp_cmdshell or to the file system.

Let me know if this does or doesn't do the trick for you.

Thanks,

Denny

|||

Why don't u use sqlserver integration services (SSIS).

That would do the job perfectly.

You just have to create a "for loop container" which loops through the set of files you have on a directory and import them.

all these functionality is in SSIS.

|||

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

|||

Denny,

Appreciate your response,

using xp_dirtree would work for me to gather file names from the folder to process...

this resolves one issue..

i also use xp_cmdshelll to copy, delete and rename these files after processing....

do you have some other xp_..... for these?

also, is there a way we can execute some exe's like we have one to retrieve the dos path for the folder with spaces etc.

Thanks.

Sunny

|||we cannto use ssis as we don't know before hand what files we have to create the process..|||

Sunnyny wrote:

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

Of course they are dynamic. Or you mean that exact directory you need to scan is dynamic also? Well, create a special table in your DB so user will insert there a path to the directory to scan. When SSIS package is started, it can retrieve required path from this table and scan it, as usual. Don't see anything too difficult here.

Sunnyny wrote:

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

Don't understand - do you really need to drop database from your code?|||

yes, the folder is dynamic too and also sometimes the path is UNC path...

I'll look into SSIS..

do you have any samples or point me.. where we could have example of substituition of filenames etc?

sometimes users uninstall our database and start afresh and because of multiple connections created due to job running every minute and using sp_OAmethod and not destorying that object... Database is unable to be dropped.

|||

If you wanted to use SSIS to you could use a .NET script block to get the list name list for processing and then process it via the Loop that was mentioned above.

You can use xp_delete_file to delete files from the hard drive.

There aren't any extended stored procedures which are nativly installed with Microsoft SQL Server which can rename or copy files. You would need to write these in C++ and attach them to the SQL Server. Your DBA may not allow this to happen either. You may need to change your processing reuirements so that you process and delete, and have the process which gets the files back a backup of the file at that time, instead of while the import is processing. Or before you start the import process use a Command Line Job Step to copy any and all files in the folder to another place for backup purposes.

mrdenny

|||

... except xp_delete_file is notoriously flakey - it reports success even when it's not deleted what it's supposed to.

Search +xp_delete_file +problem - loads of stuff out there about how it simply doesn't work and no one has an answer to it.