Utilizzare i Web Services con SQL Server 2005

Di Andrea Benedetti - Microsoft MVP

In un’architettura orientata ai servizi (SOA), l’utilizzo del protocollo HTTP consente la pubblicazione di web services basati su SOAP (Simple Object Access Protocol): messaggi costituiti da documenti XML con un’intestazione e un corpo in grado di far comunicare tra loro dei servizi.

Questa tipologia di comunicazione nasce, soprattutto, per permettere interoperatività tra sistemi anche eterogenei utilizzando XML come supporto e protocolli standard.

SQL Server, da sempre, consente ai client di comunicare tramite TDS: Tabular Data Stream.

Questa comunicazione, però, necessità di librerie apposite e conosciute dal client: ADO.Net, oledb, ODBC, JDBC, freeTDS, …

Una delle novità più interessanti di SQL Server 2005 è proprio quella di poter esporre, come Web Services, stored procedures, funzioni utente o istruzioni T-SQL fornendo supporto nativo al protocollo HTTP.

Un servizio web consente l’utilizzo (il consumo) di funzionalità attraverso internet (intranet) consentendo:

• Interoperabilità: chi è in grado di supportare HTTP e XML può accedere alle funzionalità esposte

• Riutilizzo del codice: posso esporre componenti e librerie riutilizzando funzioni senza doverle riscrivere

• Utilizzo di standard industriali

In realtà non si tratta di una necessità nuova infatti, già con la precedente versione, SQLXML consentiva di fatto la comunicazione attraverso XML.

Con questa nuova tecnologia, oggi, SQL Server mette direttamente a disposizione tutta l’infrastruttura necessaria fornendo meno strati, maggior sicurezza e maggior velocità.

Per poter utilizzare questa funzionalità è necessario definire un endpoint di tipo HTTP nel server che non è altro che una porta di comunicazione tramite il quale il client può inviare istruzioni al server stesso.

Lo stesso oggetto viene utilizzato anche dall’altra importantissima novità di SQL Server 2005: il Service Broker.

Gli ENDPOINT HTTP sono in grado di ricevere richieste sulla stessa porta (di default la 80) utilizzata da IIS (Internet Information Services) per i servizi web.

Questo è possibile perché il listener HTTP di sistema (implementato con la libreria http.sys) è stato spostato, nel sistema operativo Windows Server 2003 (necessario per poter utilizzare l’HTTP Kernel Procol Stack ), ad un livello più alto.

Proprio per questo motivo, ovvero per il fatto di ricevere le richieste prima di IIS (e quindi anche prima di SQL Server), il nostro sistema operativo può anche non avere installato IIS e rispondere perfettamente alle richieste di consumo dei web services presenti sull’istanza.

Come per tutti i Web Services è necessario avere un file WSDL (Web Service Description Language), ovvero il “contratto” che descrive i servizi e le funzionalità esposte.

Un’istanza WSDL è un documento XML che, tipicamente, presenta una struttura simile a:

<wsdl:definitions>
  <wsdl:types>...</wsdl:types>
  <wsdl:message>...<wsdl:message/>
  <wsdl:portType>...</wsdl:portType>
  <wsdl:binding>...</wsdl:binding>
  <wsdl:service>...<wsdl:service>
<wsdl:definitions>

SQL Server è in grado di generare, in autonomia, tale documento (WSDL predefinito) e restituirlo al client che ne fa richiesta, oppure di mettere in condizione i dba e gli sviluppatori per crearne una versione custom (WSDL personalizzato).

Vantaggi della tecnologia

• I web services esposti da SQL Server 2005, fondandosi su standard industriali come XML su protocollo HTTP, sono consumabili da tutte le tecnologie software in grado di analizzare XML e di dialogare, tramite richieste http. Significa, ad esempio, che i servizi esposti dalla nostra istanza possono essere utilizzati anche da macchine che hanno sistemi operativi diversi da Windows

