Aracılığıyla paylaş

SQL Server Management Studio ile Always Encrypted kullanarak sütunları sorgulama

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

Bu makalede, SQL Server Management Studio (SSMS)kullanılarak Always Encrypted ile şifrelenmiş sütunların nasıl sorgulandığı açıklanmaktadır. SSMS ile şunları yapabilirsiniz:

  • Şifrelenmiş sütunlarda depolanan şifre metni değerlerini alın.
  • Şifrelenmiş sütunlarda depolanan düz metin değerlerini alın.
  • Şifrelenmiş sütunları hedefleyen düz metin değerleri gönderin (örneğin, INSERT veya UPDATE deyimlerinde ve SELECT deyimlerindeki WHERE yan tümcelerinin arama parametresi olarak).

Not

Azure Key Vault'ta yönetilen HSM depolanan sütun ana anahtarlarını kullanmak için SSMS 18.9 veya sonraki bir sürüm gerekir.

Şifrelenmiş sütunlarda depolanan şifreleme metni değerlerini alma

Şifrelenmiş sütunlarda depolanan verilerin şifre metnini alan SELECT sorgularının çalıştırılması (verilerin şifresini çözmeden) verileri koruyan sütun ana anahtarlarına erişiminiz olmasını gerektirmez. Şifrelenmiş bir sütundaki değerleri SSMS'de şifre metni olarak almak için:

  1. Sorgunuzu çalıştırdığınız sütunları koruyan anahtarlar hakkındaki meta verilere erişebildiğinizden emin olun. Gerçek sütun ana anahtarlarına erişebilmeniz gerekmez, ancak veritabanındaki sütun ana anahtarı ve sütun şifreleme anahtarı meta veri nesnelerini görüntülemek için veritabanı düzeyinde izinlere ihtiyacınız vardır. Ayrıntılar için aşağıdaki Şifrelenmiş sütunları sorgulama izinlerine bakınız.
  2. Şifreleme metni değerlerini alan bir SELECT sorgusu çalıştırabileceğiniz Sorgu Düzenleyicisi penceresinin veritabanı bağlantısı için Always Encrypted'ı devre dışı bırakmış olduğunuzdan emin olun. Aşağıda, bir veritabanı bağlantısı için Always Encrypted'ı etkinleştirme ve devre dışı bırakma hakkında ve numaralı bölümlere bakın.
  3. SELECT sorgunuzu çalıştırın. Şifrelenmiş sütunlardan alınan tüm veriler ikili (şifrelenmiş) değerler olarak döndürülür.

Şifreli metin örneği alınıyor

SSN Patients tablosunda şifrelenmiş bir sütun olduğu varsayıldığında, veritabanı bağlantısı için Always Encrypted devre dışı bırakılmışsa aşağıda gösterilen sorgu ikili şifreleme metni değerlerini alır.

SELECT [SSN] FROM [dbo].[Patients] sorgusunun ve sorgunun ikili şifreleme metni değerleri olarak gösterilen sonuçlarının ekran görüntüsü.

Şifrelenmiş sütunlarda depolanan düz metin değerlerini alma

Şifrelenmiş bir sütundaki değerleri düz metin olarak almak için (değerlerin şifresini çözmek için):

  1. Sorgunuzu çalıştırdığınız sütunları koruyan anahtarlar hakkındaki meta verilere ve ana sütun anahtarlara erişebildiğinizden emin olun. Ayrıntılar için aşağıdaki Şifrelenmiş sütunları sorgulama izinlerine bakabilirsiniz.
  2. Verilerinizi alan ve şifresini çözen bir SELECT sorgu çalıştırabileceğiniz Sorgu Düzenleyicisi penceresi için Veritabanı bağlantısı için Always Encrypted'ı etkinleştirdiğinizden emin olun. Bu, SQL Server için .NET Framework Veri Sağlayıcısı'na (SSMS tarafından kullanılır) sorgu sonuç kümesindeki şifrelenmiş sütunların şifresini çözmesini ister. Aşağıda, numaralı "Veritabanı bağlantısı için Always Encrypted'ı etkinleştirme ve devre dışı bırakma" konusuna bakın.
  3. SELECT sorgunuzu çalıştırın. Şifrelenmiş sütunlardan alınan tüm veriler, özgün veri türlerinin düz metin değerleri olarak döndürülür.

