Meziantou.Framework.TdsServer 1.0.2

Meziantou.Framework.TdsServer

Meziantou.Framework.TdsServer is a callback-based server library for accepting TDS (SQL Server protocol) connections.

Features

• Accepts TDS client connections over TCP
• Callback for authentication (SQL login + token-based extension data)
• Callback for query handling (SQL batch and RPC requests)
• Optional query engine for stored procedures and a small SQL SELECT subset over IQueryable roots
• Serializes result sets and protocol tokens back to clients
• Native JSON column type serialization (TDSType.JSON, UTF-8 payload)
• ASP.NET Core hosting integration through IHostApplicationBuilder

Usage

using System.Net;
using System.Security.Claims;
using Meziantou.Framework.Tds;
using Meziantou.Framework.Tds.Handler;
using Meziantou.Framework.Tds.Hosting;

var builder = WebApplication.CreateBuilder(args);
builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Loopback);
});

var app = builder.Build();
app.MapTdsHandlers(
    authenticate: async (context, cancellationToken) =>
    {
        if (context.UserName == "sa" && context.Password == "Password123!")
        {
            var identity = new ClaimsIdentity(
            [
                new Claim(ClaimTypes.NameIdentifier, "42"),
                new Claim(ClaimTypes.Name, context.UserName ?? "sa"),
            ],
            authenticationType: "password");
            return TdsAuthenticationResult.Success(userContext: new ClaimsPrincipal(identity));
        }

        return TdsAuthenticationResult.Fail("Login failed");
    },
    query: async (context, cancellationToken) =>
    {
        var user = context.UserContext;

        var resultSet = new TdsResultSet();
        resultSet.Columns.Add(new TdsColumn("Message", TdsColumnType.NVarChar));
        resultSet.Rows.Add(["Hello from TDS server"]);

        var result = new TdsQueryResult();
        result.ResultSets.Add(resultSet);
        return result;
    });

app.Run();

Built-in query engine

The low-level query callback can be backed by the built-in query engine. It maps RPC requests to configured delegates and translates simple SQL text queries to typed IQueryable expressions.

using Meziantou.Framework.Tds.QueryEngine;

var customers = new[]
{
    new Customer(1, "Alice"),
    new Customer(2, "Bob"),
}.AsQueryable();

var queryEngineOptions = new TdsQueryEngineOptions();
queryEngineOptions.AddQueryRoot("customers", customers);
queryEngineOptions.StoredProcedures.Add("GetCustomer", (int id) => customers.Where(customer => customer.Id == id));

app.MapTdsAuthenticationHandler((context, cancellationToken) => ValueTask.FromResult(TdsAuthenticationResult.Success()));
app.MapTdsQueryEngine(queryEngineOptions);

By default, the query engine materializes translated queries by enumerating the IQueryable. You can replace MaterializeAsync to use an async provider-specific materializer such as Entity Framework Core's ToListAsync.

The initial SQL text support is intentionally small: one SELECT statement with optional non-recursive CTEs (WITH ... AS (...)) including CTE column lists, FROM (including derived tables), INNER JOIN (including derived tables), and .NET 10+ LEFT JOIN / RIGHT JOIN (including derived tables), CROSS APPLY for xml.nodes(...), WHERE comparisons combined with AND/OR/NOT, IS NULL/IS NOT NULL, IN/NOT IN (value lists and simple subqueries, including correlated references), EXISTS/NOT EXISTS (simple subqueries, including correlated references), SQL parameters, TOP, DISTINCT, SELECT * for single-table queries, selected columns with aliases, ORDER BY (including OFFSET/FETCH), UNION/UNION ALL, multi-column GROUP BY, HAVING, grouped aggregates (COUNT(*), SUM, MIN, MAX, AVG), scalar arithmetic operators (+, -, *, /, %), string concatenation (+, ||, CONCAT), and scalar functions (UPPER, LOWER, LEN, LTRIM, RTRIM, TRIM, LEFT, RIGHT, SUBSTRING, REPLACE, TRANSLATE, STUFF, STRING_ESCAPE, FORMAT, ISNULL, COALESCE, NULLIF, IIF, CHOOSE, CAST, CONVERT, TRY_CAST, TRY_CONVERT, GETDATE, SYSDATETIME, DATEADD, DATEDIFF, EOMONTH, YEAR, MONTH, DAY, ABS, ROUND, CEILING, FLOOR, POWER, SQRT, EXP, LOG, SIN, COS, TAN, ASIN, ACOS, ATAN, ATN2, COT, ISJSON, JSON_VALUE, JSON_PATH_EXISTS, JSON_QUERY) with SQL-style lax/strict path mode support for JSON path arguments, plus XML methods (.query(), .value(), .exist(), .nodes()) and typed/untyped XML casts.

Built-in scalar function mappings can be customized through TdsQueryEngineOptions.ScalarFunctions (or AddScalarFunction). By default UPPER maps to string.ToUpperInvariant and LOWER maps to string.ToLowerInvariant, but you can replace them (for example with provider-specific ToUpper / ToLower mappings).

Main unsupported SQL features include LIKE, recursive CTEs, many advanced subquery forms (for example scalar subqueries in projections), DML (INSERT/UPDATE/DELETE), and multiple statements/result sets.

Access command text, stored procedure name, and parameters

The query callback gives you access to both SQL batch text and RPC/stored-procedure metadata:

app.MapTdsHandlers(
    authenticate: async (context, cancellationToken) => TdsAuthenticationResult.Success(),
    query: async (context, cancellationToken) =>
    {
        if (context.RequestType == TdsQueryRequestType.SqlBatch)
        {
            var commandText = context.CommandText; // e.g. "SELECT * FROM Users WHERE Id = @id"
        }
        else if (context.RequestType == TdsQueryRequestType.Rpc)
        {
            var procedureName = context.ProcedureName; // e.g. "sp_executesql" or your procedure name
        }

        foreach (var parameter in context.Parameters)
        {
            var parameterName = parameter.Name;
            var rawValue = parameter.Value; // DBNull.Value for SQL NULL
            var columnType = parameter.Type;
            var stringValue = parameter.AsString();
            var intValue = parameter.AsInt32();
            var json = parameter.AsJson();
            var xml = parameter.AsXml();
        }

        var user = context.UserContext; // ClaimsPrincipal from authentication result, if any

        return new TdsQueryResult();
    });

TLS configuration

TLS uses SQL Server PRELOGIN encryption negotiation on the same TCP endpoint. Configure either a PFX file or a PEM certificate/private key pair. For Microsoft.Data.SqlClient interoperability, the server negotiates TLS 1.2 for encrypted TDS sessions.

PFX

builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Loopback);
    options.TlsPfxPath = "certificates/server.pfx";
    options.TlsPfxPassword = "Password123!";
});

PEM certificate + private key

builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Loopback);
    options.TlsPemCertificatePath = "certificates/server.crt.pem";
    options.TlsPemPrivateKeyPath = "certificates/server.key.pem";
});

Use client connection strings with Encrypt=True to require TLS, or Encrypt=Optional to allow non-TLS on the same endpoint.