Double Hop ovvero Abilitare la delegation su SqlServer
Dopo aver analizzato come applicare la delega in uno schema 3 tier IE-IIS/ASP.NET-SQL Server nel mio articolo precedente Impersonate & delegate, passiamo alla sua applicazione nel solo contesto SQL Server.
Applicare il paradigma della delega (delegation) a SQL Server significa permettere ad un client collegato ad un’istanza di SQL Server di connettersi implicitamente ad un’altra istanza di SQL Server inviandogli le credenziali kerberos dell’utente autenticato da Windows. Questo paradigma, applicato a Server SQL, è chiamato double hop.
Sotto delegation è proprio l’istanza di SQL Server alla quale l’utente Windows si è connesso (usando l’autenticazione integrata), che impersona quell’utente stesso nelle comunicazioni con altre istanze. La delegation è richiesta soprattutto nelle query distribuite che utilizzano linked servers.
Implementazione di un double hop
Consideriamo il caso seguente: un utente si logga una workstation e che si connette (per esempio con SQL Management Studio) ad un server all’istanza di SQL Server SQLSERVER1. L’utente vuole eseguire query distribuite anche verso un database sul linked server SQLSERVER2.
Requisiti per il Client:
- la login dell’utente deve essere di tipo Windows
- tale login deve aver diritti di accesso su SQLSERVER1 e SQLSERVER2.
- in Active Directory, bisogna verificare che la proprietà ”Account is sensitive and cannot be delegated” non deve essere selezionata.
- il computer client deve utilizzare per connettersi al DB il protocollo TCP/IP o named pipes.
Requisiti per il primo serverSQLSERVER1 (o in generale per i server intermedi):
- deve essere stato registrato l’SPN per il server.
- l’account col quale viene eseguito SQL deve essere “trusted for delegation”.
- il server deve utilizzare il protocollo TCP/IP o named pipes.
- il secondo server, SQLSERVER2, deve essere aggiunto come linked server. Può essere fatto anche eseguendo la stored procedure sp_addlinkedserver:
EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'
- il linked server deve essere configurato per effettuare il self mapping sulle login interessate (cioè deve essere passato il contesto di sicurezza della login connessa). Si può usare la stored procedure sp_addlinkedsrvlogin:
EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'
La finestra delle proprietà di sicurezza del linked server dovrà essere simile alla seguente figura:
Sotto riporto un utile script da me creato ed utilizzato nella creazione e nella verifica dei linked server utilizzati negli hop.
Requisiti per il secondo server SQLSERVER2:
- Se viene utilizzato il protocollo TCP/IP, bisogna registrare un SPN in AD.
- il server deve utilizzare il protocollo TCP/IP o named pipes.
DECLARE @sname as nvarchar(50)
SET @sname ='SERVER2' -- Il nome del linked server
-- Se il linkedsrv esiste ià, viene droppato
IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = @sname)
EXEC master.dbo.sp_dropserver @server=@sname, @droplogins='droplogins'
IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = @sname)
EXEC master.dbo.sp_dropserver @server=@sname, @droplogins='droplogins'
DECLARE @providerstr as varchar(255)
SET @providerstr= 'DRIVER={SQL Server};SERVER='+ @sname +
';Integrated Security=SSPI;'
-- Creazione del Linked server
EXEC sp_addlinkedserver
@server=@sname,
@srvproduct='',
@provider='SQLNCLI',
@datasrc=@sname,
@provstr= @providerstr
EXEC sp_addlinkedsrvlogin @sname, 'true'
-- Verifica se viene utilizzato il self mapping
SELECT uses_self_credential as delegation , S.name
FROM sys.linked_logins as L, sys.servers as S
WHERE S.server_id=L.server_id and S.name=@sname
-- Esecuzione di una query distribuita
-- (bisogna avere i diritti sul secondo server)
EXECUTE('select * from '+@sname+'.master.dbo.sysdatabases')
-- La prossima query deve restituire:
-- net_transport=TCP e auth_scheme=KERBEROS
SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id=@@spid
-- La prossima query deve elencare il linked server creato
SELECT * FROM sys.servers where name=@sname
Contesti di applicazione simili
Lo schema della delegation può facilmente essere implenmentato per gli altri servizi SQL come Analysis Services, Reporting Services ed Integration Services; bisogna solamente fare attenzione alla fase di registrazione dell’SPN e ai diritti da applicate agli user account ed ai computer account coinvolti.
Per esempio per quanto riguarda Analysis Services l’SPN che deve essere registrato è del tipo:
Setspn.exe -A MSOLAPSvc.3/Fqdn:InstanceName OLAP_Service_Account
Riferimenti
MSDN – Security for Linked Servers , Configuring Linked Servers for Delegation
Microsoft – How to configure SQL Server 2005 Analysis Services to use Kerberos authentication
MS Technet – Understanding Kerberos Double Hop
0 Comment » 
