Monday

Tag: Powershell

Disabling or Uninstalling Unnecessary Services and Apps in Windows 10

Windows 10 is a powerful and versatile operating system that offers many features and functionalities. However, not all of them are necessary or useful for every user. In fact, some of the services and apps that come preinstalled or run in the background can pose security risks or slow down your system performance.

In this blog post, we will describe which unnecessary services and apps you should disable or remove from Windows 10 for security reasons. We will also explain how to do it safely and easily.

What Are Windows Services?

Windows services are programs that run in the background and provide essential functions for the operating system, such as networking, security, printing, etc. They usually start automatically when you boot up your computer and run until you shut it down.

What Are Windows Apps?

Windows apps are applications that you can install from the Microsoft Store or other sources. They are designed to work with the modern user interface of Windows 10 and offer various functionalities, such as games, productivity tools, social media, etc.

Why Should You Disable or Remove Unnecessary Services and Apps?

There are several reasons why you may want to disable or remove unnecessary services and apps from Windows 10:

  • Security – Some services and apps may have vulnerabilities that can be exploited by hackers or malware. For example, the Remote Desktop service can allow remote access to your computer if it is not configured properly. The Bluetooth service can expose your device to wireless attacks if you don’t use it. Some apps may also collect your personal data or display unwanted ads.
  • Performance – Some services and apps may consume a lot of system resources, such as CPU, RAM, disk space, etc. This can affect your system speed and responsiveness, especially if you have a low-end device or multiple programs running at the same time.
  • Privacy – Some services and apps may send your data to Microsoft or other third-party servers for various purposes, such as diagnostics, feedback, advertising, etc. This can compromise your privacy and expose your online activities to others.
  • Storage – Some services and apps may take up a lot of disk space on your device, especially if they are rarely used or updated. This can limit your available storage space for other files and programs.

Which Services and Apps Should You Disable or Remove?

Continue reading “Disabling or Uninstalling Unnecessary Services and Apps in Windows 10”

Check Email Addresses Listed in Active Directory

One of the tasks that administrators often need to perform is to verify that each active directory user account has a valid email address. This is important for ensuring that users can receive notifications, access online services, and communicate with other users. There are different ways to verify the email addresses of active directory users, but in this article, we will focus on one method that uses PowerShell.

PowerShell is a scripting language that allows administrators to automate tasks and manage systems. PowerShell can interact with active directory through the ActiveDirectory module, which provides cmdlets for querying and modifying objects in the directory. To use PowerShell to verify the email addresses of active directory users, we need to follow these steps:

Continue reading “Check Email Addresses Listed in Active Directory”

Different Ways to Reboot Windows 10 Computer

Rebooting a Windows 10 computer is a common and simple operation that can help you fix some software issues or apply the changes you have made to your computer. However, do you know how to reboot Windows 10 properly? In this blog post, I will show you four different ways to restart your Windows 10 computer in a professional and safe manner.

Many might find these instructions too simple or too well known to even list, but some users are just learning how to use Windows 10 and might find these instructions useful.

Method 1: Reboot in a Normal Way

This is the conventional and most widely used method. You can follow these steps to reboot your Windows 10 computer in a normal way:

  1. Open Start on Windows 10.
  2. Press the Power button and select Restart from the popup menu.
  3. Wait for your computer to restart.

Alternatively, you can also use the Power User Menu to perform a normal restart of Windows 10. Here are the steps:

  1. Right-click on the Start button or press the Windows key and the X key at the same time to open the Power User Menu.
  2. Go to Shut down or sign out.
  3. Select Restart from the popup sub-menu of Shut down or sign out.
  4. Wait for your computer to restart.

Method 2: Reboot using Ctrl+Alt+Del

You can also use the keyboard shortcut Ctrl+Alt+Del to restart your Windows 10 computer. This method works on all Windows 10 computers. Here is how to do it:

  1. Press Ctrl+Alt+Del at the same time on your keyboard to open the shutdown dialog box.
  2. Click on the Power button that is on the lower-right side of your computer screen.
  3. Select Restart from the pop-out menu.
  4. Wait for your computer to restart.

Method 3: Restart from Command Prompt

The third method is to restart your Windows 10 computer from Command Prompt. This method requires you to use the shutdown command to reboot Windows 10. You can follow these steps to do it:

  1. Open Command Prompt as an administrator. You can do this by typing cmd in the Start menu, right-clicking on Command Prompt, and selecting Run as Administrator.
  2. In the Command Prompt window, type “shutdown /r” (without the quotes) and press Enter. This will initiate a restart of your computer.
  3. Wait for your computer to restart.

Continue reading “Different Ways to Reboot Windows 10 Computer”

Scripts for listing all SQL Server Databases and Objects using PowerShell

This powerful script lists all objects in an instance and scripts them into a network folder, by date and instance, so you can keep a record of the objects.

Installing PowerShell the SqlServer module:

Install-Module -Name SqlServer

If there are previous versions of the SqlServer module on the computer, you may be able to use Update-Module, or provide the -AllowClobber parameter:

Install-Module -Name SqlServer -AllowClobber

This article by Angel Gomez gives you the script and some information on how to use it.

Continue reading “Scripts for listing all SQL Server Databases and Objects using PowerShell”

Enable Reserved Storage Using DISM or PowerShell on Windows 10

How to Enable Reserved Storage on Windows 10

Windows Updates will fail to install if your PC doesn’t have enough free disk space. Before reserved space, the only workaround is to free up some storage space before continuing with your update effort. With the May 2019 Update to Windows 10, Microsoft fixed this problem by reserving disk space for future updates.

With “reserved storage,” Microsoft sets aside at least 7 gigabytes of space on your hard drive to ensure updates can download—regardless of how much normal disk space you have.

When not being used by update files, Reserved Storage will be used for apps, temporary files, and system caches, improving the day-to-day function of your PC.

When enabled, it keeps some disk space for Windows Update, apps, temporary files, and system caches because without enough disk space Windows and applications may stop working properly.

Users installing a fresh copy of Windows 10 1903 or later, or receiving a device with the OS preinstalled, should see Reserved Storage enabled out-of-the-box. Some device manufacturers choose not to enable Reserved Storage because it reduces the available disk space to users.

Those upgrading from a previous version of Windows don’t get Reserved Storage, unless the ShippedWithReserves registry key is set to 1 before the upgrade. You can find the key under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\ReserveManager

Windows Update and Reserved Storage

Windows Update gives priority access to Reserved Storage. Before an update, temporary files that are no longer needed in Reserved Storage are deleted and the remaining space is then given exclusively to Windows Update. If Reserved Storage still doesn’t have enough space, Windows Update can also spill into free disk space that is available to the user. On systems where disk space is severely limited, Windows Update might also prompt to attach external storage to complete the update process.

DISM updated with new Reserved Storage options

Admins are able to query the amount of space reserved and even disable Reserved Storage. The state of Reserved Storage is preserved across OS upgrades once it has been enabled or disabled using DISM. The following DISM command enables Reserved Storage for the online Windows image:

Enable or Disable Reserved Storage using PowerShell

If you don’t want to mess around with DISM, Windows 10 version 2004 supports a new PowerShell cmdlet that will let you enable or disable Reserved Storage for online images.

Continue reading “Enable Reserved Storage Using DISM or PowerShell on Windows 10”

History of PowerShell

