Sunday, 30 November 2014

Find Account Lockout Source for Logon Type 3

Finding root cause of the frequent Bad Password Attempts of Active Directory User is a cumbersome task now a days. Unlike other normal logon types (Logon Type 2 -Interactive Logon and Logon Type 10 -Remote Logon), we can’t easily track the failure reason for the Logon Type 3, because most of the time, the failures surrounded with this logon type are triggered or initiated by either cached credentials or through third party tools. In this article, I am going to explain about how to Find Account Lockout Source and Login Failure reason for Logon Type 3.

How to Find Logon Failure Reason for Logon Type 3

This logon type occurs due to accessing a computer from elsewhere on the network (i.e Remote Desktop sharing tool), or accessing other resources like Network Share from elsewhere on the network by passing credentials. One of the most common sources of logon events with Logon type 3 is connections to shared folders or printers. But also other over-the-network logons are classed as logon type 3 as well as most logons to IIS except Basic authentication.

Consider following scenario:
DC1         - Active Directory Domain Controller 
Morgan-PC    - End user desktop computer
Now, when a user or any other applications tries to access resources like Network Share from Morgan-PC with wrong credentials, we will get the logon failure event 4625 with logon type 3 in DC1 and it will points the machine Morgan-PC as Source Machine.

 Event 4625 for Logon Type 3:
Computer:      DC1.TestDomain.Com
Description:  An account failed to log on.

Logon Type:   3

Account For Which Logon Failed:
  Account Name:  Morgan
  Account Domain:  TESTDOMAIN

Failure Information:
  Failure Reason:  Unknown user name or bad password.
  Status:   0xc000006d
  Sub Status:  0xc000006a

Network Information:
  Workstation Name: Morgan-PC
  Source Network Address: 212.158.1.110
  Source Port:  51283

Consider another scenario:
DC1         - Active Directory Domain Controller 
Morgan-PC    - End user desktop computer
Now, when a user tries to login into DC1 from Morgan-PC via Remote Desktop sharing tool with bad password, we will get the logon failure event 4625 with logon type 3 in DC1 and it will points the machine Morgan-PC as Source Machine.

Thursday, 27 November 2014

whenChanged and modifyTimeStamp - Active Directory

Description:

In this article, I am going to explain about the Active Directory attributes whenChanged and modifyTimeStamp and how these attributes are updated in all Domain Controllers despite being a Non-Replicable attribute.

Summary:

  • WhenChanged is a date time attribute which holds an AD object's latest changed time and it is Non-Replicable attribute. 
  • ModifyTimeStamp is a computed attribute and it is also Non-Replicable attribute. 
  • Both are Non-Replicable attributes but that doesn't mean every domain controller holds very different value like lastLogon attrbute. Yes, both are non-replicable attributes but it will be updated in all DCs for every AD change.

How whenChanged attribute value get updated in all DCs?

Before explain this, I would like to explain what is Active Directory Replication?. In Active Directory, objects are distributed among all domain controllers in a forest, and all domain controllers can be updated directly. Active Directory replication is the process by which the changes that originate on one domain controller are automatically transferred to other domain controllers that store the same data.

So, AD replication ensures same data in all DCs by transferring every change automatically to other DC,

Consider this scenario:

If you change the value for description attribute of any object as "test", it will be updated in all other DC but here you have not changed either whenChanged or modifyTimeStamp then how it gets updated in your own DC?.
You know whenChanged is system attribute and it will be automatically updated for every change. So the description attribute change indirectly force the whenChanged attribute to set latest time. Like this, the replication change on every DC will automatically force the whenChanged attribute to set the particular DC's latest time. So, the value of  whenChanged attribute may or may not be identical in all DCs depends upon the replication interval.

For more clarity, consider this scenario:

DC1-  AD Domain Controller 1
DC2-  AD Domain Controller 2
U1-     an AD user

Replication Interval: 15 secs

If you change the user U1's description value in DC1 at 10:10:00 AM, the whenChanged attribute gets updated as 10:10:00 AM in DC1. Since the replication interval is 15 secs, the description value will be replicated into DC2 at 10:10:15 AM and it automatically updates the whenChanged attribute as 10:10:15 AM in DC2. So depends upon the replication interval the value of whenChanged attribute may or may not be identical in all domain controllers but it holds the updated value.

Wednesday, 26 November 2014

Reset Bulk AD Users Password from CSV with Powershell

In this article, I am going write Powershell script samples to Reset Bulk AD User's Password from CSV file and Reset set of Active Directory User's Password. You can reset an Active Directory account password using the Powershell cmdlet Set-ADAccountPassword.

Reset Password Syntax:
Set-ADAccountPassword [-Identity <adaccount>] [-NewPassword <SecurePwd>] -Reset
- The Identity parameter specifies the Active Directory user account which you want to reset password.

Reset Bulk AD Users Password from CSV

   1. Consider the CSV file ADUsers.csv (Ex file: Download ADUsers.csv) which contains set of Active Directory users to reset password with the attribute samAccountName.

Reset Bulk AD Users Password from CSV using Powershell script

   2. Copy the below Powershell script and paste in Notepad file.
   3. Change the ADUsers.csv file path with your own csv file path.
   4. SaveAs the Notepad file with the extension .ps1 like Reset-Bulk-AD-Users-Pwd-FromCSV.ps1

Powershell script as file: Download Reset-Bulk-AD-Users-Pwd-FromCSV.ps1
Import-Module ActiveDirectory
# Set the new password
$newPassword = ConvertTo-SecureString -AsPlainText "MyP@ssw0rd" -Force 
# Import users from CSV
Import-Csv "C:\Scripts\ADUsers.csv" | ForEach-Object {
 $samAccountName = $_."samAccountName" 

# Reset user password.
Set-ADAccountPassword -Identity $samAccountName -NewPassword $newPassword -Reset

# Force user to reset password at next logon.
# Remove this line if not needed for you
Set-AdUser -Identity $samAccountName -ChangePasswordAtLogon $true
Write-Host " AD Password has been reset for: "$samAccountName
}
   5. Now run the file Reset-Bulk-AD-Users-Pwd-FromCSV.ps1 from Powershell command to reset bulk AD user's password from CSV file.
PS C:\Scripts>  .\Reset-Bulk-AD-Users-Pwd-FromCSV.ps1
Modify Bulk AD Users Password from CSV using Powershell script


Reset set of Active Directory User's Password