Düz metin örneği alınıyor

SSN'nin Patients tablosunda şifrelenmiş bir char(11) sütunu olduğu varsayıldığında, veritabanı bağlantısı için Always Encrypted etkinleştirildiyse ve SSN sütunu için yapılandırılmış sütun ana anahtarına erişiminiz varsa, aşağıda gösterilen sorgu düz metin değerleri döndürür.

SELECT [SSN] FROM [Clinic].[dbo].[Patients] sorgusunun ekran görüntüsü ve sorgunun düz metin değerleri olarak gösterilen sonuçları.

Şifrelenmiş sütunları hedefleyen düz metin değerleri gönderme

Şifrelenmiş bir sütunu hedefleyen bir değer gönderen bir sorguyu yürütmek için, örneğin şifrelenmiş bir sütunda depolanan bir değere göre ekleme, güncelleştirme veya filtreleme gerçekleştirme:

  1. Sorgunuzun çalıştığı sütunları koruyan anahtarlar için ana sütun anahtarlara ve meta verilere erişebildiğinizden emin olun. Daha fazla bilgi için aşağıdaki Şifrelenmiş sütunları sorgulama izinleri bakın.

  2. Verilerinizi alan ve şifresini çözen bir SELECT sorgu çalıştırabileceğiniz Sorgu Düzenleyicisi penceresi için Veritabanı bağlantısı için Always Encrypted'ı etkinleştirdiğinizden emin olun. Bu, SQL Server için .NET Framework Veri Sağlayıcısı'na (SSMS tarafından kullanılır) sorgu sonuç kümesindeki şifrelenmiş sütunların şifresini çözmesini ister. Veritabanı bağlantısı için Always Encrypted'ı etkinleştirme ve devre dışı bırakmaya aşağıdaki ve bölümünde bakın.

  3. Sorgu Düzenleyicisi penceresi için 'Always Encrypted' özelliğinde parametreleştirmenin etkin olduğundan emin olun. (En az SSMS sürüm 17.0 gerektirir.) Veritabanına eklemek, güncellemek veya ölçüt olarak filtrelemek istediğiniz bir değerle Transact-SQL değişkenini bildirin ve başlatın. Ayrıntılar için aşağıdaki Always Encrypted Parametreleştirme bölümüne bakın.

  4. Transact-SQL değişkeninin değerini veritabanına göndererek sorgunuzu çalıştırın. SSMS değişkeni bir sorgu parametresine dönüştürür ve veritabanına göndermeden önce değerini şifreler.

Örnek

SSN'nin Patients tablosunda şifrelenmiş bir char(11) sütunu olduğu varsayıldığında, aşağıdaki betik, SSN sütununda '795-73-9838' içeren bir satır bulmaya çalışır ve veritabanı bağlantısı için "Always Encrypted" etkin, Sorgu Düzenleyici penceresi için "Always Encrypted" parametreleştirmesi etkin, ve SSN sütunu için yapılandırılmış sütun anahtarına erişiminiz varsa, LastName sütununun değerini döndürür.

@SSN değişkeni ve döndürülen sonuç satırı kullanan sorgunun ekran görüntüsü.

Şifrelenmiş sütunları sorgulama izinleri

Şifre metnindeki verileri alan sorgular da dahil olmak üzere şifrelenmiş sütunlarda sorgu çalıştırmak için veritabanındaki VIEW ANY COLUMN MASTER KEY DEFINITION ve VIEW ANY COLUMN ENCRYPTION KEY DEFINITION izinlerine sahip olmanız gerekir.

