Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 23 October 2017

How to enable both failed and Successful login in SQL Server auditing

How to enable both failed and Successful login in SQL Server auditing

  • It requires RESTART of SQL Service
  • By Default Failed Logins Only enabled in SQL SERVER



By Default Failed Logins Only enabled in SQL SERVER But we need enable both failed and Successful logins  but it requires restart



Here Now I am going to choose both failed and successful login and enable C2 auditing


After click ok it asks for restart to take effect of this features


Now am going to restart of SQL Service


after Restart of SQL Service  you can see take effect in Error log as both failed and successful logins


you can  see error log file will grow based on the login flow ( Error log file count 6 )

TSQL Query for cycle error log and monitor Error log size and location

you can cycle the error log so it will create a new one with the following command

Exec Sp_Cycle_Errorlog
 
--Monitor Error log size and location
 
Declare @ServerErrorLog TABLE
(
Archive INT,
dt DATETIME,
FileSize INT
)
 
INSERT INTO @ServerErrorLog
Exec xp_enumerrorlogs
SELECT @@servername as ServerName,Archive, FileSize/1024/1024 AS[Error Log Filef size (MB)],SUM(FileSize/1024/1024)[Size in MB]
FROM @ServerErrorLog
Group by Archive,FileSize/1024/1024
order by Archive,FileSize/1024/1024
COMPUTE SUM(FileSize/1024/1024)
 
 
Exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file',NULL, NULL, N'asc'


Happy Blogging!!!!!!!!!!!!!!!!!!!!!!



Wednesday, 6 September 2017

Step by Step Configure TDE Transparent Data Encryption Enabled Database on Log shipping

Step by Step Configure TDE Transparent Data Encryption  Enabled Database on Log shipping 

SQL Server Transparent Data Encryption

SQL Server Transparent Data Encryption (TDE), is an encryption mechanism, available in the Enterprise edition of SQL Server 2008 and later. It is used to protect the database physical files, rather than protecting the data itself. This includes the database data, log, backup and snapshot files as mentioned previously. Using the SQL Server Transparent Data Encryption, unauthorized users will not be able to access the database’s data, by preventing them from attaching or restoring the database files to another SQL instance. Also it will be protected inside the backup media if it is stolen.
The SQL TDE encryption, as the name indicates, transparent to the applications that connects to the database, as the encryption is at the page level. The data is encrypted while it is at rest on the disk and decrypted during the read process on its way to the memory. An authorized user will be able to access the encrypted database content, without being aware that the database is encrypted or any action or extra code required from his side.
more info Ref:
https://www.sqlshack.com/how-to-configure-sql-server-mirroring-on-a-tde-encrypted-database/

Demo:
Primary: User-PC\MSSQL2012Source

Secondary:1 User-PC\MSSQL2012DESTSQL

Secondary 2: User-PC\SQL2012CONVTEST

Log shipping Database: Testdbgrowth



Primary: User-PC\MSSQL2012Source



Secondary:1 User-PC\MSSQL2012DESTSQL


Here you can see the table info @ Primary

Create and Backup Master KEY @ Primary
We should need to create Master Key if not exists and Backup Master Key

See the Master Key info 
Create Database Encryption Key and Enable Encryption of Database



See the Certificate here

Backup Full Database and Transaction Log backup @ primary

Destination (Secondary)
see the master key info @ secondary


Restore Master Key of Source(Primary) to Destination (Secondary) and Open Master Key @ Secondary and Create Certificate which create on Primary

We should need to restore Master key which we backedup on primary and Open Master Key and 

Create certificate which we created and Backed up on Primary 

Note: 

Be aware  difference below to avoid confusion while create certificate

Create certificate  is using DECRYPTION BY PASSWORD 
Backup Certificate is using ENCRYPTION BY PASSWORD


RESTORE Database Backup @ Secondary:

 give overwrite option


We should need to give new path while restore on destination and

We should need to give WITH restoring option (WITH NORECOVERY) To allow more file like transaction log backup to restore again

Now we are going to restore Transaction log backup


We should need to mention as WITH Standby option to read users

Transaction Log backup Restored Successfully

Configure Log shipping @ Primary

Click Enable this as primary option and click Backup settings and give path(network and local)

Now give Add button to add secondary

connect option connect secondary and select third option as we already backup/restore database from primary to secondary

Copy  files to give Copy location (network folder path) 

Restore option to give Standby mode and give 1 minute delay to ensure proper process on copy/restore

Now Log shipping configured successfully

Backup job info @ Primary

Copy and Restore job info @ Secondary

you can see table reflected on secondary
+

Run backup job on primary


Run copy job on secondary

Run restore job @ secondary

To see the reports on log shipping

see the report shows copy and restore file same

we can query the Backup file info @ primary as see below
To see Backup file info @ primary
SELECT distinct 
    s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
    s.backup_finish_date DESC;

Compare of both as see below
Script 
To Create Master key @Primary
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
To Open Master key @Primary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
USE master
GO
To see Master key @Primary
use master
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date  
from sys.symmetric_keys
To Backup Master key @Primary
use master
BACKUP MASTER KEY TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Create Certificate  @Primary
use master
CREATE CERTIFICATE ATTL_Logshipping_TDE WITH SUBJECT = 'ATTL_Logshipping_TDE'
To Backup Certificate @Primary
use master
BACKUP CERTIFICATE ATTL_Logshipping_TDE 
TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY 
(
    FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
    ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO
To see Master key info  @Primary
USE master
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date  
FROM sys.certificates where name = 'ATTL_Logshipping_TDE'
GO
To Create Database Encryption @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ATTL_Logshipping_TDE
GO
To Enable Encryption on Database @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
ALTER DATABASE [ALL_Test_TDE_LogShipping]
SET ENCRYPTION ON
GO
To see Encryption status @Primary
use master
SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END
FROM sys.dm_database_encryption_keys
To Backup Full Backup  @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP DATABASE [ALL_Test_TDE_LogShipping] TO  DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_bk.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'ALL_Test_TDE_LogShipping-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
To Backup Log Backup  @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP LOG [ALL_Test_TDE_LogShipping] TO  DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_TLBK.trn' 
WITH NOFORMAT, NOINIT,  NAME = N'ALL_Test_TDE_LogShipping-Log Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Destination:
To see Symmetric Key file info @Secondary
USE master
GO
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date  
from sys.symmetric_keys
To Restore Master key @Secondary
USE master
RESTORE MASTER KEY
    FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
    DECRYPTION BY PASSWORD = 'Ws@2012'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Open Master key @Secondary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
To Create Certificate key @Secondary which we created and backedup on Primary
USE master
CREATE CERTIFICATE ATTL_Logshipping_TDE
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'    
WITH PRIVATE KEY (FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',   
DECRYPTION BY PASSWORD = 'Ws@2012');
GO
To see Backup file info @ primary
USE master
SELECT distinct 
    s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
    s.backup_finish_date DESC;