SQL Server can sit behind a generated data API, an existing platform with its own permission model, or an application that owns every endpoint. A generated API suits stable reads and straightforward changes to selected entities. An application API earns its development cost when a request spans multiple writes, has state transitions, or must return a long-lived contract that survives schema changes.

The shortlist begins with the shape of a real request. An internal lookup may need a narrow view and a service identity. An order approval may need a transaction, a retry policy and a record of the caller. Those are different workloads even when both read the same SQL Server database, and they expose different limitations in hosted connectors, generated endpoints and code frameworks.

SQL Server REST API builders at a glance

Choose a generated API, a prepared operation or a code-first service. Verify authorization and paging through the same SQL Server data.

Nine ways to expose SQL Server through HTTP APIs
ToolAPI modelAuthorization setupTrial evidence
Microsoft Data API builderConfigured SQL entities exposed as RESTRole, action, fields and row policyAuthorized items and documented paging
DreamFactoryConfigured REST API over SQL ServerService, role and permitted operationsActual HTTP response and database scope
Hasura GraphQL Engine v2RESTified saved GraphQL operationsGraphQL source permissions and operation exposureAllowed result through a specifically tested endpoint
CData API ServerConfigured OData and SQL data endpointsPublished data and access controlCompatible client query and authorized result
Progress DataDirect Hybrid Data PipelineOData SQL Server access serviceData source, identity and published resourceExpected scope and client pagination
DirectusAPI over supported SQL Server deploymentCollections and access policiesRole, fields and authorized records
FaucetConfigured SQL Server-backed REST interfaceSupported release and actual endpoint controlsHTTP scope and denied operations
ASP.NET Core Minimal APIsCode-first routes and SQL Server client logicApp authentication, routes and data policyPage contents, errors and server logs
FastAPI with SQLAlchemyCode-first Python API and SQLAlchemy accessDependency validation and query policyAuthorized list, paging and SQL-aware errors

Microsoft Data API builder

Microsoft Data API builder generates REST endpoints from entities declared in a configuration file. For SQL Server an entity can represent a table, a view or a supported stored procedure. Each entity declares which roles can perform actions, and read policies can narrow the rows exposed to a caller.

It fits an internal service over a small, curated schema when the application can work with its generated routes and request format. The runtime connects using its configured database identity. A team that requires per-user database impersonation or multi-step business transactions needs an explicit design for that boundary.

Use the documented SQL Server configuration and expose only the intended entities and actions. Verify authentication, role policy and the fields the role can query.

Use the actual REST continuation link or token in the pagination test. Confirm the next response remains tenant-scoped and a write is rejected under the reader role.

Microsoft’s Swagger UI example shows Data API builder REST endpoints in VS Code
Microsoft’s Swagger UI example shows Data API builder REST endpoints in VS Code. Publisher screenshot
Compatibility
SQL Server and supported Azure SQL engines using configured entities and authentication.
License and trial
Open-source Microsoft project with a self-hosted runtime.

DreamFactory

DreamFactory generates documented REST routes for a configured SQL Server service. API keys and application roles control access to service components and HTTP operations. The administration interface gives teams a place to configure the connection and permissions without writing a controller for each endpoint.

It suits an integration team that manages several database services through one platform and needs a predictable way to grant different callers access. A broad service role can expose more of a legacy database than an integration needs. The practical unit of access is the configured service, its allowed components and the database login together.

Configure the supported SQL Server service and expose the specific endpoint and operations. Record the database principal and product role used in the lab.

Test authorized list access, a request for a conflicting scope and a write attempt. Compare actual response data and logs to the source fixture.

DreamFactory SQL Server setup example with generated API documentation and methods
DreamFactory SQL Server setup example with generated API documentation and methods. Publisher screenshot
Compatibility
SQL Server service on supported DreamFactory editions and deployment targets.
License and trial
Vendor plans and trial terms apply to the SQL Server connector.

Hasura GraphQL Engine v2

Hasura GraphQL Engine v2 can publish a named GraphQL operation through a REST route for SQL Server. Request parameters become GraphQL variables, and the operation has an explicit selection set. The request still runs through the GraphQL endpoint's source and role permissions.