Yukarıdaki izinlere ek olarak, sorgu sonuçlarının şifresini çözmek veya herhangi bir sorgu parametresini şifrelemek için (Transact-SQL değişkenleri parametreleştirilerek üretilen), hedef sütunları koruyan sütun ana anahtarına erişmek ve bu anahtarı kullanmak için anahtar deposu izinlerine de ihtiyacınız vardır. Anahtar deposu izinleri hakkında ayrıntılı bilgi için Always Encrypted için sütun anahtarları oluşturma ve depolama bölümünü inceleyin ve anahtar deponuzla ilgili bir bölümü bulun.

Veritabanı bağlantısı için Always Encrypted'ı etkinleştirme ve devre dışı bırakma

SSMS'deki bir veritabanına bağlandığınızda, veritabanı bağlantısı için Always Encrypted'ı etkinleştirebilir veya devre dışı bırakabilirsiniz. Varsayılan olarak Always Encrypted devre dışıdır.

Veritabanı bağlantısı için Always Encrypted'ın etkinleştirilmesi, SQL Server Management Studio tarafından kullanılan SQL Server için .NET Framework Veri Sağlayıcısının saydam bir şekilde şunları denemesini gerektirir:

  • Şifrelenmiş sütunlardan alınan ve sorgu sonuçlarında döndürülen tüm değerlerin şifresini çöz.
  • Şifrelenmiş veritabanı sütunlarını hedefleyen parametreli Transact-SQL değişkenlerinin değerlerini şifreleyin.

Bir bağlantı için Always Encrypted'ı etkinleştirmezseniz, SSMS'nin kullandığı SQL Server için .NET Framework Veri Sağlayıcısı sorgu parametrelerini şifrelemeyi veya sonuçların şifresini çözmeyi denemez.

Yeni bir bağlantı oluşturduğunuzda veya Sunucuya Bağlan iletişim kutusunu kullanarak var olan bir bağlantıyı değiştirdiğinizde Always Encrypted'ı etkinleştirebilir veya devre dışı bırakabilirsiniz.

Always Encrypted'ı etkinleştirmek (devre dışı bırakmak) için:

  1. Sunucuya Bağlan iletişim kutusunu açın (ayrıntılar için bkz. SQL Server örneğine bağlanma ).
  2. Seçenekseçin.
  3. Always Encrypted sekmesini seçin. Always Encrypted'ı etkinleştirmek için Always Encrypted'ı etkinleştir (sütun şifreleme)seçin. Always Encrypted'ı devre dışı bırakmak için Always Encrypted'ı Etkinleştir (sütun şifrelemesi) seçili olmadığından emin olun.
  4. seçin,bağlan.

İpucu

Mevcut Sorgu Düzenleyicisi penceresinde Always Encrypted'ın etkinleştirilmesi ve devre dışı bırakılması arasında geçiş yapmak için:

  1. Sorgu Düzenleyicisi penceresinde herhangi bir yere sağ tıklayın.
  2. Bağlantı>Bağlantıyı Değiştir ...öğesini seçin. Bu işlem, Sorgu Düzenleyicisi penceresinin geçerli bağlantısı için Sunucuya Bağlan diyalog penceresi açar.
  3. Yukarıdaki adımları izleyerek Always Encrypted'ı etkinleştirin veya devre dışı bırakın ve Bağlanöğesine tıklayın.

Not

Always Encrypted ilegüvenli kapanımlar kullanırken, sunucu tarafı güvenli bir kapanımdan yararlanan deyimleri çalıştırmak için bkz. Güvenli kapanımlarıkullanarak Transact-SQL deyimlerini çalıştırma.

Always Encrypted için parametreleştirme

Always Encrypted için parametreleştirme, SQL Server Management Studio'da Transact-SQL değişkenleri otomatik olarak sorgu parametrelerine (SqlParameter Sınıfıörnekleri) dönüştüren bir özelliktir. (En az SSMS sürüm 17.0 gerektirir.) Bu, SQL Server için temel alınan .NET Framework Veri Sağlayıcısının şifrelenmiş sütunları hedefleyen verileri algılamasını ve bu verileri veritabanına göndermeden önce şifrelemesini sağlar.

