Invio di file excel personalizzato e protetto da password via mail ad un elenco di destinatari CSV

Ciao a tutti di nuovo.

Eccoci qui di nuovo a scrivere un articolo per cercare di automatizzare le cose noiose e tediose della "vita da sysadmin" quotidiana.... :-)

Nel precedente articolo ("Creare molti utenti di dominio in una volta sola da un elenco CSV / Excel") ho spiegato come sia possibile creare molte utenze partendo da un file comma-separated values (facilmente ottenibile da Excel).

Ovviamente queste credenziali vanno poi condivise in forma SICURA con l'utente... a maggior ragione se l'utente è delegato ad amministrare qualche aspetto della nostra rete (un server, un servizio/programma, un apparato, etc...)

Ma spesso non è "comodo" aspettare di vedersi fisicamente e bloccare le attività fino a quel giorno....

Nowadays (oggigiorno) le attività ICT vengono svolte sempre di più remotamente (es. via VPN) ed è quindi essenziale trovare un modo "quasi istantaneo" di condividere le credenziali in maniera sicura.

Un metodo largamente usato è quello di inviare un file crittografato con una password complessa, che viene poi dettata al telefono una volta sola (o magari scritta nella chat di una conference call).

• Ciascun utente ottiene un file specifico con le proprie credenziali (something you own)
• Tutti gli utenti di una determinata azienda (faccio l'esempio di consulenti) possono avere la stessa password di apertura del file crittografato (something you know)

Ovviamente, dovendo io creare 15 utenze, non avevo assolutamente voglia di creare 15 file crittografati a mano e nemmeno di dettare 15 password diverse a 15 persone in 15 telefonate...

Quindi ho creato uno script Powershell che, partendo dagli stessi files del precedente articolo:

1. Apre un file excel "modello" (vedi la spiegazione in basso per uno screenshot su come è fatto)
2. Scrive le credenziali (username e password) prese dai files usati per generare le utenze.
3. Salva impostando una password sicura (quindi di fatto crittografando il file)
4. Invia una mail a ciascun utente, usando il mio account di dominio, di cui mi chiede prontamente le credenziali in maniera sicura, allegando il suo file.

NOTA DI SICUREZZA: La crittografia di excel non è sempre sicura. Microsoft Stessa ci avvisa nel suo articolo: https://support.office.com/en-us/article/protect-an-excel-file-7359d4ae-7213-4ac2-b058-f75e9311b599 

In questo caso è comunque sicuro condividere le password poichè devono essere sostituite quanto prima. Di conseguenza, anche se qualcuno riuscisse a "craccare" il file, dopo aver avuto illecito accesso al messaggio.. probabilmente non riuscirebbe a farsene un granchè perché, nel tempo usato per craccare la password, il vero destinatario l'avrà già cambiata.

Fate le vostre considerazioni in base anche alle informazioni che dovete condividere.

Ecco cosa impareremo in questo articolo:

1. Aprire e modificare un file excel con Powershell 
2. Salvare un file excel con Powershell con password (crittografato)
3. Inviare una mail con Powershell con autenticazione SMTP
4. Inviare una mail con Powershell con allegato
5. Ciclare su più file elenco CSV e far corrispondere (match) le informazioni, con Powershell 

Ecco lo script completo, da leggere considerando i CSV creati per l'articolo collegato

# definisce il percorso cartella del file di modello (e dove verranno salvate le copie)

$Model_path="C:\Users\DiegoC\Desktop\TMP\Private\SCRIPTOMANE\2018-08-27_ArticoloScriptomane_inviare_excel_personalizzati_con_password_via_mail\"

#definisce il nome del file excel da prendere come modello.

$model_fname="Modello_Consegna.xlsx"

#definisce il server da usare come server di posta in invio

$smtpSrv=""

#definisce la password con cui verranno cifrati i files excel.

$xlPwd="passwordCompl1c4t1551m4!"

# definisce il formato di salvataggio (standard di excel)

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

#becco le credenziali del mio utente per poter spedire. verranno usate per autenticarsi con il server mail

$mycredentials = Get-Credential

#importo i nomi e cognomi e le pw

$a = import-csv ($Model_path+"elencoUt.txt") -Delimiter ";" #cognome;nome;nomeSAM;descrizione

$b = import-csv ($Model_path+"elencoPW.txt") -Delimiter ";" #nomeSAM;password

# $a | select -first 1 | foreach { # si può usare questo per fare un test solo con il primo utente...

$a | foreach {

    $usrN=$_.nomeSAM

    $uPwd=($b | Where { $_.nomeSAM -eq $usrN }).password

   

    # write-host ($usrN+": "+$uPwd) # 2018-08-27 test OK

    $excel = New-Object -comobject Excel.Application

    $excel.visible = $false

    $excel.DisplayAlerts = $false

    $objWorkbook = $excel.Workbooks.Open($Model_path+$model_fname)

    $objWorksheet = $objWorkbook.Worksheets(1)

    $objWorksheet.Cells(10, 2).Value = $usrN

    $objWorksheet.Cells(10, 3).Value = $uPwd

    $Istafname=($Model_path+$_.Nome+"."+$_.Cognome+".xlsx")

    # $objWorkbook.SaveAs($Istafname,$null,$xlPwd) # errore... il secondo parametro con $null non va bene.

    $objWorkbook.SaveAs($Istafname,$xlFixedFormat,$xlPwd) # FUNZIONA!!

    $excel.Quit()

   

start-sleep -Seconds 1

    Send-MailMessage -smtpServer $smtpSrv -credential $mycredentials -from '' -to ($_.Nome+"."+$_.Cognome+"@") -subject 'Invio Credenziali i rete' -attachment $Istafname `

    -Body ("Buondi "+$_.Nome+", `ti invio le credenziali di rete.`n Per la password di apertura del file, ti prego di voler far riferimento a XXXXXX (a cui l'ho gia' detta) o di contattarmi via SMS al numero +39XXXXX, indicandomi il nome e cognome. Rispondero' con la Password via SMS.`n`nGrazie e buona giornata.`nDiego.")

}

Ed ecco le spiegazioni per ogni sezione dello script:

# definisce il percorso cartella del file di modello (e dove verranno salvate le copie)

$Model_path="C:\Users\DiegoC\Desktop\TMP\Private\SCRIPTOMANE\2018-08-27_ArticoloScriptomane_inviare_excel_personalizzati_con_password_via_mail\"

#definisce il nome del file excel da prendere come modello.

$model_fname="Modello_Consegna.xlsx"

#definisce il server da usare come server di posta in invio

$smtpSrv=""

#definisce la password con cui verranno cifrati i files excel.

$xlPwd="passwordCompl1c4t1551m4!"

# definisce il formato di salvataggio (standard di excel)

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

#becco le credenziali del mio utente per poter spedire. verranno usate per autenticarsi con il server mail

$mycredentials = Get-Credential

Qui abbiamo per ora solo definito:

• il percorso del file di modello (e dei file crittografati che verranno salvati, ciascuno con il nome dell'utente)
• il nome del modello Excel da aprire (e poi salvare con nome crittografato con password)
• il nome host o IP del server smtp
• la password che sarà usata per crittografare l'excel
• il formato dell'excel (costante standard, serve per dopo)
• le credenziali per l'accesso al server SMTP, ottenute mediante un prompt sicuro di immissione, di cui faccio uno screenshot qui:

#importo i nomi e cognomi e le pw

$a = import-csv ($Model_path+"elencoUt.txt") -Delimiter ";" #cognome;nome;nomeSAM;descrizione

$b = import-csv ($Model_path+"elencoPW.txt") -Delimiter ";" #nomeSAM;password

Qui importiamo l'elenco utenti con l'altro elenco collegato delle password

# $a | select -first 1 | foreach { # si può usare questo per fare un test solo con il primo utente...

$a | foreach {

    $usrN=$_.nomeSAM

    $uPwd=($b | Where { $_.nomeSAM -eq $usrN }).password

   

    # write-host ($usrN+": "+$uPwd) # 2018-08-27 test OK

    $excel = New-Object -comobject Excel.Application

    $excel.visible = $false

    $excel.DisplayAlerts = $false

    $objWorkbook = $excel.Workbooks.Open($Model_path+$model_fname)

    $objWorksheet = $objWorkbook.Worksheets(1)

    $objWorksheet.Cells(10, 2).Value = $usrN

    $objWorksheet.Cells(10, 3).Value = $uPwd

    $Istafname=($Model_path+$_.Nome+"."+$_.Cognome+".xlsx")

    # $objWorkbook.SaveAs($Istafname,$null,$xlPwd) # errore... il secondo parametro con $null non va bene.

    $objWorkbook.SaveAs($Istafname,$xlFixedFormat,$xlPwd) # FUNZIONA!!

    $excel.Quit()

Qui, per ogni utenza in $a:

1. recupera username e password dai due elenchi (facendo corrispondere lo username con quello in $b)
2. apri il modello excel in modalità invisibile e posizionati sul primo foglio (...Worksheets(1))
3. imposta i valori delle celle con username e password (nel mio caso il modello ha i campi username e password compilati nelle celle B10 e C10, che sono rappresentati da Cells(10,2) e Cells (10,3) perchè le coordinate sono in formato riga,colonna)
4. componi il nome file che avrà questo file specifico con il nome e cognome della persona che deve riceverlo
5. salva il file con il nome creato e utilizzando la password definita in precedenza.

   

start-sleep -Seconds 1

    Send-MailMessage -smtpServer $smtpSrv -credential $mycredentials -from '' -to ($_.Nome+"."+$_.Cognome+"@") -subject 'Invio Credenziali i rete' -attachment $Istafname `

    -Body ("Buondi "+$_.Nome+", `ti invio le credenziali di rete.`n Per la password di apertura del file, ti prego di voler far riferimento a XXXXXX (a cui l'ho gia' detta) o di contattarmi via SMS al numero +39XXXXX, indicandomi il nome e cognome. Rispondero' con la Password via SMS.`n`nGrazie e buona giornata.`nDiego.")

}

• aspetta 1 secondo (necessario perchè altrimenti il send-mailmessage potrebbe trovare il file ancora aperto)
• invia la mail con 
• i parametri specificati all'inizio dello script
• le credenziali fornite in modo sicuro allo start dello script (con get-credential)
• email del destinatario composta da nome.cognome@dominio (presi dagli elenchi)
• l'allegato excel crittografato contenente le credenziali.

Creare molti utenti di dominio in una volta sola da un elenco CSV / Excel

Talvolta mi trovo a dover creare o modificare molti utenti in una sola volta e… lo sapete come sono fatto: mi piace farlo con uno script.

Powershell (il modulo activedirectory di amministrazione) offre moltissimi comandi utili e pronti da usare, oltre alla sua sintassi veramente semplice…

Ecco quindi di seguito come creare un set di utenti in un unico comando, partendo da un elenco CSV che potete facilmente creare da un Excel xlsx oppure da un elenco di nomi e cognomi che magari avete ricevuto via mail.

Riassumo la metodologia utilizzata

1. Creare un file CSV degli utenti con i dettagli.
2. Creare un file CSV con nome utente e nuova password
3. Utilizzare powershell per importare i files CSV in oggetti
4. Lanciare lo script per creare le utenze
5. Lanciare lo script per cambiare le password e riabilitare le utenze

...e cosa impareremo leggendo questo articolo:

1. Creare una serie di utenti di dominio da un elenco CSV (comma separated values)
2. Cambiare la password a più utenti Active Directory da un elenco CSV
3. Importare un elenco CSV in una variabile Powershell
4. Importare il modulo di amministrazione di Active Directory in Powershell
5. Alcune regular expression con Notepad++

... ed ora spiego tutto:

1.    Creare un file CSV degli utenti con i dettagli

Partiamo dalla “base dati”: avremo bisogno di un elenco che ci dica al minimo nome, cognome e nome utente da creare…

cognome;nome;nomeSAM;descrizione

Rossi;Mario;MarioR_cons;"Questo testo sarà in descrizione";

Bianchi;Guido;GuidoB_cons;"Questo testo sarà in descrizione";

Verdi;Giovanni;GiovanniV_cons;"Questo testo sarà in descrizione";

Castelli;Diego;DiegoC_cons;"Questo testo sarà in descrizione";

Pompilio;Numa;NumaP_cons;"Questo testo sarà in descrizione";

Stilio;Tullio;TullioS_cons;"Questo testo sarà in descrizione";

Marzio;Anco;AncoM_cons;"Questo testo sarà in descrizione";

Prisco;Tarquinio;TarquinioP_cons;"Questo testo sarà in descrizione";

Tullio;Servio;ServioT_cons;"Questo testo sarà in descrizione";

Superbo;Tarquinio;TarquinioS_cons;"Questo testo sarà in descrizione";

Come creare un file CSV da un semplice elenco di nomi e cognomi (una mail)

Se avete ricevuto una mail con un elenco di nomi e cognomi è possibile arrivare velocemente ad un file CSV con delle regular expressions con notepad++

Ecco la regular expression da inserire nel campo “Trova” e la stringa da inserire nel campo “Sostituisci con” :

Trova:                 ^([^\s]*)[\s]([\w]{1})([^\s]*)

Sostituisci con:   \2\3;\1;\1\2_cons;"Questo testo sarà in descrizione"

Qui sotto un paio di foto che mostrano come si trasforma l’elenco con questa semplice regexp

NOTA: Per gli utenti che ho dovuto creare io, la nomenclatura del nome utente doveva essere:

_cons

Ad esempio: MarioR_cons

Sicchè ho creato la regular expression perché ci desse in output un elenco CSV con le colonne giuste.

2.    Creare un file CSV con nome utente e nuova password

Qui le ho messe con gli asterischi...

nomeSAM;password

MarioR_cons;**************

GuidoB_cons;**************

GiovanniV_cons;**************

DiegoC_cons;**************

NumaP_cons;**************

TullioS_cons;**************

AncoM_cons;**************

TarquinioP_cons;**************

ServioT_cons;**************

TarquinioS_cons;**************

Ma perché un CSV diverso?

Il motivo è molto semplice: nel mio esempio ho fatto generare le password ad un programma (Keepass) ed ho quindi ottenuto, infine, un CSV con le password generate secondo la mia regola personalizzata (magari scriverò un articolo su come si fa).

Inoltre, così ho l’opportunità anche di spiegare come si cambiano le password a molti utenti in una volta sola con uno script!

Se invece avete un elenco che già contiene anche le password, vi basterà ignorare questo passaggio ed agguingere, al primo comando (New-AdUser), in fondo:

-AccountPassword (convertto-securestring -asPlainText $_.password -Force)

Sempre che la colonna del vostro CSV contenente la password si chiami, appunto “password”

3.    Utilizzare powershell per importare i files CSV in oggetti

In powershell è possibile ottenere una variabile “oggetto” da un file CSV in modo molto semplice.

Apriamo direttamente la powershell (non è necessario scomodare un file .ps1 per operazioni semplici…)

“Carichiamo” quindi i nostri dati dai nostri files così:

$a = import-csv ".csv" -Delimiter ";"

$b = import-csv ".csv" -Delimiter ";"

Dove ovviamente sostituirete con i percorsi completi dei vostri 2 file.

Importiamo anche una semplice variabile di testo che contiene il nome del nostro dominio, ci verrà utile in seguito:

$domainName = "miodominio.local"

… inoltre, SE VOGLIAMO specificare il percorso della OU nella quale creare le utenze in Active Directory, possiamo specificarlo con

$OUpath = "CN=nomeOU,DC=miodominio,DC=local"

Dove, ovviamente, il valore va sostituito con il DistinguishedName della OU in Active Directory (potete recuperare il valore da “Utenti e Computer di Active Directory” (dsa.msc) abilitando la visualizzazione avanzata e visualizzando la scheda “Editor Attributi” nelle proprietà della OU.

IMPORTANTE: prima di procedere ad operare su Active Directory da Powershell (oltre ovviamente alle raccomandazioni di fare attenzione e che lo fate a vostro rischio e pericolo, che sono d’obbligo) è necessario importare il corretto modulo di amministrazione dentro la sessione powershell.

Se state lanciando powershell dal Domain controller, non avrete nessun problema.

Se state lanciando powershell dal vostro PC, dovreste aver già installato i “Remote Server Administration Tools” di Microsoft: una serie di strumenti di base per amministrare la vostra infrastruttura server (DHCP, DNS, Active Directory, ….) direttamente dal vostro PC.

Inoltre dovreste aver lanciato la powershell con un account che ha i diritti di amministratore di dominio (o al minimo i diritti che servono per creare utenze).

Per importare il modulo di amministrazione Active Directory, lanciate quindi il comando:

import-module activedirectory

4.    Lanciare lo script per creare le utenze

Una volta importato il modulo activedirectory avremo a disposizione i comandi che ci servono. Lanciamo quindi (anche copiando e incollando):

$a | foreach {

    New-ADUser -Name ($_.cognome + " " + $_.nome ) -GivenName $_.nome -Surname $_.cognome -SamAccountName $_.nomeSAM -UserPrincipalName ($_.nomeSAM+"@"+$domainName) -Path $OUpath -Description (""+$_.description)

}

Lo script creerà le utenze DISATTIVATE nella OU di destinazione, senza specificare una password.

Varianti

Se avete già specificato la password in una colonna “password” del primo elenco CSV

Aggiungete la parte

-AccountPassword (convertto-securestring -asPlainText $_.password -Force) -Enabled $true

Così:

$a | foreach {

    New-ADUser -Name ($_.cognome + " " + $_.nome ) -GivenName $_.nome -Surname $_.cognome -SamAccountName $_.nomeSAM -UserPrincipalName ($_.nomeSAM+"@"+$domainName) -Path $OUpath -Description (""+$_.description) -AccountPassword (convertto-securestring -asPlainText $_.password -Force) -Enabled $true

}

In questo caso, non avrete bisogno della parte 5.

Se NON volete specificare la OU in cui creare le utenze

Rimuovete -Path $OUpath

$a | foreach {

    New-ADUser -Name ($_.cognome + " " + $_.nome ) -GivenName $_.nome -Surname $_.cognome -SamAccountName $_.nomeSAM -UserPrincipalName ($_.nomeSAM+"@"+$domainName) -Description (""+$_.description)

}

5.    Lanciare lo script per cambiare le password e riabilitare le utenze

Ora vediamo anche come cambiare la password a più utenti contemporaneamente partendo da un elenco CSV.

Nell’elenco numero 2, ci sono solo 2 colonne: nome utente e nuova password da impostare. Il nome utente è quello che ci permette di capire a quale utente cambiare la password, ovviamente.

Lanciamo quindi il comando:

$b | foreach{

    Set-ADAccountPassword $_.nomeSAM -newPassword (convertto-securestring -asPlainText $_.password -Force)

    set-AdUser $_.nomeSAM -Enabled $true

}

La prima riga è per cambiare la password, la seconda per riabilitare l’utente (inizialmente creato disabilitato)

6.    Ricapitoliamo

• Abbiamo creato dei files CSV contenenti uno i dettagli dell’utente da creare, l’altro la corrispondenza username - nuovapassword.
• Li abbiamo importati in due variabili powershell e abbiamo anche definito 2 variabili “stringa” per alcuni dettagli comuni
• Abbiamo importato il modulo di amministrazione AD in powershell
• Abbiamo creato le utenze senza password
• Abbiamo cambiato la password e abilitato le utenze

7.    Script completo

Una volta che avete i due files CSV potete anche copiare e incollare questo script (ricordandovi di cambiare i percorsi dei files) dentro una powershell…

… o metterlo in un file .ps1 e farlo girare

$a = import-csv ".csv" -Delimiter ";"

$b = import-csv ".csv" -Delimiter ";"

import-module activedirectory

$domainName = "miodominio.local"

$OUpath = "CN=OUName,DC=miodominio,DC=local"

# creiamo gli utenti dal file (colonne cognome;nome;nomeSAM;descrizione)

$a | foreach {

    New-ADUser -Name ($_.cognome + " " + $_.nome ) -GivenName $_.nome -Surname $_.cognome -SamAccountName $_.nomeSAM -UserPrincipalName ($_.nomeSAM+"@"+$domainName) -Path $OUpath -Description (""+$_.description)

}

# Cambiamo le pw e riabilitiamo (colonne nomeSAM;password)

$b | foreach{

    Set-ADAccountPassword $_.nomeSAM -newPassword (convertto-securestring -asPlainText $_.password -Force)

    set-AdUser $_.nomeSAM -Enabled $true

}

BUONO SCRIPTING A TUTTI!!!