Aracılığıyla paylaş

PowerShell ile Always Encrypted kullanarak sütun şifrelemeyi yapılandırma

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen Örneği

Bu makalede, Set-SqlColumnEncryption cmdlet'ini (SqlServer PowerShell modülünde) kullanarak veritabanı sütunları için hedef Always Encrypted yapılandırmasını ayarlama adımları sağlanır. Set-SqlColumnEncryption cmdlet'i hem hedef veritabanının şemasını hem de seçili sütunlarda depolanan verileri değiştirir. Sütunda depolanan veriler, sütunlar için belirlenen hedef şifreleme ayarlarına ve geçerli şifreleme yapılandırmasına bağlı olarak şifrelenebilir, yeniden şifrelenebilir veya şifresi çözülebilir.

Not

SQL Server 2019 (15.x) kullanıyorsanız ve SQL Server örneğiniz güvenli bir kapanımla yapılandırılmışsa, verileri veritabanından dışarı taşımadan şifreleme işlemlerini yerinde çalıştırabilirsiniz. Bkz. Always Encrypted ile güvenli güvenlik bölgelerini kullanarak sütun şifrelemeyi yerinde yapılandırma.

SqlServer PowerShell modülünde Always Encrypted desteği hakkında daha fazla bilgi için bkz. PowerShellkullanarak Always Encrypted'ı yapılandırma.

Önkoşullar

Hedef şifreleme yapılandırmasını ayarlamak için şunları yapmanız gerekir:

  • veritabanında bir sütun şifreleme anahtarı yapılandırılır (bir sütunu şifreler veya yeniden şifrelerseniz). Ayrıntılar için bkz. PowerShell kullanarak Always Encrypted anahtarlarını yapılandırma.
  • Şifrelemek, yeniden şifrelemek veya şifresini çözmek istediğiniz her sütunun ana sütununa PowerShell cmdlet'lerini çalıştıran bilgisayardan erişebilirsiniz.
  • SqlServer PowerShell modülü sürüm 22.0.50 veya üzerini kullanırsınız.

Performans ve Kullanılabilirlik Konuları

Veritabanı için belirtilen hedef şifreleme ayarlarını uygulamak için, Set-SqlColumnEncryption cmdlet'i hedef tabloları içeren sütunlardaki tüm verileri saydam bir şekilde indirir, verileri geçici bir tablo kümesine (hedef şifrelenmiş ayarlarla) yükler ve son olarak özgün tabloları tabloların yeni sürümleriyle değiştirir. SQL Server için temel alınan .NET Framework Veri Sağlayıcısı, hedef sütunun geçerli şifreleme yapılandırmasına ve hedef sütunlar için belirtilen hedef şifreleme ayarlarına bağlı olarak indirme veya/ve karşıya yükleme sırasında verileri şifreler veya/ve şifrelerini çözer. Etkilenen tablolardaki verilerin boyutuna ve ağ bant genişliğine bağlı olarak verileri taşıma işlemi uzun sürebilir.

Set-SqlColumnEncryption cmdlet'i, hedef şifreleme yapılandırmasını ayarlamak için iki yaklaşımı destekler: çevrimiçi ve çevrimdışı.

Çevrimdışı yaklaşımda, hedef tablolar (ve bunlarla ilgili tüm tablolar, örneğin, bir hedef tablonun yabancı anahtar ilişkisi olan tablolar) işlemin süresi boyunca yazma işlemleri için kullanılabilir değildir. Yabancı anahtar kısıtlamalarının semantiği (CHECK veya NOCHECK) çevrimdışı yaklaşım kullanılırken her zaman korunur.

Çevrimiçi yaklaşımla (SqlServer PowerShell modülünün 21.x veya sonraki bir sürümünü gerektirir), verileri kopyalama ve şifreleme, şifre çözme veya yeniden şifreleme işlemi artımlı olarak gerçekleştirilir. Uygulamalar, veri taşıma işlemi boyunca hedef tablolardan ve hedef tablolara veri okuyup yazabilir, ancak süresi MaxDownTimeInSeconds parametresi (ki bunu tanımlayabilirsiniz) ile sınırlı olan son yineleme hariç. Veriler kopyalanırken uygulamaların gerçekleştirebileceği değişiklikleri algılamak ve işlemek için cmdlet, hedef veritabanında Değişiklik İzleme etkinleştirir. Bu nedenle çevrimiçi yaklaşım, sunucu tarafında çevrimdışı yaklaşımdan daha fazla kaynak tüketir. Özellikle de veritabanında yoğun yazma gerektiren bir iş yükü çalışıyorsa, işlem çevrimiçi yaklaşımla çok daha fazla zaman alabilir. Çevrimiçi yaklaşım, tek seferde bir tabloyu şifrelemek için kullanılabilir ve tablonun birincil anahtarı olmalıdır. Varsayılan olarak yabancı anahtar kısıtlamaları, uygulamalar üzerindeki etkiyi en aza indirmek için NOCHECK seçeneğiyle yeniden oluşturulur. KeepCheckForeignKeyConstraints seçeneğini belirterek yabancı anahtar kısıtlamalarının semantiğini korumayı zorunlu tutabilirsiniz.

Çevrimdışı ve çevrimiçi yaklaşımlar arasında seçim yaparken aşağıdaki yönergeleri bulabilirsiniz:

Çevrimdışı yaklaşımı kullanın:

  • İşlemin süresini en aza indirmek için.
  • Aynı anda birden çok tablodaki sütunları şifrelemek/şifresini çözmek/yeniden şifrelemek için.
  • Hedef tabloda birincil anahtar yoksa.

Çevrimiçi yaklaşımı kullanın:

  • Veritabanının uygulamalarınızda kapalı kalma süresini/kullanılamazlığını en aza indirmek için.

