Quante volte capita di dover creare pacchetti che hanno accesso alle risorse esterne a SQL Server? Succede spesso di dover produrre file in output, log, eseguire dei processi, comprimere cartelle, e via discorrendo.
Quando pianifichiamo un'esecuzione di un pacchetto SSIS tramite Job l'account di default che esegue lo step è l'utenza che avvia il servizio dell'Agent di SQL.
Come i
Books On Line indicano, è sempre meglio utilizzare un utente di dominio o locale con i permessi più ridotti possibile. È buona norma infatti sia isolare ogni servizio dagli altri sia definire come account un membro del gruppo User o comunque un utente che non dispone di privilegi di amministrazione. Questo per garantire una ancor minore superficie di attacco dall'esterno.
In generale, si consiglia seguire le seguenti regole (dai BOL):
- Eseguire i servizi sotto
diversi account di Windows
- Impostare i
più bassi diritti possibili agli utenti che eseguono i servizi.
- Non eseguire il servizio SQL Server Agent con una utenza membro di
Administrators.
- Microsoft raccomanda di non utilizzare l'account
Network Service per il servizio SQL Server o SQL Server Agent.
- Utilizzare utenti
proxy per l'esecuzione degli step di job.
- Creare utenti dedicati per ogni
proxy.
- Garantire i permessi particolari solamente agli utenti
proxy.
- Utilizzare i proxy
solo per l'esecuzione degli step.
Gli utenti proxy
Ma per proxy account, cosa si intende? Un proxy di SQL Server Agent definisce il contesto di sicurezza per uno step di un JOB. E' possibile associarvi determinati sottosistemi utilizzando il contesto di sicurezza di un account Windows.
Prima che l'agent esegua lo step impersona le credenziali definite nel proxy rimanendo sempre nel contesto del Windows account.
Per ulteriori informazioni sui sottosistemi, leggere
questo link .
Al fine di capire meglio l'utilizzo dei proxy, facciamo un semplice esempio, utilizzando come sottosistema Integration Service.
Scenario
Supponiamo di avere l'esigenza di generare un file di output con un SSIS, eseguendo una compressione al termine della scrittura del file. Ipotizziamo di avere una tabella
Movimenti su SQL Server 2005 e di voler creare un file flat in output chiamato
Movimenti.txt. Quest'ultimo dovrà essere compresso tramite Gzip nella stessa cartella dove è stato prodotto. A titolo di esempio, un Job pianificherà inseguito il lancio del SSIS.
Qui di seguito lo script di creazione della tabella
Movimenti:
USE [master]
GO
CREATE DATABASE [ProveDotNetHell] ON PRIMARY
( NAME = N'ProveDotNetHell', FILENAME = N'C:\MSSQL\ProveDotNetHell.mdf' ,
SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ProveDotNetHell_log', FILENAME = N'C:\MSSQL\ProveDotNetHell_log.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ProveDotNetHell', @new_cmptlevel=90
GO
USE ProveDotNetHell;
GO
CREATE TABLE Movimenti
(
IDMovimento int identity(1,1) NOT NULL,
IDCausale int NOT NULL,
TipoMovimento char(1) NOT NULL,
Importo decimal(18,2) NOT NULL,
Data datetime DEFAULT(GETDATE()) NOT NULL,
Valuta char(3) NOT NULL,
CONSTRAINT PK_Movimenti PRIMARY KEY CLUSTERED
(
IDMovimento
)
)
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 1, 'D', 100, 'EUR'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 2, 'D', 100, 'EUR'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 3, 'A', 50, 'EUR'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 3, 'A', 50, 'EUR'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 4, 'D', 50, 'GBP'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 1, 'D', 50, 'GBP'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 2, 'A', 100, 'GBP'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 3, 'A', 50, 'GBP'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 3, 'A', 50, 'GBP'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 4, 'D', 10, 'GBP'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 1, 'D', 50, 'USD'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 2, 'D', 100, 'USD'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 3, 'D', 50, 'USD'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 3, 'A', 50, 'USD'
INSERT Movimenti (IDCausale, TipoMovimento, Importo, Valuta)
SELECT 4, 'A', 100, 'USD'
GO
Il SSIS Package
Ecco quello che ci serve nel SSIS:
- Connection manager file flat
- Connection manager SQL Server 2005
- OLEDB Source
- File flat Destination
- Execute Process Task
Non mi soffermerò sui dettagli per la creazione del SSIS, poiché a noi interessa solamente capire come l'Agent si comporta in fase di esecuzione dello step. Quindi descriverò solamente le cose principali del package.
Configuriamo il connection manager file flat:
Il file prodotto sarà
D:\Projects\Movimenti.txt e avrà le colonne separate da tab.
Configuriamo la connessione a SQL Server 2005:
In questa connessione puntiamo al database
ProveDotNetHell del SQL Server di riferimento (in locale nell'esempio)
Il SSIS al termine risulterà come il seguente:
A sinistra abbiamo la parte di Control Flow, con la creazione del file e la sua successiva compressione. Esplodendo il Data Flow Produzione File Movimenti possiamo notare la presenza di due task, uno Source, il Movimenti DB, ed uno Destination, il
Movimenti Txt. Ad una ad una, le colonne della tabella vengono copiate sul file di destinazione.
Per impostare l'execute process task del control flow, il Compressione File, ho impostato le seguenti proprietà:
Le voci più importanti sono
Executable, la riga di comando,
Arguments, i parametri del file e la
WorkingDirectory ovvero la cartella su cui il temporaneo prodotto da gzip per la compressione risiede. Nella cartella
D:\Project ho copiato il file
gzip.exe scaricabile gratuitamente da
qui .
Se proviamo a lanciare il SSIS direttamente da debug, il tutto funzionerà correttamente. Da notare che il nostro Connection Manager OLEDB è impostato per credenziali in Windows Authentication. Lasciando tutto così e cercando di far eseguire il SSIS da uno step di un Job di SQL Server, il processo, con buona probabilità, fallirà. Questo proprio perché, come dicevamo all'inizio dell'articolo, è l'Agent che si occupa di far “girare” i JOB, gli step e quindi il SSIS stesso. Siccome in una installazione corretta questo utente non ha alcun privilegio, il fallimento è normale.
Ed è qui che entrano in gioco i
proxy account.
Impostare i proxy account per l'esecuzione degli step
Quello che ora vogliamo fare è dare all'utente che eseguirà i nostri SSIS i permessi per poter farlo correttamente ;-).
Procediamo per passo ed introduciamo i concetti base:
1. Come già detto, un proxy di SQL Server Agent definisce il contesto di sicurezza per uno step di un Job e per la sua conseguente esecuzione.
2. Per poter creare un proxy account è necessaria una CREDENZIALE. Quest'ultima è un record che contiene i dati di autenticazione per connettersi alle risorse esterne a SQL Server.
3. Per poter creare una credenziale, dobbiamo aggiungere un utente di Windows. Ad esempio l'utente locale SSISUser membro di Users.
Come creare la credenziale
Espandendo la cartella Security a livello di server accediamo alla sottodirectory Credentials:
Premere il tasto destro sulla cartella
Credentials ed aggiungerne una con
New Credential.
Come possiamo vedere abbiamo assegnato il nome
SSISManager alla credenziale, ed essa è riferita all'utente Windows
SSISUser, locale, creato per il nostro scopo.
La password è quella dell'account di Windows.
Come creare il proxy account
Ed ora passiamo finalmente alla creazione del nostro proxy account. Espandere il
SQL Server Agent e selezionare la cartella
Proxies.
Premere il tasto destro del mouse su un sottosistema per selezionare la voce
New Proxy.
Chiamiamo il proxy
SSISLauncher e associamo la credenziale precedentemente creata. Infine selezioniamo uno o più sottosistemi per cui ottenere i permessi. Nel nostro esempio ci limitiamo a considerare SSIS, anche se nulla vieta di aggiungerne altri. Nella sezione Principals è possibile assegnare inoltre al nostro proxy anche ruoli per definirne il livello di accesso verso SQL Server.
Alla fine di questo procedimento, avremo un nuovo proxy nel sottosistema SQL Server Integration Service Package. Questo significa che quell'account potrà gestire uno step di tipo SQL Server Integration Service Package.
Come assegnare il proxy al JOB
Creare un nuovo Job su SQL Server Agent alla cartella JOB. Successivamente aggiungere uno step simile a quello dell'esempio seguente:
La parte cerchiata indica come è ora possibile vedere il nostro proxy e non il solito SQL Server Agent. Selezionare proprio
SSISLauncher e dare Ok.
I permessi al proxyOra è necessario impostare i permessi a livello di File System all'utente di windows
SSISUser (o al gruppo di cui fa parte l'utente, se ne è stato creato uno apposito per le utenze proxy).
Nel nostro caso, dovremo dare i permessi di lettura e scrittura sulla cartella
D:\Projects.
Ma questo non basta. Ora lo step potrà creare file, eseguirli, modificarli e cancellarli in quella cartella; ma ricordiamo che la sorgente dei dati per il file è SQL Server e siccome il connection manager impostato è sotto Windows Authentication,
SSISUser non potrà accedere ai dati sul database. Come fare?
Molto semplicemente, aggiungere una Login su SQL Server, relativa all'utente creato ed associarla ad un database, attraverso l'apposito user mapping presente nella form di creazione della Login stessa:
Login in Windows AuthenticationUser MappingPer comodità, nell'esempio, il ruolo assegnato a questo utente è il db_datareader, quindi sola lettura. La cosa migliore sarebbe definire un ruolo che possegga i permessi su particolari oggetti che sono poi quelli a cui SSISUser avrà accesso.
Esecuzione del Job definitiva
Proviamo a rieseguire il Job. Se tutto è stato impostato correttamente, lo step relativo al SSIS ora non darà più alcun problema; tanto meno l'accesso al database e l'accesso alle risorse esterne.
Conclusioni
In un'installazione di SQL Server in cui il
SQL Server Agent “gira” con i permessi più bassi possibile, vedremo l'esecuzione del SSIS di esempio fallire. L'Agent non ha alcuna possibilità di accedere al disco o di eseguire particolari operazioni al di fuori di SQL Server.
Se da Visual Studio tutto il debug procede per il meglio mentre da schedulazione su SQL Server Job si evidenziano ostacoli, con buone probabilità, il problema è dovuto alle credenziali di accesso. Rilanciando il SSIS con Job definendo il
proxy e con le permission impostate sulle cartelle utilizzate nel'esempio, possiamo vedere il tutto funzionare alla perfezione.
L'utilizzo di un proxy account non è obbligatorio, anche se fortemente consigliato dalle best practices sui
Books On Line.
Diventano necessari laddove si voglia accedere alle risorse esterne. Ci sono workaround meno macchinosi per risolvere il problema, come ad esempio impostare permission di amministrazione all'account che fa “girare” il servizio del
SQL Agent. Ma sono pratiche del tutto da evitare.
Nel caso in cui il SSIS utilizzi solo risorse interne a SQL Server (immaginiamo un SSIS che legge e scrive su di una tabella) il proxy non sempre serve.
È sufficiente infatti definire i connection manager con autenticazione SQL e fornire username e password di connessione. Ma questo apre un'altra grande problematica: il salvataggio e la codifica dei dati sensibili. Di conseguenza, per quanto possibile, utilizzare la Windows Authentication ed evitare di dover fornire una chiave per il salvataggio dei sensitive data.