Articoli DevLeap

SQL 2005 Mobile Replication Components

Articolo pubblicato su Computer Programming 157 - Gruppo Infomedia.

SQL 2005 Mobile Edition (aka SQLCE 3.0) è la versione "ridotta" (striminzita sarebbe il termine più corretto) di SQL Server per la piattaforma Windows CE (aka Windows Mobile), utilizzabile, in questa ultima versione anche su piattaforma Windows XP Tablet PC Edition. L’accesso ai dati dal .NET Framework o .NET Compact Framework è identico nella forma all’utilizzo di Sql Server: il provider nativo System.Data.SqlServerCe implementa, infatti, le stesse interfacce del fratello maggiore System.Data.SqlClient.

SQLCE offre nativamente, sin dalla versione 1.0 del 1998, alcune metodologie per replicare i dati da e verso un database centrale SQL Server: le due tecniche, Remote Data Access e Merge Replication, sono essenzialmente semplici da usare ed esistono molti articoli pubblicati in varie forme che ne consentono l’apprendimento in modo rapido ed efficace. La parte più difficile e poco divulgata è l’installazione delle varie componenti, non tanto come passi di setup da eseguire, ma come architettura generale di replica: viene coinvolto il protocollo HTTP e di conseguenza Internet Information Service che, come sappiamo, ha una serie di meccanismi di autenticazione; in base al meccanismo scelto il thread che esegue la replica può girare con Security Principal Windows diversi. Inoltre, dal server IIS occorre aprire una connessione verso SQL Server che dovrebbe risiedere su un server separato rispetto al server web. Tutto questo crea un po’ di confusione sull’utilizzo di queste tecnologie e, facendo il consulente in giro per varie aziende, ho notato che spesso vengono aperte porte inutili sui firewall, o ancor peggio viene fatto girare il tutto con account amministrativi perché altrimenti…le cose non funzionano…

Scopo di questo articolo è dare un occhio alle varie componenti coinvolte (IIS, Autenticazione, Autorizzazione, Connection verso SQL, Impersonation, Delegation) e far luce sui meccanismi che stanno dietro questi componenti: quanto riportato in questo articolo si applica anche a scenari ben diversi dalla replica di informazioni fra SQLCE e SQL Server; anche le applicazioni ASP.NET devono fare i conti con questi concetti, spesso trascurati dagli sviluppatori.

Partiremo con la descrizione delle varie componenti per poi affondare il colpo sui meccanismi che regolano l’autenticazione e l’autorizzazione in IIS e SQL Server.

Per prima cosa occorre ricordare che SQL 2005 Mobile Edition è integrato in SQL Server 2005 e in Visual Studio 2005 oppure può essere scaricato gratuitamente dal sito Microsoft. Per integrazione con SQL Server 2005 e Visual Studio 2005 intendo la possibilità di creare e gestire database direttamente dall’IDE dei due prodotti e nel caso di Visual Studio anche la possibilità di legare strutture dati all’interfaccia utente costruita con i designer.

Senza perdersi nei requisiti harware e software cerchiamo di capire "cosa" installare lato client (sul device o emulatore) e sulla macchina Internet Information Service. Nella seconda parte dell’articolo vedremo invece quali sono i permessi da attributire agli utenti, come arrivare alla macchina SQL Server e come gestire l’autenticazione in IIS.

Come prima cosa occorre installare sulla macchina Internet Information Service la parte server di SQLCE, ovvero una serie di componenti che prendono il nome di SQL Server Replication Components. L’installazione di Visual Studio 2005 appoggia il setup di tali componenti nella directory x:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\Sql Server\Mobile\v3.0. All’interno di tale directory si trovano due file .msi: sql2kensp3a.msi che rappresenta il setup degli strumenti server per replicare i dati con Sql Server 2000 Service Pack 3 e sqlce30setupen.msi che rappresenta il setup per SQL 2005. In entrambi i casi, la dicitura "en" rappresenta la versione Inglese: se utilizzate software in italiano troverete i componenti con dicitura "it".

Nota Bene: la versione dei componenti server deve essere allineata al database utilizzato; ad esempio, se utilizzate SQL Server 2000 Service Pack 4 occorre scaricare (partendo da questo link) http://thinkmobile.it/blogs/rob/archive/2005/06/14/4020.aspx l’aggiornamento che supporti appunti il Service Pack 4 di SQL Server 2000. Quando usciranno altri Service Pack per la versione 2000 o la versione 2005 di SQL Server è probabile che debbano essere aggiornati nuovamente questi componenti server.

Visto che la connettività dal device (o emulatore) raggiunge la parte server via HTTP occorre controllare eventuali che Firewall (anche quello di XP SP 2) non blocchino le porte destinate alla comunicazione http (per default la porta 80 o 443 in caso di SSL, ma ovviamente potreste decidere di utilizzare altre porte: più avanti torneremo su questo argomento).

Sul server IIS è necessario installare anche il .NET Framework versione 2.0: è sufficiente la versione redistributable. Se usate la macchina di sviluppo per testare le repliche non occorre installare manualmente il .NET Framework 2.0 in quanto il setup di Visual Studio 2005 lo installa come prerequisito del prodotto.

Il Setup indicato installa le componenti necessarie e un wizard che automatizza le operazioni di creazione della virtual directory in IIS, l’impostazione dei permessi di accesso sulla dll lato server e alcune impostazioni per la Merge Replication. Dopo il setup lanciamo il wizard "Configure Web Synchronization Wizard" che troviamo sotto il menù Microsoft SQL Server 2005 Mobile Edition nel menù Start.
Proviamo a dare un senso alle impostazioni richieste dal wizard:

1) Subscriber Type: occorre scegliere se questo server IIS servirà per replicare i dati verso SQLCE oppure vero altri SQL Server. Nel caso di repliche di tipo Merge dai vari device Windows Mobile occorre scegliere SQL Server Mobile Edition.

2) Scegliere il sito e la virtual directory da utilizzare. È possibile creare anche una nuova virtual directory.
In pratica la dll che dovrà essere raggiunta dai device per eseguire le repliche deve essere esposta via http. È consigliabile creare una Virtual Directory specifica. Inoltre è possibile installare sia la replica verso SQL Server 2005 e verso SQL Server 2000 sulla stessa macchina semplicemente scegliendo due virtual directory diverse. Il path fisico di default della virtual directory è c:\Program Files\Microsoft SQL Server 2005 Mobile Edition\server\sqlce. In questa directory infatti viene copiata sqlcesa30.dll che rappresenta il server agent; la parte client sul device dovrà contattare questa dll passando dalla virtual directory: le classi da utilizzare per la replica (sia Merge che RDA) utilizzeranno la proprietà InternetUrl per raggiungere la visual directory e la di conseguenza la dll sul disco. Il path di default risulta quindi è http://nomeserver/sqlce/sqlcesa30.dll.

3) Occorre scegliere se dovrà essere utilizzato il protocollo SSL over HTTP (HTTPS). In questo caso le informazioni vengono criptate dal protocollo dal device fino al server IIS. Occorre un certificato digitale valido installato sul site IIS esattamente come accade per le applicazioni web protette da HTTPS.

4) Occorre scegliere il criterio di autenticazione: Anonimo o Autenticato. Nel primo caso non occorre fornire credenziali lato client per la richiesta http verso sqlcesa30.dll e occorre scegliere server side l’account che IIS dovrà utilizzare per far girare il thread che esegue la replica (IUSR_nomemacchina per default). Nel caso si scelga Autenticato occorrerà anche indicare se la virtual directory autenticherà utenti con la basic authentication oppure con l’autenticazione integrata con Windows (ovvero NTLM/NTCR). Ritorniamo su questo punto più avanti.

5) Se abbiamo scelto di utilizzare connessioni autenticate, è necessario impostare direttamente sul file sqlcesa30.dll (è necessario NTFS per decidere quali utenti possono arrivare a invocare la dll) il permesso di Read & Execute. Il wizard compie queste operazioni sul file system per noi, ma sappiate che è sempre possibile modificare queste impostazioni agendo proprio sul file indicato. In pratica il wizard compie una serie di operazioni che è possibile eseguire a mano.

6) L’ultimo passo di installazione del wizard consente di configurare la directory di rete dove verrano creati gli shapshot per la merge replication.

È importante sottolineare che tutte queste impostazioni riguardano solamente l’interazione fra il device e il server agent (sqlcesa30.dll). Negare l’accesso a tale file per l’utente Robertob significa che nessuna richiesta da parte di Robertob verso la replica avrà esito positivo a prescindere dai permessi di accesso che Robertob potrebbe avere sul database. In pratica queste impostazioni consentono a utenti anonimi o gruppi di utenti autenticati di arrivare via HTTP e inizializzare il server agent. Sarà poi il server agent a connettersi verso SQL Server (con la stringa di connessione specificata: standard o integrata) e quindi un utente che ha il permesso di raggiungere il server agent potrebbe poi non avere nessun permesso in SQL Server…e viceversa. È importante capire che le due cose sono distinte anche se spesso ha sicuramente senso impostare gli stessi permessi. Pensate però al caso in cui più applicazioni debbano replicare i dati verso lo stesso database server. È possibile aprire l’accesso al server agent a tutti gli utenti di tutte le applicazioni e poi consentire l’accesso ai vari database di SQL Server in base ai ruoli effettivi degli utenti per il particolare db. Ad esempio Robertob e Paolop potrebbero avere entrambi accesso al server agent, ma Robertob deve replicare i dati con il database dell’applicazione X, mentre Paolop deve replicare i dati con il database dell’applicazione Y. È ovviamente anche possibile creare due virtual directory diverse che puntano a due directory fisiche dove deve essere copiato il server agent (sqlcesa30.dll) con permessi di accesso completamente diversi. A voi la scelta: non ci sono differenze da un punto di vista di performance o sicurezza. Se utilizzate due virtual directory diverse è possibile anche abilitare in una l’autenticazione Basic e arrivare a SQL Server in sicurezza integrata, mentre nell’altra abilitare una tipologia di autenticazione e arrivare a SQL Server in sicurezza standard. In soldoni, le possibilità sono varie e spero che con questa descrizione riusciate a scegliere quella che si adatta meglio al vostro scenario.

Sui vari device o sugli emulatori occorre installare la parte client di SQL 2005 Mobile Edition. Quando si effettua un deploy da Visual Studio 2005 oppure semplicemente una operazione di debug (il classico F5) è lo stesso Visual Studio a installare del device (o emulatore) corrente la parte client del prodotto.

La parte client è divisa in 3 file .cab separati:

1) sqlce30.piattaforma.processore.cab che contiene i file sqlcese30.dll (il motore di SQLCE), sqlceqp30.dll (il query processor), sqlceme30.dll (contiene il codice binario necessario alla libreria .NET System.Data.SqlServerCe). Utilizzando SQLCE localmente, senza effettuare repliche, questo è l’unico componente necessario.

2) sqlce30.repl.piattaforma.processore.cab contiene tutte le dll necessarie per eseguire repliche e più precisamente sqlceca30.dll (è il Client Agent della replica, colui che contatta il server agent esposto dalla virtual directory), sqlceoledb30.dll (necessario solo se si accede via OLE-DB al database locale, utile per applicazioni C++), sqcecompact30.dll (fornisce le funzioni di compattazione esposte in .NET dalla classe SqlCeEngine metodo Compact).

3) sqlce30.dev.lingua.piattaforma.processore.cab contiene ISQLW30, la mini interfaccia di ammistrazione utilizzabile direttamente sul device (o emulatore) e sqlceerr.dll nelle varie lingue.

SQLCE e più in generale qualunque software nativo per Windows CE è scritto in codice binario e, visto che Windows CE può girare su diversi processori, anche i file .cab devono essere forniti per i vari processori e per le varie piattaforme: sotto x:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0 si trovano infatti due directory:

1) wce400 con le varie dll e cab per la versione 4.x di Windows CE

2) wce500 con le varie dll e cab per la versione 5.x di Windows CE

All’interno di queste directory trovare una directory per ogni processore supportato: ad esempio la cartella armv4i si riferisce al processore ARM V4i, mentre la cartella X86 contiene i file necessari per un device basato su processore X86. Ricordo che tutti gli emulatori forniti con Visual Studio 2005 sono scritti per il processore ARM (al contrario degli emultatori di Visual Studio 2003 che erano basati su X86), quindi volendo provare un setup sull’emulatore Windows Mobile 5 for Pocket PC occorre prendere i vari cab dalla directory armv4i all’interno della directory wce500.

Sperando di aver fatto un po’ di luce sulle varie componenti da scegliere client-side e server-side puntiamo la prua sull’interazione fra le varie componenti.

Prima di testare una replica completa vi consiglio di testare ogni singolo componente per capire, a fronte di problemi, il punto esatto in cui intervenire.

Il primo passo è verificare da Internet Explorer sul device (o sull’emulatore) se si riesce a raggiungere il server agent tramite http://nomeserver:porta/sqlce/sqlcesa30.dll. In base a come avete configurato la virtual directory potrebbe essere richiesta una autenticazione.

Vi consiglio da subito di provare con account non amministrativi sulla macchina IIS in modo da verificare l’effettivo accesso di utenti applicativi alla virtual directory e al file su disco. La pagina che deve apparire contiene semplicemente la stringa "Sql Server Mobile Server Agent 3.0". Se avete problemi di autenticazione occorre verificare i permessi di accesso alla Virtual Directory di IIS e al file sqlcesa.dll sul file system. Vi consiglio di risolvere subito questi problemi fino ad avere la pagina funzionante.

Se non si riesce a raggiungere la dll il problema risiede nella connettività del device (o dell’emulatore) verso il server. Potrebbe essere la risoluzione del nome, la mancanza di ip sul device o semplicemente un problema di connessione fra i due. A tal proposito si veda il post http://thinkmobile.it/blogs/rob/archive/2006/03/14/5311.aspx che indica vari strumenti utili per verificare la connessione e connettere l’emulatore alla rete.

Se si riesce a raggiungere la dll non ci sono problemi di connettività fra device e server IIS. Utilizzate la stessa path per valorizzare la proprietà InternetUrl e se configurato un meccanismo di autenticazione utilizzate gli account funzionanti usati da Internet Explorer nelle proprietà InternetUser e InternetPassword delle classi utilizzate sul device.

Dopo queste operazioni iniziate a testare la replica: se qualcosa non funziona il problema risiede nella connettività fra il Server Agent e SQL Server e non fra device e server IIS. Potrebbe essere un problema semplice legato alla stringa di connessione come db sbagliato, macchina SQL inesistente. Se la stringa è corretta, provate la prima replica con RDA anche se poi deciderete di usare la Merge Replication in quanto ci sono meno implicazioni sulla security. Utilizzate semplicemente il metodo Pull su una tabella qualunque per controllare di non aver errori: provate con un account amministrativo di SQL in sicurezza standard (non con Trusted Connection per adesso) per capire se si riesce ad arrivare al DB con un account che sicuramente può entrare nel db. A prescindere dal fatto che funzioni o non funzioni leggete il prossimo punto dell’articolo dove cerco di fare luce su Impersonation, autenticazione in IIS, connessioni standard e integrate verso SQL Server.

Se avete abilitato l’accesso anonimo su IIS, quest’ultimo utilizza per default l’account IUSR_nomemacchina (o l’account Windows che avete indicato nelle proprietà della virtual directory) per far girare il thread che esegue il server agent. Questo significa che se utilizzate la sicurezza integrata per arrivare a SQL Server (trusted connection) sarà l’utente IUSR_nomemacchina a chiedere una login verso SQL Server: in questo scenario è necessario abilitare la login per tale account in SQL Server, impostare i permessi di accesso all’utente o ad un ruolo a cui appartiene per le varie tabelle da replicare.

Se invece, abilitate l’autenticazione Basic o NTCR, il client (tramite username e password impostate da codice) si presenta a IIS con le credenziali dell’utente: in questo caso IIS utilizza l’account fornito dal client per far girare il thread che esegue il server agent. Questo significa che se utilizzate la sicurezza integrata per arrivare a SQL Server (trusted connection) sarà l’utente fornito dal client a chiedere una login verso SQL Server: in questo scenario è necessario abilitare la login per tale account in SQL Server, impostare i permessi di accesso all’utente o ad un ruolo a cui appartiene per le varie tabelle da replicare. Riprovate il metodo Pull.

La cosa importante che volevo passare è la seguente: occorre capire che IIS utilizza un thread per gestire la richiesta di sincronizzazione; questo thread gira con un account windows che può essere IUSR_nomemacchina (o quello configurato come utente anonimo) in caso abilitazione dell’utente anonimo oppure l’account che il client a fornito nella chiamata; questo account è colui che si presenta a SQL Server se decidiamo di usare una trusted connection verso il database.

Se invece viene utilizzata la sicurezza standard per arrivare al SQL Server, a prescindere dall’autenticazione di IIS e quindi dall’account utilizzato per far girare il thread in IIS, sarà sempre l’utente fornito nella OleDBConnectionString (parametro da passare per Pull e Push con RDA o proprietà PublisherLogin e PublisherPassword per la Merge Replication) a chiedere una login a SQL Server. Per questo motivo conviene provare sempre per prima una connessione standard: giusto per semplificarci la vita nelle prime prove.

Se il database server è installato su un’altra macchina la situazione si complica un attimo. Non ci sono problemi se usate una connessione con autenticazione standard verso SQL Server: le connessione standard si basano sulle TCP/Socket (protocollo da abilitare in SQL Server, per default lavora sulla porta 1433); una richiesta su questo protocollo non viene autenticata dal sistema operativo della macchina che ospita SQL Server; è sufficiente che la macchina non blocchi la porta utilizzata per far arrivare la connessione fino al motore di SQL Server; vengono poi validati username e password forniti nella connection string.

Se invece usiamo la sicurezza integrata di SQL Server (Windows Authentication) è necessario in prima battuta che l’account che fa girare il thread di IIS sia un account di dominio conosciuto dalla macchina SQL Server e dalla macchina IIS: ovviamente utilizzando un account locale alla macchina IIS, il poveretto J non riesce ad arrivare all’altra macchina: non è SQL Server a bloccare il tutto, ma bensì Windows stesso che non consente ad un utente di arrivare alla macchina se non è un account valido. Inoltre è necessario che l’utente (o il suo gruppo) abbia il permesso di raggiungere la macchina SQL Server con il permesso "Access This Computer From Network": ancora una volta l’utente potrebbe essere l’amministratore di SQL, ma non riesce a "sfondare" la scheda di rete della macchina in quanto Windows lo blocca. Ricordo che per default l’utente anonimo IUSR_nomemacchina è un account locale, quindi se volete usare l’accesso anonimo occorre impostare sulla virtual directory un account di dominio (e che abbia accesso alla macchina SQL come indicato).

Capito che, con la sicurezza integrata con SQL Server, dobbiamo usare account di dominio (o almeno account identici in entrambe le macchine…non è mai una buona idea…i domini Windows sono nati per questo) svisceriamo l’ultimo problema legato all’impersonation: se consentite l’accesso anonimo o l’autenticazione Basic (ricordo che se non usate Https le credenziali passano in chiaro) non ci sono problemi a raggiungere la macchina SQL Server in quanto l’utente nasce sulla macchina IIS. IIS infatti, nel caso di utente anonimo, fa nascere l’account (dovremmo dire il Security Principal dell’utente) sulla macchina IIS e quindi può passarlo alla macchina SQL per l’autenticazione di rete. Anche nel caso di Basic Authentication, IIS fa nascere l’account sulla macchina IIS in quanto riceve dal client due stringhe rappresentanti UID e PWD che lui stesso autentica creando il Security Principal. Il Security Principal può essere passato alla macchina SQL per l’autenticazione di rete.

