SQL Azure Limit for SharePoint Autohosted App

To find the SQL Azure Edition and its maximum size in the SharePoint Online Autohosted app, firstly we need to get the connection string of the database used by the app by displaying it in an aspx page or more subtly returning it using Web API. Don’t forget that the connection string is defined in the AppSettings, snippet below shows how to get the value from the code.

public string GetConnectionString()
{
   return WebConfigurationManager.AppSettings["SqlAzureConnectionString"];
}

Normally you will get something like below.

Data Source=(a_server).database.windows.net;
Initial Catalog=db_(guid);
User ID=db_(guid_as_above)_dbo;
Password=(some_pwd)

Put this connection string to Visual Studio’s SQL explorer or SQL Management Studio. The User ID won’t have access to master db so that you need to connect directly to the database.
After it gets connected run this below scripts to get the SQL Edition and limit, thanks to Azure How-to talk series’ post

SELECT DATABASEPROPERTYEX('database name', 'EDITION')
SELECT CONVERT(BIGINT,DATABASEPROPERTYEX ( 'database name' , 'MAXSIZEINBYTES'))/1024/1024/1024 AS 'MAXSIZE IN GB'

And the result is Azure Web Edition and Max limit is 1 GB, and unfortunately we can’t change this using ALTER DATABASE command as shown here as we don’t have access to master db. Below is the screen shot from VS 2013′s SQL Server Explorer:

Azure_SQL_Autohosted

P.S. please let me know if there is a way to change the limit :)

Call Sharepoint Online CSOM from an external application

There might be time when an external application want to talk to SharePoint Online (Office 365) without user interaction. Below are several scenarios that we might want to use SharePoint 2013 resources from external applications such as:

  • A console/windows application/service to perform administration to Skydrive Pro or SharePoint Online
  • Use SharePoint Online resources from Azure worker role i.e. uploading documents to a document library or adding items in a list or interact with the workflow,etc

These are steps to allow an external application to use site collection resources:

Firstly, we need to register a new SharePoint app. If the external application needs to access a site collection in SharePoint Online for example https://mytenant.sharepoint.com/sites/dev, we need to register an app by going to an application page called appregnew.aspx. For site collection above, it will be https://mytenant.sharepoint.com/sites/dev/_layouts/15/appregnew.aspx.

appregnew.aspx

  • Generate Client Id – and copy it to notepad
  • Generate Client Secret – and copy it to notepad
  • Title is your app title
  • App domain, the domain of your app, or anything such as guid. I normally put a URI that identifies the external app
  • Redirect URI, can be blank or put the current site collection url where this app registered.

Then click create button, it will register an Azure AD’s Service Principal with Id equals to the Client Id. This Service Principal will allow the OAuth process between the external application and SharePoint Online. (You can run this command from Office 365 Powershell console  to get more information about the service principal: Get-MsolServicePrincipal -AppPrincipalId <Client Id>) This msdn article provides some guideline about registering SharePoint app.

Next step, we need to set the permission for the app by going to /_layouts/15/appinv.aspx. The permission will authorize the external application to access SharePoint resources.
appinv

  • App Id: copy the Client Id we created at the first step and click Lookup. it will populate the other information except the Permission Request XML.
  • In the Permission Request XML put the below xml. The AllowAppOnlyPolicy flags that the registered app can be access by external application regardless the user.
  • The scope represents the permission right that the app can have. I noticed that with Write permission I am still getting access denied for uploading documents or adding items in a list. So I need to have FullControl permission.
<AppPermissionRequests AllowAppOnlyPolicy="true">
    <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" />
</AppPermissionRequests>

Off course you need to be a site collection admin to be able to set permission as above, then click Create and click Trust It on the next screen. If you want to call SharePoint CSOM against any personal sites in SkyDrive Pro you need to register the app to have full control with tenant scope http://sharepoint/content/tenant. But only tenant admininstrator can register an app with this scope. This article contains all possible scopes that you can use in app permission.

TenantIDBefore we jump to the code we need to get the Realm (in the case of SharePoint Online, it is the Tenant Id). Go to /_layouts/15/appprincipals.aspx and copy the GUID after the ampersand to notepad. Click the image for more detail

Now we’re ready to write code, in your external application project add the TokenHelper.cs, you can get the file from any SharePoint App project then put the appsettings in the project’s app.config or web.config that will be used by the Token Helper as below

<appSettings>
    <add key="ClientId" value="the-client-id"/>
    <add key="ClientSecret" value="the-client-secret"/>
    <add key="Realm" value="the-tenant-id"/>
  </appSettings>

These setting will be used by the Token Helper to perform the OAuth process. Below is the code to get the SharePoint Client Context that can be used to access the site collection in SPO using CSOM.

 public static ClientContext GetClientContextForApp(Uri siteUrl)
 {
    var SharePointPrincipalId = "00000003-0000-0ff1-ce00-000000000000";
    var token = TokenHelper.GetAppOnlyAccessToken(SharePointPrincipalId, siteUrl.Authority, null).AccessToken;
    return TokenHelper.GetClientContextWithAccessToken(siteUrl.ToString(), token);
 }

The GetAppOnlyAccessToken’s 3rd parameter is targetrealm, it sets to null as it uses the one set in the appsettings. If you want to retrieve the realm dynamically you can call TokenHelper.GetRealmFromTargetUrl method, but this will make another https roundtrip to Azure AD.

With this approach we can use SharePoint Online resources from any external applications such as Azure Worker can upload files to a document library or insert items to a List or kick of a workflow in SharePoint.

Apply DesignPackage using SharePoint 2013 Client Object Model

