1

I'm ultimately trying to find a way to allow the safe use of BCP and selected other command-line functionality from within SQL Server.

The organisation I am in currently have xp_cmdshell disabled although I feel if I can present a safe security model for its use they would consider turning it on.

My idea so far is to create a wrapper stored proc that executes command-line operations on behalf of the user. The operation would be passed in by the user as a string which could then be

  1. checked to ensure it doesn't contain any obviously dodgy stuff - i found a function here that should help with this (http://msdn.microsoft.com/en-us/library/aa175398(v=sql.80).aspx)
  2. only allow calls to specific programs or operations - in my case the first being BCP

My DBA skills are somewhat limited but here is what I'm hoping from a security context

  • all normal user accounts are not allowed to execute xp_cmdshell
  • we would create a user (who the sproc would EXECUTE AS) with rights to call xp_cmdshell and not much else, not an administrator in other words
  • the users existing permissions would be used to read the data from the table but combined with the permissions of this extra account when using the wrapper sproc.

If you could tell me if I'm on the right lines and also other things I need to consider as this is something I would like to present to the DBAs

Thanks

Sam

samaspin
  • 111
  • 2

1 Answers1

0

A safer, and possibly much easier, way to give users access to command-line tools like BCP would be to use a tool like System Frontier.

The commands would run against your SQL server under an account that has the proper access while the user running it wouldn't have to have any access on the server or a SQL login. No need to deal with frowns about using xp_cmdshell either. Plus, everything like who ran it and what the output was gets logged to an audit table.

Full disclosure: I own the company that makes it.

Jay

Jay Adams
  • 306
  • 1
  • 5
  • It looks interesting Jay. Firstly, is it possible to implement only small parts of the system, I think its highly unlikely the company I am working for would implement a whole new approach to security just to safely allow command-line access. And secondly, and equally importantly, does it allow the running of the command line operations from a SQL script? – samaspin Jan 22 '13 at 15:07