Le blog technique

Toutes les astuces #tech des collaborateurs de PI Services.

#openblogPI

Retrouvez les articles à la une

PowerShell – Envoi de fichier chiffrés via FTP

Introduction

Afin de réaliser une action régulière et standart, deux solutions sont possible :

  • Réaliser l’action manuellement,
  • Développer un script qui s’en charge automatiquement.

Prérequis

  • Powershell,
  • 7zip,
  • Créer une tâche planifiée

Script

Le script suivant permet d’automatiquement envoyer par FTP dans un fichier chiffré, ce script utilise principalement les variables suivantes :

  • $days_to_send : L’age maximum des fichiers à envoyer
  • $source_path : Repertoire contenant les fichier à envoyer
  • $zipdirectory : Repertoire provisoire où seront compressé les fichiers
  • $log_location : emplacement du fichier de log
  • $sz : emplacement l’executable de 7zip
  • $ftp : FTP cible
  • $user : username utilisé pour se connecter au FTP
  • $pass : password utiliser pour se connecter au FTP et pour chiffré le fichier ZIP
  • $From : Adresse email qui emet les notifications par mail
  • $To : Adresse email recevant les notifications par mail
  • $SMTPServer : serveur SMTP utilisé pour les notifications par mail

 

#MAIN
function main
{
#Date Variables
$full_date = Get-Date
$custom_date = $full_date.ToString("yyyyMMdd")
$days_to_send = 4
$limit = $full_date.AddDays(-$days_to_send)

#Location Variables
$source_path = "E:\BACKUP\DATA\Database"
$zipdirectory = "G:\FTP_Folder\TEMP_ZIP"
$log_location = "G:\FTP_Folder\Logs"
$sz = ("C:\Program Files\7-Zip\7z.exe")

#FTP Variables
$ftp = "ftp://ftpr.domain.com/" 
$user = "FTP_Username" 
$pass = "FTP_Password"

#General variables
$LogFile
$compressed_folder
$folder_to_compress
$environment = "PROD"

#Mail Variables
$From = "Hatem.DJELASSI@piservices.com"
$To = "Hatem.DJELASSI@piservices.com"
$Cc = "Jean.DUPONT@piservices.com"
$Attachment
$Subject 
$Body
$SMTPServer = "smtp.domain.com"
$SMTPPort = "25"

Create-log-file -_log_location $log_location;

#Start Log
Append-log-file -_text "---------------------------------------------------------------"
Append-log-file -_text "START LOG @$full_date"

Create-Directory -_path $zipdirectory;
Copy-backups-older-than -_source_path $source_path -_destination_path $folder_to_compress -_limit $limit;
Compress_folder -_sz $sz -_pass $pass -_zipdirectory $zipdirectory -_folder_to_compress $folder_to_compress;
Delete_Folder -_file_to_delete $folder_to_compress;
Send_FTP -_user $user -_pass $pass -_ftp $ftp -_file_to_send $compressed_folder;
Notify -_from $From -_to $To -_cc $cc -_subject $Subject -_body $Body -_smtp $SMTPServer -_port $SMTPPort;

$compressed_folder = Get-ChildItem -Path $zipdirectory -Recurse -Force | Where-Object {$_.extension -match ".7z"} -ErrorAction stop
try
    {
        Remove-Item $compressed_folder.FullName -Recurse -ErrorAction Stop
        Append-log-file -_text "OK Deletion of the item has been made successfully" -_LogFile $LogFile;
    }
catch
    {
        Append-log-file -_text "ERROR when deleting the item $compressed_folder" -_LogFile $LogFile;
        write-host "An error occurred when deleting the item $compressed_folder" + $_.Exception.Message 
    }


#End log
Append-log-file -_text "END LOG @$full_date"
Append-log-file -_text "---------------------------------------------------------------"

}

