Figure out who is deleting files from windows operating system

File OR folder from windows operating system (client/OS) might miss due to many different reason. A user may logon to system interactively OR remotely then delete the file OR a malicious process may also delete the file. If you are unsure who is deleting files/folder then windows auditing is the best way to figure this out.

Follow this sequence to understand the concepts.

Enable windows auditing from Local Security Policy (run – secpol.msc). If you are doing against multiple servers then edit group policies from domain controller.


You can use following PowerShell to automate this step.

secedit /export /cfg c:\secpol.cfg
(gc C:\secpol.cfg).replace("AuditObjectAccess = 0", "AuditObjectAccess = 3") | Out-File C:\secpol.cfg
secedit /configure /db c:\windows\security\local.sdb /cfg c:\secpol.cfg /areas SECURITYPOLICY
rm -force c:\secpol.cfg -confirm:$false


Update group policy using following command.

gpupdate /force

Select folder that needs to be audited. In my example, I am enabling auditing for Delete action on c:\temp\temp folder


You can use below PowerShell

#Uncomment if foder you intending to be audited isn’t created so far.
#New-Item -type directory -path C:\temp\temp
$Folder= "c:\temp\temp"
$ACL = Get-Acl $Folder
$ar1 = New-object System.Security.AccessControl.FileSystemAuditRule ("EveryOne","Delete","3")
Set-Acl $Folder $ACL


Now if anyone (user/process) delete your file then event will be generated in your event viewer. For e.g. I am deleting File1.txt using windows explorer (right click \delete) second file using PowerShell.

RM -Force C:\Temp\TEMP\File2.txt -Confirm:$false


Open Event viewer and search Security log for Event ID 4656 with “File System” task category and with “Accesses: DELETE” string. “Subject: XXXX” will show you who has deleted a file.

Log Name: Security
Source: Microsoft-Windows-Security-Auditing
Event ID: 4656
Task Category: File System
Level: Information
Keywords: Audit Success
User: N/A
Computer: server.domain.local
A handle to an object was requested.
Security ID: domain\user1
Account Name: user1
Account Domain: domain
Logon ID: 0x98B5C
Object Server: Security
Object Type: File
Object Name: C:\Temp\Temp\File2.txt
Handle ID: 0x774
Resource Attributes: -
Process Information:
Process ID: 0x4c4c
Process Name: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Access Request Information:
Transaction ID: {00000000-0000-0000-0000-000000000000}
Accesses: DELETE
Access Reasons: DELETE: Granted by D:(A;ID;FA;;;BA)
ReadAttributes: Granted by D:(A;ID;FA;;;BA)
Access Mask: 0x10080
Privileges Used for Access Check: -
Restricted SID Count: 0


OR you can below basic PowerShell to query system event viewer log.

Get-EventLog -LogName Security -InstanceId 4656


#new-item, #uncomment

Overview of SQL 2016 ‘Always Encrypted’ Security feature.

In couple of my last blogs, we discussed about various security options with SQL 2016. Now in this blog, we are going to discuss & demonstrate one other important security feature of SQL 2016 that is called as ‘Always Encrypted’.

The legacy features provide encryption either on network layer (SSL) OR data files (MDF) OR Backup files(BAK) and encryption happens for all data OR none. But ‘Always encrypted’ uses client application layer via ADO.Net to encrypt/decrypt column specific data. You may have table where ‘FirstName’ & ‘LastName’ is in plain text but the ‘passport no’ is encrypted. The ‘passport no’ will always be in cipher text during transition via network and will save in encrypted format in data files OR backup files. This feature also available for SQL azure so even Microsoft will see ecrypted data.

Let’s use following steps to demonstrate how Always encrypted can be configured & use in test/production environments of SQL 2016.

---Create AlwaysEncrptedDemo database. 
USE [master]
CREATE DATABASE AlwaysEncryptedDemo
---Create table with 6 columns.   
USE [AlwaysEncryptedDemo]
       EMPID INT Primary key,
       FirstName VARCHAR(100), 
       LastName VARCHAR(100),
       MobileNo BIGINT, 
       PassportNo VARCHAR(100)

