Get list of files in directory from Azure Managed Instance

Question

From an Azure Managed Instance (MI), how can I get a list of files in a folder from an on-prem server? On our on-prem SQL Servers we use xp_cmdshell, but we've disabled xp_cmdshell for security reasons on the MI. Also, I tried xp_dirtree and it triggered a security warning from Microsoft.

I appreciate your suggestions.

Answer 1

Hi @Andre ,

maybe you can create an SSIS package to do this

use a CLR stored procedure:
https://www.mssqltips.com/sqlservertip/1518/how-to-return-a-result-set-from-a-sql-server-2005-clr-stored-procedure/

or can use powershell to list a file directory:
https://learn.microsoft.com/en-us/powershell/module/azure.storage/get-azurestoragefile?view=azurermps-6.13.0

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for the reply. I thought about powershell but on an MI we obviously don't have access to the file system to put the .ps1 file. If my scheduled task's type is Powershell am I able to type the powershell script directly into the command window?

Regarding CLR, are there security concerns with it that are similar to xp_cmdshell? We have SOC compliance issues to deal with so I want to make sure it's not going to raise any red flags.

Answer 3

I would think expect there are security concerns with about anything you try in this space. Shouldn't you take a second look of what you actually want to do, and look for a different solution where a program outside SQL Server reads the files?

Answer 4

Thanks for the reply Erland. We're simply trying to replicate what we do on-prem, which is using xp_cmdshell. I was also hopeful that maybe since the MI is the latest bits of SQL that it might have some other feature for doing this. I know SQL 2019 has a couple of new sys procs, xp_copy_files and xp_delete_files, so I was hopeful there was something else like those that interact with the file system. It seems like SSIS might be the best way to do this in an MI, which seems like a bit of overkill and more work honestly.