ENGAGE!

A Starfleet Guide to Secure API Calls

Captain's Log, Stardate 99981.3. Our mission: to transmit a document schematic (an HTML string) from the U.S.S. Enterprise's database to a networked fabrication station. This station will generate a Digital Document, place it in a subspace relay buffer, where Starfleet Support Unit's Archival Team will retrieve and log it. This complex, multi-stage operation requires the utmost precision.

The Away Team

Every successful mission depends on its crew. This operation requires a coordinated effort between several key personnel and systems, each with a specific role.

Captain

Issues the high-level command (You, the user).

Commander

Translates orders into specifics (The Stored Procedure).

Lt. Commander

The specialist who performs the action (C# CLR Assembly).

Ensign

Operates the remote station (The API Endpoint).

Archival Cadet

Monitors the buffer and files the report (SSU FTP Process).

Transporter Room Operations

The Chief Engineer has re-calibrated the transporter. The C# code handles the `HTTP POST` request. Its sole responsibility is to transmit the schematic and confirm the fabricator has received it and created the Digital Document.

PADD: DocumentFabricationProtocol.cs

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

public partial class DocumentFabricationProtocol
{
    // A single, static HttpClient is mission-critical for performance.
    private static readonly HttpClient client = new HttpClient();

    [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]
    public static SqlString CreateDocument(SqlString apiUrl, SqlString htmlPayload)
    {
        // Protocol 1.1: Verify fabrication station coordinates.
        if (apiUrl.IsNull || !Uri.TryCreate(apiUrl.Value, UriKind.Absolute, out Uri uri))
            return new SqlString("Error: Invalid API coordinates.");

        // Protocol 2.4: Secure channels only.
        if (uri.Scheme != Uri.UriSchemeHttps)
            return new SqlString("Error: Unsecure channel.");

        try
        {
            // Prepare the schematic (HTML) for transport.
            var content = new StringContent(htmlPayload.Value, Encoding.UTF8, "application/json");
            
            // Transmit data via POST and await response.
            Task<HttpResponseMessage> responseTask = client.PostAsync(uri, content);
            HttpResponseMessage response = responseTask.GetAwaiter().GetResult();
            
            response.EnsureSuccessStatusCode();
            
            // Confirmation that API placed file in buffer. Archival is handled by SSU.
            return new SqlString("Success: Schematic received by fabricator.");
        }
        catch (Exception ex) {
            return new SqlString($"Fabrication Error: {ex.Message}");
        }
    }
}

1.5

From the Bridge: The T-SQL Command

The CLR function is the away team, but this stored procedure is the Captain's command that gives them their orders. This is how you execute the C# code from within SQL Server, passing the necessary mission parameters.

PADD: usp_GenerateArchivedDocument.sql

CREATE PROCEDURE dbo.usp_GenerateArchivedDocument
    @HtmlContent NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    -- Set target fabrication station coordinates. Store this in a config table, not here!
    DECLARE @ApiEndpoint NVARCHAR(2048) = N'https://fabricator.starfleet.local/api/createpdf';
    DECLARE @Status NVARCHAR(MAX);

    -- Log the attempt.
    PRINT 'Authorizing transmission to ' + @ApiEndpoint;

    BEGIN TRY
        -- Execute the CLR function, passing the coordinates and the schematic.
        SELECT @Status = dbo.CreateDocument(@ApiEndpoint, @HtmlContent);

        -- Verify mission success.
        IF (@Status LIKE 'Success:%')
        BEGIN
            PRINT 'Confirmation received. Document fabrication initiated.';
        END
        ELSE
        BEGIN
            -- Report failure to the bridge.
            THROW 50001, @Status, 1;
        END
    END TRY
    BEGIN CATCH
        PRINT 'RED ALERT: Mission failed. An unknown anomaly occurred during transmission.';
        THROW;
    END CATCH
END

Authorizing the Away Team

Before the Stored Procedure can issue commands, the CLR assembly must be beamed into the ship's computer and registered. These commands load the compiled C# code and create the SQL Function that links to it.

PADD: DeploymentDirectives.sql

-- Load the compiled .dll file into SQL Server.
-- The DLL must be on a path accessible by the SQL Server service account.
CREATE ASSEMBLY DocumentFabricationAssembly
FROM 'C:\Starfleet\CLR\DocumentFabricationProtocol.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

-- Create the SQL function that maps to the C# method.
-- Note the parameter types match the C# SqlString types.
CREATE FUNCTION dbo.CreateDocument(
    @apiUrl NVARCHAR(2048), 
    @htmlPayload NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME DocumentFabricationAssembly.[DocumentFabricationProtocol].CreateDocument;
GO

2.5

Bridge Operations Console

These are the preliminary system checks required before deploying any CLR assembly. Execute them to ensure the ship's computer is ready to accept our new protocols.

1. Enable CLR Subsystem

2. Set Database Trustworthy

3. Assembly Ready for Deployment

Shield Harmonics

The `PERMISSION_SET` is like setting our shield strength. Even with a complex mission, we must use the correct clearance. `EXTERNAL_ACCESS` is required for any network communication. Select a level to see the corresponding power allocation on the ship's schematic.

Execute Fabrication

The moment of truth. Enter the document schematic below and authorize the transmission. The mission display will show the status of the entire operation, from fabrication to archival.

Document Schematic (HTML):

STATUS: AWAITING ORDERS