0

One of my developers was asking me to enable xp_cmdshell in our on-premises SQL server so she could test renaming files from SQL Server stored procedure. Since xp_cmdshell is not supported in Azure SQL Managed Instance (and it looks like a security risk) I told her I would not enable that for use. However, I am trying to find an alternative for this developer to go about what she was trying to do.

Does anyone know of a viable alternative to using xp_cmdshell for the purpose of renaming files in Azure SQL Managed Instance? The only thing I can think of is maybe running PowerShell in SQL Agent as a job, but wondering what other people would suggest.

Thank you in advance for all help!

Pablo
  • 5
  • 3
  • I would use PS if it was on-prem. Does Azure MI even have a filesystem? – Charlieface Feb 23 '21 at 14:46
  • The entire approach is faulty - you should not be using the database engine to manage things in the file system. This is typically a kludge to avoid writing more robust ETL logic IME. – SMor Feb 23 '21 at 15:21
  • Azure MI doesn't have a file system, but I think you could use Powershell in the SQL Agent to manipulate files on a Windows VM for example. – Pablo Feb 23 '21 at 15:55
  • Hi @Pablo, If my answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you. – Leon Yue Feb 26 '21 at 02:27

1 Answers1

0

As I searched and per my experience, there isn't a equivalent of XP_CMDSHELL in Azure SQL managed instance.

SQL Azure does not provide access to a command prompt. Think of SQL Azure as just a virtualized service, without host infrastructure (Virtualized or physical).

Azure SQLDB does not support xp_cmdshell. Full stop. There is no "equivalent" because Azure SQLDB runs in a shared environment without access to the host unlike the standalone, full access to the host environment you're used to with your on-premises instances.

As you said, if you could use Powershell in the SQL Agent to manipulate files on a Windows VM for example, that's could be good way.

Ref:

  1. Equivalent of XP_CMDSHELL in AzureDB
  2. alternative xp_cmdshell in sql azure

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23