Security Posture
Overview
The Security Posture report answers one question before you read anything else: how hard is this instance to get into?
It runs forty four checks – the CIS SQL Server benchmark items this product can evaluate from T-SQL, plus four of its own – and turns them into a score, a count of what failed, a bar per CIS domain, and a grid that opens on the failures.
What this replaces
The old page was the Security report: a node in ReportsXmlConfig.xml whose T-SQL built a string of HTML and handed it to an embedded Internet Explorer control. Nothing registered the executable for a modern document mode, so that control rendered in IE7, which is why the page was a serif wall of headings separated by horizontal rules. Finding the twelve things wrong with an instance meant reading all forty four.
Four things were wrong with it beyond the way it looked:
| Fault | What it meant |
|---|---|
| The finding text was never printed | The query collected a [message] column naming the offending database or login, and the projection selected the check title twice instead. The report could say Trustworthy was on and not say where. |
| Failures scattered among the passes | The sort key had the CIS item number stripped off the front, so the order was alphabetical on the description and a critical finding could sit anywhere in the list. |
| Documentation links were plain text | Printed as bare URLs into a control with right-click disabled, so following one meant retyping it. |
| One blocked check blanked the page | The error handler replaced everything gathered so far with the words Problem Detected. A single registry read denied by permissions turned the whole report into two words. |
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Reports → Security Posture |
The page title reads Security Posture for <server name>.
Requires SQL Server 2008 or newer, and a registered copy.
The score
The number is a weighted pass rate, not a raw one. Each check carries a severity, and the weight doubles at every step: a critical check is worth eight times a low one. Without that, an instance could fail the sa account is enabled and CHECK_POLICY is off and still score well because it passed a dozen checks about network protocols.
| Band | Score | Reads as |
|---|---|---|
| Well hardened | 95 and above | Nothing outstanding worth acting on |
| Some gaps | 80 to 94 | Real findings, none of them urgent |
| Needs attention | 60 to 79 | Something on this list should be scheduled |
| Exposed | Below 60 | Something on this list should be done today |
The thresholds are deliberately unforgiving. This is a security page, and eighty percent of the way to secure is not a pass.
What the score does not count
Some checks cannot be answered from T-SQL – whether the latest cumulative update is installed, whether application input is sanitized – and some come back Not Applicable. Those are counted and shown, and left out of both halves of the fraction. The line under the arc says how many: 37 of 44 checks scored, 7 manual.
An instance is not penalised for the checks nobody has written an automated test for.
The counts
Three tiles, each one a filter. Click a tile to hold the grid to those rows; click it again to release it.
| Tile | What it holds |
|---|---|
| Not compliant | The checks that failed, with a count of how many of those are critical |
| Manual check | The checks that made no judgement, and so were not scored |
| Compliant | The checks that passed |
The count of criticals is the line that matters. Sixteen failures with none critical is a sprint of tidying. Sixteen with three critical is an evening.
The domain bars
The CIS item numbering already groups these checks eight ways, and the report this replaced threw that grouping away. Each bar is one domain, split into failures, manual checks and passes, with the count of failures and the worst failing check in that domain, named.
The bars are all the same length rather than scaled to the number of checks in a domain, because the question they answer is how much of this domain is failing, not which domain has the most checks in it. The counts beside them carry the absolute numbers.
| Domain | Covers |
|---|---|
| Installation and Patching | Cumulative updates, what else runs on the box |
| Surface Area | The configuration options and features that widen what is reachable |
| Authentication | Who can connect, what the public role and the guest user can reach |
| Password Policy | CHECK_POLICY and CHECK_EXPIRATION on SQL authenticated logins |
| Auditing and Logging | Login auditing, the default trace, the error log retention |
| Application Development | Input sanitization, CLR assembly permission sets |
| Encryption | Symmetric and asymmetric key sizes |
| Additional Considerations | The SQL Server Browser service |
| Instance Hardening | This product’s own four checks: login auditing, TDE, connection encryption, orphaned users |
Click a bar to hold the grid to that domain. Click the score card to clear every filter at once.
Reading the grid
Failures first, worst severity first inside that, then the manual checks, then the passes.
| Column | What it holds |
|---|---|
| Item | The CIS item number, sorted numerically, so 2.9 comes before 2.10 |
| Domain | Which of the nine sections above the check belongs to |
| Check | What the benchmark asks for |
| Status | Compliant, Not compliant, Manual check, or whatever word the check itself used |
| Severity | What failing this one costs. Blank on a check that passed, because a pass has cost nothing |
| Finding | Which database, login or setting. On a check that reports several, the first few and a count of the rest |
| Why it matters | One sentence on what goes wrong if this is left as it is |
| Documentation | The Microsoft page for the setting |
Status is never carried by colour alone: every row has a glyph and a word as well.
Actions
| Action | How |
|---|---|
| Open the documentation for a check | Double-click the row, or right-click → Open the documentation for this check |
| Copy one finding | Right-click ΓåÆ Copy this finding – the check, the status, every message and the link |
| Copy the whole filtered set | Right-click ΓåÆ Copy every finding shown – headed with the score, for pasting into a ticket |
| Copy the band as a picture | Right-click → Copy Chart to Clipboard |
The four checks that are not CIS items
Numbered 9.x so they sort after the benchmark sections.
| Item | Check | Why it is here |
|---|---|---|
| 9.1 | Both failed and successful logins are audited | The CIS items cover failed logins; this one reports the actual audit level the instance is set to |
| 9.2 | User databases are encrypted with TDE | SQL Server 2019 and newer, where TDE is on every edition |
| 9.3 | Connections to the instance are encrypted | Reads the encryption option on every connection currently open |
| 9.4 | Databases have no orphaned users | Needs sysadmin; says so rather than passing silently when it does not have it |
Messages you may see
| Message | What it means |
|---|---|
| Some checks could not be completed on this instance | A check stopped part way. The rest of the report is still here; the row carries the error text. Usually a registry read the connected account is not allowed to make. |
| This check needs sysadmin, and the account the report is connected with does not have it | The orphaned user check walks every database and cannot without it. |
| None of the security checks returned an answer for this instance | Nothing came back at all. Check the permissions on the account the connection uses. |
| This report needs SQL Server 2008 or newer | Several checks read configuration values older versions do not report. |
Related reports
| Report | Why |
|---|---|
| Logins for Instance | Who the logins actually are, once this page has said the policy is wrong |
| Orphan Users | The full per database list behind items 3.3 and 9.4 |
| TDE Status | The per database encryption state behind item 9.2 |
| Configuration Values | The raw sys.configurations values behind most of the Surface Area section |
| QuickScan Report | The wider health check, of which security is one part |
Frequently asked questions
Is the score comparable between two instances? Yes, as long as both ran the same number of checks. The denominator is the weight of the checks that were actually scored, so an instance where six checks could not be read is scored on what was read, and the line under the arc says so.
Why did the score go down after I fixed something? Check the manual count. If a check that previously could not be read now returns a failure, it has joined the denominator. The instance did not get worse; the report can see more of it.
Can I change the severities? They are judgement calls and they live in one place – SecurityKnowledge.vb – precisely so they can be argued with. The rule behind them: Critical when failing hands an attacker the instance, High when it removes a barrier or hides the evidence, Medium when it widens what an attacker already inside can reach, Low when it is hygiene.
Where did the Security report go? This is it. The name changed because the old one described a wall of headings and this one answers how hard the instance is to get into. Every check the old report ran is still here.