The below powershell command reset all the user's password from TestOU because I have used this LDAP filter '(name=*)'. You can use your own LDAPfilter and SearchBase to select set of users to reset password.
Import-Module ActiveDirectory
$newPassword = ConvertTo-SecureString -AsPlainText "MyP@ssw0rd" -Force 
Get-ADUser -LDAPfilter '(name=*)'`
  -SearchBase "OU=TestOU,DC=TestDomain,DC=local" | 
Set-ADAccountPassword  -NewPassword $newPassword -Reset

Tuesday, 25 November 2014

Read Remote Registry Value in C# using WMI and Remote Registry

Description:

In this article, I am going write C# code to Read Registry Value from Remote Machine using WMI Service and Remote Registry service.

Summary:

Read Registry Value from Remote Machine via WMI Service using C#

You can read Registry Value and Key in C# by using WMI class StdRegProv instead of using .NET class RegistryKey. You can use below C# function to get list of installed programs from remote machine by reading Registry Value via WMI in C#.
private static List<string> ReadRemoteRegistryusingWMI(string machineName)
    {
        List<string> programs = new List<string>();

        ConnectionOptions connectionOptions = new ConnectionOptions();
        //connectionOptions.Username = @"Domain\Administrator";
        //connectionOptions.Password = "password";
        //connectionOptions.Impersonation = ImpersonationLevel.Impersonate;

        ManagementScope scope = new ManagementScope("\\\\" + machineName + "\\root\\CIMV2", connectionOptions);
        scope.Connect();

        string softwareRegLoc = @"Software\Microsoft\Windows\CurrentVersion\Uninstall";

        ManagementClass registry = new ManagementClass(scope, new ManagementPath("StdRegProv"), null);
        ManagementBaseObject inParams = registry.GetMethodParameters("EnumKey");
        inParams["hDefKey"] = 0x80000002;//HKEY_LOCAL_MACHINE
        inParams["sSubKeyName"] = softwareRegLoc;

        // Read Registry Key Names 
        ManagementBaseObject outParams = registry.InvokeMethod("EnumKey", inParams, null);
        string[] programGuids = outParams["sNames"] as string[];

        foreach (string subKeyName in programGuids)
        {
            inParams = registry.GetMethodParameters("GetStringValue");
            inParams["hDefKey"] = 0x80000002;//HKEY_LOCAL_MACHINE
            inParams["sSubKeyName"] = softwareRegLoc + @"\" + subKeyName;
            inParams["sValueName"] = "DisplayName";
            // Read Registry Value 
            outParams = registry.InvokeMethod("GetStringValue", inParams, null);

            if (outParams.Properties["sValue"].Value != null)
            {
                string softwareName = outParams.Properties["sValue"].Value.ToString();
                programs.Add(softwareName);
            }
        }

        return programs;
    }

Read Registry Value from Remote Computer in C# using Remote Registry Service

Noramly, you can Read Registry Value in C# by using built-in C# function RegistryKey, But when you read registry from remote machine by using this class, you need to start the Windows Service "Remote Regitry" in target machine(remote).

Read Remote Registry Value in C# using WMI and Remote Registry

By default, Remote Registry service will be enabled and started automatically in Server editions (i.e Windows Server 2008 R2) but in Client editions, you have to enable manually.

You can read Registry Value and Key from Remote Machine by using following function.
private static List<string> ReadRegistryFromRemoteMachine(string machineName)
    {
        List<string> programs = new List<string>();

        string softwareRegLoc = @"Software\Microsoft\Windows\CurrentVersion\Uninstall";
        // Open Remote Machine Registry Key 
        RegistryKey remoteKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, machineName);

        RegistryKey regKey = remoteKey.OpenSubKey(softwareRegLoc);

        foreach (string subKeyName in regKey.GetSubKeyNames())
        {
            // Open Registry Sub Key
            RegistryKey subKey = regKey.OpenSubKey(subKeyName);

            // Read Value from Registry Sub Key
            string softwareName = (string)subKey.GetValue("DisplayName");

            if (!string.IsNullOrEmpty(softwareName))
            {
                programs.Add(softwareName);
            }
        }

        return programs;
    }

Sunday, 23 November 2014

Convert Byte Array to String and Vice Versa in C#

In this article, I am going to write C# code to convert Byte Array to String and String to Byte Array by using BitConverter and Encoding classes.

Convert Byte Array to String and Vice Versa using BitConverter

You can convert byte array to string using BitConverter class. It returns a string of hexadecimal pairs separated by hyphens(-), where each pair represents the corresponding element in value; for example, "6C-2F-3C-00".

Convert Byte Array to String:

private static void ConvertByteArrayToString()
{
    byte[] byteArray = new byte[] { 0x48, 0x65, 0x6C, 0x6C, 0x6F };

    string byteString = System.BitConverter.ToString(byteArray);

   //Result: 48-65-6C-6C-6F
}

Convert String to Byte Array:

There is no built-in function a convert string to byte array that was converted from byte array by BitConverter. But you can use below method to convert that string into Byte[].

private static void ConvertStringToByteArray()
{
    string byteString = "48-65-6C-6C-6F";
    string[] strArray = byteString.Split('-');
    byte[] byteArray = new byte[strArray.Length];
    for (int i = 0; i < strArray.Length; i++)
    {
        byteArray[i] = Convert.ToByte(strArray[i], 16);
    }
}

Convert Byte Array to String and Vice Versa using Encoding in C#

You can convert byte array to string and string to byte[] using Encoding class. It returns a actual string value from byte array. Use either Encoding.UTF8 or Encoding.ASCII.

Convert Byte Array to String:

private static void ConvertByteArrayToString()
{
    byte[] byteArray = new byte[] { 0x48, 0x65, 0x6C, 0x6C, 0x6F };

    string byteString = Encoding.UTF8.GetString(byteArray);

   //Result: Hello
}

Convert String to Byte Array:

private static void ConvertStringToByteArray()
{
    string byteString = "Hello";

    byte[] byteArray = Encoding.UTF8.GetBytes(byteString);
}

Saturday, 22 November 2014

Read Password from C# Console Application

In this article, I am going to write C# Console Application (command prompt) to read password from command-line screen. It also supports to remove character from entered password string while enter Backspace key.
static void Main(string[] args)
{
    Console.WriteLine("Password Masking Console Application");
    Console.WriteLine("------------------------------------");
    Console.Write("Enter username: ");
    string username = Console.ReadLine();
    string password = "";
    Console.Write("Enter password: ");
    ConsoleKeyInfo keyInfo;

    do
    {
        keyInfo = Console.ReadKey(true);
        // Skip if Backspace or Enter is Pressed
        if (keyInfo.Key != ConsoleKey.Backspace && keyInfo.Key != ConsoleKey.Enter)
        {
            password += keyInfo.KeyChar;
            Console.Write("*");
        }
        else
        {
            if (keyInfo.Key == ConsoleKey.Backspace && password.Length > 0)
            {
                // Remove last charcter if Backspace is Pressed
                password = password.Substring(0, (password.Length - 1));
                Console.Write("\b \b");
            }
        }
    }
    // Stops Getting Password Once Enter is Pressed
    while (keyInfo.Key != ConsoleKey.Enter);
    Console.WriteLine();
    Console.WriteLine("---------------------------");
    Console.WriteLine("Welcome " + username+",");
    Console.WriteLine("Your Password is : " + password);
}

Output: Password masking console application

Get Password from Console Application  -command-line

Friday, 21 November 2014

Schedule Task to Export AD Users to CSV using Powershell script

We can export Active Directory users to CSV file using Powershell cmdlets Get-ADUser and Export-CSV. We have two type of filters, LDAP Filter and SQL Like Filter to filter and select only required users. In this article, I am going to write Powershell script to Export AD Users to CSV file and Steps to create a Schedule Task to Export AD Users to CSV using Powershell script.

Summary:

Powershell Scripts to Export AD Users to CSV

We can use Active Directory attribute name (property name) to filter users in Get-ADUser cmdlet. The following command export the selected properties of all Active Directory users to CSV file. You can add more attributes as per your wish, refer this article:Get-ADUser Default and Extended Properties to know more supported AD attributes.

Export All Users:

Import-Module ActiveDirectory
Get-ADUser -Filter * -Properties * |
 Select -Property Name,Mail,Department | 
 Export-CSV C:\\AllADUsers.csv -NoTypeInformation -Encoding UTF8

Export AD Users by Filter:

You can export users only who belongs to Admin Department by applying filter with the AD attribute 'department'.
Import-Module ActiveDirectory
Get-ADUser -Filter 'Department -like "*Admin*"' -Properties * |
  Select -Property Name,City,Mail,Department,DistinguishedName | 
  Export-CSV C:\\AdminUsers.csv -NoTypeInformation -Encoding UTF8

Export AD Users by LDAP Filter:

Instead of SQL Like Filter, you can also use LDAP filter to select only required users. Refer this article (AD LDAP Filter Examples) to get more LDAP filter examples.
Import-Module ActiveDirectory
Get-ADUser -LDAPFilter '(Department=*Admin*)' -Properties * |
  Select -Property Name,City,Mail,Department,DistinguishedName | 
  Export-CSV C:\\AdminUsers.csv -NoTypeInformation -Encoding UTF8

Export AD Users from specific OU:

We can set target OU scope by using the parameter SearchBase. This following command select all the AD users from the Organization Unit 'estOU' and export it to CSV file.
Import-Module ActiveDirectory
Get-ADUser -Filter * -SearchBase "OU=TestOU,DC=TestDomain,DC=Local" -Properties * |
 Select -Property Name,Mail,Department | 
 Export-CSV C:\\TestOUUsers.csv -NoTypeInformation -Encoding UTF8

Steps to Schedule Powershell script to Export AD Users to CSV

You can create Scheduled Task to run Powershell script using Windows Task Scheduler. Follow the below steps to create daily schedule to export all Admin users from Active Directory to CSV file.

1. Copy the below Powershell script and paste in Notepad file.
2. SaveAs the Notepad file with the extension .ps1 like Export-Admin-Users.ps1
Import-Module ActiveDirectory
Get-ADUser -LDAPFilter '(Department=*Admin*)' -Properties * |
  Select -Property Name,City,Mail,Department,DistinguishedName | 
  Export-CSV C:\\AdminUsers.csv -NoTypeInformation -Encoding UTF8
Note: You can use your own script file if you already have one.

3. Open the Windows Task Scheduler : Go to > Start > Administrative Tools and select Task Scheduler.

Schedule Task to Export AD Users to CSV using Powershell script

4. In the Task Scheduler, select the Create Task... option under the Actions menu.

Schedule Task to Export AD Users to CSV using Powershell script

5. Enter a name for the task, and give it a description (the description is optional and not required).
6. Under Security options section, you can specify different user account that the task should be run under and select the option 'Run whether user logged on or not' so that the task will run even if the user is not logged.

Schedule Task to Export AD Users to CSV using Powershell script

7. Then, select the Triggers tab, and click New to add a new trigger for the scheduled task. This new task should use the On a schedule option. The start date can be set to a desired time, and the frequency and duration of the task can be set based on your specific needs and click OK. Here, I have configured Daily schedule to Export AD Admin users to CSV file on daily basis.

Export AD Users to CSV via Schedule Task using Powershell script

8. Then, go to the Actions tab and click New to set the action for this task to run. Set the Action to Start a program.
9. In the Program/script box enter Powershell
10. In the Add arguments (optional) box enter the complete script file path. For example, if your Powershell Script is named "Export-Admin-Users.ps1" and placed under "C:\Scripts". then you have to enter path like: "C:\Scripts\Export-Admin-Users.ps1"

Export Active Directory Users to CSV via Schedule Task using Powershell script

11. That's all, we completed the new schedule task configuration and click OK to complete process.

Export Active Directory Users to CSV via Task Scheduler using Powershell script

12. Under Task Scheduler Library, You can check daily task run status of your task and you can also run the task whenever you want by right-click on the task and click Run.

Export Active Directory Users to CSV via Task Scheduler using Powershell script

Tuesday, 18 November 2014

Reset AD User Password using Powershell script

In this article, I am going write Powershell script samples to Reset AD user Password and Reset Bulk AD user's Password from CSV file. You can change and reset an Active Directory account password using the Powershell cmdlet Set-ADAccountPassword.

Change Password Syntax:
Set-ADAccountPassword [-Identity <adaccount>] [-NewPassword <SecurePwd>] [-OldPassword <SecurePwd>]
Reset Password Syntax:
Set-ADAccountPassword [-Identity <adaccount>] [-NewPassword <SecurePwd>] -Reset
- The Identity parameter specifies the Active Directory user account which you want to reset password.

Summary:


Reset AD User Password using Powershell cmdlet

You can reset a single Active Directory user password using below powershel command by passing user's samAccountName, you can also use user's GUID or DN instead of samAccountName.
Import-Module ActiveDirectory
# Set the new password
$newPassword = ConvertTo-SecureString -AsPlainText "MyP@ssw0rd" -Force 
Set-ADAccountPassword -Identity Smith -NewPassword $newPassword -Reset

Reset set of Active Directory User's Password

The below powershell command reset all the user's password from TestOU because I have used this LDAP filter '(name=*)'. You can use your own LDAPfilter and SearchBase to select set of users to reset password.
Import-Module ActiveDirectory
$newPassword = ConvertTo-SecureString -AsPlainText “MyP@ssw0rd” -Force 
Get-ADUser -LDAPfilter '(name=*)'`
  -SearchBase "OU=TestOU,DC=TestDomain,DC=local" | 
