How to Build an AI-Powered SQL Server Query Assistant in ASP.NET Core 10 Using C# (2026)

A natural-language SQL assistant should never connect a model’s free-form output directly to a database executor. Even a read-only SELECT can leak sensitive data or consume too many resources. The safest first version limits the model to selecting from a small reporting catalog.

This guide builds an AI-powered SQL Server query assistant in ASP.NET Core 10. The model selects one intent. C# resolves that label to fixed parameterized SQL and runs it through a principal that can read one reporting view.

In this guide

1. How should natural language become SQL?
2. Create the view and API
3. Test a permitted count and a destructive request
4. Add approval and production limits
5. FAQ and references

How should natural language become SQL?

The model chooses an intent label. C# maps it to fixed SQL with a parameter, and a restricted database user can only read a view.

This design separates interpretation from SQL execution. The prompt contains an approved view name and allowed operations. The model can suggest count_orders, count_open, or rejection. It cannot add a WHERE expression, specify a table name, execute a stored procedure, or change permissions. Application code accepts only exact labels and binds the region as a typed parameter.

Approach	Capability	Main concern
Fixed query templates	Small approved reporting catalog	New reports require code changes
Generated SQL plus parser	More flexible analysis	AST validation, permissions, resource and disclosure controls
Human-reviewed query	Sensitive or exceptional report	Review must be bound to the exact query and parameters

Schema discovery should be curated. Expose approved views and definitions to the model, not credentials, sample private data, hidden table names or arbitrary database metadata. Rebuild the catalog after schema migrations. This fixture writes the schema summary explicitly.

Create the view and API

Install .NET 10, SQL Server and a client that supports GO batches. Use a disposable database. Apply the script with a setup principal, then provision a separate application login mapped to a database user in the restricted role. Check that it does not inherit privileged roles or additional grants. Configure a TLS-validated connection string and store it outside source control.

schema.sql

CREATE TABLE dbo.OrdersLab (
    Id int NOT NULL PRIMARY KEY,
    Region nvarchar(20) NOT NULL,
    Status nvarchar(20) NOT NULL
);
INSERT dbo.OrdersLab VALUES (1,N'North',N'Open'),(2,N'North',N'Closed'),(3,N'South',N'Open');
GO
CREATE VIEW dbo.ReportingOrders AS SELECT Region, Status FROM dbo.OrdersLab;
GO
CREATE ROLE query_assistant_reader;
GRANT SELECT ON dbo.ReportingOrders TO query_assistant_reader;
-- Create a dedicated database user using your chosen authentication mechanism,
-- then add ONLY that user to query_assistant_reader:
-- ALTER ROLE query_assistant_reader ADD MEMBER [YourAssistantUser];

Lab.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup><TargetFramework>net10.0</TargetFramework><ImplicitUsings>enable</ImplicitUsings><Nullable>enable</Nullable></PropertyGroup>
  <ItemGroup><PackageReference Include="Microsoft.Data.SqlClient" Version="7.1.1" /></ItemGroup>
  <ItemGroup><PackageReference Include="System.Security.Cryptography.Pkcs" Version="10.0.14" /></ItemGroup>
</Project>

Program.cs

using Microsoft.Data.SqlClient;
using System.Data;
using System.Net.Http.Headers;
using System.Text.Json;

var b = WebApplication.CreateBuilder(args);
var key = b.Configuration["OpenAI:ApiKey"] ?? throw new InvalidOperationException("Set OpenAI__ApiKey.");
var model = b.Configuration["OpenAI:Model"] ?? throw new InvalidOperationException("Set OpenAI__Model.");
var connection = b.Configuration.GetConnectionString("Reporting")
    ?? throw new InvalidOperationException("Set ConnectionStrings__Reporting.");
