# Always Encrypted Columns

> Use when the user asks which plaintext columns can or should use Always Encrypted, or why a column is disqualified. Eligibility only: not for listing already-encrypted columns or their keys.

- Skill: `microsoft/always-encrypted-columns` (Agent Skill)
- Install (CLI): `npx get-skill add microsoft/always-encrypted-columns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/always-encrypted-columns/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/always-encrypted-columns

---


# Always Encrypted Column Analysis

Use this skill only when the user asks which currently plaintext columns are eligible candidates for Always Encrypted or wants a table evaluated for prospective encryption eligibility.

Do **not** use this workflow to inventory existing encryption. Requests containing "already encrypted," "currently encrypted," "which CEK," "which column encryption key," or "which column master key" need an existing-encryption catalog query, not the eligibility query below.

## Required tools

- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL
  Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,
  `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for
  running T-SQL and reading result sets;
  [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL
  Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill,
  which asks before installing or configuring anything. If no SQL tool can be made available, give the
  user the T-SQL to run and ask for the results.
- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
  holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure
  SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read
  permission these queries need. A tool is only as read-only as the login it connects with.

## SQL Queries

### GetColumnsThatCanBeEncryptedWithAlwaysEncrypted

The user must provide `$SchemaName` and `$TableName` parameters.

```sql
DECLARE @SchemaName SYSNAME = '$SchemaName',
        @TableName  SYSNAME = '$TableName';

DECLARE @TableId INT;
SELECT @TableId = t.object_id
FROM sys.tables AS t
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE s.name = @SchemaName AND t.name = @TableName
OPTION (MAXDOP 1);

IF @TableId IS NULL
BEGIN
    RAISERROR('Table %s.%s not found!', 16, 1, @SchemaName, @TableName);
    RETURN;
END;

;WITH ColumnList AS (
    SELECT
        c.object_id, c.column_id, c.name AS ColumnName,
        CASE
          WHEN t.name IN ('decimal','numeric')
            THEN t.name + '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')'
          WHEN t.name IN ('char','varchar','nchar','nvarchar')
            THEN t.name +
                 CASE WHEN c.max_length = -1 THEN '(max)'
                   WHEN t.name IN ('nchar','nvarchar') THEN '(' + CAST(c.max_length/2 AS VARCHAR(10)) + ')'
                   ELSE '(' + CAST(c.max_length AS VARCHAR(10)) + ')' END
          WHEN t.name IN ('binary','varbinary')
            THEN t.name + CASE WHEN c.max_length = -1 THEN '(max)' ELSE '(' + CAST(c.max_length AS VARCHAR(10)) + ')' END
          ELSE t.name
        END AS ColumnType,
        c.is_nullable
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = @TableId
),
Violations AS (
    SELECT c.object_id, c.column_id, CAST(NULL AS NVARCHAR(200)) AS Reason, CAST(NULL AS INT) AS ReasonCode
    FROM ColumnList AS c WHERE 1=0

    UNION ALL SELECT c.object_id, c.column_id, 'Unsupported data type (' + ut.name + ')', 1
    FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id
    JOIN sys.types ut ON col.user_type_id = ut.user_type_id
    WHERE LOWER(ut.name) IN ('xml','text','ntext','image','timestamp','rowversion','sql_variant','hierarchyid','geography','geometry') OR ut.is_user_defined = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Identity column', 2
    FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id WHERE col.is_identity = 1

    UNION ALL SELECT c.object_id, c.column_id, 'FILESTREAM column', 3
    FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id WHERE col.is_filestream = 1

    UNION ALL SELECT c.object_id, c.column_id, 'ROWGUIDCOL column', 4
    FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id WHERE col.is_rowguidcol = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Computed column', 5
    FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id WHERE col.is_computed = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Sparse column set', 7
    FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id WHERE col.is_sparse = 1 AND col.is_column_set = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Primary key column', 8
    FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    JOIN sys.indexes ix ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id WHERE ix.is_primary_key = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Unique index/constraint', 9
    FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    JOIN sys.indexes ix ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id WHERE ix.is_unique = 1 AND ix.is_primary_key = 0

    UNION ALL SELECT c.object_id, c.column_id, 'Indexed column', 10
    FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    JOIN sys.indexes ix ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id WHERE ix.is_primary_key = 0 AND ix.is_unique = 0

    UNION ALL SELECT c.object_id, c.column_id, 'Full-text index', 11
    FROM ColumnList c JOIN sys.fulltext_index_columns fti ON fti.object_id = c.object_id AND fti.column_id = c.column_id

    UNION ALL SELECT c.object_id, c.column_id, 'Default constraint', 12
    FROM ColumnList c JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id

    UNION ALL SELECT c.object_id, c.column_id, 'Check constraint', 13
    FROM ColumnList c JOIN sys.check_constraints ck ON ck.parent_object_id = c.object_id
    WHERE ck.definition LIKE '%[' + c.ColumnName + ']%' OR ' ' + ck.definition + ' ' LIKE '% ' + c.ColumnName + ' %'

    UNION ALL SELECT c.object_id, c.column_id, 'Foreign key constraint', 14
    FROM ColumnList c JOIN sys.foreign_key_columns fk
    ON (fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id)
    OR (fk.referenced_object_id = c.object_id AND fk.referenced_column_id = c.column_id)

    UNION ALL SELECT c.object_id, c.column_id, 'Statistics on column', 15
    FROM ColumnList c JOIN sys.stats_columns sc ON sc.object_id = c.object_id AND sc.column_id = c.column_id

    UNION ALL SELECT c.object_id, c.column_id, 'Partitioning column', 16
    FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.partition_ordinal = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Change Data Capture (CDC)', 17
    FROM ColumnList c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.is_tracked_by_cdc = 1

    UNION ALL SELECT c.object_id, c.column_id, 'Dynamic Data Masking', 18
    FROM ColumnList c JOIN sys.masked_columns m ON m.object_id = c.object_id AND m.column_id = c.column_id

    UNION ALL SELECT c.object_id, c.column_id, 'Stretch-enabled table', 19
    FROM ColumnList c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.is_remote_data_archive_enabled = 1

    UNION ALL SELECT c.object_id, c.column_id, 'External table', 20
    FROM ColumnList c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.is_external = 1
)
SELECT
    cl.ColumnName,
    cl.ColumnType,
    CanBeEncrypted = CASE WHEN v.ReasonList IS NULL THEN 'Yes' ELSE 'No' END,
    Reason = ISNULL(v.ReasonList, 'meets all required conditions')
FROM ColumnList AS cl
OUTER APPLY (
    SELECT STRING_AGG(Reason, '; ') WITHIN GROUP (ORDER BY ReasonCode) AS ReasonList
    FROM Violations WHERE Violations.object_id = cl.object_id AND Violations.column_id = cl.column_id
) AS v
ORDER BY cl.column_id
OPTION (MAXDOP 1);
```

## Report Format

Report **all** columns — even those that do not meet requirements — as a Markdown table:

| Column Name | Column Type | Can Be Encrypted | Reason |
|-------------|-------------|------------------|--------|

## Key Requirements for Always Encrypted

A column **cannot** be encrypted if it:
- Has unsupported types: xml, timestamp, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, or user-defined types
- Is an IDENTITY, FILESTREAM, ROWGUIDCOL, computed, or sparse column set column
- Is a primary key, unique constraint, or indexed column
- Is in a full-text index, has default/check constraints, foreign keys, or statistics
- Is a partitioning column, CDC-tracked, dynamically masked, in a Stretch-enabled or external table

**Note**: CHAR, VARCHAR, NCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX) columns will have their collation changed to binary-code point (`_BIN2`) if encrypted.