Set-ADAccountPassword  -NewPassword $newPassword -Reset

Bulk AD Users Password Reset from CSV

   1. Consider the CSV file ADUsers.csv (Ex file: Download ADUsers.csv) which contains set of Active Directory users to reset password with the attribute samAccountName.

Reset Bulk AD Users Password from CSV using Powershell script

   2. Copy the below Powershell script and paste in Notepad file.
   3. Change the ADUsers.csv file path with your own csv file path.
   4. SaveAs the Notepad file with the extension .ps1 like Reset-Bulk-AD-Users-Pwd-FromCSV.ps1

Powershell script as file: Download Reset-Bulk-AD-Users-Pwd-FromCSV.ps1
Import-Module ActiveDirectory
# Set the new password
$newPassword = ConvertTo-SecureString -AsPlainText "MyP@ssw0rd" -Force 
# Import users from CSV
Import-Csv "C:\Scripts\ADUsers.csv" | ForEach-Object {
 $samAccountName = $_."samAccountName" 

# Reset user password.
Set-ADAccountPassword -Identity $samAccountName -NewPassword $newPassword -Reset

# Force user to reset password at next logon.
# Remove this line if not needed for you
Set-AdUser -Identity $samAccountName -ChangePasswordAtLogon $true
Write-Host " AD Password has been reset for: "$samAccountName
}
   5. Now run the file Reset-Bulk-AD-Users-Pwd-FromCSV.ps1 from Powershell command to reset bulk AD user's password from CSV file.
