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 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.
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.
1---2name: always-encrypted-columns3description: "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."4user-invocable: true5metadata:6 source: https://skillmd.com/skills/microsoft/always-encrypted-columns7---89# Always Encrypted Column Analysis1011Use 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.1213Do **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.1415## Required tools1617- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL18 Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,19 `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for20 running T-SQL and reading result sets;21 [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL22 Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill,23 which asks before installing or configuring anything. If no SQL tool can be made available, give the24 user the T-SQL to run and ask for the results.25- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that26 holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure27 SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read28 permission these queries need. A tool is only as read-only as the login it connects with.2930## SQL Queries3132### GetColumnsThatCanBeEncryptedWithAlwaysEncrypted3334The user must provide `$SchemaName` and `$TableName` parameters.3536```sql37DECLARE @SchemaName SYSNAME = '$SchemaName',38 @TableName SYSNAME = '$TableName';3940DECLARE @TableId INT;41SELECT @TableId = t.object_id42FROM sys.tables AS t43JOIN sys.schemas AS s ON t.schema_id = s.schema_id44WHERE s.name = @SchemaName AND t.name = @TableName45OPTION (MAXDOP 1);4647IF @TableId IS NULL48BEGIN49 RAISERROR('Table %s.%s not found!', 16, 1, @SchemaName, @TableName);50 RETURN;51END;5253;WITH ColumnList AS (54 SELECT55 c.object_id, c.column_id, c.name AS ColumnName,56 CASE57 WHEN t.name IN ('decimal','numeric')58 THEN t.name + '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')'59 WHEN t.name IN ('char','varchar','nchar','nvarchar')60 THEN t.name +61 CASE WHEN c.max_length = -1 THEN '(max)'62 WHEN t.name IN ('nchar','nvarchar') THEN '(' + CAST(c.max_length/2 AS VARCHAR(10)) + ')'63 ELSE '(' + CAST(c.max_length AS VARCHAR(10)) + ')' END64 WHEN t.name IN ('binary','varbinary')65 THEN t.name + CASE WHEN c.max_length = -1 THEN '(max)' ELSE '(' + CAST(c.max_length AS VARCHAR(10)) + ')' END66 ELSE t.name67 END AS ColumnType,68 c.is_nullable69 FROM sys.columns AS c70 JOIN sys.types AS t ON c.user_type_id = t.user_type_id71 WHERE c.object_id = @TableId72),73Violations AS (74 SELECT c.object_id, c.column_id, CAST(NULL AS NVARCHAR(200)) AS Reason, CAST(NULL AS INT) AS ReasonCode75 FROM ColumnList AS c WHERE 1=07677 UNION ALL SELECT c.object_id, c.column_id, 'Unsupported data type (' + ut.name + ')', 178 FROM ColumnList c JOIN sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id79 JOIN sys.types ut ON col.user_type_id = ut.user_type_id80 WHERE LOWER(ut.name) IN ('xml','text','ntext','image','timestamp','rowversion','sql_variant','hierarchyid','geography','geometry') OR ut.is_user_defined = 18182 UNION ALL SELECT c.object_id, c.column_id, 'Identity column', 283 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 = 18485 UNION ALL SELECT c.object_id, c.column_id, 'FILESTREAM column', 386 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 = 18788 UNION ALL SELECT c.object_id, c.column_id, 'ROWGUIDCOL column', 489 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 = 19091 UNION ALL SELECT c.object_id, c.column_id, 'Computed column', 592 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 = 19394 UNION ALL SELECT c.object_id, c.column_id, 'Sparse column set', 795 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 = 19697 UNION ALL SELECT c.object_id, c.column_id, 'Primary key column', 898 FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id99 JOIN sys.indexes ix ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id WHERE ix.is_primary_key = 1100101 UNION ALL SELECT c.object_id, c.column_id, 'Unique index/constraint', 9102 FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id103 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 = 0104105 UNION ALL SELECT c.object_id, c.column_id, 'Indexed column', 10106 FROM ColumnList c JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id107 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 = 0108109 UNION ALL SELECT c.object_id, c.column_id, 'Full-text index', 11110 FROM ColumnList c JOIN sys.fulltext_index_columns fti ON fti.object_id = c.object_id AND fti.column_id = c.column_id111112 UNION ALL SELECT c.object_id, c.column_id, 'Default constraint', 12113 FROM ColumnList c JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id114115 UNION ALL SELECT c.object_id, c.column_id, 'Check constraint', 13116 FROM ColumnList c JOIN sys.check_constraints ck ON ck.parent_object_id = c.object_id117 WHERE ck.definition LIKE '%[' + c.ColumnName + ']%' OR ' ' + ck.definition + ' ' LIKE '% ' + c.ColumnName + ' %'118119 UNION ALL SELECT c.object_id, c.column_id, 'Foreign key constraint', 14120 FROM ColumnList c JOIN sys.foreign_key_columns fk121 ON (fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id)122 OR (fk.referenced_object_id = c.object_id AND fk.referenced_column_id = c.column_id)123124 UNION ALL SELECT c.object_id, c.column_id, 'Statistics on column', 15125 FROM ColumnList c JOIN sys.stats_columns sc ON sc.object_id = c.object_id AND sc.column_id = c.column_id126127 UNION ALL SELECT c.object_id, c.column_id, 'Partitioning column', 16128 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 = 1129130 UNION ALL SELECT c.object_id, c.column_id, 'Change Data Capture (CDC)', 17131 FROM ColumnList c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.is_tracked_by_cdc = 1132133 UNION ALL SELECT c.object_id, c.column_id, 'Dynamic Data Masking', 18134 FROM ColumnList c JOIN sys.masked_columns m ON m.object_id = c.object_id AND m.column_id = c.column_id135136 UNION ALL SELECT c.object_id, c.column_id, 'Stretch-enabled table', 19137 FROM ColumnList c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.is_remote_data_archive_enabled = 1138139 UNION ALL SELECT c.object_id, c.column_id, 'External table', 20140 FROM ColumnList c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.is_external = 1141)142SELECT143 cl.ColumnName,144 cl.ColumnType,145 CanBeEncrypted = CASE WHEN v.ReasonList IS NULL THEN 'Yes' ELSE 'No' END,146 Reason = ISNULL(v.ReasonList, 'meets all required conditions')147FROM ColumnList AS cl148OUTER APPLY (149 SELECT STRING_AGG(Reason, '; ') WITHIN GROUP (ORDER BY ReasonCode) AS ReasonList150 FROM Violations WHERE Violations.object_id = cl.object_id AND Violations.column_id = cl.column_id151) AS v152ORDER BY cl.column_id153OPTION (MAXDOP 1);154```155156## Report Format157158Report **all** columns — even those that do not meet requirements — as a Markdown table:159160| Column Name | Column Type | Can Be Encrypted | Reason |161|-------------|-------------|------------------|--------|162163## Key Requirements for Always Encrypted164165A column **cannot** be encrypted if it:166- Has unsupported types: xml, timestamp, rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, or user-defined types167- Is an IDENTITY, FILESTREAM, ROWGUIDCOL, computed, or sparse column set column168- Is a primary key, unique constraint, or indexed column169- Is in a full-text index, has default/check constraints, foreign keys, or statistics170- Is a partitioning column, CDC-tracked, dynamically masked, in a Stretch-enabled or external table171172**Note**: CHAR, VARCHAR, NCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX) columns will have their collation changed to binary-code point (`_BIN2`) if encrypted.