b.WebHost.ConfigureKestrel(o => o.Limits.MaxRequestBodySize = 8192);
b.Services.AddHttpClient("model", h =>
{
    h.BaseAddress = new Uri("https://api.openai.com/");
    h.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", key);
    h.Timeout = TimeSpan.FromSeconds(15);
});
var app = b.Build();
app.MapPost("/ask", async (Question body, IHttpClientFactory clients, ILoggerFactory logs, CancellationToken ct) =>
{
    if (string.IsNullOrWhiteSpace(body.Text) || body.Text.Length > 500 ||
        body.Region is not ("North" or "South"))
        return Results.BadRequest("Use a short question and North or South.");
    try
    {
        using var http = clients.CreateClient("model");
        using var reply = await http.PostAsJsonAsync("v1/chat/completions", new
        {
            model, messages = new[] {
                new { role = "system", content = "Available view: ReportingOrders(Region, Status). Return only count_orders for order counts or count_open for open order counts. Otherwise return reject. Never return SQL." },
                new { role = "user", content = body.Text } }
        }, ct);
        if (!reply.IsSuccessStatusCode) return Results.Problem(statusCode: 502);
        using var json = await JsonDocument.ParseAsync(await reply.Content.ReadAsStreamAsync(ct),
            cancellationToken: ct);
        var intent = json.RootElement.GetProperty("choices")[0].GetProperty("message")
            .GetProperty("content").GetString()?.Trim();
        string? sql = intent switch
        {
            "count_orders" => "SELECT COUNT_BIG(*) FROM dbo.ReportingOrders WHERE Region=@region",
            "count_open" => "SELECT COUNT_BIG(*) FROM dbo.ReportingOrders WHERE Region=@region AND Status=N'Open'",
            _ => null
        };
        if (sql is null) return Results.BadRequest("Question is outside the approved catalog.");

        await using var db = new SqlConnection(connection);
        await db.OpenAsync(ct);
        await using var cmd = new SqlCommand(sql, db) { CommandTimeout = 5 };
        cmd.Parameters.Add("@region", SqlDbType.NVarChar, 20).Value = body.Region;
        var count = (long)(await cmd.ExecuteScalarAsync(ct))!;
        logs.CreateLogger("QueryAssistant").LogInformation("Approved query template {Template}", intent);
        return Results.Ok(new { query = intent, count });
    }
    catch (OperationCanceledException) when (!ct.IsCancellationRequested)
    { return Results.Problem(statusCode: 504); }
    catch (SqlException e) when (e.Number == -2)
    { return Results.Problem(statusCode: 504); }
    catch (Exception e) when (e is SqlException or HttpRequestException or JsonException or InvalidOperationException or KeyNotFoundException or IndexOutOfRangeException)
    { return Results.Problem(statusCode: 502); }
});
await app.RunAsync();
record Question(string Text, string Region);

Set OpenAI__ApiKey, a chat-compatible OpenAI__Model, and ConnectionStrings__Reporting. The sample bounds inputs, uses a provider timeout, and uses a separate five-second SQL command timeout. It returns only a count and approved template name. Audit logs record the template, not question text or credentials. The public API would also require verified caller identity, region authorization and request quotas. This demonstration must run on loopback.

Test a permitted count and a destructive request

Start and ask for an approved report

dotnet run --no-launch-profile -- --urls http://127.0.0.1:5080

# In another terminal:
curl -i http://127.0.0.1:5080/ask -H 'Content-Type: application/json' \
  -d '{"text":"How many open orders are there?","region":"North"}'

If the model selects count_open, the fixture returns count 1. A generated string such as DROP TABLE dbo.OrdersLab does not match a catalog entry, so there is no database call. A model could still classify an unrelated question as an allowed report. Limit outputs to authorized aggregates, and verify that application ownership rules prevent a caller from choosing another user’s region.

For an adversarial test, use a fake provider that returns SQL text, a combined label, an oversized response, and an allowed label. Assert that only the exact known label reaches the database. Independently sign in as the SQL service user and verify that the view can be read and writes and underlying-table reads are denied under the deployed permission configuration.

The C# project compiled on .NET 10. A live model and SQL Server were not exercised.

Add approval and production limits

For sensitive reports, persist a proposal containing the caller, approved template, typed parameters and expiry. Require a distinct authorized approver. Execute only after checking the stored proposal version and reauthorizing the current actor. Never interpret a free-form “approved” model response as permission. Use a durable decision and receipt to handle retries.

Query cost matters even when the result is a single count. Add indexes appropriate for filters, inspect execution plans, and cap concurrency. Read-only access limits writes but does not make data safe to disclose. If future requirements truly need generated SQL, validate the parsed structure with a maintained parser and use a separate constrained service. Reject unknown statements, dynamic SQL and unsafe functions. Avoid validating SQL with keyword blacklists alone.

Symptom	Check
Every prompt returns 400	Model content must match an exact catalog label
View cannot be read	Correct role and schema-qualified grant
Query times out	Index, plan and concurrency
User can choose another region	Missing application-level data authorization
Logs contain prompts	Logging scopes and provider telemetry

FAQ and references

Does this execute model-generated SQL? It executes only one of two fixed C# query templates. The model proposes an intent.

Can parameterization make arbitrary generated SQL safe? Parameters protect value positions. They do not authorize a query’s structure or access to data.

Should sensitive queries be human-approved? For high-impact reports, bind approval to the exact operation and verify access again at execution time.

• Microsoft.Data.SqlClient.
• SQL Server permissions.
• ASP.NET PB OpenAI API guide.

Start with the smallest reporting catalog that answers real questions. Add templates through normal code and permission review.