Using Saved Credentials in a Powershell Script

Purpose

Credentials stored in plain text format in a Powershell script is a security issue that needs to be avoided. This Technote describes a way to save the password in a file as a secure string which can be read back into a script for authentication.

Procedure

Check out this great post on this subject.

https://blog.kloud.com.au/2016/04/21/using-saved-credentials-securely-in-powershell-scripts/

Tips on How To Detect Encrypted Files (or The Battle against Ransomware)

The Problem

cryptolocker

Even with multiple lines of defense in place, someone in your network is inevitably going to click on that attachment or link prompting them to open that scan or document they requested (if they would briefly stop and think about it, they actually did not scan or request any document) and the next thing you know every document on the shared drive is encrypted. Welcome to the wonderful world of ransomware!

The only real solution to this problem up until now once the damage has been done is to have a good backup (and/or Volume Shadow Copy). Although I have heard stories that you can actually pay the ransom fee and you will get your files back that may work for home users, but I doubt very much that an organization with hundreds of thousands of files infected is going to get off the hook for $500.

So for now restoring from the latest backup seems to be the best line of defense against ransomware, I do however anticipate a future where things will become much harder to protect against. For example, what if a crafty hacker was able to detect documents that are important, but are only opened once a year for review?

What can you do?

  1. For Windows workstations turn on System Protection and also make sure users either do not store documents locally (that is what I recommend) or have a backup in place.
  2. From servers: As I mentioned above, make sure you have backups and/or Volume Shadow Copy turned on.
  3. Read this
  4. This Scan Tool should be useful to identify encrypted files (I have never used it, though).
  5. Use Powershell to test files for Crypto. Typically it is easy to find out who is infected by checking the owner of an encrypted file and also recording the time when the file was last modified. The script below can be modified accordingly to scan through your shared folders and find what has been encrypted. Edit the file, then Dot-Source the file to your Powershell session (http://mikepfeiffer.net/2010/06/how-to-add-functions-to-your-powershell-session/) then run it by using:
    Get-Postcrypto Path
    Example Get-Postcrypto “E:\shared\Company”

 

HOW TO Upload a .csv file to a Sharepoint 2013 List

I recently had to write a simple Powershell script to upload Work Order data from a Maintenance Department stored in .csv format to a Sharepoint 2013 list (We wrote a cool Filemaker Go app to record the Work Orders on iPads). The script first deletes the contents of the list before uploading (Function: Delete_List). What I do not like about this approach is that the ID continues to increment and does not reset to 1. I am still looking for a solution for that.

I struggled a little bit with the DateTime formatting because I was getting an error “specified cast is not valid” but converting the date field to String solved the problem (see below).

Using SQL CASE and GROUP BY in the Same SELECT Statement

According to Wikipedia SQL has been around for a loooong time, since 1970 to be precise and it shows in the syntax. However, due to the fact that it is the most effective way to retrieve information from a relational database you cannot get around knowing the ins-and-outs of this complicated language.

Fortunately though, you can get a lot of help out there on the Internet when it comes to building complicated queries. Without sites like Stackoverflow and W3Schools I would be hopelessly lost even though I have been building SQL queries for years now. There is always something new to learn.

RTagAnd just recently I was faced with a new challenge: How to use the SQL CASE statement and GROUP BY function in one query. The requirement was to create a report that aggregated sales and cost of goods sold in an Epicor ERP System with a Microsoft SQL Server database back end. I am using the powerful reporting tool R-Tag to build Pivot Tables and Pivot Graphs to generate monthly trend reports for a Client’s sales team.

Google research lead me to some good articles that lead me on the right track:

Breakdown of this SELECT statement (note that the syntax may be specific to R-Tag and will need adjusting for other development environments such as Crystal):

I want to show sales (“extended_price”) and cost of goods sold (“cogs_amount”) for the selected period (“PeriodFromDate” to “PeriodToDate”) and sales engineer (“SalesEngineer”) aggregated into two categories:

  • SalesCategory1 if “product_group_id” is equal to  ‘GROUP1’, ‘GROUP2’, ‘GROUPX’ or ‘MISC’
  • SalesCategory2 otherwise

There are three parameters to determine the start and end dates for the report and the assigned sales engineer:

  • {@PeriodFromDate}
  • {@PeriodToDate}
  •  {@SalesEngineer}

 

ICACLS Copying Permissions from One Folder to Many Folders Without using Inheritance

ICACLS is another great command line tool but it can be a bit quirky, especially if you use the wrong character set.

My challenge was to copy the permissions from one folder to several other folders without using inheritance. I could have done this manually, but because I had to process hundreds of folders this would have taken several hours and I would have gotten a headache from clicking the same buttons over and over again.

So I decided to use ICACLS instead (this was on Windows Server 2008 R2).

Here is the Step-by-Step approach. NOTE: Make sure to use Unicode when you generate the ICACLS input file.

  1. Manually set the permissions on your folder that will serve as a template for all other folders. Example: E:\shared\template
  2. Run icacls with the /save option to save the permssions:
  3. icacls E:\shared\template /save template.txt
  4. now using Powershell get-childitem I retrieved the names of all of the folders that require the new permissions.
  5. Get-childitem -Path <pathname>|select-object Name
  6. I then copy the results of this command into a Text Editor. Example:
  7. Get-childitem -Path E:\Targetfolders|select-object Name
  8. Open the file template.txt generated in the step above and copy the line that looks similar to this into your Clipboard:
    D:PAI(D;;DTSD;;;S-1-5-21-<SID>)(A;OICI;FA;;;SY)(A;OICI;FA;;;S-1-5-21-<SID>)(A;OICI;FA;;;S-1-5-21-<SID>)(A;OICI;FA;;;DA)(A;OICI;FA;;;BA)
  9. Paste this line underneath the folder names
  10. Save the file.
    NOTE: Make sure you save it in Unicode (UTF-16) format. If you do not then ICACLS will generate an error. I personally used my favorite editor Notepad++ and selected Encoding->Encode in ECS-2 Little Endian.
    The error will look something like this:
  11. Once this is complete you can run icacls /restore to copy the permissions
    icacls E:\Targetfolders\ /restore E:\templates\template.txt

NOTE: There may be a way to output the get-childitem results directly to a Unicode format file, but I did not have the time to research this.