PowerShell is a tool developed by Microsoft to provide a powerful command-line interface to allow users to perform various automation efforts. If you are aware of Linux and mac command line, the PowerShell command-line is very similar in purpose. PowerShell version 1.o was first released in 2006 to support Windows XP SP2, Windows Server 2003 SPI, and Windows Vista. The newest version is PowerShell 7, which is the replacement for PowerShell Core 6.x products as well as Windows PowerShell 5.1, which is the previous supported Windows PowerShell version.

Development

Every version of Microsoft Windows has included a command line utility for basic scripting operations to help manage the Operating System, basically an interface similar to the older MS-DOS interface. The user would create a batch file that included basic scripting language commands which could be used to automate various tasks. The automation abilities of this older interface was limited in scope and didn’t allow full automation of all Windows administrative operations. Microsoft addressed these concerns by the introduction of the Windows Script Host in 1998 with Windows 98, and its command-line based host named cscript.exe. The problem was the Windows Script Host still had limited abilities and was quickly seen by many as more helpful to hackers than to administrators.

By 2002, Microsoft was developing a new command-line management tool called Monad. Jeffrey Snover published a white paper in August 2002, called the “Monad Manifesto”, and this paper discussed the concept of translating Unix tools to the Windows platform. Since Windows is very different from Unix, this is much harder than it might seem.

Monad was first demonstrated at the Professional Development Conference (PDC) in 2003, later it was released to private beta, and was eventually published to public beta in June 2005. By April 2006, Microsoft had announced the initial Monad product had been renamed Windows PowerShell.

PowerShell v2.0 development began before PowerShell v1.0 was shipped. 

Initial Release

Release Candidate 2 of PowerShell version 1 was released in September 2006, with the formal release in November 2006 in Barcelona. PowerShell for earlier versions of Windows was released in January 2007. PowerShell v2.0 was completed and released to manufacturing in August 2009,and it was part of Windows 7 and Windows Server 2008 R2. 

Release History

Windows PowerShell 1.0

PowerShell 1.0 was released in November 2006 for Windows XP SP2, Windows Server 2003 SP1 and Windows Vista. It is an optional component of Windows Server 2008.

Windows PowerShell 2.0

Windows PowerShell ISE v2.0 was released on Windows 7, and it was an integrated development environment for PowerShell scripts. The most remarkable feature introduced, with the help of WS-management, allowed you to write your commands to a remote machine.

PowerShell 2.0 is integrated with Windows 7 and Windows Server 2008 R2 and is released for Windows XP with Service Pack 3, Windows Server 2003 with Service Pack 2, and Windows Vista with Service Pack 1.

PowerShell v2 includes changes to the scripting language and hosting API, in addition to including more than 240 new cmdlets.

New features of PowerShell 2.0 include:

  • PowerShell remoting: Using WS-Management, PowerShell 2.0 allows scripts and cmdlets to be invoked on a remote machine or a large set of remote machines.
  • Background jobs: Also called a PSJob, it allows a command sequence (script) or pipeline to be invoked asynchronously. Jobs can be run on the local machine or on multiple remote machines. An interactive cmdlet in a PSJob blocks the execution of the job until user input is provided.
  • Transactions: Enable cmdlet and developers can perform transactional operations. PowerShell 2.0 includes transaction cmdlets for starting, committing, and rolling back a PSTransaction as well as features to manage and direct the transaction to the participating cmdlet and provider operations. The PowerShell Registry provider supports transactions.
  • Advanced functions: These are cmdlets written using the PowerShell scripting language. Initially called “script cmdlets”, this feature was later renamed “advanced functions”.
  • Modules: This allows script developers and administrators to organize and partition PowerShell scripts in self-contained, reusable units. Code from a module executes in its own self-contained context and does not affect the state outside the module. Modules can define a restricted runspace environment by using a script. They have a persistent state as well as public and private members.
  • Script debugging: It allows breakpoints to be set in a PowerShell script or function. Breakpoints can be set on lines, line & columns, commands and read or write access of variables. It includes a set of cmdlets to control the breakpoints via script.
  • You can get more information about PowerShell v2 here.
  • Microsoft recommends you no longer support, install, or use PowerShell v2

Windows PowerShell 3.0

PowerShell 3.0 is integrated with Windows 8 and with Windows Server 2012. Microsoft has also made PowerShell 3.0 available for Windows 7 with Service Pack 1, for Windows Server 2008 with Service Pack 1, and for Windows Server 2008 R2 with Service Pack 1.

PowerShell 3.0 is part of a larger package, Windows Management Framework 3.0 (WMF3), which also contains the WinRM service to support remoting. Microsoft made several Community Technology Preview releases of WMF3. An early community technology preview 2 (CTP 2) version of Windows Management Framework 3.0 was released on 2 December 2011. Windows Management Framework 3.0 was released for general availability in December 2012 and is included with Windows 8 and Windows Server 2012 by default.

New features in PowerShell 3.0 include:

  • Scheduled jobs: Jobs can be scheduled to run on a preset time and date using the Windows Task Scheduler infrastructure.
  • Session connectivity: Sessions can be disconnected and reconnected. Remote sessions have become more tolerant of temporary network failures.
  • Improved code writing: Code completion (IntelliSense) and snippets are added. PowerShell ISE allows users to use dialog boxes to fill in parameters for PowerShell cmdlets.
  • Delegation support: Administrative tasks can be delegated to users who do not have permissions for that type of task, without granting them perpetual additional permissions.
  • Help update: Help documentations can be updated via Update-Help command.
  • Automatic module detection: Modules are loaded implicitly whenever a command from that module is invoked. Code completion works for unloaded modules as well.
  • You can get more information about PowerShell v3 here.

Windows PowerShell 4.0

PowerShell 4.0 is integrated with Windows 8.1 and with Windows Server 2012 R2. Microsoft has also made PowerShell 4.0 available for Windows 7 SP1, Windows Server 2008 R2 SP1, and Windows Server 2012.

New features in PowerShell 4.0 include:

  • Desired State Configuration: Declarative language extensions and tools that enable the deployment and management of configuration data for systems using the DMTF management standards and WS-Management Protocol
  • New default execution policy: On Windows Servers, the default execution policy is now RemoteSigned.
  • Save-Help: Help can now be saved for modules that are installed on remote computers.
  • Enhanced debugging: The debugger now supports debugging workflows, remote script execution and preserving debugging sessions across PowerShell session reconnections.
  • -PipelineVariable switch: A new ubiquitous parameter to expose the current pipeline object as a variable for programming purposes
  • Network diagnostics to manage physical and Hyper-V’s virtualized network switches
  • Where and ForEach method syntax provides an alternate method of filtering and iterating over objects.
  • You can get more information about PowerShell v4 here.

Windows PowerShell 5.0

Windows Management Framework (WMF) 5.0 RTM which includes PowerShell 5.0 was re-released to web on 24 February 2016, following an initial release with a severe bug.

Key features included:

  • The new class keyword that creates classes for object-oriented programming.
  • The new enum keyword that creates enums.
  • Extending support for switch management to layer 2 network switches.
  • Debugging for PowerShell background jobs and instances of PowerShell hosted in other processes (each of which is called a “runspace”)
  • Desired State Configuration (DSC) Local Configuration Manager (LCM) version 2.0
  • DSC partial configurations
  • DSC Local Configuration Manager meta-configurations
  • Authoring of DSC resources using PowerShell classes
  • You can get more information about PowerShell v5 here.