Güvenlikle İlgili Dikkat Edilmesi Gerekenler

Veritabanı sütunları için şifrelemeyi yapılandırmak için kullanılan Set-SqlColumnEncryption cmdlet'i hem Always Encrypted anahtarlarını hem de veritabanı sütunlarında depolanan verileri işler. Bu nedenle, cmdlet'i güvenli bir bilgisayarda çalıştırmanız önemlidir. SQL Server'da bir veritabanınız varsa, cmdlet'i SQL Server örneğinizi barındıran bilgisayardan farklı bir bilgisayarda çalıştırın. Always Encrypted'ın birincil hedefi, veritabanı sistemi tehlikeye girmiş olsa bile şifrelenmiş hassas verilerin güvenli olmasını sağlamak olduğundan, SQL Server bilgisayarında anahtarları ve/veya hassas verileri işleyen bir PowerShell betiğini yürütmek özelliğin avantajlarını azaltabilir veya alt edebilir.

Görev Makale Düz metin anahtarlarına/anahtar deposuna erişir Veritabanına erişir
Adım 1. Bir PowerShell ortamı başlatın ve SqlServer modülünü içeri aktarın. SqlServer modülünü içeri aktarma Hayır Hayır
Adım 2. Sunucunuza ve veritabanınıza bağlanma Veritabanına bağlanma Hayır Evet
Adım 3. Sütun ana anahtarınız (döndürülecek sütun şifreleme anahtarını korumak) Azure Key Vault'ta depolanıyorsa Azure'da kimlik doğrulaması yapma Bağlan-AzAccount Evet Hayır
Adım 4. Sütun ana anahtarınız Azure Key Vault'ta depolanıyorsa Azure Key Vault için bir erişim belirteci alın. Get-AzAccessToken Hayır Hayır
Adım 5. Şifrelemek, yeniden şifrelemek veya şifresini çözmek istediğiniz her veritabanı sütunu için bir sqlColumnEncryptionSettings nesnesi dizisi oluşturma. SqlColumnMasterKeySettings, bellekte (PowerShell'de) bulunan bir nesnedir. Bir sütun için hedef şifreleme düzenini belirtir. New-SqlColumnEncryptionSettings Hayır Hayır
Adım 6. Önceki adımda oluşturduğunuz SqlColumnMasterKeySettings nesneleri dizisinde belirtilen istenen şifreleme yapılandırmasını ayarlayın. Belirtilen hedef ayarlara ve sütunun geçerli şifreleme yapılandırmasına bağlı olarak bir sütun şifrelenir, yeniden şifrelenir veya şifresi çözülür. Set-SqlColumnEncryption

Not: Bu adım uzun sürebilir. Uygulamalarınız, seçtiğiniz yaklaşıma (çevrimiçi veya çevrimdışı) bağlı olarak tüm işlem veya bir bölümü aracılığıyla tablolara erişemez.		 Evet Evet

Çevrimdışı Yaklaşım Kullanarak Sütunları Şifreleme - Örnek

Aşağıdaki örnekte, birkaç sütun için hedef şifreleme yapılandırmasını ayarlama gösterilmektedir. İki sütundan biri henüz şifrelenmemişse şifrelenir. Herhangi bir sütun farklı bir anahtar ve/veya farklı bir şifreleme türü kullanılarak zaten şifrelenmişse, şifresi çözülür ve belirtilen hedef anahtar/türle yeniden şifrelenir.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Çevrimiçi Yaklaşımı Kullanarak Sütunları Şifreleme - Örnek

Aşağıdaki örnekte, çevrimiçi yaklaşımı kullanarak birkaç sütun için hedef şifreleme yapılandırmasını ayarlama gösterilmektedir. İki sütundan biri henüz şifrelenmemişse şifrelenir. Herhangi bir sütun farklı bir anahtar ve/veya farklı bir şifreleme türü kullanılarak zaten şifrelenmişse, şifresi çözülür ve belirtilen hedef anahtar/türle yeniden şifrelenir.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -UseOnlineApproach -MaxDowntimeInSeconds 180 -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Sütunların Şifresini Çözme - Örnek

Aşağıdaki örnekte, bir veritabanında şu anda şifrelenmiş olan tüm sütunların şifresinin nasıl çözülmüş olduğu gösterilmektedir.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Find all encrypted columns, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$tables = $database.Tables
for($i=0; $i -lt $tables.Count; $i++){
    $columns = $tables[$i].Columns
    for($j=0; $j -lt $columns.Count; $j++) {
        if($columns[$j].isEncrypted) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType "Plaintext" 
        }
    }
}

# Decrypt all columns.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Şifreleme sonrası

Parametre şifreleme bilgilerini yenilemek için tabloya erişen tüm toplu işlemler ve saklı yordamlar için plan önbelleğini temizleyin.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Not

Etkilenen sorgunun planını önbellekten kaldırmazsanız, şifrelemeden sonra sorgunun ilk yürütmesi başarısız olabilir.

Plan önbelleğini dikkatlice temizlemek için ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE veya DBCC FREEPROCCACHE kullanın, çünkü bu, geçici olarak sorgu performansında düşüşe neden olabilir. Önbelleği temizlemenin olumsuz etkisini en aza indirmek için, yalnızca etkilenen sorgular için planları seçmeli olarak kaldırabilirsiniz.

sys.parameters içinde kalıcı olan ve sütunları şifreleyerek geçersiz kılınmış olabilecek her modülün parametrelerinin (saklı yordam, işlev, görünüm, tetikleyici) meta verilerini güncelleştirmek için sp_refresh_parameter_encryption'yi çağırın.

Sonraki Adımlar

Ayrıca Bkz.

  • Last updated on