PS C:\Scripts>  .\Reset-Bulk-AD-Users-Pwd-FromCSV.ps1
Modify Bulk AD Users Password from CSV using Powershell script

Thanks,
Morgan

Monday, 17 November 2014

Event 4769 - A Kerberos service ticket was requested.

Event ID 4769 will be logged whenever a service ticket (token to access resource) was requested by user or computer. It will be logged in Domain Controller for both Success and Failure instances. In this article, I am going to explain about how to enable Event 4769 through Default Domain Controller Policy GPO and Auditpol.exe, and how to disable Event ID 4769.

Summary:


Event ID 4769 Source:

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          11/17/2014 4:48:29 PM
Event ID:      4769
Task Category: Kerberos Service Ticket Operations
Keywords:      Audit Success
Computer:      MTSDC1.TestDomain.local
Description:
A Kerberos service ticket was requested.

Account Information:
 Account Name:  Morgan$@TESTDOMAIN.LOCAL
 Account Domain:  TESTDOMAIN.LOCAL
 Logon GUID:  {77a5de7f-8fc6-0cb6-f468-ab81a180ff0e}

Service Information:
 Service Name:  MTSDC1$
 Service ID:  TESTDOMAIN\MTSDC1$

Network Information:
 Client Address:  ::1
 Client Port:  0

Additional Information:
 Ticket Options:  0x40810000
 Ticket Encryption Type: 0x12
 Failure Code:  0x0
 Transited Services: -

This event is generated every time access is requested to a resource such as a computer or a Windows service.  The service name indicates the resource to which access was requested.

This event can be correlated with Windows logon events by comparing the Logon GUID fields in each event.  The logon event occurs on the machine that was accessed, which is often a different machine than the domain controller which issued the service ticket.

Ticket options, encryption types, and failure codes are defined in RFC 4120.

Enable Event 4769 via Group Policy

    To enable event id 4769  in every Domain Controller, We need to configure audit settings in Default Domain Controllers Policy, or you can create new GPO and links it to the Domain Controllers OU via GPMC console, or else you can configure the corresponding policies on Local Security Policy of each and every Domain Controller..

Follow the below steps to enable event 4769 via Default Domain Controllers Policy.

    1. Press the key 'Window' + 'R'
    2. Type the command gpmc.msc, and click OK.
         Note: Skip the above steps by clicking Start -->Administrative Tools -->Group Policy                            Management.
    3. Expand the domain node and Domain Controllers OU,  right-click on the Default Domain Controllers Policy, then click Edit. - refer the below image.

Steps to enable Event 4769 via GPO

    4. Expand Computer Configuration node and Security Settings and navigate to the node Audit Policy (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon).

Steps to enable Event 4769 via GPO

    5. In right-side pane, double-click on Audit account logon events and set Success and Failure setting to enable kerberos logon event 4769.
Steps to enable Event 4769 via GPO

   Note: In Windows 2008 R2 and later versions, you can also control this event by subcategory-level setting via Advanced Audit Policy Configuration.

    Expand Computer Configuration and Security Settings and navigate to the node Account Logon (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon) and set the setting Audit Kerberos Service Ticket Operations as Success and Failure

Steps to enable Event ID 4769 via GPO

    6. Run the command gpupdate /force from command prompt to update Group Policy settings.


Enable Event 4769 via Auditpol

Auditpol.exe is the command line utility tool to change Audit Security settings as category and sub-category level. It is available by default Windows 2008 R2 and later versions/Windows 7 and later versions. By using Auditpol, we can get/set Audit Security settings per user level and computer level.

Note: You should run Auditpol command with elevated privilege (Run As Administrator);

You can enable Event 4769through Kerberos Service Ticket Operations subcategory by using the following command

Success Audit:
auditpol /set /subcategory:"Kerberos Service Ticket Operations" /success:enable
Failure Audit:
auditpol /set /subcategory:"Kerberos Service Ticket Operations" /Failure:enable
To update or refresh GPO settings, run the command gpupdate/force

Disable/Stop Event ID 4769

You can disable or stop the audit Event ID 4769 by removing success and failure audit of Kerberos Service Ticket Operations subcategory by using the following command.
auditpol /set /subcategory:"Kerberos Service Ticket Operations"
 /success:disable
You can also stop this event by removing the success and failure setting from the Default Domain Controller Policy's category level setting path (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon->Audit account logon events

 or by subcategory level setting (Computer Configuration->Policies->Windows Settings->Security Settings-> Advanced Audit Policy Configuration -> Audit Policies->Account Logon->Audit Kerberos Service Ticket Operations)


Thanks,
Morgan
Software Developer

Unlock Bulk AD Users From CSV using Powershell script

In this article, I am going write Powershell script samples to Unlock set of AD Users from specific OU and Unlock Bulk AD users from CSV file using Powershell script.