Windows PowerShell 5.1

It was released along with the Windows 10 Anniversary Update in August 2016, and in Windows Server 2016. PackageManagement now supports proxies, PSReadLine now has ViMode support, and two new cmdlets were added: Get-TimeZone and Set-TimeZone. The LocalAccounts module allows for adding/removing local user accounts. A preview for PowerShell 5.1 was released for Windows 7, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, and Windows Server 2012 R2 in July 2016, and was formally released in January 2017.

PowerShell 5.1 is the first version to come in two editions of “Desktop” and “Core”. The “Desktop” edition is the continuation of the traditional Windows PowerShell that runs on full .NET Framework stack. The “Core” edition runs on .NET Core and is bundled with Windows Server 2016 Nano Server. In exchange for smaller footprint, the “Core” version lacks some features such as the cmdlets to manage clipboard or join a computer to a domain, WMI version 1 cmdlets, Event Log cmdlets, and profiles. This was the final version of PowerShell made exclusively for Windows.

PowerShell Core 6

PowerShell Core 6.0 was first announced in August 2016, when Microsoft unveiled PowerShell Core and also announced the decision to make the product cross-platform, independent of Windows, free, and open source. It achieved general availability in January 2018 for Windows, macOS, and Linux. It has its own support lifecycle and adheres to the Microsoft lifecycle policy that is introduced with Windows 10: Only the latest version of PowerShell Core is formally supported. Microsoft expects to release one minor version for PowerShell Core 6.0 every six months.

The most significant change in this version of PowerShell is the expansion to the other platforms. For Windows administrators, this version of PowerShell did not include any major new features. In an interview with the community in January 2018, the PowerShell team was asked to list the top 10 most exciting things that would happen for a Windows IT professional who would migrate from Windows PowerShell 5.1 to PowerShell Core 6.0; in response, Angel Calvo of Microsoft could only name two: cross-platform and open-source.

According to Microsoft, one of the new features of PowerShell 6.1 is “Compatibility with 1900+ existing cmdlets in Windows 10 and Windows Server 2019.” Still, no details of these cmdlets can be found in the full version of the change log. Microsoft later professes that this number was insufficient as PowerShell Core failed to replace Windows PowerShell 5.1 and gain traction on Windows. It was, however, popular on Linux.

PowerShell Core 6.2 is focused primarily on performance improvements, bug fixes, and smaller cmdlet and language enhancements that improved developer productivity.

PowerShell 7

PowerShell 7 is the replacement for PowerShell Core 6.x products as well as Windows PowerShell 5.1, which was the last supported Windows PowerShell version. The focus in development was to make PowerShell 7 a viable replacement for Windows PowerShell 5.1, i.e. to have near parity with Windows PowerShell in terms of compatibility with modules that ship with Windows.

New features in PowerShell 7 include:

  • Near parity with Windows PowerShell in terms of compatibility with built-in Windows modules
  • A new error view
  • The Get-Error cmdlet
  • Pipeline chaining operators that allow conditional execution of the next cmdlet in the pipeline
  • You can get more information about PowerShell v7 here.

Use of PowerShell

PowerShell is a fully supported scripting language that is actively under development by Microsoft and it also has a strong user community. PowerShell is a modern command shell that includes the same features as other popular shells. PowerShell accepts and returns .NET objects, which makes it a very powerful tool. The shell includes the following features:

  • Robust command-line history
  • Tab completion and command prediction
  • Supports command and parameter aliases
  • Pipeline for chaining commands
  • In-console help system
  • Extensible through functions, classes, scripts, and modules
  • Extensible formatting system for easy output
  • Extensible type system for creating dynamic types
  • Built-in support for common data formats like CSV, JSON, and XML

There are multiple sources to help you get started with PowerShell. Starting PowerShell in Windows is really easy.

Resources

Wikipedia – PowerShell

Microsoft – PowerShell

Enable Reserved Storage Using DISM or PowerShell on Windows 10

How to Enable Reserved Storage on Windows 10

Windows Updates will fail to install if your PC doesn’t have enough free disk space. Before reserved space, the only workaround is to free up some storage space before continuing with your update effort. With the May 2019 Update to Windows 10, Microsoft fixed this problem by reserving disk space for future updates.

With “reserved storage,” Microsoft sets aside at least 7 gigabytes of space on your hard drive to ensure updates can download—regardless of how much normal disk space you have.

When not being used by update files, Reserved Storage will be used for apps, temporary files, and system caches, improving the day-to-day function of your PC.

When enabled, it keeps some disk space for Windows Update, apps, temporary files, and system caches because without enough disk space Windows and applications may stop working properly.

Users installing a fresh copy of Windows 10 1903 or later, or receiving a device with the OS preinstalled, should see Reserved Storage enabled out-of-the-box. Some device manufacturers choose not to enable Reserved Storage because it reduces the available disk space to users.

Those upgrading from a previous version of Windows don’t get Reserved Storage, unless the ShippedWithReserves registry key is set to 1 before the upgrade. You can find the key under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\ReserveManager.

Windows Update and Reserved Storage

Windows Update gives priority access to Reserved Storage. Before an update, temporary files that are no longer needed in Reserved Storage are deleted and the remaining space is then given exclusively to Windows Update. If Reserved Storage still doesn’t have enough space, Windows Update can also spill into free disk space that is available to the user. On systems where disk space is severely limited, Windows Update might also prompt to attach external storage to complete the update process.

DISM updated with new Reserved Storage options

Admins are able to query the amount of space reserved and even disable Reserved Storage. The state of Reserved Storage is preserved across OS upgrades once it has been enabled or disabled using DISM. The following DISM command enables Reserved Storage for the online Windows image:

Enable or Disable Reserved Storage using PowerShell

If you don’t want to mess around with DISM, Windows 10 version 2004 supports a new PowerShell cmdlet that will let you enable or disable Reserved Storage for online images.

Continue reading “Enable Reserved Storage Using DISM or PowerShell on Windows 10”

Microsoft Announces Windows Package Manager

Microsoft is releasing an official package manager for Windows. At Build 2020, Microsoft announced the new Windows Package Manager preview, a command line tool that allows you to install your favorite tools quickly and easily. The repository of packages is open source, you can find them here.

Once you run he proper command from PowerShell, you can search for and install software to your Windows machine from the PowerShell command line.

To find a package, use “search”:

PS C:\WINDOWS\system32> winget search

You can also easily install software:

PS C:\WINDOWS\system32> winget install vscode

It is just that easy.

Enable or Disable Windows Defender Firewall with PowerShell

The Windows Defender Firewall with Advanced Security is an important feature of Windows 10 that should be enabled to help protect your computer. Many businesses disable the built-in Windows firewall to prevent it from interfering with any internal processes, but that is an extremely rare problem.

I recommend you enable the Windows Defender Firewall with Advanced Security, and use the features available to help properly secure the user’s Windows 10 endpoint.

To enable the Windows Defender Firewall with Advanced Security using PowerShell:

Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled True

To configure the firewall to configure the “default” behavior:

Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow –NotifyOnListen True -AllowUnicastResponseToMulticast True –LogFileName %SystemRoot%\System32\LogFiles\Firewall\DefenderFirewall.log

To disable the Windows Defender Firewall with Advanced Security using PowerShell:

Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False

My advice is to learn how to properly configure the Windows 10 firewall so you can use if to better secure the Windows 10 endpoint.

