0

We're currently migrating all our on-premise applications to Azure environment. Currently, most of our stored procedures have queries using xp_cmdshell to move files between Windows file shares.

In the Azure env, all these Windows file shares will be replaced by Azure file shares and the SQL Server will be replaced by Azure SQL Managed Instance.

So, is there a way to modify the queries to work for Azure file shares instead of Windows file share?

The closest help we got on this is creating tables from Azure Blob storage on Azure SQL. Is it possible to access Azure Blob from a stored procedure?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amateur_coder
  • 81
  • 9

2 Answers2

0

Below is a solution for this, but it's not tested, so kindly please do your due diligence before actually using it in production:

  1. Connect to your SQL MI server from SSMS.
  2. Enable xp_cmdshell in your server.
  3. Use net use command to map your Azure File Share to the SQL MI server.
  4. You can get the username and password for the file share from your Azure portal storage account.
  5. Once the Azure File share is mapped, you'll be able to use the File share as though it is another drive within your SQL MI server.

Things to note:

  1. There's a chance that the drive gets unmapped due to network issues. So, it is better to check if the drive is mapped before running queries related to them.
  2. Make sure to map it to the same drive letter, or better maintain a table so that you don't have too many unnecessary drive maps.
Amateur_coder
  • 81
  • 9
0

You cannot use xp_cmdshell within Azure SQL MI (T-SQL differences between SQL Server & Azure SQL Managed Instance). One reason for this is because access to the underlying server that supports the MI is restricted. Since xp_cmdshell allows you to execute operating system commands from SQL server, this would pose a huge risk to both the security and stability of this PaaS service. It has long been discouraged even for traditional SQL instances (Newly developed code shouldn't use the xp_cmdshell stored procedure and generally it should be left disabled), and most security benchmarks recommend it remain disabled.

However, if you need to access files for things like bulk insert then you can easily store them in a Blob storage container. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-access-to-data-in-azure-blob-storage?view=sql-server-ver16

If you need to manage those files then it would be best to do that from a tool like azure data factory or even Azure automation. But trying to use SQL server as a file manager would not be the ideal solution and is not possible in Azure SQL MI.

Demond Hatter
  • 1