The REST layer is useful when an existing Hasura graph needs a stable URL for an integration that has no GraphQL client. Each exposed operation has to be defined and maintained. The SQL Server feature matrix and the surrounding role configuration govern what the route can actually do.

Verify the SQL Server GraphQL source permissions and configure a saved operation as a RESTified endpoint when that workflow is supported. Check role and row access at the underlying operation.

Run the actual REST path and confirm it returns only intended fields and rows. Verify input validation and denied writes on the public route separately.

Hasura v2 Console form for a saved GraphQL operation exposed as a REST endpoint
Hasura v2 Console form for a saved GraphQL operation exposed as a REST endpoint. Publisher screenshot
Compatibility
Hasura GraphQL Engine v2 with a supported Microsoft SQL Server source.
License and trial
Open-source engine with commercial Hasura editions and services.

CData API Server

CData API Server publishes resources from a connection as an OData-compatible web service. Its SQL Server connector handles access to the database, while the API Server provides endpoint management and access control. OData clients can make requests using the protocol's conventions for selection and filters.

It fits reporting or integration clients that already consume OData and need a vendor-supported endpoint without another application codebase. An OData resource follows the model exposed by the server, so callers that require a hand-designed response or a specific write transaction may need a separate endpoint.

Check the CData API Server supported OData path, SQL Server connector and published entities. Confirm how the permitted OData filters and page navigation are configured.

Test the chosen API client with the approved role and preserve response values. Verify attempts to update object types that are read-only do not change the database.

CData API Server 25.3 documentation shows the OData and OpenAPI endpoint URLs in the endpoint dialog
CData API Server 25.3 documentation shows the OData and OpenAPI endpoint URLs in the endpoint dialog. Publisher screenshot
Compatibility
SQL Server through CData API Server on a supported installation.
License and trial
Commercial product with vendor trial and licensing terms.

Progress DataDirect Hybrid Data Pipeline

Progress DataDirect Hybrid Data Pipeline publishes OData endpoints over configured sources, including SQL Server. The service handles client access and source connections. Its on-premises connector is an option when the database lives on a private network and the service cannot reach it directly.

This fits an organization that already centralizes data access through a gateway and needs a shared connection and policy boundary for several consumers. The application depends on the gateway and its source path. Endpoint availability and identity handling therefore need to be understood alongside SQL Server availability.

Use the documented SQL Server OData configuration and confirm where the service runs and executes queries. Set up the minimum source privileges for the lab.

Verify filtered and paginated queries from the intended application and compare to the known SQL data. Record which operations and unreachable source results are visible.

Progress Hybrid Data Pipeline data store picker shows a Microsoft SQL Server option
Progress Hybrid Data Pipeline data store picker shows a Microsoft SQL Server option. Publisher screenshot
Compatibility
Microsoft SQL Server via a supported Hybrid Data Pipeline data source and gateway.
License and trial
Commercial Progress product with subscription or evaluation options.

Directus

Directus reads the database schema and exposes collections through a REST API. It also stores configuration for the platform's users, permissions and metadata. A team can manage access to items, fields and collections through the same project that serves the API.

It works well when internal users need both an admin interface and API access to the same curated data. Attaching it to an established database adds platform-managed state and a second place to reason about access. A legacy schema with unusual keys or trigger behavior deserves a separate compatibility review before writes are enabled.

Confirm the supported SQL Server version and schema and create narrowly scoped access policies for the fixture collection. Check exposed fields against the application need.

Test both eligible pages and a request for the other tenant's row under the same role. Confirm database user behavior and what the server records for a denied action.

Directus example of an item permission that restricts an update to the signed-in user’s records
Directus example of an item permission that restricts an update to the signed-in user’s records. Publisher screenshot
Compatibility
SQL Server via the supported MSSQL client and Directus deployment versions.
License and trial
Directus source-available license and commercial terms depend on the organization and use.

Faucet

Faucet is a compact API runtime that reads a configuration file and connects to supported databases, including SQL Server. It offers REST access alongside authentication options such as API keys and role-based access. The source project documents the database backends and supported operations.

It is worth considering for a small read service where a self-contained runtime and a curated database surface are enough. The application team owns deployment and support, and should account for the project's maintenance model when the API becomes a production dependency for several consumers.