Using PowerShell to Manage SQL Server Audits

PowerShell is a power scripting tool that can also be used to manage your SQL Server audits. In this article by Colleen Morrow we learn some of the advanced techniques. You can also start at the beginning here.

Creating an Audit Object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\MyServerD$Audits'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

1
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

Continue reading “Using PowerShell to Manage SQL Server Audits”

PowerShell Empire

Powershell Empire is described as “a pure PowerShell post-exploitation agent built on cryptologically-secure communications and a flexible architecture. Empire implements the ability to run PowerShell agents without needing powershell.exe, rapidly deployable post-exploitation modules ranging from key loggers to Mimikatz, and adaptable communications to evade network detection, all wrapped up in a usability-focused framework.”

This powerful tool premiered at BSidesLV in 2015 and is used by penetration tests and hackers all over the world.

“PowerShell Empire is a unique attack framework in that its capabilities and behaviors closely resemble those used by current nation state advanced persistent threat actors,” a 2018 SANS white paper on Empire said.

Continue reading “PowerShell Empire”

PowerShell Reconnaissance Using PSnmap

When you want to perform some basic reconnaissance using PowerShell, one of the first steps is to run a command to help identify other devices on the same network. Once connected to one device on the network, open a PowerShell prompt and run the following commands.

Open Powershell as Administrator and run Install-Module -Name PSnmap

Then run: psnmap -Comp 192.168.0.1/24 -Dns -Port 23, 21, 445, 3389, 25 to get some information to list every device that responds. You should scan the same network that your connected device is currently using. The results of online computers and open ports should look something like the image below:

You can get more information on PSnmap here.

This will give you basic information about other devices on the same network, which tells you potential targets for your next step: Gaining Access.

May PowerShell: Log PowerShell Events

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

On a system that you feel is at risk of malicious PowerShell attack, you should take advantige of the built-in PowerShell logging to track potential unauthorized PowerShell script execution. It may not be possible on all endpoints, but there are many opportunties to track PowerShell use.

Module Logging

Module logging records pipeline execution details as PowerShell executes, including variable initialization and command invocations. Module logging will record portions of scripts, some de-obfuscated code, and some data formatted for output. This logging will capture some details missed by other PowerShell logging sources, though it may not reliably capture the commands executed. Module logging has been available since PowerShell 3.0. Module logging events are written to Event ID (EID) 4103.

Continue reading “May PowerShell: Log PowerShell Events”

May PowerShell: Remove PowerShell V.2

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

Microsoft recommends you no longer use PowerShell V.2 for security reasons, but it is probably installed on your computers.

Microsoft has done a great job of recently adding powerful new security features in PowerShell. It is also obvious that the security features integrated in the latest versions of PowerShell do not apply to the older versions of PowerShell, which makes its use by malicious attackers to target PowerShell v.2 a risk to your computers. The older version of PowerShell does not have native logging capabilities, it remains undetected, and offers stealth in malicious operations so it is often used for lateral movement and persistence techniques.

For these reasons Microsoft decided that PowerShell v.2 is deprecated from the more recent versions of Windows, so it is also highly recommend to check and remove PowerShell v.2 from your environment.

You can check whether Windows PowerShell 2.0 is installed by running the following (as an administrator). Continue reading “May PowerShell: Remove PowerShell V.2”

May PowerShell: Auditing Office 365 using PowerShell and Hawk

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

Hawk is a Powershell based tool for gathering information related to O365 intrusions and potential Breaches. You can simply use the Hawk Powershell Script that makes use of Exchange Online and Azure powershell scripts to generate the auditing reports you may need when investigating a suspected breach.

Reports Include:

  • CAS Mailbox Info
  • Azure Audit Logs (writes AzureActiveDirectoryAccountLogon: User login events with IP addresses)
  • Mailbox Audit Report (Mailbox login report with delegate and admin actions)
  • User Mailbox Forwarding Information
  • User Inbox Rules Information
  • Mailbox Info
  • Mailbox Statistics
  • Azure Authentication logs report (All authentication activity for the user in RAW + Readable form)

Azure AD reports rely on AAD P1 and P2 licenses, make sure you have the required licenses assigned.

GitHub Repository: https://github.com/Canthv0/hawk

A good starting place is the “Start-HawkTenantInvestigation”, this will run all the tenant based cmdlets and provide a collection of data to start with. Once this data has been reviewed if there are specific user(s) that more information should be gathered on “Start-HawkUserInvestigation”, which will gather all the User specific information for a specific user.

May PowerShell: Scripts for listing all SQL Server Databases and Objects using PowerShell

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

This powerful script lists all objects in an instance and scripts them into a network folder, by date and instance, so you can keep a record of the objects.

Installing PowerShell the SqlServer module:

Install-Module -Name SqlServer

If there are previous versions of the SqlServer module on the computer, you may be able to use Update-Module, or provide the -AllowClobber parameter:

Install-Module -Name SqlServer -AllowClobber

This article by Angel Gomez gives you the script and some information on how to use it.

Continue reading “May PowerShell: Scripts for listing all SQL Server Databases and Objects using PowerShell”

Using PowerShell to Manage SQL Server Audits

PowerShell is a power scripting tool that can also be used to manage your SQL Server audits. In this article by Colleen Morrow we learn some of the advanced techniques. You can also start at the beginning here.

Creating an Audit Object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\\MyServer\D$\Audits\'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

1
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

Once we’re connected to SQL Server, we can create a new audit class object and start assigning attribute values. Here, we’re setting the destination to a file, and the file path to our $auditDir variable. We set the maximum number of rollover files, the queue delay, etc.

Continue reading “Using PowerShell to Manage SQL Server Audits”

Scripts for listing all SQL Server Databases and Objects using PowerShell

This powerful script lists all objects in an instance and scripts them into a network folder, by date and instance, so you can keep a record of the objects.

This article by Angel Gomez gives you the script and some information on how to use it.

Using PowerShell and SQL Server Agent we can create a scheduled job that runs each day and produces scripts for all objects in all databases for an instance of SQL Server and that is what this tip does.

Here is the PowerShell code to generate a script for each object in the database.  The below code will script out table definitions, stored procedures, views, user defined functions and triggers.  This will generate scripts for every database in the SQL Server instance.

You need to supply the SQL Server name and the path where the objects are to be created.

$date_ = (date -f yyyyMMdd)$ServerName = "." #If you have a named instance, you should put the name. $path = "c:\SQL_Server\Backup\Objects\"+"$date_" [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. $ExcludeSchemas = @("sys","Information_Schema")$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') $dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.foreach ($db in $dbs){   $dbname = "$db".replace("[","").replace("]","")   $dbpath = "$path"+ "\"+"$dbname" + "\"if ( !(Test-Path $dbpath))   {$null=new-item -type directory -name "$dbname"-path "$path"}foreach ($Type in $IncludeTypes)   {  $objpath = "$dbpath" + "$Type" + "\" if ( !(Test-Path $objpath))   {$null=new-item -type directory -name "$Type"-path "$dbpath"}  foreach ($objs in $db.$Type)  { If ($ExcludeSchemas -notcontains $objs.Schema )   {   $ObjName = "$objs".replace("[","").replace("]","") $OutFile = "$objpath" + "$ObjName" + ".sql"   $objs.Script($so)+"GO" | out-File $OutFile  }  }   } }

You can read the entire article here.

Your 10 Favorite SeniorDBA Blog Posts of 2016

