Posts

Image
  Transparent Data Encryption -SQL Server Overview of steps  –   1.Create Master Key on Master Database 2.Create Certificate protected by Master Key on Master Database 3.Create Database Encryption Key using Certificate on User Database that must be encrypted 4.Change the Database Option setting to Encryption 5.Backup Certificate and Private Key at safe place   Note - 1.By default certificate will expire in one year if we did not mention any date during certificate creation 2.In case of certificate expiry sql server will not stop or wait to renewal of certificate it will encrypt even certificate is expired  1.Create Master Key on Master Database Use Master Go Create Master Key Encryption By Password ='Strong@password' Go 2.Create Certificate protected by Master Key on Master Database use master Go create Certificate TDE_CERT WITH SUBJECT ='TDE Certificate' Go Select * from sys.certificates (check if certificate is created) 3.Create Database En...
Post a Comment
Read more

CDC Monitoring

Change data capture(CDC) is feature where we can track the changes on table if we enable CDC on that table and CDC should be enabled from DB level first and we can enable it for tables  As i see so many articles about CDC in the internet i don't  want to go through CDC this topic will discuss only about system tables in CDC and how to do basic monitoring , tracking the errors for CDC enabled tables  To Monitor the empty scans we can use this select statement and empty_scan_count column in sys.dm_cdc_log_scan_sessions is set to 1 whenever log scan result is empty and it will increment when ever it got empty result from scan SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0 And for latency between actuall table and CDC we can use below select SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 We can store data from the views     sys.dm_cdc_log_scan_sessions view and the sys.dm_cdc_er...
Post a Comment
Read more

Dynamic Data Masking

Image
Dynamic Data Masking can be used to mask sensitive data for testing and different purpose, It doesn't encrypt the data, It can be controlled from the database level ,Basically DBA can implement the solution with out much complexity and accessed easily by users who has access to see the data We have different types of data masking techniques used in DDM 1.Deafult Masking 2.Random Masking 3.Partial Masking 1.Default Masking – We can include default function while creating table   or we can alter table to enable default data masking ALTER TABLE [Person] . [Address] ALTER COLUMN PostalCode nvarchar ( 15 ) MASKED WITH (FUNCTION = 'default()'); Creating user to test USE Adventureworks;   GO   CREATE USER user1 WITHOUT LOGIN;   GRANT SELECT ON OBJECT:: Person].[Address] TO test;     GO EXECUTE AS USER = 'test' ;          select top 10 * from   [Perso...
1 comment
Read more

How to read MSDTC Trace logs

I have recently come across situation where i need to read the logs of MSDTC  and it is not straight forward approach after researching little bit i am able to figure out the method ,Please find detail information on how to read MSDTC logs and let me know if you have questions in comments Steps to read MSDTC logs tracefmt.exe  will play important role reading MSDTC logs 1.Please check if you have tracefmt.exe in  C:\WINDOWS\system32\MsDtc location and  copy to C:\WINDOWS\system32\MsDtc\trace ,  if you don’t have tracefmt.exe in the location please download from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11800 and need to install complete package from below link (I have attached tacefmt.exe in zip folder) 2.Once you install go to C:\WinDDK\7600.16385.1\tools\tracing\amd64 and you will find tracefmt.exe  and copy tracefmt.exe   to C:\WINDOWS\system32\MsDtc\Trace where you have trace files 3.If you al...
Post a Comment
Read more

Azure Key vault and encryption in MSSQL

Image
        Ø   Azure key vault is Microsoft centralized solution enables you to store sensitive information (passwords, keys and certificates etc) All this information is stored in HSM (Hardware security modules) in datacentre which is ( HSM) - FIPS 140 -2 Level 2 certified   Ø   It is basically a container where you store all secretes and no one can see this until they have authorized access to it Example -we have MSSQL Admin password which should be provided while installing MSSQL and if   DBA left the company then one knows the password and to manage it   if use key vault we can store admin password in key vault and use it as admin password for all the installations and even this password is Ø     A vault owner will be allocated full rights on the vault he can modify delete and audit the vault and see who has accessed key, certificates and passwor...
Post a Comment
Read more