How to Configure Semantic Kernel SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up semantic kernel sql server read only access with a restricted database user. You assign that user to a read-only database role and pass those credentials to the agent. Many teams start by reusing an app account with broad owner permissions for quick testing. That shortcut lets an AI plugin change or delete production tables if a prompt goes wrong. Giving the agent a strict read-only identity stops unintended writes at the database level. How semantic kernel sql server read only acce

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up semantic kernel sql server read only access with a restricted database user. You assign that user to a read-only database role and pass those credentials to the agent. Many teams start by reusing an app account with broad owner permissions for quick testing. That shortcut lets an AI plugin change or delete production tables if a prompt goes wrong. Giving the agent a strict read-only identity stops unintended writes at the database level.

How semantic kernel sql server read only access works natively

SQL Server separates server logins from database users to control who can read data. A database user is the identity of a login when it connects to a database. For an agent that needs many databases, teams create a login in the master database. For an agent that needs one database, a contained database user is an option. An admin must first enable contained databases on the server and the target database. This contained user has a password and does not tie to a login in master.

Once the user exists, you assign it to a fixed database role. Microsoft provides fixed roles with permissions that admins cannot change. All database roles, including the public role, have basic permission to connect. The standard choice for a read-only agent is the data reader role. Members of this role can read all data from all user tables and views. You can also add the deny data writer role to block any data changes. Members of that role cannot add, modify, or delete data in any user table. Avoid the database owner role, because members can perform maintenance and drop the database.

How Semantic Kernel plugins run database queries

Semantic Kernel connects language models to your data through native functions and plugins. Developers write a data plugin that holds the database connection string for the agent. When a user asks a question, the kernel picks the right plugin to run. Sometimes the plugin runs a fixed query, and sometimes the model writes the query itself. The plugin opens a session to SQL Server using the configured database user.

SQL Server checks the user role before it runs the query from the plugin. If the model tries to delete a row, the database blocks the action. If the model asks to read a table, the data reader role allows it. The database sends the matching rows back to the Semantic Kernel plugin. The plugin then passes those results to the language model to build the final answer.

What native read-only roles solve and what they miss

Native database roles are great at stopping an AI agent from changing your data. They enforce a hard wall between reading rows and writing rows at the storage engine. This setup decides who logs in and which broad actions they can take. Still, fixed database roles do not inspect what individual queries do in flight.

Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Remember that the data reader role grants read access to all user tables and views. If an agent uses that role, it can read every column in every table by default. That includes sensitive fields like emails, phone numbers, card numbers, and national IDs. You can use the deny data reader role, which blocks reading all user tables. To restrict single tables, you must skip fixed roles and manage table permissions by hand.

Even with custom table rules, the database returns raw text for every allowed column. Native roles cannot rewrite sensitive fields in query results before the model processes them. They also do not tie an individual SQL statement back to the user prompt. Security teams need a way to inspect queries and mask fields in real time.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server. It reads plain batches and parameterized queries on the TDS wire while passing Windows login through untouched.

Before a batch hits the server, hoop.dev guardrails check the statement against your rules. Disallowed actions get a native TDS error with your message. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns that match your rules. Every query and decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can I change the permissions of the built-in data reader role?

No, SQL Server does not let you change the permissions of fixed database roles. If you need custom read rules for specific tables, you must create a custom role.

Should I use a standard login or a contained user for my agent?

Use a contained database user if your Semantic Kernel plugin only needs one database. Use a standard login in the master database if the agent queries many databases.

Does a read-only role stop the agent from reading sensitive columns?

No, the built-in data reader role lets the user read all user tables and views. To hide specific fields like card numbers or emails, you need column permissions or data masking.

The query runs. The emails stay hidden.