This repository contains all the configuration files, SQL scripts, and Docker setup to implement a secure on-premises API using:

• Data API Builder (DAB)
• Keycloak
• SQL Server

The setup includes Role-Based Access Control (RBAC), Attribute-Based Access Control (ABAC), and Row-Level Security (RLS), providing a scalable, secure solution without cloud dependencies.

📄 Full detailed guide available on Medium:
Secure On-Premises API with Data API Builder, Keycloak, and SQL Server

• Windows Machine with .NET 8 installed
• Docker
• SQL Server (can be local or Docker-based)

secure-onprem-api-dab-keycloak-sqlserver/
├── README.md
├── dab-config.json
├── .env.example
├── sql/
│   └── setup.sql
├── keycloak/
│   ├── Dockerfile
│   └── certs/
│       ├── keycloak.crt
│       └── keycloak.key
└── openssl/
    └── generate-cert.sh

Run the SQL script to create database, users, table, insert data, and configure Row-Level Security:

# Inside sql/ directory
sqlcmd -S localhost -U sa -P YOUR_PASSWORD -i setup.sql

To generate your self-signed certificate, use:

cd openssl/
bash generate-cert.sh

cd keycloak/
docker build -t keycloak-https .
docker run -d --name keycloak-https -p 8443:8443 keycloak-https

Follow the steps in the article to:

• Create Realm
• Disable required actions
• Create roles, attributes
• Create users: Oscar & Hannah
• Configure Client & Scopes

Edit .env.example with your connection string and rename to .env:

cp .env.example .env

Start DAB:

dab start

Use Postman to:

1. Obtain JWT token from Keycloak.
2. Query DAB API with the token.

Example:

GET https://localhost:5001/api/Orders
Headers:
Authorization: Bearer <your_token>
X-MS-API-ROLE: reader