CODEWITHSUNDEEP
sql-serversecuritybcpxp-cmdshellsql-server-administration
samaspin
samaspin

Reputation: 2402

What is the most secure way to allow users to execute BCP export command from SQL?

I am currently working in an environment where the ability to export a table programatically from within a hand-run SQL script would be of great help.

Performing the exports from script will be the first step towards running the entire process from within a stored procedure, therefore I have to be able to initiate the export from SQL.

The organisation currently has the following configuration on most servers -

Ultimately, I would like to be able to call a procedure passing the following parameters and have it perform the export.

Currently BCP seems like a perfect option in terms of functionality but I am unable to invoke it via the command line due to xp_cmdshell being disabled.

The organisation is quite small and happy work towards a secure solution and my impression so far is that they have a good level of control over their security. They have made a blanket decision to disable xp_cmdshell but if I could present a safe way to allow use of it I think they would be pretty receptive.

In my research I've come across both the 'EXECUTE AS' functionality as well as signing procedures with certificates, but still cant work out if either approach can help me achieve what I want.

Also, if you have another solution that allows me to achieve the same end-result I'm all ears!

Upvotes: 0

Views: 1814

Answers (1)

EricZ
EricZ

Reputation: 6205

As Aaron Bertrand pointed out, the problem is xp_cmdshell disabled.

There are two options you may consider.

  1. Use BULK INSERT. Requires INSERT and ADMINISTER BULK OPERATIONS permissions

  2. Create a SQL Agent job that type is "Operating System(CmdExec)" to run BCP. you may need code to create/update jobs for passing parameters.

Upvotes: 0

Related Questions