By: Rick Dobson | Updated: 2017-09-06 | Comments (3) | Related: > SQL Server 2016
Problem
SQL Server 2016 introduced a feature for running external scripts, such as Python scripts. Unfortunately, many of the SQL Server installations within our organization are for earlier versions, but our IT developers still want to take advantage of Python development features. Please demonstrate how we can install Python to run Python scripts with SQL Server versions prior to 2016.
Solution
The sp_execute_external_script stored procedure was introduced with SQL Server 2016 for running external scripts from SQL Server Management Studio (SSMS). Organizations running SQL Server versions prior to 2016 do not have a built-in way to run scripts for popular external scripting environments, such as Python.
This tip starts with a review of how to download and install a recent version of Python for use by all users on a server. The standard Windows install instructions for Python default to a single-user installation. Next, this tip in its subsequent two sections reviews a simple Python script and how to run it from a cmd window session. The tip closes with a demonstration for running the Python script from SQL Server logins based on a mix of admin and non-admin Windows accounts.
Python is widely used and growing in popularity because of its ease of use, power, and extensibility. By mixing the capabilities of Python with SQL Server, you have a dynamic duo comprised of a popular programming solution along with an easy, familiar way to store and retrieve results from solutions.
Installing Python on a Server for All Users
As with most other third-party software, there is no built-in Windows installation for Python. The contributors to the CPython project released a suite of Windows installation packages for many prior versions of Python, including the most recent one as of this writing (Python 3.6) and even test versions in development. Links to a full set of Windows installation packages for Python released by the CPython project are available here. Some discussion of the differences between installation packages for different versions is available here. A more focused presentation of Python installation issues is available at this site.
I downloaded the Windows x86-64 executable installer. After downloading the installer, it was run as an administrator. The following set of screen shots show all the screens from the installer package through to the notice of a successful installation for Python 3.6.2. The download took around 10 minutes. The screens show the selections to load Python so that it is available to all users on a computer. These selections offer a work-around to the default single-user installation of Python.
Demonstrating a sample Python script from a normal Python environment
The Python software downloads with documentation on the Python language and its environment. The documentation includes many sample scripts. I adapted one of these scripts for finding odd and even numbers. The following screen shot shows the adapted script in the IDLE environment; this development environment is available with the Python download.
Notice the script includes a for loop, which is roughly like the while loop in T-SQL. This particular script loops through numbers from 2 up to and including 9. If the remainder of a number divided by two (modulo 2 or % 2) is equal to 0, then the number is labeled as an even number in the output from the script. Otherwise, the number is labeled as an odd number in the output.
The next screen shot shows the output from the preceding script. When the user chooses Run, Run Module from the IDLE menu, the output is returned to a Python Shell window. The Shell window confirms the output is from the find_odd_even_nums.py script file in the C:\python_programs path. You can put your Python script files anywhere you choose on your hard drive. Notice that the preceding screen shot showing the script's listing denotes the file name and path for the script file in the dialog box's title.
It is not likely that SQL Server developers and administrators will ultimately consume output from Python scripts in IDLE. However, the IDLE application is one of several Python script editors/development environments that facilitates testing and refining Python scripts. Once you get a script developed and unit tested in an application like IDLE, then you can reference the script file with T-SQL code for consumption and display in SSMS.
Running a Python script from a command window
This part of the tip demonstrates how to run a Python script from a cmd window. The demonstration of running a Python script from SSMS depends on this capability. In order to run a Python script from a cmd window, you need to reference two files from within a Windows batch file.
- First, the python.exe file, which interprets Python scripts.
- Second, you need to let python.exe know where is the script file to be interpreted.
The next-to-last installation screen from the Installing Python on a Server for All Users section indicates the path for the Python code, including the python.exe interpreter, is at C:\Program Files\Python36. The file name for the Python script is find_odd_even_nums.py. Python script files have a py file extension. The path to the script file is C:\python_programs. The Windows batch file for invoking the python.exe application on the script file is run_find_odd_even_nums.bat. The following screen shot shows the contents of the C:\python_programs path and the text of the batch file in that path for running the Python script file.
The next screen shot shows the output from running the run_find_odd_even_nums.bat file. The output includes two key lines: one to change the directory to c:\python_programs, a second to invoke the python.exe file from the c:\Program Files\Python36 for the python script. After these two key lines, you can find the output from the python script, which is comprised of eight lines of text that identify the numbers 2 through 9 as even or odd.
Running a Python Script from SSMS
The Window batch file from the preceding section can be adapted for use inside of SSMS. You can run the Windows batch files from SSMS with the aid of the xp_cmdshell extended stored procedure in the master database. The extended stored procedure creates a Windows command shell and passes in a string for execution, such as the contents of the run_find_odd_even_nums.bat file from the preceding section. Output can be returned as rows of text in a result set within SSMS.
The general approach demonstrated in this tip for using the xp_cmdshell extended stored procedure was adapted from an earlier demonstration of how to invoke the extended stored procedure from a T-SQL script in SSMS. I found this prior demonstration easy to follow and implement, and I am guessing that you will too.
The following screen shot shows the code and output for invoking the run_find_odd_even_nums.bat file from within SSMS. The code and its result set comprise a proof of concept about key elements for invoking python scripts from within SSMS. Some tweaking may be required if a DBA deploys the approach to a small team of developers. The Next Steps section includes a walk-through of step-by-step instructions to enable invoking the xp_cmdshell extended stored procedure from a standard SQL Server login.
Notice the code executes from the master database context. The invocation of the run_find_odd_even_nums.bat file by the xp_cmdshell extended stored procedure appears on line 15 within the SSMS code window. The output from the execution of the batch file appears in the Results tab below the code window within SSMS. For example, the Results tab shows a change directory batch command to the python.exe interpreter path in row 2, and the invocation of the interpreter for the find_odd_even_nums.py script file shows in row 4. The output from the run of the script file is in rows 5 through 12 of the Results tab.
Selected key points worthy of your attention include these.
- The xp_cmdshell extended stored procedure is executed within the context of a designated SQL Server login ('your_instance_name\python_user1'). This login is representative of a typical SQL Server login used with integrated security; it does not have to belong to the sysadmin role.
- The login for executing the xp_cmdshell extended stored procedure must have a user account in the master database.
- Execute permission for the xp_cmdshell extended stored procedure is granted to the python_user1 user account on line 10; a sysadmin login/user principal is required to grant this permission.
- This EXECUTE permission for the non-sysadmin login is revoked on line 20 after the xp_cmdshell stored procedure is invoked and permission to execute the extended stored procedure is no longer needed. The revocation of EXECUTE permission for the xp_cmdshell extended stored procedure and the disabling of the xp_cmdshell extended stored procedure in SQL Server must run from a login within the sysadmin role.
The preceding code requires a credential to work properly. The credential must have the following name ##xp_cmdshell_proxy_account##. The credential should be based on a Windows admin account (or the built-in Windows admin account depending on UAC settings). The following screen shot shows an excerpt from the Credential Properties dialog for the required credential. The domain name and Windows_account_name are for a Windows admin account on the Windows server for your SQL Server instance. The Windows account should map to a login in the SQL Server sysadmin role. Furthermore, the SQL Server login for the Windows account must also have a corresponding user account in the master database. You can start the creation of a credential, such as the one described, by choosing New Credential from the context menu from the Credentials folder under the Security folder in Object Explorer.
The use of the xp_cmdshell extended stored procedure opens a potential security gap that can be exploited by malware. There are several excellent discussions around the web on the exact nature of the xp_cmdshell security risk and how to mitigate the risk, so that solutions do not have to forego the benefits of the xp_cmdshell extended stored procedure. Here's one tip on using a proxy account with SQL Agent steps using the xp_cmdshell extended stored procedure. For security reasons, it may not be appropriate for the tip on running Python scripts from SSMS to be deployed to low-level developers or client staff end-users who wish to experiment with Python script development.
Next Steps
- If you do not already have Python available, download and install the Python application for all users on a computer that also has SQL Server installed on it. Follow the instructions and screen shots in the Installing Python on a Server for All Users section.
- Then, run the sample Python script for classifying numbers from 2 through 9 as odd or even. See the Demonstrating a sample Python script from a normal Python environment section for how to verify the successful installation of Python by confirming you can run the script file. The script file is available in the download for this article.
- Next, run the Python script with a Windows batch file in a cmd window. See the Running a Python script from a command window section for instructions on how to accomplish this.
- After completing the preceding steps, you are ready to run the proof of
concept script for running a Python script from a login session associated with
an admin login based Windows account in the computer's administrator's group
(or the built-in Windows admin account depending on UAC settings).
- Be sure to create the ##xp_cmdshell_proxy_account## credential before attempting to run the script. You also need to deploy to the C:\python_programs path the sample Python script file.
- The run should return the same contents as the Results tab in the preceding section.
- Finally, you can explore manually running from a non-sysadmin login the
xp_cmdshell extended stored procedure for the Windows batch file invoking the
sample Python script. An overview of the details for accomplishing this objective
is as follows. I tested these steps personally to confirm they work.
- You will need to configure SQL Server from a sysadmin login to enable the xp_cmdshell extended stored procedure. Also, grant Execute permission for the xp_cmdshell extended stored procedure to a non-sysadmin SQL Server login based on a non-admin Windows account.
- Next, switch Windows users to the non-admin Windows account on which the non-sysadmin SQL login is based. Then, use integrated security to login to SQL Server with the non-admin Windows account. This creates a SQL Server session for the non-sysadmin login. From the non-sysadmin SQL Server login session, run the #xp_cmdshell extended stored procedure for invoking the Python script. Confirm you generate valid output from the script.
- After the Python script runs, switch Windows users back to the admin Windows account used in the initial step and login to SQL Server. Then, perform the following steps.
- Revoke permission for the non-sysadmin login to execute the xp_cmdshell extended stored procedure.
- Configure SQL Server so the #xp_cmdshell extended stored procedure is no longer enabled.
About the author
Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-09-06
