PowerShell Encrypted Password NET USE

I needed to copy a file from a UNC to a server today. In the past I have always used the NET USE command to connect to the share. The main problem with this is that I typically stored the password un-encrypted in the script. Lame.

I figured there has to be a better way to do this. Turns out there is. Power Shell allows you to setup an encrypted password file, then decrypt that file on the fly at run-time  I know this is not the most secure solution, but it sure beats storing the password in plain text.

I’ve read that the -Credential flag is available in PowerShell 3.0 but I was forced to use v 2.0 on the server I was working on. Well not really forced, just didn’t want to jump through all the hoops to get approval for an upgrade.

#Function used to decrypt password
function Decrypt([string]$exportfile)
$securepassword = ConvertTo-SecureString $exportfile
$helper = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password
$global:pass = $helper.GetNetworkCredential().Password

#Set some variables
# Secure file stores the password for the DOMAIN\USER Account the file was generated using this command
# 		read-host -assecurestring | convertfrom-securestring | out-file C:\scripts\SecureFile.txt
$username = "DOMAIN\USERNAME"
$sourcePath = "\\someserver\x$\somefile.ext"
$destpath = "x:\some path\"
$securefile = "C:\scripts\SecureFile.txt"

#Open up the secure file and decrypt it
$exportfile = get-content $securefile
Decrypt $exportfile

#Map the Drive
#using old school NET USE command to map the drive. This will cache the credentials so the Copy-Item command will work.
net use \\server\x$ $pass /USER:$username

#copy the File and delete the drive
# Clean up the NET USE command by deleting the connection.
Copy-Item $sourcePath -Destination $destPath
net use \\server\x$ /DELETE

Format Excel based on Column Header

I got tired of constantly formatting SSMS results sets pasted in Excel. So I wrote this Excel Macro to shoulder some the load. This macro works well for me because the result sets are typically the same (as in the column headers of the data is consistent)

Option Explicit
Sub MassFormat()
 Dim ws As Worksheet
 Dim wb As Workbook
 Dim lRow As Long, aCol As Long
 Dim aCell As Range
 Dim WS_Count As Integer
 Dim I As Integer

 For Each ws In ActiveWorkbook.Worksheets
 With ws
 Set aCell = .Rows(1).Find("column_name1")

 '~~> Check if the column with "name" is found
 If Not aCell Is Nothing Then
 aCol = aCell.Column
 .Columns(aCol).EntireColumn.NumberFormat = "#################"
 End If

Set aCell = .Rows(1).Find("column_name2")
 If Not aCell Is Nothing Then
 aCol = aCell.Column
 .Columns(aCol).EntireColumn.NumberFormat = "MM/DD/YYYY HH:MM:SS"
 End If

 End With
 Next ws
End Sub

Some Notes

  • Throw this Macro in PERSONAL.XLSB, then you can assign a hot key to it and/or customize your ribbon for a shortcut
  • Just replace colum_name1 and column_name2 with your column names
  • You will need to make sure and Copy with Headers from SSMS and then paste to Excel.
  • You can of course use this for other stuff in Excel for column specific formatting, I am just using it for tweaking SSMS results sets.
  • We are currently implementing an SSRS solution to allow users to run reports. However I think this macro will serve me well for those adhoc requests for data going forward
  • I realize that this could be a little cleaner and a little smarter. Howerver I hacked this together trying to save development time and get some big impact quickly.
  • You can easily add columns as needed by just adding extra IF blocks with the column and format of your chosing.

Disable all SQL Agent Jobs

--disable all jobs
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
--enable all jobs 
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;

--disable jobs by job name
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';