{"id":4357,"date":"2025-03-24T07:59:47","date_gmt":"2025-03-24T14:59:47","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=4357"},"modified":"2025-03-24T07:59:47","modified_gmt":"2025-03-24T14:59:47","slug":"secure-apis-with-data-api-builder-keycloak-and-sql-server","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/secure-apis-with-data-api-builder-keycloak-and-sql-server\/","title":{"rendered":"Secure APIs with Data API Builder, Keycloak, and SQL Server"},"content":{"rendered":"<p data-pm-slice=\"1 1 []\"><div class=\"alert alert-info\"><p class=\"alert-divider\"><i class=\"fabric-icon fabric-icon--Info\"><\/i><strong>Guest Post<\/strong><\/p>Alessio Tugnoli is a Data Engineer at IBM and a SME within the Data &amp; AI practice. He delivers modern data warehouse solutions, leveraging his expertise in SQL, Python, distributed systems, and Azure Cloud \u2014 with Docker always present in his toolbox. Passionate about cybersecurity, he is constantly exploring, testing, and sharing new technologies. Thanks Alessio! <\/div><\/p>\n<p data-pm-slice=\"1 1 []\">In this article, we&#8217;ll explore how to create a secure, on-premises API using <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/data-api-builder\/overview\">Data API Builder<\/a> (DAB), <a href=\"https:\/\/www.keycloak.org\/\">Keycloak<\/a>, and <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/what-is-sql-server?view=sql-server-ver16\">SQL Server<\/a>. You&#8217;ll learn to set up JSON Web Token (JWT) authentication with Role-Based Access Control (RBAC), Attribute-Based Access Control (ABAC), and Row-Level Security (RLS), ideal for secure business scenarios.<\/p>\n<p data-pm-slice=\"1 1 []\"><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/Slide1.png\"><img decoding=\"async\" class=\"aligncenter wp-image-4388 size-full\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/Slide1.png\" alt=\"Image Slide1\" width=\"1142\" height=\"416\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/Slide1.png 1142w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/Slide1-300x109.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/Slide1-1024x373.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/Slide1-768x280.png 768w\" sizes=\"(max-width: 1142px) 100vw, 1142px\" \/><\/a><\/p>\n<p data-pm-slice=\"1 1 []\">Want to replicate this setup?<br data-start=\"515\" data-end=\"518\" \/>All the configuration files, SQL scripts, and Docker setup used in this article are available here: <a href=\"https:\/\/github.com\/yougnoli\/secure-onprem-api-dab-keycloak-sqlserver\">https:\/\/github.com\/yougnoli\/secure-onprem-api-dab-keycloak-sqlserver<\/a><\/p>\n<h2 data-pm-slice=\"1 1 []\">Technologies Overview<\/h2>\n<ul data-spread=\"false\" data-pm-slice=\"3 3 []\">\n<li><strong>Data API Builder (DAB)<\/strong>: A cross-platform tool from Microsoft that allows developers to easily expose REST and GraphQL APIs directly from databases, with built-in support for secure authentication, authorization, and integration with Azure and on-premises environments.<\/li>\n<li><strong>Keycloak<\/strong>: An open-source identity management solution that provides comprehensive authentication and authorization capabilities, including JWT-based security with protocols such as OpenID Connect, OAuth2, and SAML.<\/li>\n<li><strong>SQL Server<\/strong>: Microsoft\u2019s enterprise-grade database solution that supports advanced security measures, including RLS for granular data protection.<\/li>\n<\/ul>\n<h2 data-pm-slice=\"1 3 []\">What We&#8217;ll Cover<\/h2>\n<ul data-spread=\"false\">\n<li>Installing and configuring Data API Builder<\/li>\n<li>Setting up SQL Server for RLS<\/li>\n<li>Securely integrating Keycloak with JWT tokens and setting up for RBAC and ABAC<\/li>\n<li>Validating API security with Postman<\/li>\n<\/ul>\n<h2 data-pm-slice=\"1 3 []\">RBAC and ABAC Explained<\/h2>\n<p>In this implementation, security is managed using two key methodologies:<\/p>\n<ul data-spread=\"false\">\n<li><strong>RBAC (Role-Based Access Control)<\/strong>: Assigns permissions to roles rather than individuals, simplifying permission management. Users are given roles (like <code>reader<\/code>) which determine their allowed operations.<\/li>\n<li><strong>ABAC (Attribute-Based Access Control)<\/strong>: Uses dynamic attributes (e.g., privacy levels assigned to users) to determine access. It allows for flexible policies that adapt to user attributes, ensuring users can access only the data they&#8217;re authorized for.<\/li>\n<\/ul>\n<p data-pm-slice=\"1 1 []\">An example of RBAC and ABAC in the configuration file:<\/p>\n<p data-pm-slice=\"1 1 []\"><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/RBAC-ABAC-1.png\"><img decoding=\"async\" class=\"alignnone wp-image-4375\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/RBAC-ABAC-1-300x129.png\" alt=\"Image RBAC ABAC\" width=\"598\" height=\"257\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/RBAC-ABAC-1-300x129.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/RBAC-ABAC-1-1024x442.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/RBAC-ABAC-1-768x331.png 768w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/RBAC-ABAC-1.png 1491w\" sizes=\"(max-width: 598px) 100vw, 598px\" \/><\/a><\/p>\n<h2 data-pm-slice=\"1 3 []\">Prerequisites<\/h2>\n<ul data-spread=\"false\">\n<li>Windows machine with <a href=\"https:\/\/dotnet.microsoft.com\/download\/dotnet\/8.0\">.NET 8<\/a><\/li>\n<li><a href=\"https:\/\/www.docker.com\/\">Docker<\/a> (for Keycloak)<\/li>\n<li>SQL Server (local or Docker container)<\/li>\n<\/ul>\n<h2 data-pm-slice=\"1 1 []\">Step 1: Install Data API Builder<\/h2>\n<p>Follow Microsoft&#8217;s official installation guide: <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/data-api-builder\/how-to-install-cli\">Installing the Data API Builder CLI<\/a>.<\/p>\n<h2 data-pm-slice=\"1 1 []\">Step 2: Configure SQL Server (local installation)<\/h2>\n<p data-pm-slice=\"1 1 []\">On our local Windows machine, SQL Server will be running alongside Data API Builder, simplifying our environment setup.<\/p>\n<p>Use the following SQL script to create the database and <code>Orders<\/code> table necessary for implementing security configurations:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">CREATE DATABASE Test;\r\nGO\r\n\r\nCREATE LOGIN Oscar WITH PASSWORD = 'SecurePassword123!';\r\nCREATE LOGIN Hannah WITH PASSWORD = 'AnotherSecurePassword456!';\r\nGO\r\n\r\nUSE Test;\r\nGO\r\n\r\nCREATE USER Oscar FOR LOGIN Oscar;\r\nCREATE USER Hannah FOR LOGIN Hannah;\r\nGO\r\n\r\nALTER ROLE db_datareader ADD MEMBER Oscar;\r\nALTER ROLE db_datareader ADD MEMBER Hannah;\r\nGO\r\n\r\nCREATE TABLE Orders (\r\n    id INT IDENTITY(1,1) PRIMARY KEY,\r\n    username NVARCHAR(100) NOT NULL,\r\n    orderid INT NOT NULL,\r\n    privacyLevel INT NOT NULL,\r\n    info NVARCHAR(255)\r\n);\r\nGO\r\n\r\nINSERT INTO Orders (username, orderid, privacyLevel, info)\r\nVALUES \r\n('Oscar', 1001, 1, 'Order details for Oscar #1'),\r\n('Oscar', 1002, 1, 'Order details for Oscar #2'),\r\n('Hannah', 1003, 1, 'Order details for Hannah #1'),\r\n('Oscar', 1004, 3, 'Order details for Oscar #3'),\r\n('Oscar', 1005, 2, 'Order details for Oscar #4'),\r\n('Hannah', 1006, 2, 'Order details for Hannah #2');\r\nGO<\/code><\/pre>\n<p data-pm-slice=\"1 1 []\">This SQL script creates an <code>Orders<\/code> table with a <code>privacyLevel<\/code> column by allowing granular data access control. Orders are associated with users Oscar and Hannah, and will be used for validating our RLS and ABAC configurations.<\/p>\n<p>Implement Row-Level Security:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">CREATE FUNCTION dbo.f_Orders(@username varchar(max))\r\nRETURNS TABLE\r\nWITH SCHEMABINDING\r\nAS RETURN SELECT 1 AS fn_securitypredicate_result\r\nWHERE @username = CAST(SESSION_CONTEXT(N'preferred_username') AS varchar(max));\r\nGO\r\n\r\nCREATE SECURITY POLICY dbo.SecurityPolicy_Orders\r\nADD FILTER PREDICATE dbo.f_Orders(username)\r\nON dbo.Orders;\r\nGO<\/code><\/pre>\n<p data-pm-slice=\"1 1 []\">This RLS configuration ensures that only rows matching the current user\u2019s username (provided by Keycloak in the <code>preferred_username<\/code> claim) are visible. The username is passed to SQL Server via the session context, enabled by setting <code>set-session-context<\/code> to <code>true<\/code> in the Data API Builder (<code>dab-config.json<\/code>) configuration file.<\/p>\n<h2 data-pm-slice=\"1 1 []\">Step 3: Environment Variables<\/h2>\n<p data-pm-slice=\"1 1 []\">To avoid hardcoding sensitive information, create <code>.env<\/code> for sensitive configurations:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\"># SQL Server connection string\r\nSQL_CONNECTION_STRING=Server=localhost; User Id=sa; Database=Test; password=VeryStrongPassword123$; TrustServerCertificate=True; Encrypt=True; \r\n# HTTPS configuration for DAB\r\nASPNETCORE_URLS=https:\/\/localhost:5001;http:\/\/localhost:5000<\/code><\/pre>\n<p data-pm-slice=\"1 1 []\">This approach improves the security and maintainability of the project by allowing sensitive data to be separated from the source code.<\/p>\n<h2 data-pm-slice=\"1 1 []\">Step 4: Creating a Self-Signed Certificate for HTTPS<\/h2>\n<p data-pm-slice=\"1 1 []\">It&#8217;s essential for Keycloak (or any JWT provider) to run over HTTPS because DAB requires secure communication to fetch and validate JWT signing keys. If using a self-signed certificate, you must install it on the host machine running DAB.<\/p>\n<h3 data-pm-slice=\"1 1 []\">Folder Structure<\/h3>\n<p data-pm-slice=\"1 1 []\">The final structure of our demo solution will look like this:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">project-root\/\r\n\u2502\r\n\u251c\u2500\u2500 Dockerfile\r\n\u251c\u2500\u2500 dab-config.json\r\n\u251c\u2500\u2500 .env\r\n\u2514\u2500\u2500 certs\/\r\n    \u251c\u2500\u2500 keycloak.crt\r\n    \u2514\u2500\u2500 keycloak.key<\/code><\/pre>\n<p data-pm-slice=\"1 1 []\">(Note: Some files and folders will be created in later steps.)<\/p>\n<h3 data-pm-slice=\"1 1 []\">Generate Certificate<\/h3>\n<p data-pm-slice=\"1 1 []\">Install WSL if needed (via PowerShell as administrator):<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">wsl --install<\/code><\/pre>\n<p data-pm-slice=\"1 1 []\">Generate the self-signed certificate using WSL terminal (in the certs folder):<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">openssl req -x509 -newkey rsa:4096 -keyout keycloak.key -out keycloak.crt -days 365 -nodes -subj \"\/CN=localhost\"<\/code><\/pre>\n<h3 data-pm-slice=\"1 3 []\">Install Certificate on Windows<\/h3>\n<p>Transfer <code>keycloak.crt<\/code> from WSL to Windows, then:<\/p>\n<ul data-spread=\"false\">\n<li>Double-click <code>keycloak.crt<\/code><\/li>\n<li>Follow the wizard and install it in the \u201cTrusted Root Certification Authorities\u201d store.<\/li>\n<\/ul>\n<h2>Step 5: Setup Keycloak with Docker<\/h2>\n<p>Keycloak is an open-source identity and access management solution providing robust authentication and authorization capabilities using standards such as OpenID Connect, OAuth2, and SAML. For this demo, Keycloak will be configured to run securely over HTTPS within a Docker container.<\/p>\n<p>The following Dockerfile sets up Keycloak, copies the previously generated HTTPS certificate and private key into the container, configures the admin credentials, and starts the Keycloak server in HTTPS mode:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">FROM quay.io\/keycloak\/keycloak:latest\r\n# Copy certificates to container\r\nCOPY certs\/keycloak.crt \/etc\/x509\/https\/tls.crt\r\nCOPY certs\/keycloak.key \/etc\/x509\/https\/tls.key\r\n# Set environment variables for admin user\r\nENV KEYCLOAK_ADMIN=admin\r\nENV KEYCLOAK_ADMIN_PASSWORD=admin\r\n# Launch the server with HTTPS configuration\r\nENTRYPOINT [\"\/opt\/keycloak\/bin\/kc.sh\", \"start\", \"--https-certificate-file=\/etc\/x509\/https\/tls.crt\", \"--https-certificate-key-file=\/etc\/x509\/https\/tls.key\", \"--hostname-strict=false\"]<\/code><\/pre>\n<ul>\n<li data-start=\"1195\" data-end=\"1267\">The <code data-start=\"1201\" data-end=\"1207\">FROM<\/code> instruction uses the latest official Keycloak Docker image.<\/li>\n<li data-start=\"1268\" data-end=\"1333\">SSL certificates are copied into the container to enable HTTPS.<\/li>\n<li data-start=\"1334\" data-end=\"1388\">Admin credentials are set via environment variables.<\/li>\n<li data-start=\"1389\" data-end=\"1452\">Keycloak server is launched with HTTPS explicitly configured.<\/li>\n<\/ul>\n<p>Build the Docker image with the following command:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">docker build -t keycloak-https .\r\n<\/code><\/pre>\n<p>Then, run the container in detached mode, mapping port 8443 on the host to port 8443 in the container:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">docker run -d --name keycloak-https -p 8443:8443 keycloak-https\r\n<\/code><\/pre>\n<p>Once the Keycloak container is running, you can access the administration UI by navigating to:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">https:\/\/localhost:8443<\/code><\/pre>\n<p data-start=\"1925\" data-end=\"1968\">Log in using the default admin credentials:<\/p>\n<ul data-start=\"1970\" data-end=\"2009\">\n<li data-start=\"1970\" data-end=\"1989\">Username: <code data-start=\"1982\" data-end=\"1989\">admin<\/code><\/li>\n<li data-start=\"1990\" data-end=\"2009\">Password: <code data-start=\"2002\" data-end=\"2009\">admin<\/code><\/li>\n<\/ul>\n<p data-start=\"2011\" data-end=\"2118\" data-is-last-node=\"\" data-is-only-node=\"\">After successfully logging in, you will be ready to configure users, roles, and JWT tokens within Keycloak.<\/p>\n<h2 data-start=\"2011\" data-end=\"2118\">Step 6: Keycloak Configuration for Secure API<\/h2>\n<h3 data-start=\"620\" data-end=\"640\">Creating a Realm<\/h3>\n<p data-start=\"642\" data-end=\"772\">A Realm in Keycloak is a security domain that encapsulates users, roles, and clients into an isolated security management context.<\/p>\n<ul data-start=\"774\" data-end=\"861\">\n<li data-start=\"774\" data-end=\"819\">Select <strong data-start=\"783\" data-end=\"799\">Create Realm<\/strong> from the left menu.<\/li>\n<li data-start=\"820\" data-end=\"861\">Provide a Realm name (e.g., <code data-start=\"850\" data-end=\"859\">TestDAB<\/code>).<\/li>\n<\/ul>\n<h3 data-start=\"3067\" data-end=\"3111\">Disabling Mandatory Actions for Testing<\/h3>\n<p data-start=\"3113\" data-end=\"3133\">To simplify testing:<\/p>\n<ul data-start=\"3135\" data-end=\"3251\">\n<li data-start=\"3135\" data-end=\"3251\"><strong data-start=\"3137\" data-end=\"3174\">Authentication<\/strong> from the left menu <strong>&gt; Required Actions<\/strong>: disable all mandatory actions like email verification and password updates.<\/li>\n<\/ul>\n<h3>Setting up Roles and Attributes (RBAC &amp; ABAC)<\/h3>\n<p data-start=\"2434\" data-end=\"2506\">To support ABAC policies in DAB, create a <code data-start=\"2476\" data-end=\"2485\">privacy<\/code> attribute for users:<\/p>\n<ul data-start=\"2508\" data-end=\"2673\">\n<li data-start=\"2508\" data-end=\"2589\"><strong data-start=\"2510\" data-end=\"2543\">Realm Settings<\/strong> from the left menu <strong>&gt; User Profile<\/strong>: Add new attribute (<code data-start=\"2564\" data-end=\"2573\">privacyLevel<\/code>, <strong>Required field<\/strong>: On).<\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/ABAC-privacyLevel.png\"><img decoding=\"async\" class=\"wp-image-4370 size-large alignnone\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/ABAC-privacyLevel-1024x658.png\" alt=\"Image ABAC privacyLevel\" width=\"1024\" height=\"658\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/ABAC-privacyLevel-1024x658.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/ABAC-privacyLevel-300x193.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/ABAC-privacyLevel-768x494.png 768w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/ABAC-privacyLevel.png 1030w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>We will find this attribute in the <strong>Users<\/strong> section when we create users.<\/p>\n<p>To support RBAC, create a <code data-start=\"2476\" data-end=\"2485\">reader<\/code> role in Realm roles to determine the permissions available to users:<\/p>\n<ul>\n<li><strong data-start=\"2510\" data-end=\"2543\">Realm roles <\/strong>from the left menu <strong>&gt; Create role<\/strong>: Enter new Role name (<code data-start=\"2564\" data-end=\"2573\">reader<\/code>).<\/li>\n<\/ul>\n<h3 data-start=\"1169\" data-end=\"1187\">Creating Users<\/h3>\n<p data-start=\"1189\" data-end=\"1265\">Add users corresponding to those created earlier in the SQL Server database:<\/p>\n<ul data-start=\"1267\" data-end=\"1478\">\n<li data-start=\"1267\" data-end=\"1331\">From the left menu, click <strong data-start=\"1295\" data-end=\"1304\">Users<\/strong>, then select <strong data-start=\"1318\" data-end=\"1330\">Create new user<\/strong>.<\/li>\n<li data-start=\"1332\" data-end=\"1478\">Create users:\n<ul data-start=\"1350\" data-end=\"1478\">\n<li data-start=\"1350\" data-end=\"1433\">Username: <code data-start=\"1362\" data-end=\"1369\">Oscar<\/code><\/li>\n<li data-start=\"1350\" data-end=\"1433\">privacyLevel:\u00a0<code data-start=\"1362\" data-end=\"1369\">2<\/code><\/li>\n<\/ul>\n<\/li>\n<li data-start=\"1332\" data-end=\"1478\">From the upper tab, click <strong>Credentials <\/strong>and set a permanent password after disabling the temporary option.<\/li>\n<li data-start=\"1332\" data-end=\"1478\">From the upper tab, click <strong>Role mapping &gt; Assign role &gt; Filter by realm roles <\/strong>(in the table at the top left) <strong>&gt; Assign <\/strong>the <code data-start=\"2564\" data-end=\"2573\">reader<\/code>role.<\/li>\n<\/ul>\n<p>Configure similarly for the <code data-start=\"1448\" data-end=\"1456\">Hannah<\/code> user profile.<\/p>\n<h3>Creating a Client Scope for reader Role and privacyLevel Attribute<\/h3>\n<p>To have the claims associated in the token with the created role and attribute, create the client scopes:<\/p>\n<p><code data-start=\"2564\" data-end=\"2573\">privacyLevel<\/code> Attribute:<\/p>\n<ul>\n<li data-start=\"1267\" data-end=\"1331\">From the left menu, click <strong data-start=\"1295\" data-end=\"1304\">Client Scopes<\/strong>, then select <strong data-start=\"1318\" data-end=\"1330\">Create client scope<\/strong>.\n<ul>\n<li data-start=\"1332\" data-end=\"1478\">Name: <code data-start=\"2564\" data-end=\"2573\">privacyLevelScope<\/code><\/li>\n<li data-start=\"1332\" data-end=\"1478\">Type: Default<\/li>\n<li data-start=\"1332\" data-end=\"1478\">Include in token scope: On<\/li>\n<\/ul>\n<\/li>\n<li data-start=\"1332\" data-end=\"1478\">From the upper tab, click <strong>Mappers &gt; Configure a new mapper &gt; User Attribute<\/strong>\n<ul>\n<li data-start=\"1332\" data-end=\"1478\">Name: <code data-start=\"2564\" data-end=\"2573\">privacyLevelMapper<\/code><\/li>\n<li data-start=\"1332\" data-end=\"1478\">User Attribute: <code data-start=\"2564\" data-end=\"2573\">privacyLevel<\/code><\/li>\n<li data-start=\"1332\" data-end=\"1478\"><label class=\"pf-v5-c-form__label\" for=\"config.claim\ud83c\udf7aname\"><span class=\"pf-v5-c-form__label-text\">Token Claim Name: <code data-start=\"2564\" data-end=\"2573\">privacy<\/code><\/span><\/label><\/li>\n<li data-start=\"1332\" data-end=\"1478\">Claim JSON Type: int<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/privacyLevelMapper.png\"><img decoding=\"async\" class=\"wp-image-4377 size-full alignnone\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/privacyLevelMapper.png\" alt=\"Image privacyLevelMapper\" width=\"887\" height=\"812\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/privacyLevelMapper.png 887w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/privacyLevelMapper-300x275.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/privacyLevelMapper-768x703.png 768w\" sizes=\"(max-width: 887px) 100vw, 887px\" \/><\/a><\/p>\n<p><code data-start=\"2564\" data-end=\"2573\">reader<\/code>\u00a0Role:<\/p>\n<ul>\n<li data-start=\"1267\" data-end=\"1331\">From the left menu, click <strong data-start=\"1295\" data-end=\"1304\">Client Scopes<\/strong>, then select <strong data-start=\"1318\" data-end=\"1330\">Create client scope<\/strong>.\n<ul>\n<li data-start=\"1332\" data-end=\"1478\">Name: <code data-start=\"2564\" data-end=\"2573\">rolesScope<\/code><\/li>\n<li data-start=\"1332\" data-end=\"1478\">Type: Default<\/li>\n<li data-start=\"1332\" data-end=\"1478\">Include in token scope: On<\/li>\n<\/ul>\n<\/li>\n<li data-start=\"1332\" data-end=\"1478\">From the upper tab, click <strong>Mappers &gt; Configure a new mapper &gt; User Realm Role<\/strong>\n<ul>\n<li data-start=\"1332\" data-end=\"1478\">Name: <code data-start=\"2564\" data-end=\"2573\">rolesMapper<\/code><\/li>\n<li data-start=\"1332\" data-end=\"1478\">Multivalued: On<\/li>\n<li data-start=\"1332\" data-end=\"1478\"><label class=\"pf-v5-c-form__label\" for=\"config.claim\ud83c\udf7aname\"><span class=\"pf-v5-c-form__label-text\">Token Claim Name: <code data-start=\"2564\" data-end=\"2573\">roles<\/code><\/span><\/label><\/li>\n<li data-start=\"1332\" data-end=\"1478\">Claim JSON Type: String<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/rolesMapper.png\"><img decoding=\"async\" class=\"wp-image-4378 size-full alignnone\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/rolesMapper.png\" alt=\"Image rolesMapper\" width=\"821\" height=\"749\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/rolesMapper.png 821w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/rolesMapper-300x274.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/rolesMapper-768x701.png 768w\" sizes=\"(max-width: 821px) 100vw, 821px\" \/><\/a><\/p>\n<h3 data-start=\"863\" data-end=\"887\">Configuring a Client<\/h3>\n<p data-start=\"889\" data-end=\"996\">The Client represents the application protected by Keycloak. Create the client with the following settings:<\/p>\n<ul data-start=\"998\" data-end=\"1125\">\n<li data-start=\"1267\" data-end=\"1331\">From the left menu, click <strong data-start=\"1295\" data-end=\"1304\">Clients<\/strong>, then select <strong data-start=\"1318\" data-end=\"1330\">Create client<\/strong>.<\/li>\n<li data-start=\"998\" data-end=\"1027\"><strong data-start=\"1000\" data-end=\"1013\">Client ID<\/strong>: <code data-start=\"1015\" data-end=\"1027\">dab-client<\/code><\/li>\n<li data-start=\"998\" data-end=\"1027\"><strong>Authentication flow<\/strong>: Direct access grants (\u201cResource Owner Password Credentials Grant\u201d). Useful for quick testing, but not ideal in production because the client has access to username\/password of user.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/client-config.png\"><img decoding=\"async\" class=\"alignnone wp-image-4379\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/client-config-300x151.png\" alt=\"Image client config\" width=\"564\" height=\"284\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/client-config-300x151.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/client-config-768x386.png 768w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/client-config.png 778w\" sizes=\"(max-width: 564px) 100vw, 564px\" \/><\/a><\/p>\n<p>Leave the remaining settings as default.<\/p>\n<p>Finally, associate the scopes (<code data-start=\"2564\" data-end=\"2573\">privacyLevelScope<\/code>, <code data-start=\"2564\" data-end=\"2573\">rolesScope<\/code>) created to the <code data-start=\"1927\" data-end=\"1939\">dab-client<\/code> under <strong data-start=\"1946\" data-end=\"1986\">Clients &gt; dab-client &gt; Client Scopes <\/strong>upper tab <strong>&gt; Add client scope<\/strong>.<\/p>\n<p>Note: they may have been imported automatically to the client. Verify in the list.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/add-scopes.png\"><img decoding=\"async\" class=\"alignnone wp-image-4380\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/add-scopes-300x185.png\" alt=\"Image add scopes\" width=\"569\" height=\"351\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/add-scopes-300x185.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/add-scopes-768x474.png 768w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/03\/add-scopes.png 778w\" sizes=\"(max-width: 569px) 100vw, 569px\" \/><\/a><\/p>\n<p>Add as Default.<\/p>\n<h2>Step 7: Testing the JWT Token in Postman<\/h2>\n<p>After configuring Keycloak, it is essential to verify that JWT tokens are generated correctly. This test will confirm that the authentication setup using the <strong data-start=\"360\" data-end=\"405\">Resource Owner Password Credentials Grant<\/strong> method works as expected.<\/p>\n<p data-start=\"739\" data-end=\"853\">To obtain a JWT token from Keycloak, we&#8217;ll perform a POST request in Postman to the token endpoint of our configured realm.<\/p>\n<ul data-start=\"855\" data-end=\"956\">\n<li data-start=\"855\" data-end=\"875\"><strong data-start=\"857\" data-end=\"867\">Method<\/strong>: <code data-start=\"869\" data-end=\"875\">POST<\/code><\/li>\n<li data-start=\"876\" data-end=\"956\"><strong data-start=\"878\" data-end=\"885\">URL<\/strong>: <code data-start=\"887\" data-end=\"956\">https:\/\/localhost:8443\/realms\/TestDAB\/protocol\/openid-connect\/token<\/code><\/li>\n<\/ul>\n<p data-start=\"996\" data-end=\"1121\">In the <strong data-start=\"1003\" data-end=\"1011\">Body<\/strong> section of our Postman request, select the <code data-start=\"1056\" data-end=\"1079\">x-www-form-urlencoded<\/code> tab and include the following parameters:<\/p>\n<div class=\"overflow-x-auto contain-inline-size\">\n<table data-start=\"1123\" data-end=\"1752\">\n<thead data-start=\"1123\" data-end=\"1227\">\n<tr data-start=\"1123\" data-end=\"1227\">\n<th data-start=\"1123\" data-end=\"1139\">Key<\/th>\n<th data-start=\"1139\" data-end=\"1161\">Value<\/th>\n<th data-start=\"1161\" data-end=\"1227\">Description<\/th>\n<\/tr>\n<\/thead>\n<tbody data-start=\"1333\" data-end=\"1752\">\n<tr data-start=\"1333\" data-end=\"1437\">\n<td><code data-start=\"1335\" data-end=\"1347\">grant_type<\/code><\/td>\n<td><code data-start=\"1351\" data-end=\"1361\">password<\/code><\/td>\n<td>Specifies the OAuth2 grant type<\/td>\n<\/tr>\n<tr data-start=\"1438\" data-end=\"1542\">\n<td><code data-start=\"1440\" data-end=\"1451\">client_id<\/code><\/td>\n<td><code data-start=\"1456\" data-end=\"1468\">dab-client<\/code><\/td>\n<td>The Keycloak client ID you created<\/td>\n<\/tr>\n<tr data-start=\"1543\" data-end=\"1647\">\n<td><code data-start=\"1545\" data-end=\"1555\">username<\/code><\/td>\n<td><code data-start=\"1561\" data-end=\"1568\">Oscar<\/code><\/td>\n<td>Username of the user for whom you&#8217;re generating the token<\/td>\n<\/tr>\n<tr data-start=\"1648\" data-end=\"1752\">\n<td><code data-start=\"1650\" data-end=\"1660\">password<\/code><\/td>\n<td><em data-start=\"1666\" data-end=\"1685\">(User\u2019s password)<\/em><\/td>\n<td>The user&#8217;s password configured earlier in Keycloak<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p data-start=\"1754\" data-end=\"1944\">This configuration allows Postman to send an authentication request to Keycloak, specifying that we want to obtain a JWT token for the user <strong data-start=\"1895\" data-end=\"1904\">Oscar<\/strong> through the <strong data-start=\"1917\" data-end=\"1931\">dab-client<\/strong> application.<\/p>\n<p data-start=\"1946\" data-end=\"2068\">When correctly configured, Keycloak responds with a JSON payload containing the JWT token within the <code data-start=\"2047\" data-end=\"2061\">access_token<\/code> field.<\/p>\n<p data-start=\"2103\" data-end=\"2285\">To verify that our JWT token is correctly structured and contains the expected claims, copy the <code data-start=\"2200\" data-end=\"2214\">access_token<\/code> value from the response in Postman and go to <a href=\"https:\/\/jwt.io\" target=\"_new\" rel=\"noopener\" data-start=\"2260\" data-end=\"2284\">jwt.io<\/a>:<\/p>\n<ul data-start=\"2287\" data-end=\"2397\">\n<li data-start=\"2287\" data-end=\"2332\">Paste the token into the &#8220;Encoded&#8221; section.<\/li>\n<li data-start=\"2333\" data-end=\"2397\">Ensure the following claims match the configuration in our <code data-start=\"2844\" data-end=\"2861\">dab-config.json<\/code>(Step 8) file and our Keycloak setup:<\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul data-start=\"2892\" data-end=\"3032\">\n<li data-start=\"2892\" data-end=\"2912\"><code data-start=\"2894\" data-end=\"2899\">iss<\/code> (issuer URL)<\/li>\n<li data-start=\"2913\" data-end=\"2931\"><code data-start=\"2915\" data-end=\"2920\">aud<\/code> (audience)<\/li>\n<li data-start=\"2932\" data-end=\"2974\"><code data-start=\"2934\" data-end=\"2941\">roles<\/code> (assigned roles and <code data-start=\"2965\" data-end=\"2973\">reader<\/code>)<\/li>\n<li data-start=\"2975\" data-end=\"2994\"><code data-start=\"2977\" data-end=\"2983\">preferred_username<\/code>\u00a0(username)<\/li>\n<li data-start=\"2995\" data-end=\"3032\"><code data-start=\"2997\" data-end=\"3006\">privacy<\/code> (privacy attribute level)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Keycloak might include additional claims. If necessary, unnecessary claims can be removed through the <strong data-start=\"3151\" data-end=\"3168\">Client Scopes<\/strong> settings in Keycloak for cleaner token management.<\/p>\n<h2>Step 8: Creation of the <code data-start=\"202\" data-end=\"219\">dab-config.json<\/code> Configuration File<\/h2>\n<p data-start=\"240\" data-end=\"494\">The <code data-start=\"244\" data-end=\"261\">dab-config.json<\/code> file is the core configuration of Data API Builder (DAB). It defines how DAB connects and interacts with the SQL Server database, manages authentication, and specifies how data entities will be exposed through REST APIs and GraphQL.<\/p>\n<p data-start=\"496\" data-end=\"561\">Here&#8217;s the complete configuration file tailored for our scenario:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">{\r\n  \"$schema\": \"https:\/\/github.com\/Azure\/data-api-builder\/releases\/download\/v1.2.10\/dab.draft.schema.json\",\r\n  \"data-source\": {\r\n    \"database-type\": \"mssql\",\r\n    \"connection-string\": \"@env('SQL_CONNECTION_STRING')\",\r\n    \"options\": {\r\n      \"set-session-context\": true\r\n    }\r\n  },\r\n  \"runtime\": {\r\n    \"rest\": {\r\n      \"enabled\": true,\r\n      \"path\": \"\/api\",\r\n      \"request-body-strict\": true\r\n    },\r\n    \"graphql\": {\r\n      \"enabled\": true,\r\n      \"path\": \"\/graphql\",\r\n      \"allow-introspection\": true\r\n    },\r\n    \"host\": {\r\n      \"cors\": {\r\n        \"origins\": [\"*\"],\r\n        \"allow-credentials\": false\r\n      },\r\n      \"authentication\": {\r\n        \"provider\": \"AzureAD\",\r\n        \"jwt\": {\r\n          \"audience\": \"account\",\r\n          \"issuer\": \"https:\/\/localhost:8443\/realms\/TestDAB\"\r\n        }\r\n      },\r\n      \"mode\": \"development\"\r\n    }\r\n  },\r\n  \"entities\": {\r\n    \"Orders\": {\r\n      \"source\": {\r\n        \"object\": \"dbo.Orders\",\r\n        \"type\": \"table\"\r\n      },\r\n      \"graphql\": {\r\n        \"enabled\": true,\r\n        \"type\": {\r\n          \"singular\": \"Order\",\r\n          \"plural\": \"Orders\"\r\n        }\r\n      },\r\n      \"rest\": {\r\n        \"enabled\": true\r\n      },\r\n      \"permissions\": [\r\n        {\r\n          \"role\": \"reader\",\r\n          \"actions\": [\r\n            {\r\n              \"action\": \"read\",\r\n              \"policy\": {\r\n                \"database\": \"@item.privacyLevel lt @claims.privacy\"\r\n              }\r\n            }\r\n          ]\r\n        }\r\n      ]\r\n    }\r\n  }\r\n}\r\n<\/code><\/pre>\n<h3 data-start=\"2001\" data-end=\"2037\">Explanation of <code data-start=\"2020\" data-end=\"2037\">dab-config.json<\/code><\/h3>\n<ul data-start=\"2039\" data-end=\"3541\">\n<li data-start=\"2039\" data-end=\"2201\"><strong data-start=\"2041\" data-end=\"2058\"><code data-start=\"2043\" data-end=\"2056\">data-source<\/code><\/strong>: Defines the database type (<code data-start=\"2087\" data-end=\"2094\">mssql<\/code>) and securely references the SQL connection string using environment variables defined in the <code data-start=\"2189\" data-end=\"2195\">.env<\/code> file.<\/li>\n<li data-start=\"2202\" data-end=\"2500\"><strong data-start=\"2204\" data-end=\"2229\"><code data-start=\"2206\" data-end=\"2227\">set-session-context<\/code><\/strong>: This parameter is crucial as it enables DAB to pass JWT claims (e.g., username from Keycloak) into SQL Server\u2019s session context. This allows SQL Server to enforce security policies such as Row-Level Security (RLS) accurately, based on the authenticated user&#8217;s identity.<\/li>\n<li data-start=\"2501\" data-end=\"3037\"><strong data-start=\"2503\" data-end=\"2535\">Authentication Configuration<\/strong>:\n<ul data-start=\"2539\" data-end=\"3037\">\n<li data-start=\"2539\" data-end=\"2727\"><strong data-start=\"2541\" data-end=\"2555\"><code data-start=\"2543\" data-end=\"2553\">provider<\/code><\/strong>: Set to <code data-start=\"2564\" data-end=\"2573\">AzureAD<\/code>, which in this context means that DAB will validate JWT tokens provided by external identity providers compatible with OpenID Connect (such as Keycloak).<\/li>\n<li data-start=\"2730\" data-end=\"3037\"><strong data-start=\"2732\" data-end=\"2753\">JWT Configuration<\/strong>:\n<ul data-start=\"2759\" data-end=\"3037\">\n<li data-start=\"2759\" data-end=\"2880\"><code data-start=\"2761\" data-end=\"2771\">audience<\/code>: Must exactly match the <code data-start=\"2796\" data-end=\"2801\">aud<\/code> claim present in the JWT token generated by Keycloak (<code data-start=\"2856\" data-end=\"2865\">account<\/code> in this case).<\/li>\n<li data-start=\"2885\" data-end=\"3037\"><code data-start=\"2887\" data-end=\"2895\">issuer<\/code>: Must match the URL of your Keycloak realm (e.g., <code data-start=\"2946\" data-end=\"2985\">https:\/\/localhost:8443\/realms\/TestDAB<\/code>). This ensures that tokens are validated correctly.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li data-start=\"3038\" data-end=\"3541\"><strong data-start=\"3040\" data-end=\"3071\">Entities Section (<code data-start=\"3060\" data-end=\"3068\">Orders<\/code>)<\/strong>:\n<ul data-start=\"3075\" data-end=\"3541\">\n<li data-start=\"3075\" data-end=\"3162\">Defines how the SQL Server table (<code data-start=\"3111\" data-end=\"3123\">dbo.Orders<\/code>) is exposed via REST and GraphQL APIs.<\/li>\n<li data-start=\"3165\" data-end=\"3541\"><strong data-start=\"3167\" data-end=\"3182\">Permissions<\/strong>: Implements both RBAC and ABAC:\n<ul data-start=\"3219\" data-end=\"3541\">\n<li data-start=\"3219\" data-end=\"3287\">Assigns the <code data-start=\"3233\" data-end=\"3241\">reader<\/code> role to users, aligning with RBAC principles.<\/li>\n<li data-start=\"3292\" data-end=\"3541\">Includes an ABAC-based policy, explicitly comparing the user&#8217;s JWT token <code data-start=\"3367\" data-end=\"3376\">privacy<\/code> claim (<code data-start=\"3384\" data-end=\"3401\">@claims.privacy<\/code>) to the database&#8217;s <code data-start=\"3421\" data-end=\"3435\">privacyLevel<\/code> column. Users will only be able to read records with a privacy level lower than their assigned attribute.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>With this configuration, we&#8217;re now ready to start Data API Builder by executing the following command at the level of the\u00a0 <code data-start=\"244\" data-end=\"261\">dab-config.json<\/code> file:<\/p>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">dab start<\/code><\/pre>\n<h2>Step 9: Final Test &#8211; Validate Secure API with Keycloak and DAB via Postman<\/h2>\n<p>Now that your environment is fully configured, it&#8217;s time to verify that the authentication and authorization setup is working correctly. We&#8217;ll use Postman to simulate API requests authenticated by JWT tokens issued by Keycloak:<\/p>\n<table style=\"width: 40.5771%;\" data-start=\"1489\" data-end=\"1945\">\n<thead data-start=\"1489\" data-end=\"1565\">\n<tr data-start=\"1489\" data-end=\"1565\">\n<th style=\"width: 33.7607%;\" data-start=\"1489\" data-end=\"1509\">Property<\/th>\n<th style=\"width: 83.9744%;\" data-start=\"1509\" data-end=\"1565\">Value<\/th>\n<\/tr>\n<\/thead>\n<tbody data-start=\"1642\" data-end=\"1945\">\n<tr data-start=\"1642\" data-end=\"1716\">\n<td style=\"width: 33.7607%;\">Method<\/td>\n<td style=\"width: 83.9744%;\"><code data-start=\"1664\" data-end=\"1669\">GET<\/code><\/td>\n<\/tr>\n<tr data-start=\"1717\" data-end=\"1792\">\n<td style=\"width: 33.7607%;\">URL<\/td>\n<td style=\"width: 83.9744%;\"><code data-start=\"1738\" data-end=\"1773\">https:\/\/localhost:5001\/api\/Orders<\/code><\/td>\n<\/tr>\n<tr data-start=\"1793\" data-end=\"1869\">\n<td style=\"width: 33.7607%;\">Authorization Header<\/td>\n<td style=\"width: 83.9744%;\"><code data-start=\"1818\" data-end=\"1846\">Bearer &lt;your_access_token&gt;<\/code><\/td>\n<\/tr>\n<tr data-start=\"1870\" data-end=\"1945\">\n<td style=\"width: 33.7607%;\">Additional Headers<\/td>\n<td style=\"width: 83.9744%;\"><code data-start=\"1893\" data-end=\"1916\">X-MS-API-ROLE: reader<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Make sure to replace <code data-start=\"1968\" data-end=\"1989\">[your_access_token]<\/code> with the JWT token retrieved previously.<\/p>\n<\/div>\n<div><\/div>\n<div>Upon successful validation, you should see a JSON response containing only the rows from the <code data-start=\"2153\" data-end=\"2161\">Orders<\/code> table associated with the authenticated user (<code data-start=\"2208\" data-end=\"2215\">Oscar<\/code>). For example:<\/div>\n<div>\n<pre class=\"prettyprint language-default\"><code class=\"language-default\">{\r\n    \"value\": [\r\n        {\r\n            \"id\": 1,\r\n            \"username\": \"Oscar\",\r\n            \"orderid\": 1001,\r\n            \"privacyLevel\": 1,\r\n            \"info\": \"Order details for Oscar #1\"\r\n        },\r\n        {\r\n            \"id\": 2,\r\n            \"username\": \"Oscar\",\r\n            \"orderid\": 1002,\r\n            \"privacyLevel\": 1,\r\n            \"info\": \"Order details for Oscar #2\"\r\n        }\r\n    ]\r\n}<\/code><\/pre>\n<\/div>\n<div>\n<p data-start=\"2689\" data-end=\"2715\">This result confirms that:<\/p>\n<ul data-start=\"2717\" data-end=\"2986\">\n<li data-start=\"2717\" data-end=\"2794\"><strong data-start=\"2719\" data-end=\"2727\">RBAC<\/strong>: Only users assigned the <code data-start=\"2753\" data-end=\"2761\">reader<\/code> role can read the Orders entity.<\/li>\n<li data-start=\"2795\" data-end=\"2890\"><strong data-start=\"2797\" data-end=\"2805\">ABAC<\/strong>: Data access is correctly restricted based on the user&#8217;s assigned <code data-start=\"2874\" data-end=\"2883\">privacy<\/code> level.<\/li>\n<li data-start=\"2891\" data-end=\"2986\"><strong data-start=\"2893\" data-end=\"2900\">RLS<\/strong>: Users see only rows matching their username, thanks to SQL Server&#8217;s session context.<\/li>\n<\/ul>\n<h2 data-start=\"2988\" data-end=\"3012\">Final Considerations<\/h2>\n<p data-start=\"3014\" data-end=\"3321\">This setup demonstrates a practical, secure configuration suitable for testing and development environments. Before moving to production, consider enhancing this solution with certificates from a trusted Certificate Authority (CA), implementing more granular security policies within Keycloak, and integrating robust auditing and logging mechanisms to ensure secure and traceable operations.<\/p>\n<p data-start=\"3014\" data-end=\"3321\">Thanks for reading the article and good work with Data API Builder and Keycloak!<\/p>\n<p data-start=\"3014\" data-end=\"3321\"><div  class=\"d-flex justify-content-left\"><a class=\"cta_button_link btn-primary mb-24\" href=\"https:\/\/github.com\/yougnoli\/secure-onprem-api-dab-keycloak-sqlserver\" target=\"_blank\">GitHub Repo<\/a><\/div><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this article, we&#8217;ll explore how to create a secure, on-premises API using Data API Builder (DAB), Keycloak, and SQL Server. You&#8217;ll learn to set up JSON Web Token (JWT) authentication with Role-Based Access Control (RBAC), Attribute-Based Access Control (ABAC), and Row-Level Security (RLS), ideal for secure business scenarios. Want to replicate this setup?All the [&hellip;]<\/p>\n","protected":false},"author":184535,"featured_media":4390,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,597,576,648,619],"tags":[560,531,410,552,34],"class_list":["post-4357","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-data-api-builder-2","category-rest","category-security","category-t-sql","tag-data-api-builder","tag-docker","tag-rest","tag-sql-server","tag-t-sql"],"acf":[],"blog_post_summary":"<p>In this article, we&#8217;ll explore how to create a secure, on-premises API using Data API Builder (DAB), Keycloak, and SQL Server. You&#8217;ll learn to set up JSON Web Token (JWT) authentication with Role-Based Access Control (RBAC), Attribute-Based Access Control (ABAC), and Row-Level Security (RLS), ideal for secure business scenarios. Want to replicate this setup?All the [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4357","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/184535"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=4357"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4357\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/4390"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=4357"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=4357"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=4357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}