OS : Server Clocks out of sync issue with NTP

Server Clocks out of sync issue with NTP

These type of issues triggers if any host's clock goes out of sync with the time given by NTP.

In resolution, can perform following steps as troubleshooting:

1. Open Command prompt as Administrator
2. Check the current source Server to time sync on host:
     w32tm /query /source

3. Command to check Host Server date and time:

     echo %date%:%time%

4. Command to check DC’s current time settings:
     w32tm /stripchart /computer:<DC_Name> /dataonly

Steps to reconfigure Win time services:
Net stop w32time
W32tm /unregister
W32tm /register
Net start w32time

W32tm /config /syncfromflag:DOMHIER /update
W32tm /resync /nowait
Net stop w32time
Net start w32time

If doesn’t works then try with rediscover:
W32tm /resync /nowait /rediscover
W32tm /query /source
W32tm /monitor

Sync time with specific DC :

Net time \\<DC_NAME> /set /y

Command to check DC’s current time settings:

W32tm /stripchart /computer:<DC_Name> /dataonly

To Sync with DC current system time with an external time server:

W32tm /resync /computer:<DC_Name> /nowait

SQL Server : How to configure Transparent Data Encryption (TDE)?

How to configure Transparent Data Encryption (TDE)?

USE master;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password@123'; -- Create master Key

/*command to view Keys*/
SELECT name KeyName,
  symmetric_key_id KeyID,
  key_length KeyLength,
  algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;



CREATE CERTIFICATE TDECerti WITH SUBJECT = 'TDE certificate'; --Create Certificate

/*command to view certificate */
SELECT name CertName,
  certificate_id CertID,
  pvt_key_encryption_type_desc EncryptType,
  issuer_name Issuer
FROM sys.certificates
WHERE issuer_name = 'TDE certificate';


/* create DEK */

USE SQLDummyDB;
GO
 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE TDECerti;


/*Command to view all DEK */
SELECT DB_NAME(database_id) DbName,
  encryption_state EncryptState,
  key_algorithm KeyAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;



ALTER DATABASE SQLDummyDB SET ENCRYPTION ON; --Enable TDE

Backup the certificate and keys

Use master;
GO
 
BACKUP SERVICE MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\SvcMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123';


BACKUP MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\DbMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123'


BACKUP CERTIFICATE TDECerti 
TO FILE = 'E:\MSSQLServer\Backup\TdeCerti.cer'
WITH PRIVATE KEY(
  FILE = 'E:\MSSQLServer\Backup\TdeCerti.pvk',
  ENCRYPTION BY PASSWORD = 'Password@123');
GO

/* Restore Certificate Key */

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='DPassword@123';
GO


USE MASTER
GO
Create CERTIFICATE TDECerti
FROM FILE = 'D:\Backups\TdeCerti.cer'
WITH PRIVATE KEY (FILE = 'D:\Backups\TDECerti.pvk',
DECRYPTION BY PASSWORD = 'Password@123' );
GO

Advantages
1. The performance impact of TDE is minor.
2. Performs real-time I/O encryption and decryption of the data and log files.
3. No application code changes are required.
4. No architectural changes required.
5. Encrypts the Entire data in Database.

Disadvantages
1. Not granular – Cannot just encrypt specific tables/columns
2. Not good for high CPU bottleneck servers
3. No protection for data in memory
4. Not protected through communication/networks