Review the supported SQL Server and application configuration for the installed Faucet version. Define access to the exact endpoint and output fields needed by the test.

Verify authentication and data scope using the documented route. Capture successful page responses, an out-of-scope request and the result of a disallowed write.

Faucet console role editor showing per-database and table access
Faucet console role editor showing per-database and table access. Publisher screenshot
Compatibility
Faucet binary or container with a supported Microsoft SQL Server configuration.
License and trial
Open-source project with a self-managed deployment.

ASP.NET Core Minimal APIs

ASP.NET Core Minimal APIs let a .NET service define its own HTTP routes, input contracts and authorization rules. SQL Server access lives in the application through a provider such as Microsoft.Data.SqlClient or an ORM. Endpoint code controls how database work maps to responses and transactions.

It fits operations whose rules cannot be expressed cleanly as generic entity changes, such as an approval that updates several tables and adds an audit record. That control takes development and operational ownership across input validation, pooling, error handling, versioning and tests.

Implement the selected route with the approved ASP.NET Core authorization and parameterized SQL. Derive tenant scope from the verified principal and apply a stable sort.

Test actual response codes and page identifiers under the approved caller. Record how query errors and denied writes surface through the app logs and response contract.

Microsoft’s ASP.NET Core tutorial shows the Todo Minimal API responding to a POST request in Visual Studio. Source example uses a tutorial data store
Microsoft’s ASP.NET Core tutorial shows the Todo Minimal API responding to a POST request in Visual Studio. Source example uses a tutorial data store. Publisher screenshot
Compatibility
Supported ASP.NET Core and SQL Server provider versions on the selected .NET runtime.
License and trial
Open-source framework with hosting and database costs determined separately.

FastAPI with SQLAlchemy

FastAPI builds Python endpoints from typed request and response definitions, with OpenAPI generated from the application. SQLAlchemy supplies the database dialect and session or connection layer. Its SQL Server dialect supports clients such as pyodbc with a suitable Microsoft ODBC driver.

This is a practical fit for a Python team that wants explicit routes around existing SQL Server data or stored procedures. The team owns session lifetime, transaction boundaries and stable error responses. Synchronous database calls also require a request execution model that accounts for blocking driver work.

Use the documented API route and a compatible SQLAlchemy SQL Server connection. Choose authentication, valid query inputs and data scoping in the actual service code.

Verify a permitted list and rejected write with the same fixtures. Keep the request, response and source record proof together and test database errors independently.

FastAPI’s example shows an interactive OpenAPI request and response in Swagger UI. The example is independent of a database
FastAPI’s example shows an interactive OpenAPI request and response in Swagger UI. The example is independent of a database. Publisher screenshot
Compatibility
FastAPI and SQLAlchemy with a compatible SQL Server driver on the deployment host.
License and trial
Open-source software with separate hosting and SQL Server licensing.

Choose a tool for your API contract

What you needTools to shortlistWhat to verify
Expose a small set of SQL Server resourcesVerify resource keys, allowed methods and fields, authentication, and exact pagination behavior.
Add HTTP endpoints to an existing API platformCheck which operations or collections you want to expose. Test the account scope on both collection and detail routes.
Serve an OData clientTest the OData version and which resource, metadata and query options the intended client can use.
Own the request and response contract in codePrice writing and operating the API. Test authorization, response fields and paging with real runtime credentials.

Choose which SQL Server data and operations the API exposes

A generated API can expose selected entities and operations quickly. A saved operation can give consumers a fixed shape. Code-first routes can implement custom responses and business logic. Check which role and database identity enforce allowed rows and fields.

Define query limits and a stable order before comparing results. Use the built-in continuation mechanism where the product supplies it, and verify that paging still enforces the authorization policy.

What API contract does the client need?
  • Defined read or write entities with minimal implementation

    Trial a configured API server

    Review exposed operations and row permissions.

  • A fixed set of existing GraphQL operations

    Trial a documented RESTified path

    Verify the underlying query policy and public response.

  • Custom parameters, errors and business logic

    Trial the code-first service

    Verify authorization, SQL binding and stable paging.

Check the authorized REST result through two pages

