Data discovery and classification in SqlClient

Article
11/18/2022

Applies to: .NET Framework .NET .NET Standard

Data Discovery & Classification is a set of advanced services for discovering, classifying, labeling & reporting the sensitive data in your databases. SqlClient provides an API exposing read-only Data Discovery and Classification information when the underlying source supports the feature. This information is accessed through SqlDataReader.

Microsoft.Data.SqlClient v2.1.0 introduces support for Data Classification's Sensitivity Rank information. Sensitivity Rank is an identifier based on a predefined set of values, which define sensitivity rank. It can be used by other services like Advanced Threat Protection to detect anomalies based on their rank. The following Data Classification APIs are now available in Microsoft.Data.SqlClient.DataClassification namespace:

Code example

// New in Microsoft.Data.SqlClient v2.1.0
public enum SensitivityRank
{
    NOT_DEFINED = -1,
    NONE = 0,
    LOW = 10,
    MEDIUM = 20,
    HIGH = 30,
    CRITICAL = 40
}

public sealed class SensitivityClassification
{
  // Returns the sensitivity rank for the query associated with the active 'SqlDataReader'.
  // New in Microsoft.Data.SqlClient v2.1.0
  public SensitivityRank SensitivityRank;

  // Returns the labels collection for this 'SensitivityClassification' Object
  public ReadOnlyCollection<Label> Labels;

  // Returns the information types collection for this 'SensitivityClassification' Object
  public ReadOnlyCollection<InformationType> InformationTypes;

  // Returns the column sensitivity for this 'SensitivityClassification' Object
  public ReadOnlyCollection<ColumnSensitivity> ColumnSensitivities;
}

public sealed class SensitivityProperty
{
  // Returns the sensitivity rank for this 'SensitivityProperty' Object
  // New in Microsoft.Data.SqlClient v2.1.0
  public SensitivityRank SensitivityRank;

  // Returns the label for this 'SensitivityProperty' Object
  public Label Label;

  // Returns the information type for this 'SensitivityProperty' Object
  public InformationType InformationType;
}

public sealed class Label
{
  // Gets the name for this 'Label' object
  public string Name;

  // Gets the ID for this 'Label' object
  public string Id;
}

public sealed class InformationType
{
  // Gets the name for this 'InformationType' object
  public string Name;

  // Gets the ID for this 'InformationType' object
  public string Id;
}

public sealed class ColumnSensitivity
{
  // Returns the list of sensitivity properties as received from Server for this 'ColumnSensitivity' information      
  public ReadOnlyCollection<SensitivityProperty> SensitivityProperties;
}

Note

Microsoft.Data.SqlClient reads Sensitivity Rank information only if SQL Server supports Data Classification with rank. For servers use old version of Data Classification without rank, the rank value for queries is "NOT DEFINED".

This sample application demonstrates how to access the Data Classification properties of SqlDataReader.

using System;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.DataClassification;

class Program
{
    // Name of the temporary table created for this sample program.
    static string tableName = "SQLCLIENT_DATA_DISCOVERY_CLASSIFICATION";

