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 server SQLSERVER1 (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.

Aggiungere il linked server programmaticamente

Script da eseguirsi con privilegi amministrativi su SERVER1:

— Il nome del linked server

DECLARE @sname as nvarchar(50) SET @sname =’SERVER2′

— Se il linkedsrv esiste già, 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

(**) nel caso la query restituisse “TCP,NTLM” verificare se, nella connection string, si stia utilizzando il nome NETBIOS (“SERVER1”) oppure  l’FQDN (per es. “SERVER1.DOMINIO.LOCAL”). Nel caso utilizzare quest’ultimo.

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

Questa voce è stata pubblicata in Active Directory, SQL Server, Windows Autentication e contrassegnata con , , , , . Contrassegna il permalink.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *


*