You can Unlock an AD User Account by using Active Directory Powershell cmdlet Unlock-ADAccount.
Unlock-ADAccount -Identity <adaccount>

Unlock Active Directory Users from Specific OU

The below powershell script unlock all the locked-out users from TestOU, you can add your own filter criteria to select users to unlock.
Import-Module ActiveDirectory
Get-ADUser -Filter 'Name -like "*"' `
  -SearchBase "OU=TestOU,DC=TestDomain,DC=local" | Unlock-ADAccount

Unlock Bulk AD Users from CSV file using Powershell Script

   1. Consider the CSV file LockedOutUsers.csv (Ex file: Download ADUsers.csv) which contains set of Locked-out Active Directory users to unlock with the attribute samAccountName.
Unlock Bulk AD Users from CSV file using Powershell script

   2. Copy the below Powershell script and paste in Notepad file.
   3. Change the LockedOutUsers.csv file path with your own csv file path.
   4. SaveAs the Notepad file with the extension .ps1 like Unlock-Bulk-AD-Users-FromCSV.ps1

Powershell Script: Download Unlock-Bulk-AD-Users-FromCSV.ps1
Import-Module ActiveDirectory
Import-Csv "C:\Scripts\LockedOutUsers.csv" | ForEach-Object {
 $samAccountName = $_."samAccountName" 
Get-ADUser -Identity $samAccountName | Unlock-ADAccount
Write-Host "-User "$samAccountName" unlocked"
}
   5. Now run the Unlock-Bulk-AD-Users-FromCSV.ps1 file from Powershell console to Unlock Bulk Active Directory users from CSV file.

Sunday, 16 November 2014

Event ID 4724 - An attempt was made to reset an account's password

Event ID 4724 will be logged in Domain Controller whenever we reset an user's password. In this article, I am going to explain about the Active Directory password reset audit Event ID 4724, how to enable or configure Event ID 4724 through Default Domain Controller Policy GPO and Auditpol.exe.

Summary:

Event ID 4724 sample Source

This sample 4724 event info logged while reset the user Smith's password. here, the Subject field indicates 'Who' reset the password and Target Account indicates which user account's password reset.
An attempt was made to reset an account's password.

Subject:
 Security ID:  TESTDOMAIN\Morgan
 Account Name:  Morgan
 Account Domain:  TESTDOMAIN
 Logon ID:  0xede9c

Target Account:
 Security ID:  TESTDOMAIN\Smith
 Account Name:  Smith
 Account Domain:  TESTDOMAIN
 

Enable Event 4724 through Group Policy

To enable event id 4724 in every Domain Controller, We need to configure audit policy settings in Default Domain Controllers Policy, or you can create new GPO and links it to Domain Controllers OU via GPMC console, or else you can configure the corresponding audit policy on Local Security Policy of each and every Domain Controller..

Follow the below steps to enable AD password reset audit event 4724 via Default Domain Controllers Policy.

    1. Press the key 'Window' + 'R'
    2. Type the command gpmc.msc, and click OK.
         Note: Skip the above steps by clicking Start -->Administrative Tools -->Group Policy Management.
    3. Expand the domain node and Domain Controllers OU,  right-click on the Default Domain Controllers Policy, then click Edit. - refer the below image.
Event ID 4724 - An attempt was made to reset an account's password

    4. Expand Computer Configuration node, go to the node Audit Policy (Computer Configuration->Policies->Windows Settings->Security Settings->Local Policies->Audit Policy).
    5. Navigate to the right side pane, select the policy Audit account management, and set the success audit value.
Event ID 4724 - An attempt was made to reset an account's password

6. Update/Refresh GPO settings by running the command gpupdate/force.

Enable Password Reset Event 4724 via Auditpol

Auditpol.exe is the command line utility tool to change Audit Security settings as category and sub-category level. It is available by default Windows 2008 R2 and later versions/Windows 7 and later versions. By using Auditpol, we can get/set Audit Security settings per user level and computer level.

Note: You should run Auditpol command with elevated privilege (Run As Administrator);

You can enable Active Directory Account user's password reset audit event (Event ID 4724) through User Account Management subcategory by using the following command
auditpol /set /subcategory:"User Account Management" /success:enable
Update/Refresh GPO settings by running the command gpupdate/force/


Disable/Stop Password Reset Event 4724

You can disable or stop Active Directory password reset audit event (Event ID 4724) by removing success audit in User Account Management subcategory by using the following command.
auditpol /set /subcategory:"User Account Management" /success:disable
You can also stop this event by removing the success setting from the Default Domain Controllers GPO in the setting path Computer Configuration->Polices->Windows Settings->Security Settings->Audit Policy->Account Management

Thanks,
Morgan
Software Developer

Saturday, 15 November 2014

Event ID 10 - SELECT * FROM __InstanceModificationEvent - WMI

I have got below error Event 10 in Application log after installed the Windows Server 2008 R2 Service Pack 1.
Log Name:      Application
Source:        Microsoft-Windows-WMI
Date:          11/15/2014 1:48:57 PM
Event ID:      10
Level:         Error
Computer:      TPVServer.MTS.local
Description:
Event filter with query "SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA "Win32_Processor" AND TargetInstance.LoadPercentage > 99" could not be reactivated in namespace "//./root/CIMV2" because of error 0x80041003. Events cannot be delivered through this filter until the problem is corrected.

Fix/Solution for Windows 7 or Windows Server with Service Pack 1

I have got the quick fix for this error Event 10 from microsoft support forum: Fix Application Error Event ID 10

We need to run below script to stop the Event ID 10 messages. To run the script follow these steps:

1) Copy the below script code and paste in Notepad file.

2) Save As the Notepad file with the extension .vbs like FixErrorEvent10.vbs
strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\subscription")
Set obj1 = objWMIService.ExecQuery("select * from __eventfilter where name='BVTFilter' and query='SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA ""Win32_Processor"" AND TargetInstance.LoadPercentage > 99'")

For Each obj1elem in obj1

set obj2set = obj1elem.Associators_("__FilterToConsumerBinding")
set obj3set = obj1elem.References_("__FilterToConsumerBinding")

For each obj2 in obj2set
WScript.echo "Deleting the object"
WScript.echo obj2.GetObjectText_
obj2.Delete_
next

For each obj3 in obj3set
WScript.echo "Deleting the object"
WScript.echo obj3.GetObjectText_
obj3.Delete_
next

WScript.echo "Deleting the object"
WScript.echo obj1elem.GetObjectText_
obj1elem.Delete_
Next
3) Open the command prompt with elevated privilege (Run As Administrator)

4) Now run the vb script file FixErrorEvent10.vbs using CScript from command prompt to stop the event 10 with the above error message.

Event ID 10 - SELECT * FROM __InstanceModificationEvent - WMI

After running this script the Event ID 10 errors related to this event should stop occurring.

Note: If you receive the Event ID 10 with different error message, there can be other reasons, this only prevents the Event ID 10 with above error message.

Thanks,
Morgan
Software Developer

SQL Server - Could not allocate space for object in Database

I got the below SQL Database error which primarily states Could not allocate space for object in Database and suggests to deleted unwanted data from the related SQL Server database.
Could not allocate space for object 'dbo.MyTable' in database 'MyDatbase' because the 'PRIMARY' filegroup is full.Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
After I have analyzed some time found my SQL Database already takes around 10 GB memory size. I am using SQL Server 2008 R2 Expression edition, since it is a free version, it allows only 10 GB maximum size for every single database. Now we should free some space from database by deleting unwanted files after taking backup of unwanted data, so that we can add new data hereafter. I strongly believe, you also getting error due to this reason. So, next step is before deleting data we need to find the tables which are taking high memory space.

SQL query to List Used and Unused Size for all Tables

USE [MorganDB];
GO
SELECT t.NAME AS TableName, p.rows AS RowsCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB Desc
Output: Form the below output you can easily find what are tables are taking high memory space.

SQL Server- Could not allocate space for object in Database

Thanks,
Morgan
Software Developer

Friday, 14 November 2014

Create new SQL Database in different location using SSMS

When you create new SQL Server Database, both Primary data file and Log file of the database will be created under default sql server location (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA). Since most of the applications are using C drive location to store their required files, we can not ensure the availability of free space in future for growing database. So better idea is create new SQL Database files in a dedicated location that has more free space availability without any other disturbance. You can easily create new sql server database in different location using SQL Server Management Studio.

Steps to create new SQL Database in specified location using SSMS

1. Connect SQL Server Management Studio by entering SQL Server details.

Create new SQL Database in different location using SSMS

2. Right-click on the node Databases, and click New Database...

Create new SQL Database in different location using SSMS

3. Enter the new Database name and drag the below horizontal scroll bar into right-side direction as shown in below image.

Create new SQL Database in new location using SSMS

4. Now, you can see the column Path which holds the default locations for Primary database file and Log file.

Create new SQL Database in different location using SSMS

5. Either you can manually type the new database location for primary database file and log file or you can use browse (...) button to select the database location where you want to create new database files.

Create new SQL Database in specified location using SSMS

6. Click OK to create new sql database in the specified location. Now, you can see new database files are created under the specified location D:\SQLDB instead of default sql primary storage location.

Create new SQL Server Database in different location using SSMS


Morgan,
Thanks
Software Developer

Thursday, 13 November 2014

List Table Size for all Tables in SQL Database

When you feel the size of your SQL Database growing larger, you need to find the reason for what are the tables taking more storage memory space in your database. I am also came through the same situation in my customer end, because he is using SQL Server 2008 R2 Express. As you know, this is a free version, so it will allow only 20 GB storage size for every single database. I have got the following T-SQL query to list the size of all the tables in a sql databse and I have modified the query to list larger size tables first by ORDER BY statement, so that we can easily identify which tables are taking high storage memory size.

T-SQL: List Used and Unused Size for all Tables

USE [MorganDB];
GO
SELECT t.NAME AS TableName, p.rows AS RowsCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB Desc

Output: List total space, used size and unused size for all tables

List Table Size for all Tables in SQL Database

SQL Server query to get Complete Database size

You can use the built in stored procedure sp_spaceused to get the total space used by a SQL Database.
USE [MorganDB];
GO
exec sp_spaceused

Output: Returns total Database size

sql server query to get database size in sql

Thanks,
Morgan
Software Developer

FOR LOOP in SQL Server Database

I had a requirement to insert continuous numbers into continuous rows of SQL Database table for testing purpose. After gone through the web, I found there is no FOR LOOP statement in T-SQL, but we can achieve it easily through While Loop statement. While Loop sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the While Loop can also be controlled from inside the loop with the BREAK and CONTINUE keywords.

While LOOP for FOR LOOP Replacement

You can easily replace the FOR LOOP by While Loop with while condition and increment statement inside the While Loop.
USE [MorganDB];
GO
DECLARE @i int = 0
While (@i<100)
BEGIN
Set @i=@i+1
--You can do your work here
INSERT INTO Books Values(@i,'Book'+cast(@i as varchar(20)))
END

While LOOP with Break and Continue

Along with While LOOP condition you can also exit the Loop by BREAK statement.
USE [MorganDB];
GO
DECLARE @i int = 0
While (@i<100)
BEGIN
Set @i=@i+1
--You can do your work here
INSERT INTO Books Values(@i,'Book'+cast(@i as varchar(20)))
   
IF ((SELECT Count(*) From Books)>=50)
BREAK
ELSE
CONTINUE
END

Thanks,
Moragn
Software Developer

Wednesday, 12 November 2014

The samAccountName IdentityType must be in the form "domainname\userName", "machinename\userName", or "userName"

Hi, I got this error 'The samAccountName IdentityType must be in the form "domainname\userName", "machinename\userName", or "userName"' when trying to find user Identity by using its samAccountName.
public static void EnableUser(string domain,string userName)
{
   PrincipalContext ctx = new PrincipalContext(domain);

   UserPrincipal usr = UserPrincipal.FindByIdentity(ctx, IdentityType.SamAccountName, 
                                           userName);
  if(usr != null)
  {
    if (usr.Enabled == false)
        usr.Enabled = true;
        usr.Save();
  }
}

Solution: Error due to 'Empty User Name'

After, I have analyzed some time found the issue due to Empty UserName. the user name was wrongly sent as empty string from other part of the code. You will get the same error when run the below code.
PrincipalContext ctx = new PrincipalContext("yourdomain");

UserPrincipal usr = UserPrincipal.FindByIdentity(ctx, IdentityType.SamAccountName, "");

Thanks
Morgan
Software Developer

Tuesday, 11 November 2014

Get Volume Path from Drive Name using Powershell script

I have been working with file access events in Clustered File Server. In event log, for every file access, we are getting 4663 and 4656 events with file path like: \Device\HarddiskVolume4\MyShare\test.txt instead of my local path F:\MyShare\test.txt. After I have analyzed , found the prefix path \Device\HarddiskVolume4 is mapped with the device F:. Finally, I found this link (http://poshcode.org/4768), it contains powershell script to list Device Name (Drive letter) and mapped Device Path (Volume Path).

List Device Name and Volume Path using Powershell script

We can get the Volume Path from Device Name using the Kernel32 module function QueryDosDevice and we can list the available device names (drive letter) using WMI class Win32_Volume. Use the the below Powershell script to list device path and device name.

   1. Copy the below Powershell script and paste in Notepad file.
   2. Save As the Notepad file with the extension .ps1 like List-Device-Name-and-Path.ps1

Powershell Script: Download List-Device-Name-and-Path.ps1
 # Biuild System Assembly in order to call Kernel32:QueryDosDevice. 
    $DynAssembly = New-Object System.Reflection.AssemblyName('SysUtils')
    $AssemblyBuilder = [AppDomain]::CurrentDomain.DefineDynamicAssembly($DynAssembly, [Reflection.Emit.AssemblyBuilderAccess]::Run)
    $ModuleBuilder = $AssemblyBuilder.DefineDynamicModule('SysUtils', $False)

    # Define [Kernel32]::QueryDosDevice method
    $TypeBuilder = $ModuleBuilder.DefineType('Kernel32', 'Public, Class')
    $PInvokeMethod = $TypeBuilder.DefinePInvokeMethod('QueryDosDevice', 'kernel32.dll', ([Reflection.MethodAttributes]::Public -bor [Reflection.MethodAttributes]::Static), [Reflection.CallingConventions]::Standard, [UInt32], [Type[]]@([String], [Text.StringBuilder], [UInt32]), [Runtime.InteropServices.CallingConvention]::Winapi, [Runtime.InteropServices.CharSet]::Auto)
    $DllImportConstructor = [Runtime.InteropServices.DllImportAttribute].GetConstructor(@([String]))
    $SetLastError = [Runtime.InteropServices.DllImportAttribute].GetField('SetLastError')
    $SetLastErrorCustomAttribute = New-Object Reflection.Emit.CustomAttributeBuilder($DllImportConstructor, @('kernel32.dll'), [Reflection.FieldInfo[]]@($SetLastError), @($true))
    $PInvokeMethod.SetCustomAttribute($SetLastErrorCustomAttribute)
    $Kernel32 = $TypeBuilder.CreateType()

    $Max = 65536
    $StringBuilder = New-Object System.Text.StringBuilder($Max)

    Get-WmiObject Win32_Volume | ? { $_.DriveLetter } | % {
        $ReturnLength = $Kernel32::QueryDosDevice($_.DriveLetter, $StringBuilder, $Max)

        if ($ReturnLength)
        {
            $DriveMapping = @{
                DriveLetter = $_.DriveLetter
                DevicePath = $StringBuilder.ToString()
            }

            New-Object PSObject -Property $DriveMapping
        }
    }
   3. Now run the script file List-Device-Name-and-Path.ps1 from Powershell to list all the Device Name and Volume Path in local machine.

Get Device Path from Device Name using Powershell script

Get Device Path from Device Name (Drive Letter) using Powershell script

The below script displays Device Path for the given Device Name(Derive letter). The device name(drive letter) cannot have a trailing backslash; for example, use "C:", not "C:\". Follow the below steps to get volume path from drive letter.

   1. Copy the below Powershell script and paste in Notepad file.
   2. Save As the Notepad file with the extension .ps1 like Get-DevicePath-from-DeviceName.ps1

Powershell Script: Download Get-DevicePath-from-DeviceName.ps1
    $driveLetter = Read-Host "Enter Drive Letter:"
    Write-Host " "
    $DynAssembly = New-Object System.Reflection.AssemblyName('SysUtils')
    $AssemblyBuilder = [AppDomain]::CurrentDomain.DefineDynamicAssembly($DynAssembly, [Reflection.Emit.AssemblyBuilderAccess]::Run)
    $ModuleBuilder = $AssemblyBuilder.DefineDynamicModule('SysUtils', $False)

    # Define [Kernel32]::QueryDosDevice method
    $TypeBuilder = $ModuleBuilder.DefineType('Kernel32', 'Public, Class')
    $PInvokeMethod = $TypeBuilder.DefinePInvokeMethod('QueryDosDevice', 'kernel32.dll', ([Reflection.MethodAttributes]::Public -bor [Reflection.MethodAttributes]::Static), [Reflection.CallingConventions]::Standard, [UInt32], [Type[]]@([String], [Text.StringBuilder], [UInt32]), [Runtime.InteropServices.CallingConvention]::Winapi, [Runtime.InteropServices.CharSet]::Auto)
    $DllImportConstructor = [Runtime.InteropServices.DllImportAttribute].GetConstructor(@([String]))
    $SetLastError = [Runtime.InteropServices.DllImportAttribute].GetField('SetLastError')
    $SetLastErrorCustomAttribute = New-Object Reflection.Emit.CustomAttributeBuilder($DllImportConstructor, @('kernel32.dll'), [Reflection.FieldInfo[]]@($SetLastError), @($true))
    $PInvokeMethod.SetCustomAttribute($SetLastErrorCustomAttribute)
    $Kernel32 = $TypeBuilder.CreateType()

    $Max = 65536
    $StringBuilder = New-Object System.Text.StringBuilder($Max)
    $ReturnLength = $Kernel32::QueryDosDevice($driveLetter, $StringBuilder, $Max)

     if ($ReturnLength)
     {
         Write-Host "Device Path: "$StringBuilder.ToString()
      }
      else
      {
          Write-Host "Device Path: not found"
      }
    Write-Host " "
    

   3. Now run the script file Get-DevicePath-from-DeviceName.ps1 from Powershell, and give the Drive letter as argument which you want to get device path.

Get Volume Path from Drive Name using Powershell script
Thanks,
Morgan
Software Developer

Monday, 10 November 2014

Get List of Network Shares using Powershell script

Description:

We can easily get the list of Network Shares/Share Folder, Devices, Disk Drives and Printers by using WMI class Win32_Share. But it will lists only NTFS Shares, not the Cluster Share Folders. You can use the alternative WMI class Win32_ClusterShare to list Cluster Shares. In this article, I am going write Powershell script samples to get list of Network Shares in Local Machine and Remote Computer.

Summary:


List Network Shares in Local Machine using Powershell

You can enumerate the Network Shares list by using Powerahell's WMI class Win32_Share. Here, FT is nothing but the Format-Table cmdlet, you can change it into FL to display result in list view.
Get-WMIObject -Query "SELECT * FROM Win32_Share" | FT
Get List of Network Shares using Powershell script

List Network Shares from Remote Computer using Powershell

You can list the Network Shares from Remote Machine by giving name of the remote computer through argument syntax -ComputerName.
Get-WMIObject -ComputerName "your-pc" -Query "SELECT * FROM Win32_Share" | FL
List Network Shares from Remote Computer using Powershell script

List only Network Share Folders (Not hidden shares) using Powershell

You can use SQL Query like syntax to apply filter in Win32_Share class. The following Powershell script, filters and list only Network Share Folders (Not hidden shares) by adding filter Type=0.
Get-WMIObject -ComputerName "your-pc" -Query "SELECT * FROM Win32_Share Where Type=0" | FT
List only Network Share Folders (Not hidden) using Powershell script

Export Network Shares to CSV using Powershell

You can export the Network Shares list into CSV using Powershell's Export-CSV cmdlet. The following script exports not hidden Network Share Folders to CSV file from Remote Computer.
Get-WMIObject -ComputerName "your-pc"`
 -Query "SELECT * FROM Win32_Share Where Type=0" |
 Select-Object Name,Path,Description |
 Export-CSV 'C:\NetworkShares.csv'