    public static void Main()
    {
        // To avoid storing the connection string in your code, you can retrieve it from a configuration file. 
        string connectionString = "Data Source=localhost; Integrated Security=true; Initial Catalog=AdventureWorks;";

        // Open a connection to the AdventureWorks database.
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            try
            {
                // Check if the target SQL Server supports Data Discovery and Classification.
                if (DataClassificationSupported(connection))
                {
                    // Create the temporary table and retrieve its Data Discovery and Classification information.
                    // Set rankEnabled to be true if testing with rank information.
                    CreateTable(connection, rankEnabled : true);
                    RunTests(connection, rankEnabled : true);
                }
            }
            finally
            {
                // Drop the temporary table.
                DropTable(connection);
            }
        }
    }

    /// <summary>
    /// Verifies if SQL Data Discovery and Classification feature is available on the target server.
    /// </summary>
    /// <param name="connection">The SqlConnection to work with.</param>
    /// <returns>True if the target SQL Server supports the feature and false otherwise.</returns>
    public static bool DataClassificationSupported(SqlConnection connection)
    {
        try
        {
            SqlCommand command = new SqlCommand(null, connection);
            command.CommandText = "SELECT * FROM SYS.SENSITIVITY_CLASSIFICATIONS";
            command.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            // Error 208: Object Not Found
            if (e.Errors != null && e.Errors[0].Number == 208)
            {
                Console.WriteLine("This feature is not supported on the target SQL Server.");
                return false;
            }
        }
        return true;
    }

    /// <summary>
    /// Creates a temporary table for this sample program and sets tags for Sensitivity Classification.
    /// </summary>
    /// <param name="connection">The SqlConnection to work with.</param>
    /// <param name="rankEnabled">True if rank information is enabled and false otherwise</param>
    private static void CreateTable(SqlConnection connection, bool rankEnabled = false)
    {
        SqlCommand command = new SqlCommand(null, connection);

        // Creates table for storing Supplier data.
        command.CommandText = $"CREATE TABLE {tableName} ("
            + "[Id] [int] IDENTITY(1,1) NOT NULL,"
            + "[CompanyName] [nvarchar](40) NOT NULL,"
            + "[ContactName] [nvarchar](50) NULL,"
            + "[ContactTitle] [nvarchar](40) NULL,"
            + "[City] [nvarchar](40) NULL,"
            + "[CountryName] [nvarchar](40) NULL,"
            + "[Phone] [nvarchar](30) MASKED WITH (FUNCTION = 'default()') NULL,"
            + "[Fax] [nvarchar](30) MASKED WITH (FUNCTION = 'default()') NULL)";
        command.ExecuteNonQuery();

        if (rankEnabled)
        {
            // Set Sensitivity Classification tags for table columns with rank information
            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".CompanyName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Company Name', INFORMATION_TYPE_ID='COMPANY', RANK=LOW)";
            command.ExecuteNonQuery();

            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".ContactName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Person Name', INFORMATION_TYPE_ID='NAME', RANK=LOW)";
            command.ExecuteNonQuery();

            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".Phone WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT', RANK=MEDIUM)";
            command.ExecuteNonQuery();

            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".Fax WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT', RANK=MEDIUM)";
            command.ExecuteNonQuery();
        }
        else
        {
            // Set Sensitivity Classification tags for table columns without rank information
            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".CompanyName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Company Name', INFORMATION_TYPE_ID='COMPANY')";
            command.ExecuteNonQuery();

            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".ContactName WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Person Name', INFORMATION_TYPE_ID='NAME')";
            command.ExecuteNonQuery();

            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".Phone WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT')";
            command.ExecuteNonQuery();

            command.CommandText = $"ADD SENSITIVITY CLASSIFICATION TO {tableName}"
                    + ".Fax WITH (LABEL='PII', LABEL_ID='L1', INFORMATION_TYPE='Contact Information', INFORMATION_TYPE_ID='CONTACT')";
            command.ExecuteNonQuery();
        }        
    }

    /// <summary>
    /// Run query to fetch result set from target table.
    /// </summary>
    /// <param name="connection">The SqlConnection to work with.</param>
    /// <param name="rankEnabled">True if rank information is enabled and false otherwise</param>
    private static void RunTests(SqlConnection connection, bool rankEnabled = false)
    {
        SqlCommand command = new SqlCommand(null, connection);
        command.CommandText = $"SELECT * FROM {tableName}";
        using (SqlDataReader reader = command.ExecuteReader())
        {
            PrintSensitivityClassification(reader, rankEnabled);
        }
    }

    /// <summary>
    /// Prints Sensitivity Classification data as received in the result set.
    /// </summary>
    /// <param name="reader">The SqlDataReader to work with.</param>
    /// <param name="rankEnabled">True if rank information is enabled and false otherwise</param>
    private static void PrintSensitivityClassification(SqlDataReader reader, bool rankEnabled = false)
    {
        if (reader.SensitivityClassification != null)
        {
            for (int columnPos = 0; columnPos < reader.SensitivityClassification.ColumnSensitivities.Count; columnPos++)
            {
                foreach (SensitivityProperty sp in reader.SensitivityClassification.ColumnSensitivities[columnPos].SensitivityProperties)
                {
                    if (sp.Label != null)
                    {
                        Console.WriteLine($"Labels received for Column : {columnPos}");
                        Console.WriteLine($"Label ID: {sp.Label.Id}");
                        Console.WriteLine($"Label Name: {sp.Label.Name}");
                        Console.WriteLine();
                    }

                    if (sp.InformationType != null)
                    {
                        Console.WriteLine($"Information Types received for Column : {columnPos}");
                        Console.WriteLine($"Information Type ID: {sp.InformationType.Id}");
                        Console.WriteLine($"Information Type: {sp.InformationType.Name}");
                        Console.WriteLine();
                    }

                    Console.WriteLine($"Sensitivity Rank: {sp.SensitivityRank.ToString()}");
                }
            }
            Console.Writeline($"reader.SensitivityClassification.SensitivityRank : {reader.SensitivityClassification.SensitivityRank.ToString()}");
        }
    }

    /// <summary>
    /// Deletes the table created for this sample program.
    /// </summary>
    /// <param name="connection">The SqlConnection to work with.</param>
    private static void DropTable(SqlConnection connection)
    {
        SqlCommand command = new SqlCommand(null, connection);
        command.CommandText = $"DROP TABLE {tableName}";
        command.ExecuteNonQuery();
    }
}

Share via

Data discovery and classification in SqlClient

Code example

See also

Feedback

Additional resources