In this post I’m querying Shodan‘s REST API directly from SQL Server using SQL Server 2025’s sp_invoke_external_rest_endpoint stored procedure.
In my previous post I looked through Shodan for publicly exposed SQL Server instances.
And, at one of the steps, pulled some of the data into SQL Server to get a better sense of the major versions of SQL Server that were out there on the public internet.
At that point I mentioned I would go into the details about that process in a separate post, so here we are.
What is sp_invoke_external_rest_endpoint?
In short, sp_invoke_external_rest_endpoint has been introduced in SQL Server 2025.
It allows users/developers to interact with HTTPS REST API endpoints from within SQL Server itself.
Obviously, this comes with some security implications since it facilitates interaction with external REST APIs.
It can even be used by an attacker to exfiltrate data from SQL Server to an endpoint that they control.
If you want to read more on data exfiltration from SQL Server with sp_invoke_external_rest_endpoint, I wrote about it in-depth in this post.
In order to be able to use the sp_invoke_external_rest_endpoint stored procedure, the following requirements must be met:
- The “external rest endpoint enabled” instance level configuration needs to be enabled (set to 1).
This enables or disables invocations of external REST endpoints. - Logins that need to be able to execute sp_invoke_external_rest_endpoint must be granted the
EXECUTE ANY EXTERNAL ENDPOINTpermission.
Initial configuration
I’m doing this on a SQL Server 2025 container running on my QNAP NAS.
First, I need to enable the external rest endpoint configuration option.
| 1 2 3 4 5 6 | USE [master]; GO EXEC sp_configure 'external rest endpoint enabled', 1; GO RECONFIGURE; GO |
Next, I create a database to store the database scoped credential that contains the API key.
| 1 2 | CREATE DATABASE [shodan_test] GO |
To actually be able to create a credential I’ll need to create a master key.
| 1 2 3 4 | USE [shodan_test]; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0MeP4sS!'; GO |
Then I open the master key, create the database scoped credential containing the Shodan API key, and then close the master key.
| 1 2 3 4 5 6 7 8 9 10 | USE [shodan_test]; GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'S0MeP4sS!'; GO CREATE DATABASE SCOPED CREDENTIAL [https://api.shodan.io/shodan/host/search] WITH IDENTITY = 'HTTPEndpointQueryString', SECRET = '{"key":"YourShodanAPIKeyGoesHere"}'; GO CLOSE MASTER KEY; GO |
An important note here is that Shodan takes the API key as part of the URL and not part of the request header.
This means that the database scoped credential needs to be defined as follows:
- The credential’s
IDENTITYhas to beHTTPEndpointQueryString. - The SECRET has to be in a flat JSON format with the key and value pair.
- And the name of the database scoped credential has to be the base endpoint, without the query and other parameters.
It’s a bit more clear once you see an example of how the endpoint URL would look like when called using curl:
| 1 | https://api.shodan.io/shodan/host/search?key=YourShodanAPIKeyGoesHere&query=[query][&facets=[facets]] |
I opted for the database-scoped credential approach over inlining the API key in the URL.
While the latter works for quick tests, it exposes the key in SQL Server’s plan cache, Extended Events sessions, and Profiler traces (eww).
Anyone with access to DMVs like sys.dm_exec_query_text or the ability to run trace captures could extract the credential.
The named credential approach keeps the key out of the plan cache while still being accessible to authorized procedures.
Finally create a few tables to dump the results.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | USE [shodan_test]; GO CREATE TABLE [dbo].[raw_json_result] ([id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, [query] NVARCHAR(100), [result] JSON, [result_date] DATETIME2(3) DEFAULT GETDATE()); GO CREATE TABLE [dbo].[sql_server_results] ([id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, [product] NVARCHAR(200), [version] NVARCHAR(20), [ip] NVARCHAR(39), [port] INT, [country] NVARCHAR(100), [city] NVARCHAR(100), [org] NVARCHAR(200), [exposed_fqdn] NVARCHAR(200), [netbios_name] NVARCHAR(200), [os] NVARCHAR(200), [last_seen] DATETIME2(6)); GO CREATE TABLE [dbo].[version_counts] ([id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, [product_version] NVARCHAR(200), [match_count] INT); GO |
Retrieving host data from Shodan with SQL Server 2025’s sp_invoke_external_rest_endpoint
Now that all the prerequisites are in place, I query Shodan for data about exposed SQL Server instances using the same query string as in the previous post “ms-sql -browser”.
Since the query string has to be URL-encoded, I use a + instead of the space, so it turns into “ms-sql+-browser”.
I run the following T-SQL as a whole:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | USE [shodan_test]; GO /*Relevant parameters*/ DECLARE @response NVARCHAR(MAX), @shodan_query NVARCHAR(100) = N'ms-sql+-browser' /*actual search query*/, @shodan_base_url NVARCHAR(100) = N'https://api.shodan.io/shodan/host/search', @shodan_query_url NVARCHAR(200); /*Put the query API URL together*/ SET @shodan_query_url = @shodan_base_url + N'?query='+ @shodan_query; /*Send the request to the Shodan's API*/ EXEC sys.sp_invoke_external_rest_endpoint @url = @shodan_query_url, @credential = @shodan_base_url, @method = 'GET', @timeout = 30, @response = @response OUTPUT; /*Request status and raw JSON*/ SELECT JSON_VALUE(@response, '$.response.status.http.code') AS [status_code], @response AS [full_json_response]; /*total matches (this is not the total number of results returned)*/ SELECT JSON_VALUE(@response, '$.result.total') AS [total_matches]; /*Dump data to tables*/ /*Dump raw JSON response*/ INSERT INTO [dbo].[raw_json_result] ([query], [result]) VALUES (@shodan_query,@response); /*Extract and dump SQL Server results data*/ INSERT INTO [dbo].[sql_server_results] ([product], [version], [ip], [port], [country], [city], [org], [exposed_fqdn], [netbios_name], [os], [last_seen]) SELECT JSON_VALUE([m].[value], '$.product') AS [product], JSON_VALUE([m].[value], '$.version') AS [version], JSON_VALUE([m].[value], '$.ip_str') AS [ip], CAST(JSON_VALUE([m].[value], '$.port') AS INT) AS [port], JSON_VALUE([m].[value], '$.location.country_name') AS [country], JSON_VALUE([m].[value], '$.location.city') AS [city], JSON_VALUE([m].[value], '$.org') AS [org], JSON_VALUE([m].[value], '$.ntlm.fqdn') AS [exposed_fqdn], JSON_VALUE([m].[value], '$.ntlm.netbios_domain_name') AS [netbios_name], JSON_VALUE([m].[value], '$.os') AS [os], CONVERT(DATETIME2(6),JSON_VALUE([m].[value], '$.timestamp'),126) AS [last_seen] FROM OPENJSON(@response, '$.result.matches') AS [m]; GO |
The T-SQL above declares variables for the query and endpoint URL, constructs the full request URL, invokes the REST endpoint, extracts the HTTP status code, extracts the total match count.
It then dumps the full raw JSON response in the raw_json_result table (which uses 2025’s new JSON data type), then uses JSON_VALUE to shred the JSON matches into the sql_server_results table for analysis.
In the above screenshot we can see that the response code was 200 (success), and that there are 209099 matches for that query.
Short detour
One thing to keep in mind here is that Shodan returns 100 such results per API call.
You can advance through the results, 100 at a time, by using the optional page parameter (the default is page=1 so you already get the first page even when not using the parameter).
More on that here.
This means that you can use the total matches number and the page parameter in a WHILE loop to… uhm.. well, loop through the 209099/100 = 2091 pages to retrieve all the results.
Just note that this has some cost and throttling implications.
Each search API call costs one Shodan query credit, so that would amount to 2091 query credits, and you would have to add a 1-2 second WAITFOR DELAY in the loop to avoid getting rate limited by Cloudflare/Shodan.
There are better ways of getting bulk data from Shodan outside of SQL Server, and then you can import that data in SQL Server to slice and dice as you might need.
But, for small data sets, using sp_invoke_external_rest_endpoint is perfectly fine.
And you can narrow down the number of results with additional search filters.
Detour done
And now, I can query the sql_server_results table to see what it contains.
| 1 2 3 4 5 | SELECT [product], [version], [ip], [port], [country], [city], [org], [exposed_fqdn], [netbios_name], [os], [last_seen] FROM [dbo].[sql_server_results]; GO |
Note that there are 2 stragglers here that have nothing to do with SQL Server.
Those being the MS RPC Endpoint Mapper entries on rows 2 and 17.
Shodan gets some of these wrong, but they’re well under 0.5% of the total results for this query, and I get to confirm that later on.
Retrieving facet data from Shodan with sp_invoke_external_rest_endpoint
First things first: What’s a facet in Shodan?
The simplest T-SQL related analogy is to think of a facet as a GROUP BY equivalent through which Shodan aggregates the result data of your query.
You want to know how many SQL Server instances are exposed on the public internet for each country? Then you’ll need to use the country facet.
Want to know how many SQL Server instance for each identified organization? Then you’ll need the org facet.
For my example I’m only interested in the product facet because it also allows us to get the human-friendly major versions of SQL Server.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | USE [shodan_test]; GO DECLARE @response NVARCHAR(MAX), @shodan_query NVARCHAR(100) = N'ms-sql+-browser&facets=product:500' /*actual search query + facet and facet result count*/, @shodan_base_url NVARCHAR(100) = N'https://api.shodan.io/shodan/host/search', @shodan_query_url NVARCHAR(200); SET @shodan_query_url = @shodan_base_url + N'?query='+ @shodan_query; EXEC [sys].[sp_invoke_external_rest_endpoint] @url = @shodan_query_url, @credential = @shodan_base_url, @method = 'GET', @timeout = 30, @response = @response OUTPUT; INSERT INTO [dbo].[raw_json_result] ([query], [result]) VALUES (@shodan_query,@response); INSERT INTO [version_counts] ([product_version],[match_count]) SELECT JSON_VALUE([f].[value], '$.value') AS [product_version], CAST(JSON_VALUE([f].[value], '$.count') AS INT) AS [match_count] FROM OPENJSON(@response, '$.result.facets.product') AS [f]; GO |
The only differences here are:
- The value of the
@shodan_queryparameter now also includes&facets=product:500.
Where facets=product is pretty much self-explanatory and :500 tells the REST API how many results I want for that facet. I know that there are around 320 so I use 500 just to be sure. - The number of columns I’m interested in is reduced to 2 and the data is going into a different target table.
Additional aggregation
Now, the content of the version_counts table looks like this:
| 1 2 3 | SELECT [product_version],[match_count] FROM [dbo].[version_counts]; GO |
As you can see, each patch level of every major version is treated by Shodan as a distinct product.
To get an actual idea of the distribution by Major version, I need to do some additional aggregation.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT 0 AS [position], CASE WHEN [product_version] LIKE N'%R2%' THEN LEFT([product_version],22) ELSE LEFT([product_version],19) END AS [major_version], SUM([match_count]) AS [count] FROM [dbo].[version_counts] WHERE [product_version] LIKE N'MS-SQL%' GROUP BY CASE WHEN [product_version] LIKE N'%R2%' THEN LEFT([product_version],22) ELSE LEFT([product_version],19) END UNION ALL SELECT 1 AS [position], N'_total' AS [product_version], SUM([match_count]) AS [count] FROM [dbo].[version_counts] WHERE [product_version] LIKE N'MS-SQL%' ORDER BY [position] ASC, [count] DESC; |
To get a breakdown of SQL Server versions exposed to the public internet.
I’ve commented more on the version distribution in my previous post.
What I want to point out here is that:
- There is a generic “MS-SQL Server” entry with a count of 1097.
These are instances for which Shodan could not determine the version, at least not as part of the product name. - I’m filtering for
[product_version] LIKE N'MS-SQL%'because of the previously mentioned stragglers.
Speaking of stragglers, I can check what they are and how many.
(oh, no, it’s a SELECT *! Someone call the police!)
| 1 2 | SELECT * FROM [dbo].[version_counts] WHERE [product_version] NOT LIKE N'MS-SQL%'; |
As you can see, these have nothing to do with SQL Server and their total count doesn’t even add up to 500
But it’s really interesting to see people exposing RPC, WinRM, and RDP to the public internet.
Conclusion
Querying Shodan’s REST API with SQL Server 2025’s sp_invoke_external_rest_endpoint was a fun little learning exercise for me.
And it can also be pretty handy if your goal is to capture specific data from Shodan in SQL Server.
Just remember to filter Shodan data as much as possible and be mindful of those query credits.
Also, limit access to sp_invoke_external_rest_endpoint as much as possible, and properly store and handle your API keys.
Additionally, if you haven’t read my previous post about internet-exposed SQL Server instances, what versions are out there, where they’re located, and what to do about it, feel free to check it out.