Enable sp_execute_external_script to Run Python or R Script on SQL Server
SQL Server developers or administrators including data engineeers who want to work with R-Script or Python scripts should first enable sp_execute_external_script stored procedure on SQL Server instance by using sp_configure to modify "external scripts enabled" system configuration variable. Otherwise, SQL engine will throw following SQL error or exception preventing execution of R script and Python script on SQL Server 2017.
Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.
sp_execute_external_script is disabled on this instance of SQL Server
After I download SQL Server 2017, I immediately install SQL Server 2017 including In-Database Machine Learning Services for both R and Python.
When I tried to execute my first R Script on SQL Server database was to return a string value "Hello World" as seen in the following SQL and R-Script code;
EXEC sp_execute_external_script
@language = N'R',
@script = N'OutputDataSet <- as.data.frame( "Hello World" );';
I got following SQL error
Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.
As from experience and the error itself explains, on the current SQL Server instance the system stored procedure sp_execute_external_script is disabled which is default behavior with new installations.
Such configurations and security-critic stored procedures, database administrator or security administrators can use sp_configureenable sp_execute_external_script procedure to use it for executing R and Python scripts on SQL Server instance.
Use sp_configure 'external scripts enabled' to enable sp_execute_external_script
Let's execute sp_configure stored procedure first and display the output of this system stored procedure. Feel free to execute sp_configure without any additional parameters, as you will see this does not modify any configuration settings on the related SQL Server instance.
EXECUTE sp_configure;
GO
Here is the result of executing sp_configure SQL Server instance configuration procedure.
Of course the all SQL Server configuration settings is not limited with those listed ones. But that is enough for data engineers who want to work with statistical libraries of Python and R and execute scripts based on those two languages using sp_execute_external_script stored procedure.
If database developers checks the line 9, they will see that config_value and run_value of external scripts enabled configuration option is set to "0" which means false that is external scripts are disabled on the current SQL Server instance.
Run_Value is currently active value of the related configuration.
If there is a different between config_value and run_values, this means after a restart of SQL Server instance services, the config_value (configration value) will be active and it will be displayed on run_value afterwards.
What I mean actually is that, although if you (think!) you enabled sp_execute_external_script be sure that config_value and run_value are equal to 1.
Probably config_value is 1 (since you enabled external scripts) but if run_value is 0 then you might have to restart SQL Server services for the related instance.
Of course this is easy if you are working on a developer edition instance whose single user is me. But restarting services on a productive server should require a pre-work and preparation
Let's enable external scripts by modifying "external scripts enabled" configuration option using sp_configure stored procedure.
EXECUTE sp_configure 'external scripts enabled', 1;
GO
RECONFIGURE;
GO
sp_configure requires execution of ReConfigure command as the following output message implies. But we have already executed RECONFIGURE as you can see in the above script. So don't worry about the message.
Configuration option 'external scripts enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
At this time, if you again execute sp_configure procedure, you will see that config_value is updated to 1 but since config_value is still 0, our R-scripts or Python scripts will not be executed and fail again because sp_execute_external_script is disabled and waiting the restart of services on SQL Server.
I can easily restart SQL Server using SQL Server Management Studio as follows:
On SQL Server Management Studio (SSMS) right click on SQL Server instance within Object Explorer window and select Restart option on the context menu
Confirm operations when you are asked for permission or approval for stopping and starting SQL Server instance
After restart, if you again execute sp_configure, you will see that run_value is also set to 1 which means external scripts enabled and developers are enabled to run sp_execute_external_script stored procedure.
Now data engineers who prefer to work on SQL Server can run Python and R Scripts on SQL Server instance using stored procedure sp_execute_external_script
Let's execute our sample R script to see if we have solved the configuration problem and "'sp_execute_external_script' is disabled on this instance of SQL Server." error
EXEC sp_execute_external_script
@language = N'R',
@script = N'OutputDataSet <- as.data.frame( "Hello World" );';
Database developers can see how they can use sp_execute_external_script stored procedure to run R-Scripts (also Python scripts) on SQL Server with a simple Hello World R script sample
Disable sp_execute_external_script Stored Procedure
Of course, if the requirement is to disable sp_execute_external_script stored procedure in order to prevent exxecuting R script and Python scripts using SQL on SQL Server databases, database administrators can use following SQL script.
EXECUTE sp_configure 'external scripts enabled', 0;
GO
RECONFIGURE;
GO
Of course an instance restart is required to take into account the configuration changes on SQL Server