Accessing Databases in a Sandboxed Solution

Article
08/25/2010

Sandboxed Solution is a cool feature of SharePoint 2010, but as all of us know, not everything can be done with the Sandboxed Solution. For security consideration, Sandboxed Solution has its own CAS to restrict the call to some SharePoint APIs as well as some sensitive resources like network connections, local disk IOs etc. Of course we can modify the oob CAS, but it is definitely not a recommended way.

So what should we do if we want to access these resources in our sandboxed solutions? The answer is the full trust proxy. We can create a full trust proxy and register it in SharePoint 2010. Then we will be able to do what we want in the sandboxed solution by calling this proxy. The following is an example that I created in order to accessing SQL database in my sandboxed solution.

Creating a full trust proxy

Usually, a full trust proxy consists of two classes, a proxy operation class derives from SPProxyOperation and a corresponding arguments class derives from SPProxyOperationArgs.

In VS 2010, create a Class Library project, and add a reference to SharePoint assembly.
The assembly of our proxy should be able to be called by other partially trusted callers. So add AllowPartiallyTrustedCallers attribute in AssemblyInfo.cs

Add two classes, one is based on SPProxyOperation and another is based on SPProxyOperationArgs. The following is the code of my proxy.

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.UserCode;
using System.Data;

namespace Morpheus.Demo.FullTrustProxy
{
    [Serializable]
    public class SQLProxyArgs : SPProxyOperationArgs
    {
        public string ConnectionString;
        public string Command;
    }

    public class SQLFullTrustProxy : SPProxyOperation
    {
        public override object Execute(SPProxyOperationArgs args)
        {
            if (args != null)
            {
                SQLProxyArgs ftArgs = args as SQLProxyArgs;
                SQLDBAccess dba = new SQLDBAccess();
                dba.ConnectionString = ftArgs.ConnectionString;
                return dba.GetSqlResult(ftArgs.Command);
            }

            return null;
        }
    }
}

Build the project and deploy the assembly to GAC.

Registering the proxy in SharePoint

The following PowerShell commands can be used to register the proxy.

Add-PSSnapin Microsoft.SharePoint.PowerShell
$ucService = [Microsoft.SharePoint.Administration.SPUserCodeService]::Local
$assemblyName = "FullTrustProxy, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=75e25e9dc5ff21aa"
$typeName = "Morpheus.Demo.FullTrustProxy.SQLFullTrustProxy"
$proxyOperationType = New-Object -TypeName Microsoft.SharePoint.UserCode.SPProxyOperationType -ArgumentList $assemblyName, $typeName
$ucService.ProxyOperationTypes.Add($proxyOperationType)
$ucService.Update()

Using the proxy in the Sandboxed Solution

In the Sandboxed Solution, we can call our proxy by using SPUtility.ExecuteRegisteredProxyOperation.

 SQLProxyArgs proxyArgs = new SQLProxyArgs();
proxyArgs.ConnectionString = "Server=tcp:hostname.database.windows.net;Database=dbname;User ID=username@hostname;Password=password;Trusted_Connection=False;Encrypt=True;";
proxyArgs.Command = "select top (100) CustomerID, Title, FirstName, LastName from SalesLT.Customer";
string assemblyName = "FullTrustProxy, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=75e25e9dc5ff21aa";
string typeName = "Morpheus.Demo.FullTrustProxy.SQLFullTrustProxy";
DataTable dt = (DataTable)SPUtility.ExecuteRegisteredProxyOperation(assemblyName, typeName, proxyArgs);

The connection string I used here is like the one used to connect to the database in SQL Azure.

The attachment in this post is the source code of the full trust proxy.

FullTrustProxy.zip

Accessing Databases in a Sandboxed Solution

Additional resources