MS SQL Server
The database behind a very large number of enterprise line-of-business systems, and behind a very large number of questions that currently require a report request.
The database behind a very large number of enterprise line-of-business systems, and behind a very large number of questions that currently require a report request.
What MS SQL Server holds, and why that matters here
SQL Server sits under an enormous amount of enterprise application estate, particularly in organisations with a Microsoft-centred technology strategy. Much of what it holds is operational data whose only interface is an application built for a different purpose.
The recurring pattern is that people ask for reports because asking a question directly is not possible. Most of those report requests are one fact.
- Line-of-business data — whatever the applications on top of it record.
- Historical records — often the only place older data survives.
- Reference data — the master lists other systems rely on.
- Views and stored procedures — the curated access your team already built.
- Reporting structures — the shapes designed for analysis.
Questions Elbi can answer from SQL Server
What is the status of this?
A record retrieved and described in plain language.
How many are outstanding?
A count that would otherwise be a report request.
What is the history here?
Previous states against a record.
Which are overdue?
A filtered set, summarised rather than exported.
What were the figures for that period?
A period total without building anything.
Is this reference valid?
Existence and state.
How Elbi reads from SQL Server
- A login or a contained userAccess starts either with a server login mapped to a user in one database, or with a contained database user that authenticates at the database and has no server login at all. Either way it is created for this connection and used by nothing else.
- SELECT on a reporting schemaThe user is granted SELECT on a schema that holds only the views agreed for the assistant — GRANT SELECT ON SCHEMA::reporting, for instance — and belongs to no fixed database roles. db_datareader is left out, because it can read every table in the database.
- Ownership chaining does the restWhen the views and the tables beneath them share an owner, SQL Server checks permission on the view alone, so the user can read through the view without any rights on the tables. Reporting views and stored procedures your team has already built work the same way.
- Parameterised calls, not composed SQLEach question maps to a parameterised query against a view, or to a stored procedure the user may EXECUTE. Whatever the person supplies goes in as a parameter; the assistant does not write SQL of its own.
- A readable secondary where you have oneIn an Always On availability group with read-only routing configured, a connection that declares ApplicationIntent=ReadOnly is sent to a readable secondary, keeping lookups away from the primary that serves the application.
- Results phrased for peopleCodes, bit flags and datetime values come back as sentences in Arabic or English, with the internal keys left out and names shown as your records hold them.
Logins, users and what stays yours
| Direction of access | Read-only. SELECT on one schema of views, EXECUTE on any agreed read-only procedures, and no membership of db_datareader, db_owner or any role that can write. |
|---|---|
| Who can see what | The schema defines what the user can read. The asker's identity decides what they are told, and SQL Server's own row-level security can enforce per-customer limits inside the database as well. |
| Credentials | A login or contained user you create, with a password you set, or a service account you control. Disabling the login or dropping the user ends access from your side, without involving us. |
| Where data goes | What a query returns is used for the answer it was run for. Message content is destroyed within 24 hours, generally once the conversation finishes, and no external AI service ever receives it. |
| Audit | SQL Server Audit can record this user's SELECT and EXECUTE on the reporting schema, written to a file or to the Windows event log that your team controls. |
| If it is unavailable | If the instance or the secondary cannot be reached, the login is disabled or a query times out, the assistant says it could not get the answer and hands over to a person rather than offering anything stale. |
What the SQL Server connection replaces
SQL Server estates usually already have curated access — views and stored procedures built for reporting. Those are exactly the right foundation for an assistant, because someone has already decided what is safe and sensible to expose.
Building on them rather than around them means the assistant inherits work that has already been done and reviewed, and it keeps the connection narrow without additional design effort.
The value is the same as with any database: removing the intermediary from questions that do not need one, and freeing technical staff from a stream of individually trivial requests.
Where SQL Server lookups save the most time
Enterprise operations
Status and count questions across line-of-business systems.
Finance functions
Period figures without a report cycle.
Manufacturing
Production and inventory data from older systems.
Any Microsoft-centred estate
Data whose only interface is an application built for something else.
Getting value from SQL Server
Start from the report requests. In SQL Server estates many of them are a single fact dressed up as a report — the status of a request, a count for last month — and each of those is a candidate for one view. Counting them over a few weeks shows which to build first.
Check how Arabic is stored. Arabic held in varchar columns under a non-Arabic collation comes back as question marks, while nvarchar columns keep it intact. Confirming this on the tables behind each view before launch decides whether names and addresses can be read back correctly.
Look at query plans before go-live. The lookups are small, but where there is no readable secondary they share the primary with the application; a missing index on a lookup key is the usual reason a small query turns out to be slow, and it is something your DBA can check quickly.
What connecting SQL Server involves
- Map questions to objectsMatch each recurring question to an existing view or procedure where one fits, and to a new view where none does.
- Create a reporting schemaPut the agreed views in a schema of their own, owned by the same principal as the underlying tables so that ownership chaining applies.
- Create the login or contained userCreate the principal, grant SELECT on the schema and EXECUTE on the agreed procedures, then confirm from its effective permissions that it holds nothing else.
- Route reads to a secondaryIf you run an availability group with a readable secondary, configure read-only routing and have the connection declare read-only intent. A readable secondary is licensed differently from a passive one, so confirm the position with whoever manages your SQL Server licences.
- Switch on the auditDefine a database audit specification for the user's SELECT and EXECUTE on the reporting schema, so the record exists from the first lookup.
- Test, then launch narrowTry a reference that does not exist, another customer's record and a query that would scan a large table, then launch with the handful of views that answer the most frequent questions.
Personal data and audit in SQL Server
Line-of-business databases on SQL Server hold personal data alongside everything else — HR, customer, patient or payroll records, depending on the application. Under the Saudi Personal Data Protection Law the reporting schema is where minimisation happens: columns a question does not need stay out of the view, and dynamic data masking can hide sensitive fields from a user that has no need to see them in full.
Residency follows the instance: your own data centre, a hosting provider, or a managed SQL service in a region you selected. The connection does not move data anywhere; record the location next to the schema definition.
SQL Server Audit and the schema's permission list give a reviewer the two answers they need — what the user could read and what it did read — from your own systems.
What organisations typically see
How SQL Server questions are answered elsewhere
The traditional answer is a report: SQL Server Reporting Services, or a BI tool built on the same data. That suits recurring, structured needs and depends on someone building and maintaining each report.
Microsoft has been adding Copilot features to its SQL tools and to Azure SQL, aimed mainly at people who already write and tune queries. They make technical staff quicker rather than giving everyone else direct answers.
Spreadsheets with a live connection to the database are common and quietly risky: the connection details travel with the file, and so does the data.
The recurring mistake is adding the account to db_datareader because the views were not ready yet. From then on it can read every table in the database, including the ones nobody meant to expose.
Common questions about the SQL Server connection
Yes, and it should. Curated views represent decisions someone already made about safe exposure.
No. Read-only is the default and the recommendation.
Named parameterised lookups against curated structures, not arbitrary queries against production tables.
Where the connection can authenticate against your Active Directory, a dedicated service account works well and keeps passwords out of connection strings. Otherwise a SQL login or contained user with a strong password is used, rotated on your schedule.
Yes, if they only read. EXECUTE is granted on each agreed procedure individually, and a procedure that writes, even incidentally, stays off the list.
Yes: a contained database user, SELECT on a reporting schema and auditing on your side. Azure SQL Database also supports Microsoft Entra authentication for that user, so the identity can be managed alongside your other Entra accounts instead of as a separate SQL password.
Connect it to your own systems
A working assistant against your own records, in both languages.