Export Network Shares to CSV file using Powershell script


Thanks,
Morgan

Sunday, 9 November 2014

Event ID 4663 - An attempt was made to access an object

Event ID 4663 is logged whenever an object accessed by user or other sources. It will be used mainly for File System Access auditing, but we can also use to monitor other object types like Registry, SAM and etc. Event 4663 logged along with the events 4656 and 4658, event 4656 contains information of what kind of access permission requested, where as the event 4658 tells when the access operation completed, but only the event 4663 contains what type of access made on the particular object. This access either be Add file, Delete file, Write file, Read file and just Access file. In this article, I am going write how to enable Audit Policy to log this event via Local Security Policy. And please mind, you have to also enable File Access Audit Security (SACL) of the corresponding File of Folder that you want to monitor the access.

Summary:


Enable Event ID 4663 via Local Security Policy

Event 4663 controlled by the Audit Policy setting Audit object access. When you enable this setting you will get all the three file access audit events (4663, 4656 and 4658). If  you want to get logged only 4663 event, you can do it by enable the sub category setting Audit File System under Advanced Audit Policy Configuration (But it will be available only from Window 7/2008 R2 and later versions).

Follow the below steps to configure Audit Policy to log event 4663:

1. Open the Local Security Policy by running the command secpol.msc.

