SQL Reporting Server – Self Certificate & Veeam ONE

Veeam One è uno splendido strumento di Analisi e Reportistica avanzata per ambienti virtuali e di backup.

In un’architettura Enterprise i ruoli di Veeam One, sono distribuiti su differenti Server.

Parliamo del DataBase (MS-SQL), del Reporting Server (SSRS) e ovviamente del Server Veeam ONE (VOS)

In questo articolo illustrerò come sia possibile ottimizzare la creazione dei report creando contestualmente una connessione cifrata tra Veeam ONE e il Reporting Server.

La procedura consta di tre macro fasi.

  1. Nella prima si crea il certificato che abilita la connessione cifrata HTTPS sul server SSRS.
  2. Nella seconda si configura SSRS in modo che possa accettare connessioni HTTPS.
  3. Nella terza si configura il server Veeam ONE ad utilizzare SSRS per la creazione dei report.

1- Creazione del certificato

Se nel vostro dominio non è installata alcuna autorità di certificazione (come nel mio), è necessario creare un Self-Signed certificate.

Vediamo come procedere:

Sul SSRS avviate come amministratori una console powershell e lanciate i seguenti comandi:

  1. New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\my -dnsname NAMESERVER -NotAfter (Get-Date).AddMonths(60) (sostituite NAMESERVER con il nome del vostro Server).
  2. $pwd=ConvertTo-SecureString “yourpassword” -asplainText -force (sostituire yourpassword con una complicata e di vostra scelta).
  3. $file=”C:\MyFolder\SQLcertificate.pfx” (è la location dove verrà esportato il certificato).
  4. Export-PFXCertificate -cert cert:\LocalMachine\My\<Thumbprint creato dall’output del primo comando> -file $file -Password $pwd (Copia il certificato nel file creato nel  punto 3).
  5. Import-PfxCertificate -FilePath $file cert:\LocalMachine\root -Password $pwd (importa all’interno del SSRS il certificato).

Ora è il momento di copiare il file SQLcertificate.pfx (punto 3) nel VOS e procedere alla sua installazione come indicato nelle prossime righe.

  1. Fare doppio click sul file e nella prima finestra scegliere “Macchina Locale”.
  2. Quando viene richiesta la password, fornite quella impostata nel passaggio 2.
  3. Nella schermata successiva selezionate “Posiziona tutti i certificati nel seguente archivio“, e dopo aver selezionato Sfoglia, selezionate dall’elenco “Autorità di certificazione radice attendibili“.
  4. Fate Ok e dopo aver selezionato Avanti,  terminate l’installazione.

2- Configurazione SSRS

Utilizzando il configuration manager di SSRS è possibile impostare la connessione https come illustrato nelle immagini 1,2 e 3.

Immagine 1

Immagine 2

 Immagine 3

3- Configurazione Veeam ONE

Le immagini 4 e 5 mostrano come configurare VOS in modo che debba utilizzare SSRS per generare i report.

Immagine 4

Immagine 5

Nota 1: Dall’immagine 5 possiamo osservare che è possibile testare la connessione tramite il pulsante Test Connection.

Nota 2: Il dettaglio su quali aprire sui firewall sono documentate nella guida. (helpcenter.veeam.com); ricordatevi di aggiungere la porta 443 🙂

A presto

Ransomware defense part 3: Monitoring and more

In the previous articles, I described some good ideas to design your architecture to keep it safer as much as possible.

One of the greatest challenges the IT guys have to face is finding the right balance among design, deployment and budget.

It’s very important to have the right tools to measure architectural behaviour. In this way you can easily:

  1. Watch from a privileged point of view the architecture. Let’s image to be on the top of a mountain watching people and goods moving at the bottom of the valley”
  2. Launch the defending actions when an attack is on-going. Referring to my previous example, it’s like blocking some passages to people and goods.
  3. When the attack is over remove any possible threat left (cleaning the passages).
  4. Do a thorough workup understanding of the weak points of your architecture and create a plan to reinforce it.

Monitor tools are your sentinels, but they need to be trained to trigger also the first defense lines. Imagine the new sentinel as a lieutenant warrior with a varied arsenal of weapons.
To be clearer: the required features monitor and respond to actions in function of the severity of the alarm.

But why is measuring so important? The reason is that you can define the KPI (Key performance indicator) for your environment and periodically check if the measures are respected.
In other words, it is possible to measure the service level and understand if the budget and skill invested in the company are enough to address the backup security challenges or if more tunings actions or some great changes are needed.

Let’s see how to use Veeam One to address this common request:

The Possible ransomware activity alarm keeps tracking of the Operating system of the VM.

As shown in picture 1 the monitored counters are by default CPU, Datastore write Rates and networking transmit rate (the case of copy offsite of sensible data for future blackmail).
The value counters can be changed to adapt to your own needs (Tuning phases) and more counters can be added to monitor more objects as shown in picture 2.

Picture 1

Picture 2

Another alarm already present in Veeam One is “Suspicious increment backup size“.

It checks if the restore point size is significantly different from the previously created ones.

The two main reasons I like  Veeam ONE are:

  1. Very easy to use
  2. Customizing the action after an alarm has been triggered

Thx to “customizing action” it’s possible to launch your antivirus/antimalware on the VMs belonging to the backup job that has triggered the suspicious alarm, or disconnect the repository from the network, or what else you wrote on your incident and rescue procedure.

The main point here is that you can manually click on it or automatically execute the action as shown in picture 3

Picture 3

Veeam One has furthermore an exhaustive technology of reporting.

If an alarm is a good way to intercept an error or a misconfiguration because  it works in real-time, through the reporting it is possible to check the status of your protection (KPI, SLA….), understanding the exercise and security cost of your production environment and forecasting the new investment to implement in the next years.

Which are the reports to use?

All of them are important and an all report list is available from the following link: Reports 

Just as an example please check the use of the following

The next article will talk about which are the automatic procedures you can adopt to check your backup infrastructure.

Take care and see you soon