Home Page Home Page Articoli Utilizzare i SQL Server Agent Proxies con i Package SSIS

Utilizzare i SQL Server Agent Proxies con i Package SSIS

Chi utilizza frequentemente i SSIS Packages e accede a risorse esterne a SQL Server non può fare a meno di conoscere ed utilizzare i Proxy Account (o SQL Server Agent Proxies). Vediamo che cosa sono e come si utilizzano in modo proficuo.
Autore: Alessandro Alpi Livello:
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:

Codice SQL n°1
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 proxy
Ora è 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 Authentication



User Mapping



Per 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.
Voto medio articolo: 4.8 Numero Voti: 13
Alessandro Alpi

Alessandro Alpi

Il mio avvicinamento all'informatica si ha nel 1992 all'età di 11 anni, grazie al mio stupendo C64 ed il suo basic. Dopo essermi diplomato in informatica, inizio subito il lavoro da sviluppatore in una ditta di Parma. Passo 6 anni qui, lavorando per i primi 6 mesi su prodotti win32 (per lo più gestionali per ... Profilo completo

Articoli collegati

Garantire l'univocità di una colonna ammettendo valori NULL multipli
Chi sviluppa in ambito professionale o per puro diletto prima o poi si troverà ad affrontare tematiche nuove ed avvincenti. In questo articolo cercherò di illustrare tre tecniche con i relativi pregi e difetti per permettere con SQL Server di garantire l'univocità di una colonna senza alcuna limitazione sui valori NULL
Autore: Lorenzo Benaglia | Difficoltà:
Integrare Crystal Reports con una Windows Form
Ecco come sfruttare in modo semplice e rapido le funzionalità offerte dal tool di reportistica Crystal Reports di Business Objects all'interno di una Windows Form .NET, dalla crazione del Report al Binding dei dati.
Autore: Matteo Raumer | Difficoltà: | Commenti: 2
Il Logging con Integration Services 2005
Perchè si è bloccata la mia applicazione ? Che errori si sono verificati ? Perchè il flusso d'esecuzione non procede nel modo corretto ? Cerchiamo di capire come possono esserci utili le funzionalità di logging offerte da SQL Server e gli Integration Services per rendere più affidabili e consistenti le nostre applicazioni.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 3
SQL Server 2005 - La Import/Export utility
Chi lavora sui Database si ritrova quasi quotidianamente a dover importare o esportare dati. SQL Server 2005 agevola notevolmente il compito tramite l'Import/Export Utility (ex DTS Import/Export di SQL 2000). Vediamo come funziona.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 14
Visual Studio 2005 Team System for Database Professionals
Scopriamo questo nuovo e potente Tool della famiglia Visual Studio 2005 Team System per creare, sviluppare, testare e gestire con più facilità e flessibilità i database di SQL Server.
Autore: Marco Caruso | Difficoltà:
Parallelismo tra SSIS e DTS, due prodotti a confronto
In questo articolo un'analisi sulle novità dei SQL Server 2005 Integration Services rispetto ai DTS di SQL Server 2000.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 2
Overview dei SQL Server Integration Services (SSIS)
Scopriamo i nuovi Sql Server Integration Services presenti in SQL Server 2005 che sostituisco i DTS aggiungendo delle nuove funzionalità davvero interessanti
Autore: Alessandro Alpi | Difficoltà: | Commenti: 6 | Voto:
Leggere un file XML con SQL Server 2005
Scopriamo in questo articolo alcune potenzialità offerte dal nuovo database SQL 2005, tra cui il nuovo data type XML, e le Stored Procedures per importare e gestire all'interno del database file testuali in formato XML.
Autore: Lorenzo Benaglia | Difficoltà: | Commenti: 5
ExcelDataSetLib, una libreria .NET per esportare dati in formato Excel
In questo articolo verrà dimostrato come derivare la classe DataSet e integrare funzionalità per esportare in formato Excel XLS, XML o CSV i dati provenienti da un base dati qualsiasi.
Autore: David De Giacomi | Difficoltà: | Commenti: 3 | Voto:
Microsoft Application Blocks Data Access
Quante volte vi è capitato di riscrivere decine di volte lo stesso pezzo di codice all'interno di un progetto ? Scopriamo in questo articolo l'utilità dei Microsoft Application Blocks che vi permettono di ottimizzare il vostro codice evitando ripetizioni e vi facilitano la lettura.
Autore: Marco Caruso | Difficoltà:
Comprimere e decomprimere files ZIP con .NET e #ZipLib
Visualizzare il contenuto di un file Zip da una applicazione vi sembra una Mission Impossibile? Oppure la vostra azienda ha fatto un mutuo per pagare il componente che vi aiuta nel lavoro? Ecco un articolo che parla della libreria sharpZipLib, libreria Open Source per la gestione dei formati compressi.
Autore: Giovanni Ferron | Difficoltà: | Commenti: 7
Rivoluzione nel campo dei Reports con i SQL Reporting Services
Finalmente Microsoft ci mette a disposizione uno straordinario tool per la produzione di Report perfettamente integrato con l'architettura .NET. Fino ad ora siamo sempre stati abituati ad utilizzare il buono seppur limitato Crystal Report. Vediamo cosa cambia da ora in poi.
Autore: David De Giacomi | Difficoltà: | Commenti: 6 | Voto:
Scopriamo cosa sono gli Office XP PIA
Vediamo in quest'articolo una dettagliata introduzione dei PIA gli assemblies che permettono ad un'applicazione .NET di interagire e di dialogare con i prodotti della suite Microsoft Office XP.
Autore: David De Giacomi | Difficoltà: | Commenti: 6
Migliorare la qualità dei grafici nei Crystal Reports
In questo breve articolo vedremo come aumentare la qualità dei grafici prodotti da Crystal Reports durante il rendering tramite Web con ASP.NET e all'interno di applicazioni WinForms.
Autore: David De Giacomi | Difficoltà:
Costruire Report con ASP.NET e Crystal Reports
Vedremo in questo semplice Tutorial le istruzioni base che ci permetteranno di costruire un report avanzato usando ASP.NET e l'oggetto CrystalReportViewer integrato in questa versione di Visual Studio .NET.
Autore: David De Giacomi | Difficoltà: | Commenti: 14
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5