Enable Event 4663 via Local Security Policy

2. Navigate to the node Audit Policy (Security Settings/Local Policies/Audit Policy). In right-hand side, select the setting Audit object access.

3. Double-click on Audit object access, and check the Audit options Success and Failure to monitor successful file accesses and access denied file accesses and click Apply button.

Enable Event ID 4663 via Local Security Policy


Note: In Windows 7/2008 R2 and later versions, you can enable sub category level setting Audit File System under Advanced Audit Policy Configuration (Security Settings/Advanced Audit Policy Configuration/Object Access/Audit File System).

Enable Event ID 4663 via Advanced Security Policy

Steps to Configure File Access Audit Security (SACL)


System Access Control Lists (SACL) determines file access events for the particular File or Folder should generated or not. So that, you should enable SACL for the File or Folder which you want monitor or track the access events.

Follow the below steps to enable File Access Audit Security:

1. Right-click on the Folder which you want to configure audit events, and click Properties.

Steps Enable Event ID 4663

2. Select Security tab, and click Advanced button.

Steps Enable Event ID 4663

3. Navigate to the tab Audit, and click Add button.

Steps Enable Event ID 4663

4. Select the account Everyone, and check Successful and Failed Audit options which are you want to audit, click the button OK, and click Apply. 

Steps Enable Event ID 4663