The fixture defines keys 11 and 13 for tenant 7 and key 12 for tenant 8. A read-only tenant 7 caller should receive 11 and 13 across pages of size one. A conflicting filter or key lookup must leave other-tenant data out. Test an approved write-denial scenario without giving the role write access.

Use the same order and caller through the actual protocol. Keep raw request and response examples from the lab, while preserving tokens through approved secret handling. Compare the result with the independent SQL query.

REST acceptance cases using the SQL fixture
RequestExpected dataProof
First authorized pageKey 11 onlyRole, effective filter and continuation
Follow continuationKey 13 onlyStable ordering and retained scope
Request other tenant's keyNo key 12 dataExpected restricted response
Try a write as readerNo database changeDenied response and unchanged data

How to compare these tools

Expose the same table and test a scoped read, an authorized write and a forbidden operation. Check SQL permissions, pagination and whether repeated writes change the result. Sources checked .

Publish a read-only order catalog and try both account credentials

Prepare the same synthetic rows in the API's disposable SQL Server datasource. Run this reference query in RestApiTrial with the chosen authorized tenant. Set @AfterItemId to 11 for the second page. Configure authorization in the service and compare through the actual client.

SQL

Show the expected scoped ordering for SQL Server REST pagination

T-SQL · 14 lines

SET NOCOUNT ON;IF DB_NAME() <> N'RestApiTrial'    THROW 51000, 'Use the disposable RestApiTrial database.', 1; DECLARE @AuthorizedTenantId int = 7, @AfterItemId int = 0, @PageSize int = 1;WITH Fixture AS (    SELECT TenantId, ItemId, Label    FROM (VALUES (7,11,N'First'),(8,12,N'Other tenant'),(7,13,N'Second'))        AS v(TenantId,ItemId,Label))SELECT TOP (@PageSize) ItemId, LabelFROM FixtureWHERE TenantId = @AuthorizedTenantId AND ItemId > @AfterItemIdORDER BY ItemId;
Review before runningT-SQLUTF-814 lines
  1. Set up the intended API and an authorized read-only role for tenant 7. Compare source fixture results with page one and page two through the actual endpoint.
  2. Request key 12 and try the approved write-denial case in the lab. Check response, stored rows and backend policy for each request.
  3. Record the actual auth and paging controls, request limits and operational errors. Keep one repeatable test for the configured deployment and client.

Conclusion

Choose the smallest API you can support. Compare what each tool needs to keep rows scoped, fields private and paging stable. Use the authenticated test results to price deployment and maintenance before committing to a product.

Frequently asked questions

Can I generate a REST API from an existing SQL Server database?

Yes. Microsoft Data API builder, DreamFactory, CData API Server and other tools can expose selected SQL Server data as HTTP endpoints. Check the supported object types, connection method, and how the selected edition handles API permissions.

What is an OData API for SQL Server?

OData defines conventions for reading and modifying resources over HTTP, including filtering, sorting and field selection. CData API Server and Progress DataDirect Hybrid Data Pipeline document OData access for SQL Server. Verify the protocol version with the consumer.

How do I expose only certain SQL Server rows and fields?

Use authenticated role or account policies that apply to list and individual-item reads. You can also give account-specific database credentials access to scoped SQL views that include only approved fields. Test the paths with ordinary client credentials.

How should I test SQL Server REST pagination?

Choose a stable order that uses a unique key as the last sort field. Read multiple pages from an unchanged dataset and verify that a filter and the account scope still apply to later pages.

When should I build a custom REST API over SQL Server?

Use a framework if the consumer needs custom request or response schemas, application validation or business actions that go beyond configured data endpoints. Include authorization, driver compatibility and ongoing maintenance in the estimate.

Does a SQL Server REST API's query filter enforce tenant scope?

Use verified identity and an approved server-side row-access policy. Test that a caller cannot retrieve another tenant when changing filters or requesting a key directly.

What should we verify in pagination from a generated REST API?

Test the configured stable sort, documented continuation and authorization on each page. Confirm expected record keys and avoid assuming a token is interchangeable across clients or filters.

Related guides

Guide~13 min readUpdated 16 Nov 2026
LinkedInXEmail