Come sapete, a partire da SQL Server 2005, è possibile scrivere funzioni, stored procedure e altri oggetti in codice .net. Visual studio fornisce un tipo di progetto atto proprio a soddisfare questo tipo di attività. E' quindi possibile creare oggetti CLR che sono facilmente pubblicabili su SQL Server tramite poche semplici istruzioni. Inoltre, è anche possibile uscire da quello che è il contesto di SQL Server stesso, anche se in questo caso vi è da impostare opportunamente il tipo di accesso al deploy ed il database verso il quale si effettua la pubblicazione. E' altresì buona norma segnare con una strong name key (file .snk) il nostro assembly per aumentare ancora di più il livello di sicurezza.

A titolo di esempio, prendiamo questo scenario: abbiamo la necessità di creare una stored procedure che vada a creare un file in un percorso passato come parametro. Ecco come procedere:

1) Creiamo un progetto di tipo database (C# nell'esempio)



2) Creiamo un oggetto di tipo stored procedure



3) Ecco il codice per la stored procedure:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

// namespace File System (Esce dal contesto di sql server)

using System.IO;

 

 

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void CreateFile(String path)

    {

        // creo il file se non esiste, nel path passato come parametro.

        if (!File.Exists(path)) {

            FileStream fs = File.Create(path);

            fs.Close();

            fs.Dispose();

        }

 

    }

};

 

4) Ora passiamo al deploy:

Nelle proprietà del progetto andiamo ad indicare il nome dell'assembly ed il root namespace.

Nella parte Database andiamo a specificare la connection string al Db target.
Nella parte di deploy indichiamo se andare a pubblicare il codice o meno. Di solito evito di pubblicarlo per ragioni di "offuscamento" dei sorgenti .
Dopo aver impostato le proprietà come meglio si crede, effettuiamo il deploy Tasto destro del mouse--> Deploy, direttamente dal solution explorer sul progetto).

5) Controlliamo l'esistenza della stored procedure su SQL Server Management Studio:



dbo.CreateFile, presente! Inoltre, tra gli assembly, vi è anche il nostro FileUtilities (definito al punto 4).

6) Proviamo l'esecuzione

EXEC dbo.CreateFile 'C:\prova.ale'

GO

Lasciando impostato tutto come già proposto da Visual Studio nelle proprietà di progetto, ed in particolare con l'opzione PermissionLevel a SAFE, ovvero con il permission set più restrittivo possibile, otterremo il seguente errore:

Msg 6522, Level 16, State 1, Procedure CreateFile, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "CreateFile":

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

   at System.Security.CodeAccessPermission.Demand()

   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

   at System.IO.File.Create(String path)

   at StoredProcedures.CreateFile(String path)

.

Non è quindi possibile uscire dal contesto di SQL Server anche l'utente con cui siamo connessi ha le permission per scrivere su quel file.

6) Impostiamo il PermissionLevel a EXTERNAL, ovvero quel permission set che permette di "uscire" dal contesto SQL Server:

Effettuando il deploy otterremo il seguente errore:

Questo perchè è necessario impostare il database con l'opzione TRUSTWORTHY ad ON:

ALTER DATABASE <nomeDB> SET TRUSTWORTHY ON

Rieseguendo il deploy, con il database TRUSTWORTHY, con il permission set a EXTERNAL_ACCESS, siamo pronti per pubblicare la nostra stored procedure ed eseguirla. Ora tutto dovrebbe funzionare.

7) Segnare con un snk (facoltativo, ma più indicato)

Tramite la tool sn.exe inclusa nell'installazione di Visual Studio, è possibile creare dei file .snk (Strong Name Key) utili per firmare i nostri assembly.
Una volta creato il file, è possibile assegnarlo direttamente dalle proprietà del progetto:



Rieseguire il deploy per pubblicare il nostro assembly strongly signed.

Il tutto poteva essere eseguito anche tramite T-SQL utilizzando le istruzioni CREATE ASSEMBLY e il successivo CREATE PROCEDURE (o function, tipo utente, ecc.) indicando la clausola

EXTERNAL NAME assembly_name.class_name.method_name

Stay Tuned!