Zero Trust and private devices

graph TD
    C["Client: Access, Excel, Power BI, line-of-business app – company PC, private device, contractor"] -->|"request with sign-in"| S["Signals: origin, country, sign-in method, person, key, second factor"]
    S --> R["Sign-in policy: deny, require a code, or nothing"]
    R --> A["Rights: alias, object, verb, read-only; restrictions of the key"]
    A --> D[("Database: views and procedures for rows and columns")]
    R --> L["Records: sign-in event, audit, findings, change log"]
    A --> L

Figure: Every request runs through the same chain. The operator sets policy and rights; the service enforces them on every request and records what it decided.

The data service doesn't set business policy. Whether a private laptop may see customer data, whether an employee works from abroad, or whether a contractor gets two weeks of access is the operator's decision. The service gives the operator the means to configure these decisions, enforce them on every request, and prove them afterward. This page shows which signals the service has for that, how reliable they are, and where its limits are.

Key features

No trust from the network alone. Every request carries a sign-in; the corporate network can spare a code but never replaces a sign-in.
Access per person and purpose. A person has several personal keys, one per device or purpose, each restricted, time-limited, and revocable on its own.
Policy by situation. Sign-in rules require a second factor or deny, per sign-in method, network, country, network operator, program, time of day, person, and Windows group; exceptions can apply only until a given time.
Least privilege down to the object. Rules name aliases, object kinds, single objects, verbs, and read-only; the database limits rows and columns through views, under the person's login.
Immediate revocation. Revoked keys, disabled people, and ended sessions take effect with the next request.
Evidence. Sign-in events with origin and reason, an audit entry per request, findings of the sign-in rules, and a change log of every setting.

Core concepts

What the service establishes and what not

The service distinguishes three kinds of information: what it observes on its own connection, what a client merely claims, and what a trusted party confirms. In rules, claimed information can only restrict, never serve as an exception on its own.

Information	Kind	As a rule condition	Limit
Network address of the request (behind a trusted proxy, the forwarded one)	observed	yes: networks, own networks	Behind NAT or a proxy it's that device's address
Country and network operator from the address	observed, estimated	yes: country, network operator	VPN, mobile networks, and cloud egress move the country and the network operator
Sign-in method (Windows, personal key, SQL login, service key)	confirmed	yes	–
Person and Windows groups	confirmed	yes	Groups only with Windows sign-in
Second factor (code from an authenticator app)	confirmed	yes	Proves possession of the phone, not of the device making the request
Program (`User-Agent`)	claimed	yes, to restrict; as an exception on its own it exempts anyone who sends the text	Excel and Power BI Desktop send the same text; any program can pretend to be another
Time of day	observed	yes: time windows	Time zone from `Gateway:SignInLog:TimeZone`
Device state (managed, encrypted, antivirus current)	–	no	The service has no connection to device management (MDM) or endpoint protection

The service claims nothing about a device it can't check. It doesn't recognize a „private device“ as such. It can, however, distinguish how someone signs in and from where: a Windows sign-in from the corporate network almost always comes from a company PC in the domain; a personal key from the internet can come from any device. The guides below use exactly this distinction.

What the service doesn't control

Data on the device. Whatever a client has received stays there: in an Excel file, in Power BI's cache, in an Access database. The service doesn't control saving, copying, or printing; that's up to device management and the application.
The state of the phone holding the second factor. The code proves possession of the app, not that the requesting device is secure.
Business rules per row. Whether an agent may see a particular customer is decided by the database (view, procedure), not by a second rule system in the service; see row-level restriction.

Everyday situations

Situation	Main means	Guide
Managed company PC	Windows sign-in, rules by Windows group, corporate network	Set up Windows sign-in, tier access by location
Private laptop	own personal key, restricted, code from outside	Admit private devices
Employee abroad	rule by country, time-limited exception	Allow access while travelling
Agent with own device	view per agent, rule on the view, time-limited key	Restrict agents to their customers
Excel and Power BI	one key per purpose, read-only, one alias	Separate reports from refresh
Access and VBA line-of-business app	pass the person to SQL Server, own alias per application	Connect a line-of-business application
Unknown or suspicious device	denial by origin, code, findings with notification	Check sign-in patterns
Temporary contractor	person with time-limited key, on-demand key	Grant temporary access
Lost device	revoke key, end sessions, reset factor	Respond to a lost device
Review by auditors	sign-in event, audit, change log, explanation of the policy	Trace an access decision

Check a rule before enforcing it

An enforced sign-in rule takes effect as soon as it's saved. So check a new rule in trial mode:

Create the rule with Mode = Report. It's judged at every sign-in but changes nothing.
Use explain-sign-in for typical people and origins to work out what it would do (tried).
Wait a few days of real operation and look in SignInRuleReports at how many sign-ins it would have hit, from how many people and origins. SignInEvents with contains(Rules,'"mode":"report"') finds the single sign-ins.
If the result fits, switch the rule to Enforce; otherwise adjust conditions or exceptions and keep trying.

Details under Try a rule.

Create denials with save-sign-in-rule in one step together with their exceptions, so you don't lock yourself out between two changes (see sign-in).