-- Insert 5 records. 
USE [AlwaysEncryptedDemo]
       (1, 'Pradeep', 'Papnai', 9595046647, 50000, 'ATNPP12345'), 
       (2, 'Vikram', 'Jadhav', 8088145698, 52000, 'YAG8383P'),
       (3, 'Darshan', 'Bandodkar', 9198234567, 51000, 'DD123453'),
       (4, 'Ajay', 'Jain', 875691232, 55000, 'AJ123JK12'),
       (5, 'Yogesh', 'Ghute', 8787453212, 49000, 'PT9876KA12')

Normal retrieval of rows.

-- Retrieve all records, our next action will encrypt last three columns those are confidential. 
USE [AlwaysEncryptedDemo]
SELECT EMPID, FirstName, LastName,MobileNo, SALARY, PassportNo
FROM EmployeeData


Configure Always Encrypted on columns of a table

Please note these step can also be done using SQL query & Powershell but it’s recommended to do it using wizard for better understanding.

  1. Expand ‘AlwaysEncrptedDemo’ database, Right click on ‘EmployeeData’, select ‘Encrypt columns’
  2. Click NEXT on introduction page.
  3. Check on columns ‘MobileNo, Salary, PassportNo. Select EncrptionType click NEXT.

There are two type of encryption is supported:-

Deterministic – Always encrypts to the same cipher text means decrypted data will always be the same and data can be indexed.

Randomized – It’s consider as more secure as the cipher text will always be different.  The data of this column cannot be indexed.

  1. Key Store provider = Windows Certificate Store, Master key source = current user, click NEXT.
  2. Select ‘Proceed to finish now’, although you can generate PowerShell next time you do it, click NEXT.
  3. Verify the choice you made, click ‘NEXT’.
  4. Encryption might take time due to size of column present in table. In production system, it is usually performed off peak hours.

Here is the slide show for above mentioned steps:-

This slideshow requires JavaScript.

-- Now if you run select query again the results for column MobileNo, Salary, PassportNo will be encrypt
USE [AlwaysEncryptedDemo]
SELECT EMPID, FirstName, LastName, MobileNo, SALARY, PassportNo
FROM EmployeeData


If you wish to see encrypted data using SQL Management studio then the connection string should include following line ‘column encryption setting=enabled’

Right click on query windows, connection-change connection, Additional Connection Parameters


-- Now the columns can be seen in without encryption. 
USE [AlwaysEncryptedDemo]
SELECT EMPID, FirstName, LastName, MobileNo, SALARY, PassportNo
FROM EmployeeData


Well this is not the end of story. If you connect to this SQL server using SSMW from other machine you will notice columns are encrypted even though connection string encrypted is enabled, it’s because you don’t have certificate for encryption.

Always encrypted feature maintain two keys :-

Column Master Key – this is an encryption key that protects the column encryption key(s). You must have at least one master key before encrypting any columns.

Column Encryption Key – this is the encryption key that actually protects that encrypted columns.


You can either use existing keys OR create a new one. In order to copy the certificate, use following steps.

Click on ‘RUN’, Type ‘MMC’ and press the ENTER key. On the ‘File’ menu, click on ‘Add/Remove Snap In’.
Select ‘Certificates’. Select ‘My user account’ and click ‘Finish’ and ‘OK’.
Expand ‘Personal’, then ‘Certificate’, Select ‘Always Encrypted auto certificate’

Certificate 1

You can export this certificate to other machine where ever client OR application connects to SQL servers for encryption purpose. Here is example how connection string can be used with PowerShell.

Use case without encryption connection string

#Change SQL server name
$connectionString = "Data Source=SQL1;Integrated Security=true;Initial Catalog=AlwaysEncryptedDemo;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$query = “SELECT EMPID, FirstName, LastName,MobileNo, SALARY, PassportNo FROM EmployeeData”
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object "System.Data.DataTable"
$table | format-table 


Use case with encryption connection string

$connectionString = "Data Source=SQL1;Integrated Security=true;Initial Catalog=AlwaysEncryptedDemo;column encryption setting=enabled"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$query = “SELECT EMPID, FirstName, LastName,MobileNo, SALARY, PassportNo FROM EmployeeData”
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object "System.Data.DataTable"
$table | format-table 


More Details

#sql, #sql-security, #sql2016