مقدمه و مسیر یادگیری

ENCRYPTBYKEY یکی از قابلیت‌های تخصصی SQL Server در حوزه امنیت داده است. استفاده درست از آن فقط حفظ Syntax نیست؛ باید نوع بایت‌های ورودی، محل نگهداری خروجی، سطح مجوز، رفتار تابع در برابر NULL و هزینه پردازشی را هم شناخت. این مقاله از تعریف پایه شروع می‌کند و با مثال‌های مستقل، سناریوی سازمانی، خطاهای رایج و نکات بهینه‌سازی ادامه می‌یابد.

ENCRYPTBYKEY داده را با کلید متقارنِ موجود در سلسله‌مراتب رمزنگاری پایگاه داده تبدیل می‌کند. کلید متقارن برای حجم زیاد سریع‌تر از رمزنگاری نامتقارن است و معمولاً توسط Certificate محافظت می‌شود. هدف این راهنما آن است که Query نمونه به یک طراحی قابل اداره تبدیل شود؛ یعنی برنامه بتواند شکست را تشخیص دهد، تغییر تنظیم یا کلید را مدیریت کند و در زمان Restore نیز داده یا کنترل امنیتی از دست نرود.

برای دیدن جایگاه این موضوع در کنار سایر قابلیت‌ها، راهنمای جامع توابع رمزنگاری SQL Server را مطالعه کنید. در آن مقاله تفاوت هش، رمزنگاری متقارن، Passphrase، امضای دیجیتال و تنظیم VerifySignature مقایسه شده است.

تعریف و نحو ENCRYPTBYKEY

ENCRYPTBYKEY داده را با کلید متقارنِ موجود در سلسله‌مراتب رمزنگاری پایگاه داده تبدیل می‌کند. کلید متقارن برای حجم زیاد سریع‌تر از رمزنگاری نامتقارن است و معمولاً توسط Certificate محافظت می‌شود.

نکته امنیتی: نمونه‌های مقاله برای آموزش هستند. Passwordها و Secretهای نمایشی را در محیط Production استفاده نکنید و قبل از هر تغییر Server-wide یا ساخت شیء امنیتی، Change Management سازمان را رعایت کنید.

Syntax

ENCRYPTBYKEY ( key_GUID, cleartext [ , add_authenticator, authenticator ] )

پارامترها و خروجی

• key_GUID: شناسه یکتای کلید متقارن بازشده که معمولاً از KEY_GUID به دست می‌آید.
• cleartext: داده متنی یا باینری با حداکثر اندازه پشتیبانی‌شده تابع.
• add_authenticator: مقدار 1 برای افزودن Authenticator و 0 برای رمزنگاری ساده.
• authenticator: داده sysname که ciphertext را به هویت یک ردیف پیوند می‌دهد.

تابع یک مقدار varbinary با حداکثر طول ۸۰۰۰ بایت برمی‌گرداند. اگر کلید باز نباشد، ورودی نامعتبر باشد یا عملیات ممکن نشود، معمولاً نتیجه NULL است؛ بنابراین کنترل خروجی قبل از ذخیره ضروری است.

مدل امنیت، مجوز و چرخه عمر

بازبودن کلید به Session وابسته است، نه Security Context. کمترین مجوز، ثبت چرخه عمر کلید، Backup گواهی و Master Key و جداسازی نقش‌های خواندن و رمزگشایی از اصول حیاتی‌اند. Authenticator احتمال جابه‌جایی ciphertext میان ردیف‌ها را کم می‌کند.

در طراحی حرفه‌ای ENCRYPTBYKEY مالک فنی، مالک داده و مسئول امنیت باید مشخص باشند. حساب برنامه فقط حداقل مجوز لازم را دریافت می‌کند و حساب استقرار یا DBA برای ایجاد اشیای امنیتی از مسیر کنترل‌شده استفاده می‌شود. هیچ Secret، Password، متن واضح حساس یا Private Key نباید در Source Control، خروجی خطا و لاگ عمومی ثبت شود.

چرخه عمر شامل ایجاد، فعال‌سازی، نسخه‌بندی، تعویض، Backup، Restore، ابطال و حذف کنترل‌شده است. تست بازیابی باید ثابت کند که Backup صرفاً وجود ندارد، بلکه واقعاً قابل استفاده است. در سامانه‌های چندمحیطی نیز کلیدها و Secretهای Development، Test و Production باید مستقل باشند.

مثال‌های عملی مستقل

ده مثال زیر جنبه‌های متفاوت ENCRYPTBYKEY را از مقدار ثابت تا داده نمونه، NULL، حالت مرزی، خطای رایج، گزارش سازمانی و سنجش Performance پوشش می‌دهند. اشیایی با پیشوند Article صرفاً آزمایشی هستند و پیش از اجرا باید نام‌گذاری و سیاست Password محیط خود را جایگزین کنید.

مثال 1: رمزنگاری پایه با AES_256

سلسله‌مراتب کلید را می‌سازیم، کلید متقارن را باز می‌کنیم و یک متن فارسی را رمز می‌کنیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
OPEN SYMMETRIC KEY Article_AES256_Key
DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000)=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'متن محرمانه');
SELECT DATALENGTH(@Cipher) AS CipherBytes, @Cipher AS CipherText;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: کلید باید پیش از تابع در همان Session باز باشد؛ بازبودن آن بین Connectionهای جدا منتقل نمی‌شود.

مثال 2: ذخیره ciphertext در جدول نمونه

دو شماره حساب را در ستون varbinary رمز می‌کنیم و هیچ متن واضحی در ستون مقصد قرار نمی‌دهیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Accounts TABLE(AccountID int PRIMARY KEY,EncryptedIBAN varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT @Accounts VALUES
(1,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'IR100000000000000000000001')),
(2,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'IR200000000000000000000002'));
SELECT AccountID,DATALENGTH(EncryptedIBAN) AS StoredBytes FROM @Accounts;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: نوع varbinary برای ciphertext مناسب است؛ تبدیل آن به رشته احتمال تخریب بایت‌ها را ایجاد می‌کند.

مثال 3: پیوند ciphertext به ردیف با Authenticator

شناسه مشتری به‌عنوان Authenticator استفاده می‌شود تا کپی ciphertext به ردیف دیگر در رمزگشایی معتبر نباشد.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @CustomerID int=901;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SELECT ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'09120000000',1,CONVERT(sysname,@CustomerID)) AS ProtectedMobile;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: Authenticator محرمانه نیست؛ باید پایدار باشد و هنگام Decrypt دقیقاً با همان قرارداد ساخته شود.

مثال 4: رمزنگاری متن Unicode در SELECT

نام فارسی را بدون تبدیل به Code Page رمز می‌کنیم و طول بایت ورودی و خروجی را مقایسه می‌کنیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Plain nvarchar(100)=N'شرکت مهندسی پارس';
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SELECT DATALENGTH(@Plain) AS PlainBytes,
       DATALENGTH(ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),@Plain)) AS CipherBytes;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: ستون مقصد را بر پایه بیشترین ciphertext آزمایش‌شده طراحی کنید، نه فقط طول ظاهری متن.

مثال 5: کنترل رفتار NULL

ورودی NULL و نتیجه رمزنگاری را بررسی می‌کنیم تا برنامه میان نبود داده و شکست عملیات تفاوت بگذارد.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Plain nvarchar(50)=NULL;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SELECT ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),@Plain) AS EncryptedNull;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: اگر NULL برای ستون مجاز نیست، پیش از فراخوانی Validate کنید و آن را به خطای دامنه تبدیل کنید.

مثال 6: بررسی ورودی نزدیک محدودیت تابع

یک payload چند هزار بایتی را رمز می‌کنیم و اندازه خروجی را پیش از انتخاب نوع ستون می‌سنجیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Plain nvarchar(max)=REPLICATE(CONVERT(nvarchar(max),N'الف'),3000);
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Cipher varbinary(8000)=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),@Plain);
SELECT DATALENGTH(@Plain) AS PlainBytes,DATALENGTH(@Cipher) AS CipherBytes;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: برای payload بزرگ، محدودیت ۸۰۰۰ بایتی خروجی را با نسخه و الگوریتم هدف آزمایش کنید.

مثال 7: رمزنگاری داده در سناریوی منابع انسانی

شماره بیمه کارکنان با Authenticator مبتنی بر EmployeeID در یک مجموعه نمونه ذخیره می‌شود.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Employees TABLE(EmployeeID int PRIMARY KEY,InsuranceCipher varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT @Employees VALUES
(10,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'INS-10010',1,N'10')),
(20,ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'INS-10020',1,N'20'));
SELECT EmployeeID,DATALENGTH(InsuranceCipher) AS Bytes FROM @Employees;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: در تولید، مجوز درج و رمزگشایی را از دسترسی گزارش عمومی جدا کنید.

مثال 8: تشخیص کلید بسته

روش اشتباهِ فراخوانی بدون OPEN را به‌صورت امن آزمایش می‌کنیم و نتیجه NULL را آشکار می‌سازیم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
CLOSE ALL SYMMETRIC KEYS;
DECLARE @Cipher varbinary(8000)=ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'آزمون');
SELECT @Cipher AS CipherText,CASE WHEN @Cipher IS NULL THEN N'کلید باز نیست' ELSE N'موفق' END AS Status;

نکته کاربردی: تابع لزوماً Exception تولید نمی‌کند؛ کنترل NULL بخشی از مسیر صحیح خطاست.

مثال 9: جلوگیری از استفاده کلید اشتباه

دو کلید می‌سازیم ولی فقط کلید اصلی را باز می‌کنیم؛ کلید بسته برای رمزنگاری نتیجه معتبر نمی‌دهد.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys WHERE name=N'Article_Second_Key')
    CREATE SYMMETRIC KEY Article_Second_Key WITH ALGORITHM=AES_256 ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
SELECT ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),N'صحیح') AS OpenKeyCipher,
       ENCRYPTBYKEY(KEY_GUID(N'Article_Second_Key'),N'اشتباه') AS ClosedKeyCipher;
CLOSE SYMMETRIC KEY Article_AES256_Key;

نکته کاربردی: نام و مالکیت کلیدها را ثابت و قابل ممیزی نگه دارید تا انتخاب اشتباه در Deployment رخ ندهد.

مثال 10: رمزنگاری Batch با یک بار بازکردن کلید

هزار مقدار نمونه را در یک واحد کاری رمز می‌کنیم و کلید را بیرون حلقه منطقی باز نگه می‌داریم.

IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Article-Demo-Master-Key#1405!';
IF CERT_ID(N'Article_Encryption_Cert') IS NULL
    CREATE CERTIFICATE Article_Encryption_Cert WITH SUBJECT = N'گواهی آزمایشی مقاله رمزنگاری';
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'Article_AES256_Key')
    CREATE SYMMETRIC KEY Article_AES256_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Article_Encryption_Cert;
DECLARE @Batch TABLE(ID int,SecretCipher varbinary(8000));
OPEN SYMMETRIC KEY Article_AES256_Key DECRYPTION BY CERTIFICATE Article_Encryption_Cert;
INSERT @Batch(ID,SecretCipher)
SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
       ENCRYPTBYKEY(KEY_GUID(N'Article_AES256_Key'),CONVERT(nvarchar(20),ROW_NUMBER() OVER(ORDER BY (SELECT NULL))))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
CLOSE SYMMETRIC KEY Article_AES256_Key;
SELECT COUNT(*) AS EncryptedRows FROM @Batch WHERE SecretCipher IS NOT NULL;

نکته کاربردی: بازکردن یک‌باره کلید سربار را کم می‌کند؛ مدت Transaction و فشار Log نیز باید اندازه‌گیری شود.

خطاهای رایج و روش عیب‌یابی

در عیب‌یابی ENCRYPTBYKEY ابتدا یک نمونه کوچک با مقدار ثابت بسازید و سپس همان Session، Database Context، نوع داده و مجوز حساب برنامه را بازسازی کنید. Error Message، مقدار NULL، طول بایتی ورودی و خروجی و وضعیت اشیای امنیتی را ثبت کنید، اما داده محرمانه را در Log قرار ندهید.

• 1. فراموش‌کردن OPEN SYMMETRIC KEY. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 2. ذخیره ciphertext در ستون کوتاه. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 3. استفاده دوباره از داده حساس به‌عنوان Authenticator نامناسب. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 4. نداشتن Backup امن از Certificate و کلید. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.
• 5. اجرای تابع روی ستون در شرط جست‌وجوی حجیم. برای رفع آن قرارداد داده و پیش‌شرط تابع را صریح کنترل کنید.

روش اشتباه رایج این است که با COALESCE یک NULL امنیتی به رشته خالی تبدیل شود و فرایند موفق تلقی گردد. مسیر درست باید میان «داده واقعاً تهی»، «مجوز ناکافی»، «کلید یا Secret نامعتبر» و «ورودی خراب» تفاوت بگذارد و در رخداد مشکوک Fail Closed باشد.

Performance Considerations

کلید را یک بار در ابتدای واحد کاری باز و در پایان ببندید؛ باز و بسته‌کردن برای تک‌تک ردیف‌ها سربار می‌سازد. رمزنگاری در WHERE قابلیت SARGable بودن ستون اصلی را از بین می‌برد؛ برای جست‌وجوی برابر، یک ستون Hash کمکی با سیاست امنیتی جداگانه طراحی کنید.

برای ساخت Baseline، زمان CPU، Duration، تعداد Logical Read، اندازه Log و نرخ تراکنش را پیش و پس از افزودن ENCRYPTBYKEY اندازه بگیرید. Query Store برای تغییر Plan و Extended Events برای خطا و زمان‌های غیرعادی مفید است؛ ثبت payload حساس در Session پایش ممنوع است.

بهینه‌سازی باید با حفظ مدل امنیت انجام شود. حذف Authenticator، نگهداری متن واضح یا بازکردن بیش‌ازحد مجوزها شاید آزمایش را سریع‌تر نشان دهد، ولی ریسک را به‌شدت بالا می‌برد. نتیجه قابل قبول توازنی مستند میان محرمانگی، تمامیت، دسترس‌پذیری و هزینه است.

Best Practices و کاربرد واقعی

• AES_256 را برای طراحی جدید ارزیابی کنید.
• کلید را فقط در محدوده لازم باز نگه دارید.
• Ciphertext را در varbinary مناسب ذخیره کنید.
• خروجی NULL را به خطای قابل مشاهده تبدیل کنید.
• Authenticator پایدار و یکتای ردیف به‌کار ببرید.
• بازیابی کلیدها را در محیط آزمایشی تمرین کنید.

شماره ملی، شماره حساب و شناسه‌های قراردادی نمونه‌های رایج رمزنگاری ستونی هستند. راهکار باید علاوه بر محرمانگی، کنترل دسترسی، Audit، پشتیبان‌گیری و برنامه چرخش کلید داشته باشد. رمزنگاری بدون عملیات بازیابی آزموده‌شده می‌تواند داده سازمان را برای همیشه غیرقابل استفاده کند.

در اجرای سازمانی ENCRYPTBYKEY بهتر است منطق در Stored Procedure یا Service مشخص متمرکز شود تا همه برنامه‌ها قرارداد یکسانی برای نوع داده، خطا و نسخه امنیتی داشته باشند. تست خودکار باید نتیجه صحیح، ورودی دستکاری‌شده، مجوز ناکافی و بازیابی پس از Restore را پوشش دهد.

سؤالات متداول

1. ENCRYPTBYKEY در SQL Server دقیقاً چه کاری انجام می‌دهد؟

ENCRYPTBYKEY داده را با کلید متقارنِ موجود در سلسله‌مراتب رمزنگاری پایگاه داده تبدیل می‌کند. کلید متقارن برای حجم زیاد سریع‌تر از رمزنگاری نامتقارن است و معمولاً توسط Certificate محافظت می‌شود. در یک پروژه واقعی باید ورودی، خروجی، مجوز و رفتار خطا پیش از استفاده نهایی با نسخه SQL Server مقصد آزمایش شود.

2. نوع خروجی ENCRYPTBYKEY چیست و چگونه باید ذخیره شود؟

تابع یک مقدار varbinary با حداکثر طول ۸۰۰۰ بایت برمی‌گرداند. اگر کلید باز نباشد، ورودی نامعتبر باشد یا عملیات ممکن نشود، معمولاً نتیجه NULL است؛ بنابراین کنترل خروجی قبل از ذخیره ضروری است. انتخاب نوع ستون کوتاه یا تبدیل ضمنی از خطاهای مهم طراحی است؛ Schema باید بر پایه حداکثر خروجی قابل انتظار ساخته شود.

3. استفاده تجاری از ENCRYPTBYKEY چه ارزشی ایجاد می‌کند؟

این قابلیت می‌تواند بخشی از کنترل محرمانگی، تمامیت یا پایش امنیتی سامانه باشد و ریسک تغییر یا افشای داده را کاهش دهد. ارزش تجاری زمانی واقعی است که کنار کنترل دسترسی، Audit، Backup و فرایند پاسخ‌گویی به رخداد پیاده‌سازی شود.

4. هزینه اجرای پروژه ENCRYPTBYKEY چگونه برآورد می‌شود؟

برآورد به حجم داده، تعداد محیط‌ها، نرخ تراکنش، مجوزهای موجود، عملیات مهاجرت و الزامات بازیابی وابسته است. یک ارزیابی فنی کوتاه و Benchmark روی داده نماینده، برآورد آموزش، مشاوره یا اجرای پروژه SQL Server را دقیق‌تر می‌کند.

5. ENCRYPTBYKEY چه تفاوتی با HASHBYTES یا Always Encrypted دارد؟

HASHBYTES یک‌طرفه است و برای بازگرداندن متن طراحی نشده؛ قابلیت‌های رمزنگاری سمت سرور داده را در موتور قابل پردازش می‌کنند؛ Always Encrypted می‌تواند کلید و plaintext را از Database Engine دور نگه دارد. انتخاب درست تابع به مدل تهدید و نیاز عملیاتی بستگی دارد.

6. چه زمانی برای ENCRYPTBYKEY به مشاوره SQL Server نیاز داریم؟

وقتی داده حساس تولیدی، چند برنامه مصرف‌کننده، چرخش کلید، الزامات قانونی یا دسترس‌پذیری بالا مطرح است، بازبینی معماری ارزش زیادی دارد. مشاوره باید خروجی‌های قابل تحویل مانند Threat Model، ماتریس مجوز، Runbook بازیابی و آزمون Performance داشته باشد.

7. رایج‌ترین علت خطا یا NULL در ENCRYPTBYKEY چیست؟

فراموش‌کردن OPEN SYMMETRIC KEY، ذخیره ciphertext در ستون کوتاه، استفاده دوباره از داده حساس به‌عنوان Authenticator نامناسب از علت‌های متداول‌اند. عیب‌یابی را با نوع داده، طول واقعی، وضعیت اشیای امنیتی، مجوز کاربر و اجرای یک نمونه حداقلی در همان Session شروع کنید.

8. اثر ENCRYPTBYKEY بر Performance چقدر است؟

کلید را یک بار در ابتدای واحد کاری باز و در پایان ببندید؛ باز و بسته‌کردن برای تک‌تک ردیف‌ها سربار می‌سازد. رمزنگاری در WHERE قابلیت SARGable بودن ستون اصلی را از بین می‌برد؛ برای جست‌وجوی برابر، یک ستون Hash کمکی با سیاست امنیتی جداگانه طراحی کنید. نتیجه را با STATISTICS TIME، Query Store یا ابزار پایش مناسب روی بار مشابه تولید بسنجید و از تعمیم یک آزمایش کوچک خودداری کنید.

9. بهترین روش امنیتی هنگام استفاده از ENCRYPTBYKEY چیست؟

AES_256 را برای طراحی جدید ارزیابی کنید، کلید را فقط در محدوده لازم باز نگه دارید، Ciphertext را در varbinary مناسب ذخیره کنید. علاوه بر آن، اصل کمترین دسترسی، جداسازی محیط‌ها و آزمون Restore باید به‌صورت مستند و دوره‌ای اجرا شود.

10. ENCRYPTBYKEY با کدام نسخه‌های SQL Server سازگار است؟

سطح پشتیبانی دقیق تابع، الگوریتم و محدودیت طول میان نسخه‌ها و سرویس‌های ابری می‌تواند متفاوت باشد. پیش از انتشار، مستند نسخه مقصد و Compatibility Level را بررسی و همه مثال‌ها را در محیط Stage اجرا کنید؛ الگوریتم‌های قدیمی را برای طراحی جدید انتخاب نکنید.

سؤالات مصاحبه SQL Server

1. چرا ENCRYPTBYKEY به‌تنهایی یک راهکار امنیتی کامل نیست؟

زیرا امنیت به مدیریت هویت، مجوز، کلید یا Secret، ثبت رخداد، Backup، چرخه تغییر و مدل تهدید وابسته است و یک تابع فقط یکی از کنترل‌ها را اجرا می‌کند.

2. چگونه نوع داده ورودی و خروجی ENCRYPTBYKEY را کنترل می‌کنید؟

تبدیل را صریح می‌کنم، طول بایتی را با DATALENGTH می‌سنجم، Unicode را از varchar جدا می‌کنم و تست Round-trip یا اعتبارسنجی خودکار می‌نویسم.

3. برای جلوگیری از افت Performance چه می‌کنید؟

ابتدا با Predicate ایندکس‌پذیر دامنه ردیف را کم می‌کنم، عملیات امنیتی را فقط روی داده لازم انجام می‌دهم و هزینه CPU، حافظه و Log را با بار واقعی اندازه می‌گیرم.

4. برنامه بازیابی قابلیت ENCRYPTBYKEY چیست؟

وابستگی‌ها و نسخه‌ها را مستند، Backup امن تهیه، Restore را در محیط جدا تمرین و معیار RTO و RPO را با مالک کسب‌وکار هماهنگ می‌کنم.

5. چه تست‌هایی پیش از Production لازم است؟

تست مقدار صحیح، NULL، طول مرزی، ورودی Unicode، مجوز ناکافی، داده دستکاری‌شده، همزمانی، Failover و بازیابی از Backup را اجرا می‌کنم.

چک‌لیست نهایی

1. Syntax و محدودیت ENCRYPTBYKEY با نسخه مقصد کنترل شده است.
2. نوع و طول ورودی و خروجی صریح است.
3. مجوزها بر اساس اصل کمترین دسترسی تنظیم شده‌اند.
4. Secret یا plaintext در Log و کد منبع وجود ندارد.
5. تست NULL، Unicode، مرز طول و داده دستکاری‌شده اجرا شده است.
6. Benchmark و معیار قابل قبول Performance ثبت شده است.
7. Backup، Restore، چرخش و Runbook رخداد آزمایش شده‌اند.

جمع‌بندی

ENCRYPTBYKEY زمانی ارزش واقعی دارد که در یک معماری قابل اداره به‌کار رود. تعریف درست نوع داده، کنترل خطا، مجوز حداقلی، پایش بدون افشای محتوا و برنامه بازیابی، Query آموزشی را به قابلیت امن Production تبدیل می‌کند.

برای مقایسه این موضوع با شش قابلیت دیگر، به مقاله مادر توابع رمزنگاری SQL Server با مثال‌های کامل بازگردید و پیش از انتخاب نهایی، مدل تهدید و محدودیت نسخه مقصد را مستند کنید.