1

Is there an alternative to xp_cmdshell BCP to write to a file from MSSQL?

Some context: This is to be built into a stored procedure called by an external program. I do not have the luxury to work through executable files, export functions of ssms, or any such things which require more than the calling of this stored procedure.

The reason; there's a lot of odd stuff on this server to do with user rights, and I'm not the SA. I cannot create the ##xp_cmdshell_proxy_account## (let alone assign credentials to it), and xp_cmdshell 'whoami' returns a user noone has ever seen or heard from. I've tried creating a new user based on an existing windows user and granting xp_cmdshell execute rights to it, but this still did nothing. I'm not sure if I don't have the rights or if it's something else.

So long story short, I'm fed up with trying to get this to work on this environment and am looking for an alternative. Is there one?

Maarten H
  • 37
  • 5
  • There's no reason to use `xp_cmdshell` in the first place. On the one hand you can use `BULK INSERT` to import data. On the other hand, the normal way to use bcp is either from a batch file or Sql Server Agent Job - *not* through `xp_cmdshell`. Simply enabling that functionality increases the chances of a security breach, or allowing malicious code to execute using the SQL Server service account's privileges. It's just not needed – Panagiotis Kanavos Jul 30 '20 at 18:11
  • Not needed as in haven't used it in 20 years. Agent jobs that run commands or Powershell scripts, yes. SSIS jobs that call CLI programs yes, I'm doing this for some complex jobs right now. – Panagiotis Kanavos Jul 30 '20 at 18:12
  • `there's a lot of odd stuff on this server` it's not the server that's odd. By default, `xp_cmdshell` is disabled, along with other sensitive features. Some of them, like SQLCLR, need to be enabled for some advanced functionality. `xp_cmdshell` and ActiveX exist mainly for backwards compatibility and are usually *not* enabled. – Panagiotis Kanavos Jul 30 '20 at 18:14

1 Answers1

4

Write a SQL Agent Job and kick it off with sp_start_job. You can control the identity the job uses with a SQL Agent Proxy.

Or write an SSIS package, deploy it to the SSIS Catalog and run it from your stored procedure.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67