locked
Azure SQL Managed Instance xp_cmdshell 'unsupported' or simply won't work? RRS feed

  • Question

  • Hi

    Looking to move to a SQL Managed Instance and establishing what changes we'll need to make to our internal monitoring code and see that xp_cmdshell is unsupported in MI does this mean it will work still but isn't supported or it simply will not work in a MI?

    ...asking here as opposed to Azure forum as there isn't a MI section as yet!

    Thanks

    Friday, October 19, 2018 12:15 PM

Answers

  • It's not only "not supported", none of the Extended stored procedure (XP) are available in SQL Azure.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 19, 2018 12:21 PM

All replies

  • It's not only "not supported", none of the Extended stored procedure (XP) are available in SQL Azure.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 19, 2018 12:21 PM
  • Hi flakey321,

     

    You can refer to the document "Azure SQL Database Managed Instance T-SQL differences from SQL Server"

     

    Under sub-title "Stored procedures, functions, triggers", You can see that xp_cmdshell is not supported.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 22, 2018 5:51 AM
  • There does not need to be integration with the file system and Azure SQL Database because Azure uses URL endpoints (HTTP) instead of UNC share names. For example, the following Bulk Provider OPENROWSET interface and TSQL Code is very common in the industry for ETL, and the pattern ought to function on Azure SQL Database without too much hassle. 

    SELECT SourceLoad.* FROM OPENROWSET(BULK '\\HOSTNAME\Source\Locales.txt', 
    FORMATFILE = '\\HOSTNAME\Formats\Locales.xml',   (replaced with URL in Azure)
    FIRSTROW = 2,  CODEPAGE = 'RAW') AS SourceLoad

    This is part of a common data inspection routine, where parameters can be added to a Stored Procedure as needed. The beauty of OPENROWSET is that it allows instant viewing of sample TAB text files in testing, where there is some intention of increasing the number of rows later. The critical point is that a BLOB HTTP endpoint is used instead of a UNC Path. These HTTP Endpoints need to be easily created by the developer during testing, with the intent of ultimately adding the endpoint and the requisite text file (with a requisite Format File comprising XML) into production. Thus, the creation of a BLOB Endpoint should not be problematic even with diminished permissions. Accessing the endpoint creation facility ought not be problematic neither. This is all supported by Microsoft, using HTTP endpoints. The NET SHARE command in Windows really only needs to execute in an xp_cmdshell call when we are using UNC Shares, which is not supported because you don't need it anymore. That whole thing is replaced by the "BLOB" and the semantics of HTTP Endpoints.

    • Edited by CubeSparkle Monday, July 1, 2019 5:46 PM Grammar errors, sorry.
    Monday, July 1, 2019 5:09 PM