La version finale de SQL Server 2016 est sortie récemment. C’est donc le moment de s’intéresser aux nouvelles fonctionnalités apportées par cette version. Aujourd’hui nous allons découvrir la fonctionnalité Always Encrypted.
Le chiffrement de données sensibles telles que certaines informations personnelles est un plus pour la sécurité. Actuellement SQL Server propose des fonctionnalités de chiffrement avec les fonctions TSQL ENCRYPTBY... permettant de chiffrer une valeur. Les requêtes d’insertion ou de sélection doivent donc utiliser ces fonctions à chaque fois. L’autre solution de chiffrement proposée est Transparent Data Encryption (TDE pour les intimes). Cette fonctionnalité permet de chiffrer les fichiers de données et de log de la base de données de manière transparente pour l’utilisateur. Cependant un utilisateur avec suffisamment de permission peut accéder aux données en clair.
Always Encrypted permet de répondre à ce besoin. On chiffre directement les données (comme ENCRYPTBY...) et la clé de chiffrement des données n’est plus sur le serveur hébergeant la base de données mais du côté de l’application. Ainsi seule l’application possède la clé permettant de déchiffrer les données et ce déchiffrement se passe de manière transparente au niveau de l’application et non au niveau de la base de données. De cette façon un DBA n’aura pas de moyen de voir les données sensibles.
Les données peuvent être chiffrées de 2 façons :
- De manière déterministe : le chiffrement de la même donnée donne toujours le même résultat (un peu comme un hash)
- De manière aléatoire : le chiffrement de la même donnée peut donner des résultats différents
On utilisera la première méthode lorsque l’on a besoin de faire des recherches sur ces données. Dans ce cas seuls les opérateurs = et <> peuvent être utilisés (ce qui est plutôt logique). La deuxième méthode ne permet pas la recherche mais offre une meilleure sécurité.
Les clés de chiffrement
Il y a 2 types de clé de chiffrement :
- Column encryption key : clé permettant de chiffrer le contenu d’une colonne (stocké côté SQL)
- Column master key : clé protégeant les Column Encryption Keys (stocké côté application)
Le client ADO.NET fait une première requête pour récupérer les clés de chiffrement des colonnes et les déchiffre avec la master key. Lorsqu’il exécute une requête il commence par chiffrer les paramètres qui doivent l’être et envoie la requête au server SQL pour traitement puis les résultats sont déchiffrés. La sécurité des données est donc assurée.
Mise en place du serveur
On commence par déclarer la Master Key. Pour cela on indique son emplacement sur la machine hébergeant l’application ainsi que son empreinte :
Il est important de noter qu’il s’agit d’une simple déclaration. La clé n’est en effet pas stockée sur le server SQL mais bien sur le serveur applicatif.
Maintenant on crée la clé de chiffrement des colonnes :
Puis on crée la table avec les colonnes en spécifiant la clé de chiffrement ainsi que le type de chiffrement pour les colonnes que l’on souhaite chiffrer :
CREATE TABLE Patient (
Id [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[FullName] [nvarchar](50) NULL,
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = SampleCEK) NOT NULL,
PhoneNumber nchar(10)
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = SampleCEK) NOT NULL
)
Mise en place du client
Note : Il faut utiliser le Framework .NET 4.6
La première chose est d’indiquer dans la chaine de connexion que nous souhaitons utiliser Always Encrypted :
string connectionString = "Data Source=(local);Database=SampleAlwaysEncrypted; Integrated Security=true;Column Encryption Setting=enabled";
Ensuite tout se passe normalement. La seule contrainte est d’utiliser les requêtes paramétrées mais ça tout le monde le fait déjà, n’est-ce pas ? Par exemple pour insérer une ligne on peut faire :
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Patient(FullName, SSN, PhoneNumber) VALUES (@FullName, @SSN, @PhoneNumber)";
command.CommandType = System.Data.CommandType.Text;
var fullNameParameter = command.CreateParameter();
fullNameParameter.ParameterName = "@FullName";
fullNameParameter.Value = "John Doe";
command.Parameters.Add(fullNameParameter);
var ssnParameter = command.CreateParameter();
ssnParameter.ParameterName = "@SSN";
ssnParameter.Value = "1234";
command.Parameters.Add(ssnParameter);
var phoneNumberParameter = command.CreateParameter();
phoneNumberParameter.ParameterName = "@PhoneNumber";
phoneNumberParameter.Value = "0123456789";
command.Parameters.Add(phoneNumberParameter);
int result = command.ExecuteNonQuery();
}
}
La colonne SSN utilisant un chiffrement déterministe, on peut effectuer une recherche utilisant ce paramètre :
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM dbo.Customer WHERE SSN = @SSN";
command.CommandType = System.Data.CommandType.Text;
var parameter = command.CreateParameter();
parameter.ParameterName = "@SSN";
parameter.Value = "1234";
command.Parameters.Add(parameter);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader.GetString(reader.GetOrdinal("SSN")));
}
}
}
Si on regarde le contenu de la table on voit que les données sont chiffrées :
Et si on utilise SQL Server Profiler on peut vérifier que les données sont bien chiffrées lors de l’insertion :
Conclusion
Always Encrypted apporte une solution de chiffrement des données de manière transparente et garantit que seule l’application peut déchiffrer les données. Cela réduit donc fortement les risques d’accès aux données sensibles par des personnes non autorisées (incluant les sysadmin).