• Barriere di sicurezza come i firewall non devono consentire accesso a porte differenti da quelle usualmente utilizzate per il traffico HTTP

Configurazione e utilizzo

Vediamo quali sono i passaggi necessari e suggeriti per poter rendere disponibile, e quindi utilizzare, questa tecnologia:

1. Creazione di un ENDPOINT HTTP

2. Sicurezza dell’ ENDPOINT HTTP

3. Riservare un namespace URL

4. Utilizzo e consumo

Il linguaggio T-SQL è stato ampliato con una serie di istruzioni CREATE / ALTER / DROP ENDPOINT per la gestione degli ENDPOINT (che esistono per poterli utilizzare tramite SOAP, SERVICE BROKER, DATABASE MIRRORING).

Con l’istruzione di CREATE andremmo a configurare il nostro ENDPOINT con la sua location, la sua sicurezza e le operazioni permesse (WebMethod).

Ad esempio:

CREATE ENDPOINT sql_endpoint 
STATE = STARTED
AS HTTP
(
    PATH = '/sql',
    AUTHENTICATION = (INTEGRATED), CLEAR_PORT = 8080,
    PORTS = (CLEAR),
    SITE = 'localhost'
   )
FOR SOAP 
(
   WEBMETHOD 'GetSqlInfo' 
            (name='master.dbo.xp_msver', SCHEMA=STANDARD ),
   WSDL = DEFAULT,
   SCHEMA = STANDARD,
   DATABASE = 'master',
   NAMESPACE = 'http://tempUri.org/'
   ); 
GO

La prima parte dell’istruzione “.. AS HTTP …” definisce tutte le informazioni relative al protocollo di trasmissione, l’eventuale porta di comunicazione e la modalità di autenticazione.

Con PATH andremo a specificare l’URL virtuale dove risiederà il nostro WS.

Con PORTS andremo a specificare l’utilizzo del protocollo HTTP su porta 80 di default, piuttosto che HTTPS (SSL, con porta 443 di default).

Con SITE andremo a specificare il nome del server dove il nostro WS sta girando.

La seconda parte “… FOR SOAP…” definisce i metodi che saranno resi disponibili all’esterno, la tipologia di WSDL, il database di utilizzo.

Da ricordare che le istruzioni sugli oggetti ENDPOINT possono essere compiute solo da:

• Membri appartenenti al ruolo sysadmin

• Proprietari degli ENDPOINT

• Utenti ( gruppi) a cui è stato concesso il CONNECT sull’ENDPOINT

Nel codice di esempio il servizio che andremo a costruire avrà un solo metodo utilizzabile (consumabile) di nome “GetSqlInfo” che, alla sua chiamata, andrà a eseguire, all’interno del database di sistema MASTER la stored procedure di sistema xp_msver che ritorna un result simile a quello in figura.

Molto importanti, su questa istruzione, le diverse possibilità di configurazione che permettono, tra l’altro, la possibilità di utilizzare protocollo SSL con possibilità di specificarne le porte di utilizzo (SSL_Port), o la modalità di autenticazione (basic authentication, WS-Security, integrata).

Per poter accettare connessioni gli ENDPOINT devono essere avviati (STATE = STARTED), mentre non potrebbero farlo se fermi (STATE = STOPPED).

Con WEBMETHOD, invece, specifichiamo il nome dell’oggetto e la stored procedure o l’user function che lo implementa.

Le viste di sistema di SQL Server 2005 consentono, tra l’altro, anche un monitoraggio degli ENDPOINT creati e in uso:

• sys.endpoints fornisce informazioni circa tutti gli endpoint presenti e i loro stati

• sys.endpoints_webmethod fornisce informazioni sia sulle operazioni (funzionalità) messe a disposizione dai WS, sia i riferimenti alle SP o UDF utilizzate

I WebMethod possono fornire cinque tipologie di output, restituiti all’interno dell’elemendo <nomeStoredProcedureResult>:

