Skip to content

Latest commit

 

History

History
83 lines (64 loc) · 3.48 KB

3-.NETClient.md

File metadata and controls

83 lines (64 loc) · 3.48 KB

.NET Client SQL Query

The code below represent a SQL query. This is coded in PowerShell but this exactly the same in C#.

  • Import the Generic provider
  • Create a connectionString (configure with your own settings)
  • Create the wrapped provider (MSSQL_CERTIFICATE_STORE)
  • Create the generic provider (GENERIC) with the wrapped provider, providing the right path to the certificate for the wrapped provider.
  • Create a SQL command with parameters
  • Execute Query
  • Check the request is successfull and data unencrypted
#import the extended Always Encrypted types (the Generic provider)
Add-type -Path '.\bin\SqlServerAlwaysEncrypted.dll'

#Configure your connection settings
$constr = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder
$constr.Item("Data Source") = "192.168.2.19"
$constr.Item("Initial Catalog") = "CLINIC"
$constr.Item("Authentication") = [System.Data.Sqlclient.SqlAuthenticationMethod]::ActiveDirectoryIntegrated

#ensure to setup the Column Encryption Setting
$constr.Item("Column Encryption Setting") = [System.Data.SqlClient.SqlConnectionColumnEncryptionSetting]::Enabled
$constr.Item("TrustServerCertificate") = $true

#access to your certificate from the Windows Certificate Store (take care if there are many to select the right one, here we get only the fisrt)
$cert = Get-childitem -Path (Cert:\CurrentUser\My)[0]

#create the wrapped Windows Certificate Store provider
$sqlstoreprovider = new-object -TypeName System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider

#Create the generic provider, providing the inner provider and the right path for this inner provider
$genericProvider = New-Object -TypeName SqlServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider($sqlstoreprovider, "CurrentUser/My/$($cert.Thumbprint)")

#create a dictionnary, fill this dictionnary with the generic provider and register this provider.
$customproviders = New-Object -TypeName 'System.Collections.Generic.Dictionary[String,System.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider]'
$customproviders.Add([SqlServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider]::ProviderName, $genericProvider)
[System.Data.SqlClient.SqlConnection]::RegisterColumnEncryptionKeyStoreProviders($customproviders)

#create the connection and connect
$connection = New-Object System.Data.SqlClient.SqlConnection($constr.ConnectionString);
            
try
{
    $connection.Open();
} catch {
    Write-Host "Error connecting"
}

#create the command with parameter @SSN
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE SSN=@SSN";
$command.Connection = $connection

$paramSSN = $command.CreateParameter();
$paramSSN.ParameterName = "@SSN";
$paramSSN.DbType = [System.Data.DbType]::AnsiStringFixedLength;
$paramSSN.Direction = [System.Data.ParameterDirection]::Input;

#provide clear-text value to search in the database. it's the driver responsability to encrypt the value
$paramSSN.Value = "795-73-9838";
$paramSSN.Size = 11;
$command.Parameters.Add($paramSSN);

#execute the query
$reader = $command.ExecuteReader()

 if ($reader.HasRows) {

     while ($reader.Read())
     {
        #print result, ensure one entry is returned and the data is in clear-text
        [String]::Format("{0}, {1}, {2}, {3}", $reader[0], $reader[1], $reader[2], ([DateTime]$reader[3]).ToShortDateString());
     }
 }

Sample Output :

795-73-9838, Catherine, Abel, 9/10/1996