Here’s the top 10 items you clicked on the most in 2016:

  1. 20 SQL Server DBA Interview Questions – Some sample questions you might be asked about in an interview for a DBA position. Also used by the hiring managers to make sure they have some relevant questions during your interview.
  2. Comparing SQL Server vs. Oracle License Cost – Looking at the difference in cost between SQL Server and Oracle.
  3. SQL Server and Windows 10 Compatibility – Quick instructions on using SQL Server Management Studio on a Windows 10 desktop.
  4. SQL Server Trace Flag List – List of available trace flags for use in SQL Server.
  5. Using PowerShell to Manage Audits – Using PowerShell as a powerful scripting tool that can manage your SQL Server audits.
  6. SQL Server TCP and UDP Ports – This post lists the ports and protocols required to communicate with an instance of SQL Server. This can be very helpful if you need to create or manage firewall rules blocking unauthorized access.
  7. SQL Server End-Of-Life Schedule – This is a useful reference if you need to know if your version of SQL Server is still supported, and when it will no longer be supported.
  8. Common Database Design Mistakes – I short list of common database design mistakes, and how to avoid them in your environment.
  9. Free eBooks from Microsoft Blog – A list of free ebooks from Microsoft, in a wide range of technical topics from SQL Server, Windows, Azure, etc.
  10. Reset Password and Disable SQL Server SA Account – With auditors wanting all user accounts to have passwords that change at least every 90 days, or the account must be disabled, this provides some guidance on how to make that work with the SA account.

Happy New Year! I hope you will continue to visit this site for helpful information on a variety of topics.

Scripts for saving all SQL Server Databases Objects using PowerShell

Saving all the objects in a server instance into scripts on a network folder is a useful utility. In this article by Angel Gomez we see his PowerShell script that will allow you to schedule the automatic creation of T-SQL scripts to a network folder on a scheduled basis.

Using PowerShell and SQL Server Agent we can create a scheduled job that runs each day and produces scripts for all objects in all databases for an instance of SQL Server and that is what this tip does.

Here is the PowerShell code to generate a script for each object in the database.  The below code will script out table definitions, stored procedures, views, user defined functions and triggers.  This will generate scripts for every database in the SQL Server instance.

You need to supply the SQL Server name and the path where the objects are to be created.

$date_ = (date -f yyyyMMdd)$ServerName = "." #If you have a named instance, you should put the name. $path = "c:\SQL_Server\Backup\Objects\"+"$date_" [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. $ExcludeSchemas = @("sys","Information_Schema")$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') $dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.foreach ($db in $dbs){   $dbname = "$db".replace("[","").replace("]","")   $dbpath = "$path"+ "\"+"$dbname" + "\"if ( !(Test-Path $dbpath))   {$null=new-item -type directory -name "$dbname"-path "$path"}foreach ($Type in $IncludeTypes)   {  $objpath = "$dbpath" + "$Type" + "\" if ( !(Test-Path $objpath))   {$null=new-item -type directory -name "$Type"-path "$dbpath"}  foreach ($objs in $db.$Type)  { If ($ExcludeSchemas -notcontains $objs.Schema )   {   $ObjName = "$objs".replace("[","").replace("]","") $OutFile = "$objpath" + "$ObjName" + ".sql"   $objs.Script($so)+"GO" | out-File $OutFile  }  }   } }

When the PowerShell code is run, it will create the folder and all of the subfolders for the objects like the following. Each folder will contain the objects for that database.

You can read the entire article here.

AzureAD PowerShell V2.0 is now GA

Microsoft has announced that PowerShell Azure AD v2.0 cmdlets are now generally available. They updated the names of all cmdlets to conform with the Azure PowerShell naming conventions. Since they’re publishing a new module for these cmdlets, the name of the module has changed as well: the existing module’s name was “MSOL”, the new module is now called “AzureAD”.

