This article will learn about SQL servers and how to exploit their external scripts to our potential.
Table of content
- Introduction to SQL Server
- Installation of SQL Server
- Executing Python Script
- Executing R Script
Introduction to SQL Server
Microsoft has released a lot of versions for SQL servers. Microsoft has released version 2019 of this server more than twenty times. When you look at the different versions, you quickly get a sense of how their goal continues to be moving towards making improvements and innovations to the product every year. This goes on for even the minor changes that include an updated name or logo for each new release. In short, there is something for everyone in each release, and more importantly, that means there is something new for your business or organization that wants to use a particular version of SQL Server.
In the same fashion, this 2019 version of SQL Server adds several improvements and new features to give it a slight edge over its predecessors in providing a higher level of performance. With that in mind, I thought it would be a good idea to bring you up to speed on all the new features in this version and what they can do. The big news with this version is that SQL Server 2019 changes to different areas within the server. In doing so, the following are some of the immediate changes such as MISRA C#/Clojure/Python/Java/etc. with SQL Server.
Installation of SQL Server
For this article, we have performed all our practicals on SQL Server 2019. You can download this version of the server from here. Once the server is downloaded, let’s install it. For installation of the said server, choose the Basic option as shown in the image below:
Now, choose the location for the server, then click on the Install button; as shown in the image below:
Now from the SQL server Installation Centre, choose the New SQL Server stand-alone installation or add features to an existing installation option as shown in the image below:
And then, from the Feature Selection dialogue box, select Machine Learning Services and Languages and check the boxes for R, Python, Java. These options are offered only in the versions of SQL, which were launched after 2015. The same can be seen in the image below:
Now from Java Install Location, choose the Install Open JRE 11.0.3 included with this installation option. And then click on the Next button as shown in the image below:
And then, for the server configuration, select the Automatic option and then press the Next button as shown in the image below:
From the Consent to install Microsoft R open dialogue box, click on the Accept button and then click on the Next button as shown in the image below:
Do the same when the Consent to install Python dialogue box opens; as shown in the image below:
Once the installation is successful, click on the Close button as shown in the image below:
Following the previous steps has allowed us to install the SQL server 2019 install successfully.
Enable External Scripts
As our SQL server is installed, we will now try to manipulate external scripts to our advantage. But first, we have to check that whether the external scripts are enabled or not. To check the said, we will run the following query:
sp_configure 'external scripts enabled' GO
The result of the above query, which you can see in the image above, tells us that config_value and run_value are 0, which means that external scripts are not enabled. Hence, we will enable the scripts now. For this, we will execute the following query:
EXECUTE sp_configure 'external scripts enabled', 1; GO RECONFIGURE; GO
As you can see in the image above, the query has been executed successfully. Now, let us confirm if the scripts are enabled or not, and for this, run the following query:
sp_configure 'external scripts enabled' GO
As the result of the above query, Config_value and run_value have been changed to 1, which means that the externals scripts are enabled now.
Executing Python Script
As we have enabled External scripts. We will now execute the Python script. This python script will run the command “ipconfig”. And if successfully executed, it will give us the result for the said command. To execute python script type:
EXECUTE sp_execute_external_script @language = N'Python', @script = N'print(__import__("os").system("ipconfig"))'
As you can see in the image above, the Python script was executed successfully.
Executing R Script
Now we will execute R script. To do so, execute the following commands:
EXEC sp_execute_external_script
@language=N'R',
@script=N'OutputDataSet <- data.frame(system("cmd.exe /c ipconfig",intern=T))'
WITH RESULT SETS (([cmd_out] text));
GO
And as you can see in the image above, the R script has been executed successfully. So, this way, we can use external scripts to our advantage. And use various programming languages to get the desired results.
Author: Yashika Dhir is a Cyber Security Researcher, Penetration Tester, Red Teamer, Purple Team enthusiast. Contact her on Linkedin and Twitter