Skip to content

Instantly share code, notes, and snippets.

@james-otten
Last active May 19, 2024 18:46
Show Gist options
  • Save james-otten/63389189ee73376268c5eb676946ada5 to your computer and use it in GitHub Desktop.
Save james-otten/63389189ee73376268c5eb676946ada5 to your computer and use it in GitHub Desktop.
Executing Python inside MSSQL using sp_execute_external_script

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.

To execute Python code:

  • 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
    • 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)))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment