Mauro Dalfreddo

Un workaround per eseguire stored procedures in functions UDF con Microsoft SQL SERVER. Analizziamo la problematica e scriviamo una .NET external function che permette l’esecuzione di dynamic SQL.

Ultimamente mi sono scontrato con la problematica di eseguire una stored procedure da una funzione SQL UDF (google: How to execute Stored procedures into a UDF function).
L’esecuzione di codice INSERT/UPDATE/DELETE/STORED PROCEDURE che alterano il contesto esterno alla funzione non è possibile by design; è possibile solo apportare modifiche a variabili locali definite nella funzione. Infatti appena tentiamo di alterare il contesto del database otteniamo uno dei seguenti messaggi di errore, rispettivamente se stiamo utilizzando comandi TSQL INSERT/UPDATE/DELETE, chiamando STORED PROCEDURE o cercando di eseguire SQL dinamico:

Invalid use of side-effecting or time-dependent operator in ‘INSERT’ within a function.

Only functions and extended stored procedures can be executed from within a function.

Invalid use of side-effecting or time-dependent operator in ‘EXECUTE STRING’ within a function.

L’utilizzo delle funzioni SQL è utilizzato per leggere e applicare una certa logica ai dati letti, ma non a trasformarli o modificarli. E’difficile pensare che leggendo dei dati da una tabella con una SELECT questi vengano modificati sotto il naso.
Se ci dovessero essere logiche più complesse, come l’utilizzo di tabelle di appoggio temporanee, certo è preferibile e doveroso racchiudere il codice in una stored procedure.

Questo in linea di principio e come best practice.
Un caso che ho dovuto affrontare era quello dell’ottimizzazione delle performance di una serie di applicazioni. Queste, accedendo al DB, ed utilizzando gli strumenti offerti TSQL, utilizzavano tutte, in più punti e ripetutamente una certa funzione, che, accedendo a dati esterni al DB con protocollo TCP/IP, risultava particolarmente lenta a rispondere.

Da qui l’idea di implementare un meccanismo di caching nella funzione stessa; non era assolutamente pensabile cambiare la logica
applicativa modificando il codice e le modalità di interfacciamento al DB. L’operazione doveva essere completamente trasparente per le applicazioni!

L’algoritmo di caching può essere banalmente il seguente:

IF (prima volta)
 BEGIN
  recupera i dati online
  memorizzali nella cache
 ELSE
  recupera i dati dalla cache
 END

Includere tale logica in una funzione SQL è semplice…
Peccato che il significato di memorizzare i dati è quello di utilizzare un’istruzione del tipo INSERT/UPDATE/ST.PROCEDURE e che quindi modifica il contesto esterno alla funzione.

Nel seguito espongo la soluzione di come eseguire una stored procedure in una funzione, descrivendo i passi utili a creare una logica di caching sopra descritta.

(continua…)

Filed under : SQL Server, Software

0 Comment »

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

Filed under : Active Directory, Kerberos, SQL Server

0 Comment »

Finalmente trovo un po’ di tempo per continuare la trattazione iniziata in un mio articolo precedente dei Motivi per veicolare il contesto utente fino al database, e a questo rimando dando per scontati alcuni argomenti.

Sono già stati evidenziati i benefici e le caratteristiche dell’utilizzo dell’autenticazione integrata e del contesto utente anche fino all’ultimo livello applicativo (tier). E’ stata poi spiegata la configurazione da  utilizzare in un’archiettura a 3-tier (IE-IIS-SQL) tutta implementata in un unica macchina (single server).

Per capire come il security context di un utente possa essere veicolato esternamente ad un server da un servizio ad un altro,  bisogna comprendere i protocolli di autenticazione utilizzati.

NTLM e Kerberos a confronto

Il protocolli di autenticazione supportati da Windows sono NTLM (NT Lan Manager, fino dalla versione 4.0)  e Kerberos (da Win 2000 in poi).  Mentre NTLM si basa su un meccanismo di  challenge/response (ovvero lo scambio di un hash criptato tra client e server), Kerberos basa la sua architettura sull’esistenza di server autenticatori (KDC, Key distribuition center), che distribuiscono tikets (cifrati e firmati e quindi affidabili) ai client per utilizzare specifici servizi.

I pregi di Kerberos sono:

• lo standard aperto, basato sull’RFC 4120
• la maggiore velocità, poiché, diversamente da NTLM, il servizio non deve verificare la validità dell’utente/client presso un DC, ma gli basta semplicemente il ticket stesso
• la mutua autenticazione, poichè non solo il client si autentica al servizio (come NTLM),  ma il client stesso è certo dell’identità del servizio, poichè solo quello può leggere il ticket fornito
• supporto per il logon tramite smart card
• supporto per la delega dell’autenticazione (Authentication delegation o Authentication forwarding): grazie a questa caratteristica un servizio può accedere ad un risorsa remota per conto di un utente.

Quet’ultimo punto sta a significare che un utente A può dare diritti ad un’entità intermedia B per autenticarsi ad un terzo server C, come se
l’entità B fosse l’utente A stesso. Il server C baserà le sue decisioni autorizzative sull’identità di A invece che su quella di B.

E’ possibile iterare questo schema per più livelli applicativi distribuiti (multi-tier applications) solamente utilizzando Kerberos e non NTLM, semplicemente perchè NTLM non supporta la delegation! Quindi NTLM funziona su un’architettura a single server solo perchè il security context dell’utente viene creato alla sua connessione al primo livello sul server (dopo l’autenticazione) ed è disponibile poi per i livelli applicativi sucessivi.

Nell’implementazione Microsoft di Kerberos, il servizio stesso è altamente integrato con Active Directory (AD) e tutti i dati e le credenziali sono memorizzati in essa

(continua…)

Filed under : Active Directory, Kerberos, SQL Server, Web, Windows Autentication

0 Comment »

Ho sempre piacevolmente apprezzato le implicazioni dell’autenticazione integrata windows e nelle mie applicazioni, a meno che non mi sia esplicitamente richiesto, cerco sempre di portare il contesto di sicurezza dell’utente più avanti possibile nelle sue varie componenti.

Per esempio: il browser viene eseguito nel contesto di sicurezza dell’utente che si logga al computer, poi l’utente si autentica al Web server e le pagine chiamate ed i componenti software che le sottintendono sono eseguiti nel contesto di sicurezza dell’utente stesso. Similmente accade per l’autenticazione e la fruizione di altri servizi di rete o della connessione al database (anche in cascata tramite linked servers).

La seguente figura rappresenta tale scenario.

Dopo aver effettuato un brevissimo e sintetico excursus sui concetti di base, che verranno dati per scontati nei miei prossimi articoli, comincerò a descriverne l’implementazione. Nei prossimi articoli descriverò come implementare questa funzionalità in un ambiente distribuito per mezzo del modello di impersonazione e delega.

(continua…)

Filed under : Active Directory, SQL Server, Web

0 Comment »

Sempre nell’ambito di una migrazione talvolta è comodo disabilitare temporaneamente i check constraints e le foreign keys relativi alla/e tabelle di un database, per poter effettuare delle operazioni di manutenzione estemporanee o cancellazioni e reinserimenti multipli.

E’ altresì comoda la disabilitazione temporanea quando si deve svuotare (totalmente o parzialmente) una tabella per effettuarne il carico tramite BCP. Dopo aver disabilitato le foreign keys, visto che comunque non è possibile effettuare il truncate (essendo queste ultime ancora presenti sebbene disabilitate), è possibile effettuare le DELETE selettive o sull’intera tabella.  Ovviamente in presenza di milioni di righe nella tabella consiglio di SCRIPTARE i constraints, DROPPARLI, effettuare il TRUNCATE, e RICREARLI.

Se volessimo effettuare la disabilitazione complessiva dei constraints su tutto il DB basterebbe eseguire la seguente stored procedure:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

e per la riabilitazione:

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

(continua…)

Filed under : SQL Server

0 Comment »

Mi sono dedicato ultimamente alla migrazione di alcuni database da un’istanza di SQL Server 2005 ad un’altra; come spesso succede, mi sono trovato di fronte al tedioso problema del cambiamento di collation, nel caso specifico da SQL_Latin1_General_CP1_CI_AS al Latin1_General_CI_AS. Se si utilizzano le funzionalità di backup e restore del database sulla nuova istanza, purtroppo ci si porta dietro la vecchia collation.

Esistono in internet degli script (ne viene riportato sotto uno stralcio) che modificano la collation degli oggetti contenuti nel DB (tabelle, colonne, viste, ecc.) e infine quella del DB stesso (utilizzando iterativamente dapprima ALTER TABLE tablename ALTER COLUMN columnname COLLATE default, ed infine ALTER DATABASE databasename COLLATE default). Funzionano in casi semplici, ma ci sono seri problemi quando la struttura del DB non è banale, ovvero sono presenti anche oggetti complessi (funzioni, foreign keys, trigger, indici, librerie, ecc. con le relative dipendenze), che devono essere droppati e ricreati.

La soluzione più semplice per migrare un database senza mantenere la collation originale da SERVER1 a SERVER2 è quella che descrivo nel seguito; i passi sono 3:

1. scriptare il DB (e tutti gli oggetti contenuti) senza generare le collation sulla prima istanza.
2. eseguire tale script sulla seconda istanza
3. esportare ed importare i dati di tutte le tabelle con l’utility BCP

I primi due passi sono banali. Per automatizzare il terzo, ho sviluppato il seguente script: in funzione del database MyDatabase scelto, l’obiettivo è quello di generate tutte le istruzioni BCP, una per ogni tabella (nel relativo schema) con i corretti parametri.

use MyDatabase
DECLARE @srv_source varchar(50)
DECLARE @srv_destination varchar(50)
DECLARE @dbname varchar(50)
DECLARE @tmp varchar(max)
SELECT @dbname = db_name(), @tmp =char(13)+char(10),
           @srv_source = 'SERVER1', @srv_destination = 'SERVER2'

select @tmp = @tmp + 'bcp '+@dbname+'.'+schema_name(so.schema_id)+ '.'+so.name +
' out '+so.name+'.dat -c -T -q -S '+ @srv_source + char(13)+char(10) +
'bcp '+@dbname+'.'+schema_name(so.schema_id)+ '.'+so.name +
' in '+so.name+'.dat -c -T -E -q -S '+ @srv_destination + char(13)+char(10)
from sys.objects so where so.type ='U'

SELECT  1 AS TAG, 0 AS PARENT, @tmp
            as [SqlCommand!1!!cdata]
FOR XML
EXPLICIT, xmldata

(continua…)

Filed under : SQL Server

3 Comments »

Posto di seguito un semplice script per effettuare il backup di tutti i database (tranne il tempdb) presenti in un’istanza di SQL Server. L’ho implementato su un’istanza SQL Server 2005 a 64bit, ma funziona anche per le versioni precedenti come SQL2000 e sul nuovissimo SQL2008.

Backuppare i backup in un ambiente di produzione è un esigenza indiscutibile; in questo caso la tipologia di backup prescelta è quella full, che può essere tranquillamente utilizzata per DB di piccole e medie dimensioni, ma che può essere modificata per esigenze diverse. I backup vengono salvati nella cartella C:\BACKUP\SQL\.

Da notare che un backup automatizzato tramite script di tutti i DB presenti sull’istanza minimizza sicuramente l’intervento amministrativo.

DECLARE @dbid int
DECLARE @dbname varchar(100)
DECLARE @sql varchar(1000)

DECLARE curs CURSOR LOCAL FORWARD_ONLY FOR
select database_id, [name] from msdb.sys.databases where name <> ''tempdb''
OPEN curs
FETCH NEXT FROM curs INTO @dbid, @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = ''BACKUP DATABASE [''+@dbname+''] TO 

DISK = N''''C:\BACKUP\SQL\''+@dbname+''.bak''''
WITH NOFORMAT, INIT,  NAME = N''''''+@dbname+''-Full Database Backup'''',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10''

RAISERROR(N''Executing... %s'',2,-1,@sql)
EXEC( @sql )
FETCH NEXT FROM curs INTO @dbid, @dbname
END

(continua…)

Filed under : SQL Server

0 Comment »