Che c’entra tutto questo ? Windows, per default, non può inviare ad un’altra macchina un Security Principal che lei stessa ha ricevuto dalla rete. Con l’autenticazione NTCR (denominata Windows Integrated nella maschera di autenticazione di IIS), è il client a far nascere il Security Principal che viene passato (criptato: in realà non passa neanche l’account ma un valore calcolato in base alle credenziali) alla macchina IIS: a questo punto la macchina IIS non può passare il Security Principal ricevuto dal client alla macchina SQL per l’autenticazione di rete. Il comportamento sembra strano: utilizzando la Basic funziona tutto, ma se abilitiamo anche l’autenticazione NTCR in IIS non siamo più in grado di raggiungere il database…in realtà il database server. Quindi, il meccanismo più sicuro (NTCR) di autenticazione non consente di arrivare al database server, mentre il metodo meno sicuro (Basic Auth) consente di arrivare al database server. È possibile modificare questa restrizione (non è una limitazione, ma un criterio di protezione) abilitando gli utenti e le due macchine coinvolte al meccanismo di "Delegation": questa operazione si effettua dalla gestione delle macchine e degli utenti negli Administrative Tools del dominio. In pratica la Delegation, nel nostro scenario di replica, indica a Windows che la macchina IIS è delegata ad usare il Security Principal arrivato via http verso l’altra macchina. La Delegation si usa in tutti i contesti in cui si vogliono far passare queste credenziali, non solo per gestire la replica di SQLCE quando IIS e SQL sono su due macchine diverse e autentichiamo gli utenti con NTCR.

Ultime considerazioni sui meccanismi di autenticazione:

1) L’autenticazione NTCR non passa per default dai firewall

2) L’autenticazione Basic fa passare le credenziali (uid e pwd) in chiaro dal client al server web

3) In ogni caso se non usate Https, anche se l’autenticazione è protetta con NTCR, i dati che poi passano sul cavo non sono criptati. Usate sempre Https se passate da Internet senza VPN.

Ultimo punto: se utilizzate la Merge Replication occorre inserire l’utente o il gruppo che chiede la login a SQL Server nella PAL (Publication Access List) per consentire appunto all’utente di accedere alla pubblicazione delle informazioni.

Sperando di aver fatto luce sulle componenti della replica e aver chiarito un processo che, dal 1996, fa diventare matti gli sviluppatori che si trovano un server IIS in mezzo alle scatole, vi indico alcune risorse sul web che contengono informazioni correlate agli argomenti trattati.

http://www.thinkmobile.it - comunità italiana per lo sviluppo mobile con strumenti Microsoft – Molti forum contengono info, problemi e soluzioni sui meccanismi di replica, sia RDA che Merge Replication: ad esempio la gestione dei campi identity e la gestione dei GUID.

http://msdn.microsoft.com/mobility/windowsmobile - sito ufficiale Microsoft per gli sviluppatori mobile. Da qui tonnellate di link verso articoli e knowledge base in inglese.

http://blogs.devleap.com – sito web con articoli tecnici su .NET, Mobility, Architetture distribuite, Visual Studio Team System.

Roberto Brunetti

roberto@devleap.it

Roberto è un libero professionista del gruppo DevLeap (www.devleap.com) sul cui sito si trovano articoli e blog tecnici sulle tecnologie legate allo sviluppo software in .NET. E’ specializzato in ASP.NET, Sviluppo mobile, Architetture distribuite e Visual Studio Team System. E’ l’autore del libro ASP.NET Full Contact edito da Mondadori Informatica e numerose pubblicazioni su riviste del settore. Ha partecipato come speaker a numerose conferenze del settore ed è MCT da 1997. È il fondatore della community www.ThinkMobile.it dedicata allo sviluppo mobile.