SharePoint 2013 introduced Design Manager as part of Publishing feature to provision look and feel such as master page, page layouts, theme, composed look,etc. We can also package the design files as a design package which actually is a sandbox solution file (wsp). Whenever we import a design package to SharePoint site, in the background it performs these tasks:

  • Rename the wsp (design package) file and upload it to the Solution Gallery
  • Activate the solution file and Activate the features inside the solution. The feature will provision the design files into the master page gallery
  • And finally apply the design such as applying the master page to the site.

There are also API available both server and client APIs to perform this task. The client API gives us the ability to push the Design Package to remote site such as SharePoint Online (Office 365). This below code show how to use the client API to provision design package to remote site. The fileUrl is the location of the file in the document libraryrelative to the site such as “SiteAssets/Bootstrap.wsp”. This post shows the routine to upload the file to the document library using Client Object Model.

private static void ApplyDesign(ClientContext context, string fileUrl)
{

  if (context.IsSiteFeatureActivated(PublishingFeature))
  {
    DesignPackageInfo info = new DesignPackageInfo()
    {
       PackageGuid = Guid.Empty,
       MajorVersion = 1,
       MinorVersion = 1,
       PackageName = "Bootstrap"
    };
    Console.WriteLine("Installing design package ");

    string fileRelativePath = GetSiteRelativePath(context.Url) + fileUrl;
    DesignPackage.Install(context, context.Site, info, fileRelativePath);
    context.ExecuteQuery();

    Console.WriteLine("Applying Design Package!");
    DesignPackage.Apply(context, context.Site, info);
    context.ExecuteQuery();
  }
}

First we check if publishing feature GUID (f6924d36-2fa8-4f0b-b16d-06b7250180fa) is activated in site collection level. This method as well as the GetSiteRelativePath are a custom methods. The DesignPackageInstall url requires to pass the wsp url relative to the root for example in a site collection: https://xx.sharepoint.com/sites/TestA/ we need to pass /sites/TestA/SiteAssets/Bootstrap.wsp to the method.

 public static bool IsSiteFeatureActivated(this ClientContext context, Guid guid)
 {
    var features = context.Site.Features;
    context.Load(features);
    context.ExecuteQuery();

    foreach (var f in features)
    {
        if (f.DefinitionId.Equals(guid))
          return true;
    }
    return false;
  }

  public static string GetSiteRelativePath(string url)
  {
     string urlDoubleSlsRemoved = url.Replace("://", string.Empty);
     int index = urlDoubleSlsRemoved.IndexOf("/");
     return urlDoubleSlsRemoved.Substring(index);
   }

Another thing that I found is the DesignPackage.Install method will install the wsp file into the solution gallery. As there is no client API to perform Sandbox solution installation we can literally use this method(DesignPackage.Install) to install ANY sandbox solution packages.

Upload a wsp file to Office 365 (SP 2013) using WebClient

In recent project I need to create a script to apply a design package to site collections in SharePoint Online. The first thing I need to do is to upload the design package (wsp file) to a document library. There is Client API to upload the file to SharePoint but there is size limitation, so I decided to use WebClient  PUT method instead. There are some catches that I found when using this approach:

  • I couldn’t just set the WebClient.Credentials to SharePoint Online Credentials as I would get 403 error. Solution: I need to send the SharePoint security token back as a FedAuth cookie, as a result I need to have a custom WebClient that has Cookie Container
  • I was getting ServerException (Unable to read cabinet info from) when run the Client API DesignPackage.Install on the uploaded wsp file. And I found that the WebClient.UploadFile method somehow didn’t maintain the file format properly. Solution: I need to use the OpenWrite method and upload it in a stream

Below is the working code. First I created a custom web client that accepts Cookie. There is other benefit of using this approach as we have more control on the web request object such as we can increase the timeout as well.

    class SPWebClient : WebClient
    {
        public CookieContainer CookieContainer { get; set; }

        protected override WebRequest GetWebRequest(Uri address)
        {
            HttpWebRequest request = base.GetWebRequest(address) as HttpWebRequest;
            if (request != null)
            {
                request.CookieContainer = CookieContainer;
            }
            return request;
        }
    }

Below is the upload routine. the site Url is the siteUrl in Office 365, the file Url is the document library url such as “SiteAssets/MyDesignPackage.wsp”, local path is the location of the wsp file in the filesystem.

As I mentioned above that I need to have claim base authentication cookie (“FedAuth”) and set its value as the security token found in the SPOIDCRL. And lastly upload the file in a stream (DON’T use UploadFile method!)

   private static void UploadFile(string siteUrl ,string fileUrl, string localPath, SharePointOnlineCredentials credentials)
        {
            var targetSite = new Uri(siteUrl);

            using (var spWebClient = new SPWebClient())
            {
                var authCookie = credentials.GetAuthenticationCookie(targetSite);
                spWebClient.CookieContainer = new CookieContainer();
                spWebClient.CookieContainer.Add(new Cookie("FedAuth",
                          authCookie.Replace("SPOIDCRL=", string.Empty),
                          string.Empty, targetSite.Authority));
                spWebClient.UseDefaultCredentials = false;
                try
                {
                    Stream stream = spWebClient.OpenWrite(targetSite + fileUrl, "PUT");
                    BinaryWriter writer = new BinaryWriter(stream);
                    writer.Write(IO.File.ReadAllBytes(localPath));
                    writer.Close();
                    stream.Close();
                }
                catch (WebException we)
                {
                    Console.WriteLine(we.Message);
                }
                Console.WriteLine("done");
            }
        }

In the next post I will show you how to use the DesignPackage CSOM to install and apply the Design Package.