Python integration with SQL Server 2019 brings the power of Python scripts directly into your database environment. However, setting up the Python custom runtime can sometimes be challenging, leading to frustrating error messages and roadblocks.
Overall, Python integration with SQL Server offers organizations a powerful and flexible solution for advanced analytics and data processing capabilities directly within the database environment
In this technical blog post, we will explore a step-by-step approach to install, troubleshoot and resolve of the 'Python' runtime issue encountered on the SQL Server 2019.
Problem Overview:
When attempting to call Python scripts over SQL Server, users may encounter the following error message:
Msg 39021, Level 16, State 1:
Unable to launch runtime for 'Python' script. Please check the configuration of the 'Python' runtime.
Msg 39019, Level 16, State 2:
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070057: 87 (The parameter is incorrect.).
This error indicates a failure in launching the Python runtime within SQL Server, preventing the execution of Python scripts.
The solution involves ensuring that the Python runtime is properly configured and accessible within SQL Server. I'll cover the installation of the Python custom runtime, granting necessary permissions, registering the language extension, enabling external scripts, and finally troubleshooting the Python runtime issue.
1. Prerequisites: Ensure that SQL Server 2019 is installed with fallowing features.
Verify that the Python 3.7 version is installed on the system.
2. Install Python: Download Python 3.7 from the official website.
Install Python 3.7, ensuring to add it to the system PATH during installation.
Install the pandas library using the command python.exe -m pip install pandas, run as admin.
3. Grant Access: Open PowerShell as administrator.
Run the following commands to grant access to the Python folder:
icacls "C:\Program Files\Python37" /grant "NT Service\MSSQLLAUNCHPAD":(OI)(CI)RX /T
icacls "C:\Program Files\Python37" /grant *S-1-15-2-1:(OI)(CI)RX /T
4. Restart SQL Services: Restart SQL services to apply the changes, including SQL Launchpad.
5. Register Language Extension: Execute the following SQL script to register the language extension in SQL Server:
CREATE EXTERNAL LANGUAGE [myPython]
FROM (CONTENT = N'C:\path\to\python-lang-extension-windows-release.zip',
FILE_NAME = 'pythonextension.dll',
ENVIRONMENT_VARIABLES = N'{"PYTHONHOME": "C:\\Program Files\\Python37"}');
6. Enable External Scripts: Configure SQL Server to enable external scripts using the following commands:
sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
7. Verify Installation: Execute the test script to verify the Python runtime installation. If errors occur, proceed to troubleshooting steps.
EXEC sp_execute_external_script
@language=N'myPython',
@script=N'
import sys
print(sys.path)
print(sys.version)
print(sys.executable)'
At this point we will get the Python runtime error mentioned.
8. The fix: Navigate to the pythonlauncher.config file in your SQL Server installation directory.
I:\<yoursqlinstance>\Program Files\MSSQL15.<yoursqlinstance>\MSSQL\Binn
Ensure that the WORKING_DIRECTORY parameter is correctly set to the ExtensibilityData folder.
Fire a notepad as admin and then open pythonlauncher.config, replace line "WORKING_DIRECTORY= " pointing to your ExtensibilityData folder, on my installation: I:\<yoursqlinstance>\Program Files\MSSQL15.<yoursqlinstance>\MSSQL\ExtensibilityData
Restart Python and SQL services again just for good luck, and then hopefully you'll see a response from Python queries.
Let's consider the following troubleshooting steps that could trigger other errors:
- Review Python installation process.
- Ensure that the Python service account has the necessary permissions to access relevant resources. You can compare your setup with the guidance provided by Microsoft documentation.
- Double-check compatibility between your specific versions of Python and SQL Server.
It appears there may be some installation issues with Python that Microsoft hasn't addressed. Additionally, there are several online posts mentioning potential problems with incorrect permission settings.
Resources: https://learn.microsoft.com/en-us/sql/machine-learning/install/custom-runtime-python?view=sql-server-ver15&pivots=platform-windows
https://blog.greglow.com/2018/05/16/machine-learning-testing-your-installation-of-r-and-python-in-sql-server-2017/