Event 4663 Sample Source

The following image shows 4663 event log info for delete file access.

File Delete Audit Event - 4663

Thanks,
Morgan
Software Developer

Powershell script to Backup and Restore SQL Database

Getting Backup of SQL Database is important task for every DBA before making any changes in production environment, so that we can easily Restore the Backup of the database if anything goes wrong during database migration. If you have SQL Server Management Studio(SSMS), you can easily Backup and Restore database using simple user interface (Refer this artcle: Backup and Restore SQL Database using SSMS). In Powershell, you can get backup and restore SQL database by using Server Management Objects (SMO).

Backup SQL Server Database using Powershell script

We are going to get the backup of SQL Database by using Server Management Objects (SMO) namespace class Microsoft.SqlServer.Management.Smo.Backup.  Follow the below steps to complete the backup process.

   1. Copy the below Powershell script and paste in Notepad file.
   2. Change the values for the variables $sqlName, $dbname and $backupPath with your own SQL Server instance name, database name and destination path for backup.
   3. SaveAs the Notepad file with the extension .ps1 like Backup-SQL-Database.ps1

Powershell Script: Download Backup-SQL-Databse.ps1
# Set SQL Server instance name
$sqlName= "localhost\SQLExpress"

# Set the databse name which you want to backup
$dbname= "MorganDB"

# Set the backup file path
$backupPath= "D:\SQLBackup\MorganDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

#Create SMO Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

$dbBackup.Database = $dbname

#Add the backup file to the Devices
$dbBackup.Devices.AddDevice($backupPath, "File")

#Set the Action as Database to generate a FULL backup 
$dbBackup.Action="Database"

#Call the SqlBackup method to complete backup 
$dbBackup.SqlBackup($sqlServer)

Write-Host "...Backup of the database"$dbname" completed..."
   4. Now run the file Backup-SQL-Database.ps1 from Powershell to get backup of sql database. 

Powershell script to Backup SQL Database

Restore SQL Server Database using Powershell script

We are going to restore the backup of sql database by using  Server Management Objects (SMO) namespace class Microsoft.SqlServer.Management.Smo.Restore. Follow the below steps to complete the restore database process.

   1. Copy the below Powershell script and paste in Notepad file.
   2. Change the values for the variables $sqlName, $dbname and $backupPath with your own SQL Server instance name, database name and destination path of the backup file.
   3. SaveAs the Notepad file with the extension .ps1 like Restore-SQL-Database.ps1

Powershell Script: Download Restore-SQL-Database.ps1
# Set SQL Server instance name
$sqlName= "localhost\SQLExpress"

# Set new or existing databse name to restote backup
$dbname= "MorganDB"

# Set the existing backup file path
$backupPath= "D:\SQLBackup\MorganDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database 
$dbRestore.SqlRestore($sqlServer)

Write-Host "...SQL Database"$dbname" Restored Successfullyy..."
   4. Now run the file Restore-SQL-Database.ps1 from Powershell to restore backup of SQL Database.

Powershell script to Restore SQL Database
Note: I have placed Powershell script file in the location C:\Scripts, if you placed in any other location, you can navigate to the corresponding path using CD path command (like cd "C:\Downloads").

Thanks,
Morgan