Parametreleme olmadan, .NET Framework Veri Sağlayıcısı, Sorgu Düzenleyicisi'nde yazdığınız her deyimi, parametrelenmemiş bir sorgu olarak iletir. Sorgu şifrelenmiş sütunları hedefleyen değişmez değerler veya Transact-SQL değişkenleri içeriyorsa, SQL Server için .NET Framework Veri Sağlayıcısı sorguyu veritabanına göndermeden önce bunları algılayamaz ve şifreleyemez. Sonuç olarak, tür uyuşmazlığı (düz metin değişmez Transact-SQL değişkeni ile şifrelenmiş sütun arasındaki) nedeniyle sorgu başarısız olur. Örneğin, SSN sütununun şifrelendiğinden aşağıdaki sorgu parametreleme olmadan başarısız olur.

DECLARE @SSN NCHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Always Encrypted için Parametreleştirmeyi etkinleştirme ve devre dışı bırakma

Always Encrypted için parametreleştirme varsayılan olarak devre dışıdır.

Geçerli Sorgu Düzenleyicisi penceresinde Always Encrypted için Parametreleştirmeyi etkinleştirmek/devre dışı bırakmak için:

  1. Ana menüden sorgu seçin.
  2. Sorgu Seçenekleri...seçin.
  3. Yürütme>Gelişmişbölümüne gidin.
  4. Always Encrypted için Parametreleştirmeyi Etkinleştir'ı seçin veya kaldırın.
  5. 'yı seçinOK .

Gelecekteki Sorgu Düzenleyicisi pencerelerine yönelik Always Encrypted için Parametreleştirmeyi etkinleştirmek/devre dışı bırakmak için:

  1. Ana menüden Araçları'ni seçin.
  2. Seçenekler'i seçin....
  3. Sorgu Yürütme>SQL Server>Gelişmişgidin.
  4. Always Encrypted için Parametreleştirmeyi Etkinleştirseçin veya seçimini kaldırın.
  5. Tamamseçin.

Sorgu Düzenleyicisi penceresinde Always Encrypted etkin bir veritabanı bağlantısı kullanan bir sorgu yürütürseniz ancak Sorgu Düzenleyicisi penceresi için parametreleme etkinleştirilmediyse, bunu etkinleştirmeniz istenir.

Not

Always Encrypted için parametreleştirme yalnızca Always Encrypted etkin veritabanı bağlantılarını kullanan Sorgu Düzenleyicisi pencerelerinde çalışır (bkz. Always Encryptediçin Parametreleştirmeyi etkinleştirme ve devre dışı bırakma ). Sorgu Düzenleyicisi penceresinde Always Encrypted etkinleştirilmemiş bir veritabanı bağlantısı kullanılıyorsa hiçbir Transact-SQL değişkeni parametrelendirilmeyecektir.

Always Encrypted için Parametreleştirme nasıl çalışır?

Sorgu Düzenleyicisi penceresi için hem Always Encrypted için Parametreleştirme hem de veritabanı bağlantısındaki Always Encrypted davranışı etkinleştirilirse, SQL Server Management Studio aşağıdaki önkoşul koşulları karşılayan Transact-SQL değişkenleri parametreleştirmeyi dener:

  • "Aynı ifadede bildirilip (satır içi başlatma) başlatılır." Ayrı SET deyimleri kullanılarak bildirilen değişkenler parametrelendirilmeyecek.
  • Tek bir değişmez değer kullanılarak başlatılır. tüm işleçler veya işlevler dahil olmak üzere ifadeler kullanılarak başlatılan değişkenler parametrelendirilmeyecektir.

Aşağıda, SQL Server Management Studio'nun parametreleştireceği değişken örnekleri verilmiştir.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

Sql Server Management Studio'nun parametreleştirmeye çalışmayacağından birkaç değişken örneği aşağıda verilmiştir:

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

Parametreleştirme girişiminin başarılı olması için:

  • Parametre olarak başlatılacak değişkenin sabitinin türü, değişken bildirimindeki türle eşleşmelidir.
  • Değişkenin bildirilen türü bir tarih veya saat türüyse, değişkenin ISO 8601 ile uyumluaşağıdaki biçimlerden biri kullanılarak bir dize kullanılarak başlatılması gerekir.