Azure Active Directory V2 PowerShell Cmdlets
  • Add-AzureADAdministrativeUnitMember – Add an administrativeUnit member
  • Add-AzureADApplicationOwner – Add an owner to an application
  • Add-AzureADDeviceRegisteredOwner – Add an owner to a device
  • Add-AzureADDeviceRegisteredUser – Add a user to a device.
  • Add-AzureADDirectoryRoleMember – Add a member to a directory role
  • Add-AzureADGroupMember – Add a member to a group
  • Add-AzureADGroupOwner – Add an owner to a group
  • Add-AzureADScopedRoleMembership – Add a scoped role
  • Add-AzureADServicePrincipalOwner – Add an owner to a service principal
  • Confirm-AzureADDomain – Validate the ownership of the domain.
  • Connect-AzureAD – Connect with an authenticated account to use Azure Active Directory cmdlet requests.
  • Disconnect-AzureAD – Disconnects the current session from an Azure AD tenant
  • Enable-AzureADDirectoryRole – Activates an existing directory role in Azure Active Directory
  • Get-AzureADAdministrativeUnit – Get an Administrative Unit by objectId
  • Get-AzureADAdministrativeUnitMember – Get administrativeUnit members.
  • Get-AzureADApplication – Get an application by objectId
  • Get-AzureADApplicationExtensionProperty – Get group extension properties
  • Get-AzureADApplicationKeyCredential – Get an application’s key credentials
  • Get-AzureADApplicationOwner – Get owners of an application.
  • Get-AzureADApplicationPasswordCredential – Get and application’s password credentials
  • Get-AzureADApplicationPolicy
  • Get-AzureADContact – Retrieves a specific contact from Azure Active Directory
  • Get-AzureADContactDirectReport – Get the contact’s direct reports.
  • Get-AzureADContactManager – Retrieves the manager of a contact from Azure Active Directory
  • Get-AzureADContactMembership – Get contact memberships.
  • Get-AzureADContract – Retrieves a specific contract from Azure Active Directory
  • Get-AzureADDevice – Retrieves a specific device from Azure Active Directory
  • Get-AzureADDeviceRegisteredOwner – Get users that are registered as owner on the device.
  • Get-AzureADDeviceRegisteredUser – Get users that are marked as users on the device.
  • Get-AzureADDirectoryRole – Retrieves a specific directory role from Azure Active Directory
  • Get-AzureADDirectoryRoleMember – Get the members of a directory role.
  • Get-AzureADDirectoryRoleTemplate – Retrieves a list of directory role templates in Azure Active Directory
  • Get-AzureADDirectorySetting – Retrieves a directory setting from Azure Active Directory.
  • Get-AzureADDirectorySettingTemplate – Retrieves directory setting template from Azure Active Directory.
  • Get-AzureADDomain – Get an domain by objectId
  • Get-AzureADExtensionProperty – A collection that contains the extension properties registered with the directory.
  • Get-AzureADGroup – Get a group by objectId
  • Get-AzureADExtensionProperty – Gets extension properties registered with Azure AD.
  • Get-AzureADGroupAppRoleAssignment – Get group application role assignments.
  • Get-AzureADGroupMember – Get members of a group.
  • Get-AzureADGroupOwner – Get owners of a group.
  • Get-AzureADMSGroup – Retrieves a group from the directory
  • Get-AzureADMSGroup – Gets information about groups in Azure AD.
  • Get-AzureADOAuth2PermissionGrant – Get a list of all oAuth2PermissionGrants granted by users within the directory.
  • Get-AzureADObjectSetting – Retrieves a object setting from Azure Active Directory.
  • Get-AzureADPolicy
  • Get-AzureADPolicyAppliedObject
  • Get-AzureADScopedRoleMembership
  • Get-AzureADServiceAppRoleAssignment – Get service principal application role assignments.
  • Get-AzureADServiceConfigurationRecord – Get serviceConfigurationRecords
  • Get-AzureADServicePrincipal – Get a service principal by objectId
  • Get-AzureADServicePrincipalCreatedObject – Get objects created by the service principal.
  • Get-AzureADServicePrincipalKeyCredential – Get a service principal’s key credentials
  • Get-AzureADServicePrincipalMembership – Get service principal memberships.
  • Get-AzureADServicePrincipalOAuth2PermissionGrant – Get the list of the oAuth2PermissionGrants that a user granted this service principal.
  • Get-AzureADServicePrincipalOwnedObject – Get objects owned by the service principal.
  • Get-AzureADServicePrincipalOwner – Get owners of a service principal.
  • Get-AzureADServicePrincipalPasswordCredential – Get a service principal’s password credentials
  • Get-AzureADServicePrincipalPolicy
  • Get-AzureADSubscribedSku – Retrieves a list of subscribed SKUs (subscriptions) to Microsoft services.
  • Get-AzureADTenantDetail – Retrieves the details of a tenant in Azure Active Directory
  • Get-AzureADTrustedCertificateAuthority
  • Get-AzureADUser – Retrieves a specific user from Azure Active Directory
  • Get-AzureADUserAppRoleAssignment – Get user application role assignments.
  • Get-AzureADUserCreatedObject – Get objects created by the user.
  • Get-AzureADUserDirectReport – Get the user’s direct reports.
  • Get-AzureADUserExtension
  • Get-AzureADUserManager – Retrieves the manager of a user from Azure Active Directory
  • Get-AzureADUserMembership – Get user memberships.
  • Get-AzureADUserOAuth2PermissionGrant – Get the list of the oAuth2PermissionGrants that the user granted applications.
  • Get-AzureADUserOwnedDevice – Get registered devices owned by the user.
  • Get-AzureADUserOwnedObject – Get objects owned by the user.
  • Get-AzureADUserRegisteredDevice – Get registered devices registered by the user.
  • Get-AzureADVerificationDnsRecord – Get verificationDnsRecords
  • New-AzureADAdministrativeUnit – Create a new administrativeUnit in Azure Active Directory
  • New-AzureADApplication – Create a new application in Azure Active Directory
  • New-AzureADApplicationExtensionProperty – Create application extension property
  • New-AzureADApplicationKeyCredential – Create a new key credential for an application
  • New-AzureADApplicationPasswordCredential – Create a new password credential for an application
  • New-AzureADDevice – Create a new device in Azure Active Directory
  • New-AzureADDirectorySetting – Creates a directory settings object in Azure Active Directory.
  • New-AzureADDomain – Create a new domain in Azure Active Directory
  • New-AzureADGroup – Create a new group in Azure Active Directory
  • New-AzureADGroupAppRoleAssignment – Assign a group of users to an application role.
  • New-AzureADMSGroup
  • New-AzureADMSInvitation
  • New-AzureADMSGroup – Creates an Azure AD group.
  • New-AzureADObjectSetting – Creates a settings object in Azure Active Directory.
  • New-AzureADPolicy
  • New-AzureADServiceAppRoleAssignment – Assign a service principal to an application role.
  • New-AzureADServicePrincipal – Create a new application in Azure Active Directory
  • New-AzureADServicePrincipalKeyCredential – Create a new key credential for a service principal
  • New-AzureADServicePrincipalPasswordCredential – Create a new password credential for a service principal
  • New-AzureADTrustedCertificateAuthority
  • New-AzureADUser – Create a new user in Azure Active Directory
  • New-AzureADUserAppRoleAssignment – Assign a user to an application role.
  • Remove-AzureADAdministrativeUnit – Delete an administrativeUnit by objectId.
  • Remove-AzureADAdministrativeUnitMember – Removes an administrativeUnit member.
  • Remove-AzureADApplication – Delete an application by objectId.
  • Remove-AzureADApplicationExtensionProperty – Delete an application extension property.
  • Remove-AzureADApplicationKeyCredential – Remove a key credential from an application
  • Remove-AzureADApplicationOwner – Removes an owner from an application.
  • Remove-AzureADApplicationPasswordCredential – Remove a password credential from an application
  • Remove-AzureADContact – Deletes a specific contact in Azure Active Directory
  • Remove-AzureADContactManager – Deletes the contact’s manager in Azure Active Directory
  • Remove-AzureADDevice – Deletes a specific device in Azure Active Directory
  • Remove-AzureADDeviceRegisteredOwner – Removes an owner from a device.
  • Remove-AzureADDeviceRegisteredUser – Removes a user from a device.
  • Remove-AzureADDirectoryRoleMember – Removes a specific member from a directory role.
  • Remove-AzureADDirectorySetting – Deletes a directory setting in Azure Active Directory.
  • Remove-AzureADDomain – Delete an domain by objectId.
  • Remove-AzureADGroup – Delete a group by objectId.
  • Remove-AzureADGroupAppRoleAssignment – Delete a group application role assignment.
  • Remove-AzureADGroupMember – Removes a member from a group.
  • Remove-AzureADGroupOwner – Removes an owner from a group.
  • Remove-AzureADMSGroup – This cmdlet removes a group from the directory
  • Remove-AzureADMSGroup – Removes an Azure AD group.
  • Remove-AzureADOAuth2PermissionGrant – Delete an oAuth2PermissionGrant.
  • Remove-AzureADObjectSetting – Deletes settings in Azure Active Directory.
  • Remove-AzureADPolicy
  • Remove-AzureADScopedRoleMembership
  • Remove-AzureADServiceAppRoleAssignment – Delete a service principal application role assignment.
  • Remove-AzureADServicePrincipal – Delete an application by objectId.
  • Remove-AzureADServicePrincipalKeyCredential – Remove a key credential from a service principal
  • Remove-AzureADServicePrincipalOwner – Removes an owner from a service principal.
  • Remove-AzureADServicePrincipalPasswordCredential – Remove a password from a service principal
  • Remove-AzureADTrustedCertificateAuthority
  • Remove-AzureADUser – Deletes a specific user in Azure Active Directory
  • Remove-AzureADUserAppRoleAssignment – Delete a user application role assignment.
  • Remove-AzureADUserExtension
  • Remove-AzureADUserManager – Deletes the user’s manager in Azure Active Directory
  • Revoke-AzureADSignedInUserAllRefreshToken – Invalidates all of the currently signed in user’s refresh tokens issued to applications (as well as session cookies in a user’s browser), by resetting the refreshTokensValidFromDateTime user property to the current date-time.
  • Revoke-AzureADUserAllRefreshToken – Invalidates all of the user’s refresh tokens issued to applications (as well as session cookies in a user’s browser), by resetting the refreshTokensValidFromDateTime user property to the current date-time.
  • Revoke-AzureADSignedInUserAllRefreshToken – Invalidates the refresh tokens issued to applications for the current user.
  • Revoke-AzureADUserAllRefreshToken – Invalidates the refresh tokens issued to applications for a user.
  • Select-AzureADGroupIdsContactIsMemberOf – From a list of groups Ids select those that the contact is a member of.
  • Select-AzureADGroupIdsGroupIsMemberOf – From a list of groups Ids select those that the group is a member of.
  • Select-AzureADGroupIdsServicePrincipalIsMemberOf – From a list of groups Ids select those that the service principal is a member of.
  • Select-AzureADGroupIdsUserIsMemberOf – From a list of groups Ids select those that the user is a member of.
  • Set-AzureADAdministrativeUnit – Updates a specific administrativeUnit in Azure Active Directory
  • Set-AzureADApplication – Updates a specific application in Azure Active Directory
  • Set-AzureADContact – Updates a specific contact in Azure Active Directory
  • Set-AzureADContactManager – Updates the contact’s manager in Azure Active Directory
  • Set-AzureADDevice – Updates a specific device in Azure Active Directory
  • Set-AzureADDirectorySetting – Updates a directory setting in Azure Active Directory.
  • Set-AzureADDomain – Updates a specific domain in Azure Active Directory
  • Set-AzureADGroup – Updates a specific group in Azure Active Directory
  • Set-AzureADMSGroup – Set a group’s attributes
  • Set-AzureADMSGroup – Changes attribute values on an Azure AD group.
  • Set-AzureADObjectSetting – Updates settings in Azure Active Directory.
  • Set-AzureADPolicy
  • Set-AzureADServicePrincipal – Updates a service principal in Azure Active Directory
  • Set-AzureADTrustedCertificateAuthority
  • Set-AzureADUser – Updates a specific user in Azure Active Directory
  • Set-AzureADUserExtension
  • Set-AzureADUserLicense – Add and remove one or more licenses for a Microsoft online service to the list of assigned licenses for the user.
  • Set-AzureADUserManager – Updates the user’s manager in Azure Active Directory
  • Set-AzureADUserPassword – Sets the password of a user in Azure AD
  • Update-AzureADSignedInUserPassword – Updates the password for the signed in user in Azure AD