#SendMail Function
function Notify
{
    param($_from, $_to, $_cc, $_attachment, $_subject, $_body, $_smtp, $_port)
    try 
            {           
                Send-MailMessage -From $_From -to $_To -Cc $_Cc -Subject $_Subject `
                -Body $_Body -SmtpServer $_SMTP -port $_Port 
                Append-log-file -_text "The email notification has been sent successfully" -_LogFile $LogFile;
            }
        catch
            {
                write-host "An error occurred when sending the notification" + $_.Exception.Message 
                Append-log-file -_text "The email notification has not been sent" -_LogFile $LogFile;
            }
}


#Log file functions
function Create-log-file 
{
    param ([string]$_log_location)        
    if (!(Test-Path "$_log_location\$custom_date - FTP_Script.log"))
    {
        try 
            {           
                $_LogFile = "$_log_location\$custom_date - FTP_Script.log"
                Set-Variable -Name LogFile -Value $_LogFile -Scope 1
            }
        catch
            {
                write-host "An error occurred when creating the log file" + $_.Exception.Message 
            }
        
    }
    else
    {
        try 
            {
                $_LogFile = "$log_location\$custom_date - FTP_Script.log"
                Set-Variable -Name LogFile -Value $_LogFile -Scope 1
            }
        catch
            {
                write-host "An error occurred when reusing the log file" + $_.Exception.Message 
            }
    }
}

function Append-log-file 
{
    param ($_text)
    try 
        {
            $custom_date+" "+$_text | Out-File $LogFile -Append -Force
        }
    catch
        {
            write-host "An error occurred when writing into the log file" + $_.Exception.Message 
        }
}

#Folders & files functions
function Create-Directory
{
    param ($_path)
    try 
        {
            $_folder_to_compress = New-Item -ItemType directory -Path "$_path\$custom_date - $environment Database Export" -force -ErrorAction Stop
            Set-Variable -Name folder_to_compress -Value $_folder_to_compress -Scope 1
            Append-log-file -_text "OK New folder to compress created successfully" -_LogFile $LogFile;

        }
    catch
        {
            Append-log-file -_text "ERROR when creating new folder to compress" -_LogFile $LogFile;
            write-host "An error occurred when creating new folder to compress" + $_.Exception.Message 
        }
}

function Copy-backups-older-than
{
    param ($_source_path, $_destination_path, $_limit)
    try
        {
            Get-ChildItem -Path $_source_path -Recurse -Force | Where-Object {$_.extension -in ".dif",".bak" -and !$_.PSIsContainer -and $_.CreationTime -gt $_limit } | copy-item -Destination  $_destination_path -Force -Container -ErrorAction stop
            Append-log-file -_text "OK The copy of the backups made after $limit has been made successfully" -_LogFile $LogFile;
        }
    catch
        {
            Append-log-file -_text "ERROR when copying the backups made after $limit" -_LogFile $LogFile;
            write-host "An error occurred when copying the backups made after $limit" + $_.Exception.Message 
        }
}

function Compress_folder
{
    param ($_sz, $_pass, $_zipdirectory, $_folder_to_compress)
    try
    {
        & $sz a -p"$_pass" "$_zipdirectory\$custom_date - $environment Database Export.7z" "$_folder_to_compress\*"
        $_compressed_folder = Get-ChildItem -Path $_zipdirectory -Recurse -Force | Where-Object {$_.extension -match ".7z"} -ErrorAction stop
        Set-Variable -Name compressed_folder -Value $_compressed_folder -Scope 1
        Append-log-file -_text "OK Compression/encryption of the folder has been made successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR during compression/encryption of the folder" -_LogFile $LogFile;
        write-host "An error occurred during compression/encryption of the folder" + $_.Exception.Message 
    }
}

function Delete_Folder
{
    param ($_file_to_delete)
    try
    {
        Remove-Item "$_file_to_delete" -Recurse -ErrorAction stop
        Append-log-file -_text "OK Deletion of the item has been made successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when deleting the item $_file_to_delete" -_LogFile $LogFile;
        write-host "An error occurred when deleting the item $_file_to_delete" + $_.Exception.Message 
    }
}

#FTP Function
function Send_FTP
{
    param ($_user, $_pass, $_ftp, $_file_to_send, $_subject, $_body)
    #Create Web client
    try
    {
        $webclient = New-Object System.Net.WebClient -ErrorAction stop
        $webclient.Credentials = New-Object System.Net.NetworkCredential($_user,$_pass) -ErrorAction Stop 
        $webclient.Proxy = $NULL 
        Append-log-file -_text "OK FTP WebClient created successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when creating the FTP WebClient" -_LogFile $LogFile;
        write-host "An error occurred when creating the FTP WebClient" + $_.Exception.Message 
    }
    #Create URI
    try
    {
        $_uri = New-Object System.Uri($_ftp+$_file_to_send.Name) -ErrorAction stop 
        Append-log-file -_text "OK FTP URI created successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when creating the FTP URI" -_LogFile $LogFile;
        write-host "An error occurred when creating the FTP URI" + $_.Exception.Message 
    }
    #Send the file
    try
    {
        $webclient.UploadFile($_uri, $_file_to_send.FullName)
        Append-log-file -_text "OK FTP file sent successfully" -_LogFile $LogFile;
        $global:Subject = "FTP Upload Success"
        $global:Body = "The file $_file_to_send has been sent successfully @$full_date"
    }
    catch
    {
        Append-log-file -_text "ERROR when sending the file through FTP" -_LogFile $LogFile;
        write-host "An error occurred when sending the file through FTP" + $_.Exception.Message 
        $global:subject = "FTP Upload Failure"
        $global:Body = "An error occurred when sending the FTP backups from $environment to FTP @$full_date"
    }
}

main

SQL Server – Executer une procédure stockée en “RUN AS”

Introduction

Afin de fournir à un utilisateur le droit d’executer une procédure stockée, un administrateur SQL peut fournir à un utilisateur le droit “EXECUTE” sur celle-ci.

Cependant, il est possible que l’utilisateur rencontre une erreur lors de l’execution de la procédure stockée de type :

image

Celà est souvent du à la présence de code SQL dynamique, généré lors de l’execution.

Explication

Il est possible de fournir à l’utilisateur le droit “SELECT” sur l’objet de la requête et le script pourras être executé avec succès, néanmoins, celà vas contre le principe d’une procédure stockée qui permet de fournir aux utilisateurs un accès aux données via l’interface restrictive de la procédure stockée.

Afin de résoudre ce problème il est possible (à partir de SQL 2008) d’utiliser la clause “EXECUTE AS” en spécifiant un compte disposant des droits nécessaires à l’execution de la procédure stockée.

Il est important de noter que l’utilisation de “EXECUTE AS” ne fournit pas à l’utilisateur final des droits d’impersonnalisation qui permetteraient à l’utilisateur de “récuperer” les droits du compte d’execution.

Réalisation

L’exemple suivant explique comment utiliser “EXECUTE AS” :

image

La ligne 2 spécifie le compte “SQLUser” à utiliser pour l’execution du script,

La ligne 5 retourneras le compte “SQLUser”,

La ligne 7 “EXECUTE AS CALLER” permet d’utiliser le compte de l’utilisateur executant la procédure stockée,

La ligne 8 retourneras le compte de l’utilisateur executant la procédure stockée,

La ligne 10 “REVERT” retablis l’utilisation du compte “SQLUser” spécifié au début du script.

Source

https://msdn.microsoft.com/fr-fr/library/ms181362.aspx

http://www.sqlmatters.com/Articles/Using%20Execute%20As%20with%20Stored%20Procedures%20Containing%20Dynamic%20SQL.aspx

SQL Server – Envoyer par email le résultat d’une requête

Introduction

L’une des requêtes qui peut être demandé à un adminsitrateur SQL est l’execution régulière d’une requête et le transmission de son résultat par email.

Prérequis

Afin de répondre à ce type de demande, les outils suivants seront utiles :

  • La procédure “sp_send_dbmail”,
  • L’agent SQL Server,
  • La configuration d’un profile Database Mail.

Réalisation

Le script suivant permet d’envoyer par mail le résultat de la procédure stockée “usp_StoredProcedure” :

image

Depuis la base MSDB, executer la procedure “sp_send_dbmail” (ligne 1), cette procédure prends les paramètres suivants :

@profile_name : profile mail configuré sur SQL Server sous le dossier “Management”

@recipients : adresse mail du destinataire

@subject : sujet qui figureras dans le mail envoyé

@query : requête à executer

@query_result_width : largeur en nombre de caractère du fichier qui seras envoyé en pièce jointe, par défaut la valeur est de 256 caractères, ce paramètre est important si l’on souhaite réaliser par la suite un import vers excel

@attach_query_result_as_file : utiliser la valeur “1” afin de fournir le resultat de cette requête dans un fichier en pièce jointe

@query_attachment_filename : nom du fichier en pièce jointe

@query_result_separator : caractère de séparation qui seras utilisé

L’article suivant explique plus en détails les paramètres qu’il est possible d’utiliser : https://msdn.microsoft.com/fr-fr/library/ms190307.aspx