1. SqlRowSet: risultato di un’istruzione di SELECT

2. SqlXml: risultato di un’istruzione SELECT … FOR XML

3. SqlRowCount: restituisce il numero di righe interessate dall’istruzione eseguita

4. SqlResultCode: return code di SP / UDF

5. SqlMessage: restituisce messaggi di errore, avvisi e informazioni

A questo punto non ci resta che provare a vedere se il nostro server risponde regolarmente andando a chiedere, ad esempio, il WSDL del Web Service appena creato.

Ci è sufficiente aprire un browser e digitare l’indirizzo: https://localhost/sql?wsdl

Il browser, a questo punto, ci fornirà un documento XML che è proprio la spiegazione del servizio web presente a quell’indirizzo.

Adesso, con un qualisiasi strumento di sviluppo (ad esempio Visual Studio), è sufficiente referenziare il WS (add Web Reference al nostro progetto) per poter utilizzare le funzionalità esposte ricordando i tipi di oggetto di ritorno:

SQL Server	Oggetti .Net
Risultato di Select	System.Data.DataSet
Risultato di Select… FOR XML	System.Xml.XmlElement
Errore	SqlMessage (from WSDL)
Messaggio	SqlMessage (from WSDL)
Parametro di uscita	SqlParameter (from WSDL)
Numero di righe ritornate	SqlRowCount (from WSDL)
Valore di RETURN	System.Int32

Riservare URL

SQL Server fornisce anche una stored procedure di sistema, sp_reserve_http_namespace, che permette di riservare, ovvero di registrare, degli URL a uso esclusivo di questa nuova tecnologia.

Con un’istruzione come:

EXEC sp_reserve_http_namespace N'https://localhost:80/myWsTest'

Andremo a riservare, sulla nostra macchina, il path “myWsTest” in modo che questo non possa essere utilizzato, ad esempio, da altre virtual directory.

Questa pratica non è necessaria, il comando CREATE ENDPOINT, con la definizione di PORTS, SITE e PATH, di fatto effettua per noi l’operazione di registrazione ma è comunque consigliata se si vuole avere la garanzia che nessun altro possa utilizzare (registrare) lo stesso URL anche quando il nostro endpoint dovesse essere fermo (inattivo).

Mettere in sicurezza i WS

Un aspetto da non sottovalutare è, come sempre, la sicurezza dei nostri dati e delle nostre informazioni.

Per garantire un facile controllo di accesso ai nostri ENDPOINT creiamo sulla nostra macchina, facendo un esempio, un gruppo in cui andremo a inserire gli utenti a cui vorremmo consentire l’utilizzo e il consumo dei WS.

Supponendo di chiamare il gruppo come “WS_User” vediamo le istruzioni che ci sono necessarie per garantirgli l’accesso e l’utilizzo:

USE MASTER
GO

EXEC SP_GRANTLOGIN @loginame = 'chicago\WS_User'
EXEC SP_GRANTDBACCESS @loginame = 'chicago\WS_User'
GO

USE myDatabase
GO

GRANT EXECUTE ON myStoredProcedure to [chicago\WS_User]
GO

USE MASTER
GO

GRANT CONNECT ON ENDPOINT::myEndpoint to [chicago\WS_User]
GO

Per prima cosa creiamo al nostro gruppo un account a SQL Server, poi lo aggiungiamo al database corrente.

Permettiamo l’esecuzione della stored procedure che avremo esposto come Web Method e, come ultima istruzione (la più importante), permettiamo al gruppo di potersi connettere al nostro ENDPOINT.

Conclusioni

L’utilizzo di questa tecnologia può diventare molto interessante in tutti quegli scenari in cui dobbiamo poter fornire delle informazioni anche a sistemi differenti, mantenendo un alto livello di sicurezza.

La trovo comunque non adatta, per motivi prestazionali, in tutti quegli ambienti in cui i nostri dati verranno utilizzati con molti accessi simultanei e concorrenti con tra