Parametreleştirme hatalarına neden olacak Transact-SQL değişken bildirimlerinin örnekleri aşağıda verilmiştir:

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable

SQL Server Management Studio, hangi değişkenlerin başarıyla parametrelendirilebileceğini ve hangi parametreleştirme girişimlerinin başarısız olduğunu (ve nedenini) size bildirmek için IntelliSense kullanır.

Başarıyla parametrelendirilebilen bir değişkenin bildirimi, Sorgu Düzenleyicisi'nde bir uyarı altı çizili olarak işaretlenir. Bir uyarı alt çizgisiyle işaretlenmiş bir bildirimin üzerine geldiğinizde, parametreleştirme işleminin sonuçlarını, elde edilen SqlParameter nesnesinin anahtar özelliklerinin değerleri de dahil olmak üzere (değişkene eşlendiği): SqlDbType, Boyut, Hassasiyet, Ölçek, SqlValuegöreceksiniz. Ayrıca, Hata Listesi görünümünün Uyarı sekmesinde başarıyla parametreleştirilmiş tüm değişkenlerin tam listesini de görebilirsiniz. Hata Listesi görünümünü açmak için, ana menüden Görünüm'ni seçin ve ardından hata listesi seçin.

SQL Server Management Studio bir değişkeni parametreleştirmeye çalıştıysa ancak parametreleştirme başarısız olduysa, değişkenin bildirimi bir hata altı çizili olarak işaretlenir. Hata altı çizili olarak işaretlenmiş bildirim üzerine geldiğinizde hata ile ilgili sonuçları alırsınız. Ayrıca, Hata Listesi görünümünün Hata sekmesinde tüm değişkenler için parametreleştirme hatalarının tam listesini de görebilirsiniz. Hata Listesi görünümünü açmak için, ana menüden Görünüm'ni seçin ve ardından hata listesi seçin.

Aşağıdaki ekran görüntüsünde altı değişken bildirimi örneği gösterilmektedir. SQL Server Management Studio ilk üç değişkeni başarıyla parametrelendirdi. Son üç değişken parametreleştirme için önkoşul koşulları karşılamadı ve bu nedenle SQL Server Management Studio bunları parametreleştirmeye çalışmadı (bildirimleri hiçbir şekilde işaretlenmedi).

Üç başarılı parametreli ve üç hata ve ilişkili uyarı iletisi içeren altı değişken bildiriminin örneğini gösteren ekran görüntüsü.

Aşağıdaki başka bir örnek, parametreleştirme için önkoşul koşulları karşılayan iki değişkeni gösterir, ancak değişkenler yanlış başlatıldığı için parametreleştirme girişimi başarısız olmuştur.

İlişkili hata iletileriyle sonuç olarak başarısız olan iki değişken bildiriminin örneğini gösteren ekran görüntüsü.

Not

Always Encrypted, tür dönüştürmelerinin sınırlı bir alt kümesini desteklediğinden, çoğu durumda bir Transact-SQL değişkeninin veri türünün hedeflediği veritabanı sütununun türüyle aynı olması gerekmektedir. Örneğin, Patients tablosundaki SSN sütununun türünün char(11)olduğunu varsayarsak, nchar(11)olan @SSN değişkeninin türü sütunun türüyle eşleşmediğinden aşağıdaki sorgu başarısız olur.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

Not

Parametreleştirme olmadan, tür dönüştürmeleri de dahil olmak üzere sorgunun tamamı SQL Server/Azure SQL Veritabanı içinde işlenir. Parametreleştirme etkinleştirildiğinde, bazı tür dönüştürmeleri SQL Server Management Studio'da .NET Framework tarafından gerçekleştirilir. .NET Framework tür sistemi ile SQL Server tür sistemi (örneğin, float gibi bazı türlerin farklı duyarlığı) arasındaki farklar nedeniyle, parametreleştirme etkinken yürütülen bir sorgu parametreleştirme etkinleştirilmeden yürütülen sorgudan farklı sonuçlar üretebilir.

Sonraki adımlar

Ayrıca bkz.