Example

Update-AzureADSignedInUserPassword – Update a password

PS C:\>Update-AzureADSignedInUserPassword -CurrentPassword $CurrentPassword -NewPassword $NewPassword

This command updates the password for the signed-in user.

Query SQL Server Using PowerShell

The ability for you to query a SQL Server instance from PowerShell might actually be helpful, especially if you don’t have access to an instance of SQL Server Management Studio.

In this article by Grant Fritchey we see a simple way to connect to and execute a SQL Server query using PowerShell.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null# Get the connection$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString = 'Server=WIN-3SRG45GBF97\DOJO;Database=WideWorldImporters;trusted_connection=true'# Retrieve test data$BillToCustomerCmd = New-Object System.Data.SqlClient.SqlCommand$BillToCustomerCmd.CommandText = "SELECT DISTINCT i.BillToCustomerIDFROM Sales.Invoices as i;"$BillToCustomerCmd.Connection = $SqlConnection$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlAdapter.SelectCommand = $BillToCustomerCmd$BillToCustomerList = New-Object System.Data.DataSet$SqlAdapter.Fill($BillToCustomerList)# Set up test query$SQLCmd = New-Object System.Data.SqlClient.SqlCommand$SQLCmd.Connection = $SqlConnection$SQLCmd.CommandText = "DECLARE @sqlquery NVARCHAR(MAX);SET @sqlquery= N'SELECT si.StockItemName,i.InvoiceDate,i.SalespersonPersonIDFROM Sales.Invoices AS iJOIN Sales.InvoiceLines AS ilON il.InvoiceID = i.InvoiceIDJOIN Warehouse.StockItems AS siON si.StockItemID = il.StockItemIDWHERE i.BillToCustomerID = @BillToCustomerID;';DECLARE @parms NVARCHAR(MAX);SET @parms = '@BillToCustomerID int';EXEC sys.sp_executesql @stmt = @sqlquery,@params = @parms,@BillToCustomerID = @btc;"$SQLCmd.Parameters.Add("@btc",[System.Data.SqlDbType]"Int")# Run the testsforeach($row in $BillToCustomerList.Tables[0]){$SqlConnection.Open()$SQLCmd.Parameters["@btc"].Value = $row[0]$SQLCmd.ExecuteNonQuery() | Out-Null$sqlconnection.Close()}

Read the entire article here.

Managing Active Directory with PowerShell

There are plenty of maintenance tasks that take a significant amount of time to manually perform. They are often avoided or left undone because there are usually more important tasks that must be completed using the limited resources available to the IT technicians.

In this article by Luca Sturlese, we see how many of these maintenance tasks can be completed using PowerShell scripts.

Inactive Users:

#requires -version 2<#.SYNOPSIS  Find and manage inactive Active Directory users..DESCRIPTION  This script allows you to specify the criteria required to identify inactive users within your AD environment. This script also allows  for the management of found users. Management of users includes one or more of the following options:- Reporting- Disabling Users- Deleting Users.PARAMETER SearchScope  Optional. Determines the search scope of what type of user you would like to include in the inactive user search. Options available are:   - All: Default option. All user types including all standard users, service accounts and never logged on accounts.   - OnlyInactiveUsers  : Only standard user accounts. This option excludes service accounts and never logged on accounts.   - OnlyServiceAccounts: Only server accounts. This option excludes standard user accounts and never logged on accounts.   - OnlyNeverLoggedOn  : Only never logged on accounts. This option excludes standard user accounts and service accounts.   - AllExceptServiceAccounts   : All user account types excluding service accounts.   - AllExceptNeverLoggedOn : All user account types excluding never logged on accounts.   Note: If not specified, the default search scope is All (i.e. all user accounts, service accounts and never logged on accounts)..PARAMETER DaysInactive  Optional. The number of days a user account hasn't logged into the domain for in order to classify it as inactive. The default option is 90  days, which means any user account that hasn't logged into the domain for 90 days or more is considered inactive and therefore managed by this  script..PARAMETER ServiceAccountIdentifier  Optional. The username prefix or postfix that is used to indetify a service account from a standard user account. The default option is 'svc'.  Determining whether an account is a service account is useful in order to be able to include or exclude service accounts from the search scope.  Note: For more information see the help information on the parameter SearchScope.   Example: All accounts with the prefix or postfix of svc (e.g. svc-MyAccount or MyAccount-svc) are identified as service accounts and can  therefore be included or exclueded from the search scope..PARAMETER ReportFilePath  Optional. This is the location where the report of inactive users will be saved to. If this parameter is not specified, the default location the  report is saved to is C:\InactiveUsers.csv.  Note: When specifying the file path, you MUST include the file name with the extension of .csv. Example: 'C:\MyReport.csv'..PARAMETER DisableUsers  Optional. If this parameter is specified, this script will disable the inactive users found based on the search scope specified.  Note: If this parameter is not specified, then by default this script WILL NOT disable any inactive users found..PARAMETER DeleteUsers  Optional. If this parameter is specified, this script will delete the inactive users found based on the search scope specified.  Note: If this parameter is not specified, then by default this script WILL NOT delete any inactive users found..INPUTS  None..OUTPUTS  Report of inactive users found. See ReportFilePath parameter for more information..NOTES  Version:1.0  Author: Luca Sturlese  Creation Date:  16.07.2016  Purpose/Change: Initial script development.EXAMPLE  Execution of script using default parameters. Default execution performs reporting of inactive AD user only, not disabling or deleting any accounts.  By default the report is saved in C:\.  .\Find-ADInactiveUsers.ps1.EXAMPLE  Reporting and disabling all user accounts, except never logged on accounts. Storing the report in C:\Reports.  .\Find-ADInactiveUsers.ps1 -SeachScope AllExceptNeverLoggedOn -ReportFilePath 'C:\Reports\DisabledUsers.csv' -DisableUsers.EXAMPLE  Find & delete all inactive users (not service accounts) that haven't logged in for the last 30 days. Include never logged on accounts in this search.  .\Find-ADInactiveUsers.ps1 -SeachScope AllExceptServiceAccounts -DaysInactive 30 -DeleteUsers.EXAMPLE  Delete all user accounts that have never been logged into. Store the report in C:\Reports.  .\Find-ADInactiveUsers.ps1 -SeachScope OnlyNeverLoggedOn -ReportFilePath 'C:\Reports\NotLoggedOnAccounts.csv' -DeleteUsers#>#---------------------------------------------------------[Script Parameters]------------------------------------------------------Param (  [Parameter(Mandatory = $false)][string][ValidateSet('All', 'OnlyInactiveUsers', 'OnlyServiceAccounts', 'OnlyNeverLoggedOn', 'AllExceptServiceAccounts', 'AllExceptNeverLoggedOn')]$SearchScope = 'All',  [Parameter(Mandatory = $false)][int]$DaysInactive = 90,  [Parameter(Mandatory = $false)][string]$ServiceAccountIdentifier = 'svc',  [Parameter(Mandatory = $false)][string]$ReportFilePath = 'C:\InactiveUsers.csv',  [Parameter(Mandatory = $false)][switch]$DisableUsers = $false,  [Parameter(Mandatory = $false)][switch]$DeleteUsers = $false)#---------------------------------------------------------[Initialisations]--------------------------------------------------------#Set Error Action to Silently Continue$ErrorActionPreference = 'SilentlyContinue'#Import Modules & Snap-insImport-Module ActiveDirectory#----------------------------------------------------------[Declarations]----------------------------------------------------------#Set Inactive Date:$InactiveDate = (Get-Date).Adddays(-($DaysInactive))#-----------------------------------------------------------[Functions]------------------------------------------------------------Function Find-Accounts {  Param ()  Begin {Write-Host "Finding inactive user accounts based on search scope specified [$SearchScope]..."  }  Process {Try {  #Set Service Account Identifier  $ServiceAccountIdentifier = '*' + $ServiceAccountIdentifier + '*'  Switch ($SearchScope) {'All' {  $global:Results = Get-ADUser -Filter { (LastLogonDate -lt $InactiveDate -or LastLogonDate -notlike "*") -and (Enabled -eq $true) } -Properties LastLogonDate | Select-Object @{ Name="Username"; Expression = {$_.SamAccountName} }, Name, LastLogonDate, DistinguishedName}'OnlyInactiveUsers' {  $global:Results = Get-ADUser -Filter { LastLogonDate -lt $InactiveDate -and Enabled -eq $true -and SamAccountName -notlike $ServiceAccountIdentifier } -Properties LastLogonDate | Select-Object @{ Name="Username"; Expression = {$_.SamAccountName} }, Name, LastLogonDate, DistinguishedName}'OnlyServiceAccounts' {  $global:Results = Get-ADUser -Filter { LastLogonDate -lt $InactiveDate -and Enabled -eq $true -and SamAccountName -like $ServiceAccountIdentifier } -Properties LastLogonDate | Select-Object @{ Name="Username"; Expression = {$_.SamAccountName} }, Name, LastLogonDate, DistinguishedName}'OnlyNeverLoggedOn' {  $global:Results = Get-ADUser -Filter { LastLogonDate -notlike "*" -and Enabled -eq $true } -Properties LastLogonDate | Select-Object @{ Name="Username"; Expression = {$_.SamAccountName} }, Name, LastLogonDate, DistinguishedName}'AllExceptServiceAccounts' {  $global:Results = Get-ADUser -Filter { LastLogonDate -lt $InactiveDate -and Enabled -eq $true -and SamAccountName -notlike $ServiceAccountIdentifier -or LastLogonDate -notlike "*" } -Properties LastLogonDate | Select-Object @{ Name="Username"; Expression = {$_.SamAccountName} }, Name, LastLogonDate, DistinguishedName}'AllExceptNeverLoggedOn' {  $global:Results = Get-ADUser -Filter { LastLogonDate -lt $InactiveDate -and Enabled -eq $true } -Properties LastLogonDate | Select-Object @{ Name="Username"; Expression = {$_.SamAccountName} }, Name, LastLogonDate, DistinguishedName}Default {  Write-Host -BackgroundColor Red "Error: An unknown error occcurred. Can't determine search scope. Exiting."  Break}  }}Catch {  Write-Host -BackgroundColor Red "Error: $($_.Exception)"  Break}End {  If ($?) {Write-Host 'Completed Successfully.'Write-Host ' '  }}  }}Function Create-Report {  Param ()  Begin {Write-Host "Creating report of inactive users in specified path [$ReportFilePath]..."  }  Process {Try {  #Check file path to ensure correct  If ($ReportFilePath -notlike '*.csv') {$ReportFilePath = Join-Path -Path $ReportFilePath -ChildPath '\InactiveUsers.csv'  }  # Create CSV report  $global:Results | Export-Csv $ReportFilePath -NoTypeInformation}Catch {  Write-Host -BackgroundColor Red "Error: $($_.Exception)"  Break}  }  End {If ($?) {  Write-Host 'Completed Successfully.'  Write-Host ' '}  }}Function Disable-Accounts {  Param ()  Begin {Write-Host 'Disabling inactive users...'  }  Process {Try {  ForEach ($Item in $global:Results){Disable-ADAccount -Identity $Item.DistinguishedNameWrite-Host "$($Item.Username) - Disabled"  }}Catch {  Write-Host -BackgroundColor Red "Error: $($_.Exception)"  Break}  }  End {If ($?) {  Write-Host 'Completed Successfully.'  Write-Host ' '}  }}Function Delete-Accounts {  Param ()  Begin {Write-Host 'Deleting inactive users...'  }  Process {Try {  ForEach ($Item in $global:Results){Remove-ADUser -Identity $Item.DistinguishedName -Confirm:$falseWrite-Host "$($Item.Username) - Deleted"  }}Catch {  Write-Host -BackgroundColor Red "Error: $($_.Exception)"  Break}  }  End {If ($?) {  Write-Host 'Completed Successfully.'  Write-Host ' '}  }}#-----------------------------------------------------------[Execution]------------------------------------------------------------Find-AccountsCreate-ReportIf ($DisableUsers) {  Disable-Accounts}If ($DeleteUsers) {  Delete-Accounts}

There are several more example scripts available here.

Microsoft Replaces Command Prompt with PowerShell in Windows 10

The Command Prompt has been part of Windows for a very long time, but it is being replaced starting with Windows 10 build 14971. It looks like Microsoft is trying to make PowerShell the main command shell in their latest update to their premier operating system.

In this build, PowerShell will officially replace the traditional Command Prompt in most any way you used to run the utility. Even typing cmd in the run dialog will launch PowerShell.

PowerShell for SSRS

The SSRS team announced that a PowerShell module is available that supports SQL Server Reporting Services (SSRS) in PowerShell.

To get this new module just run this command in your PowerShell console:

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

After the module has finished downloading and you have it unpacked you can run this command to list available SSRS commands:

Get-Command -Module ReportingServicesTools
%d bloggers like this: