Devendra-Techs

Qsn) How to Implement Encrypted backup Ans : To create an encrypted backup an encryption algorithm as shown in the following steps: Create a master key.   Create or obtain a certificate protected by the master key. Create a database backup and protect it with the certificate.   The following encryption algorithms can be used:   AES_128 AES_192 AES_256 TRIPLE_DES_3KEY USE   master ; GO CREATE   MASTER   KEY   ENCRYPTION   BY   PASSWORD   = 'XYZ@123; CREATE   CERTIFICATE  BackupCertificate  WITH   SUBJECT   =   'certificte_bkp' ; BACKUP   DATABASE  [trans_DB] TO   DISK   =   N'D:\SQLBackupCRT\trans_DB_Certificate.bak' WITH   ENCRYPTION  ( ALGORITHM   =   AES_256 , SERVER   CERTIFICATE   =  certificte_bkp ); Qsn) How to restore Encrypted backup on database? Ans : In order ...

Qsn)  what is always encrypted? Ans : Always Encrypted (AE) is a new feature in SQL Server 2016 that allows you encrypt both data at rest and data in flight. most important capability is its ability to secure the data with your database outside of the database engine in the client application. AE was designed so that encryption and decryption of the data happens transparently at the driver level, which minimizes the changes that have to be made to existing applications. At a high level the AE architecture works as below : The client application issues a parameterized query. It uses the new  Column Encryption Setting=Enabled;  option in the  connection string . The enhanced ADO.NET driver interrogates the database engine using the [sp_describe_parameter_encryption]   system stored procedure to determine which parameters target encrypted columns. For each parameter that will require encrypting the driver retrieves the encryption al...

Example of Column level Encryption using Certificate USE  [trans_DB] ; GO -- Create database master key CREATE   MASTER   KEY   ENCRYPTION   BY   PASSWORD   =   'GoodLuckWithExam!' -- Create certificate CREATE   CERTIFICATE  Cert_BAN WITH   SUBJECT   =   'Bank Account Number' ; GO -- Create SMK CREATE   SYMMETRIC   KEY  Key_BAN WITH   ALGORITHM   =   AES_256 ENCRYPTION   BY   CERTIFICATE  Cert_BAN ; GO -- Create a column to store encrypted data ALTER   TABLE  Purchasing . Suppliers ADD  EncryptedBankAccountNumber  varbinary ( 128 ); GO -- Open the SMK to encrypt data OPEN   SYMMETRIC   KEY  Key_BAN DECRYPTION   BY   CERTIFICATE  Cert_BAN ; GO -- Encrypt Bank Account Number UPDATE  Purchasing . Suppliers SET  EncryptedBankAccountNumber  =   Encr...