Yammer OAuth for data export

Anyone familiar with Yammer will know that many of the APIs are a bit lacking.  Also, all API connections will need an oAuth token that has access to the data in question.  So what do you do if you have a console application that needs to use Yammer Data Export API and drop the into a database or more to the point, how do you get the oAuth token?  I am not going to post the specifics on how to get the data and extract it but I will post some code on how to get the oAuth token.

To start there is some information you will need from Yammer which is only obtained after creating a Yammer app (https://developer.yammer.com/v1.0/docs/app-registration).  Your redirect URL for the included code needs to be http://localhost:80/Temporary_Listen_Addresses/  (if you get an error relating to access denied the port may be being used or it could be an issue with your privileges on the device).  *NOTE you do not need to publish your app for this functionality to work, so you can create the app then never finalize it.  Yammer's documentation https://developer.yammer.com/v1.0/docs/authentication-1 does not include the specifics on how to implement this on a console application and only includes the basic information.

The included code makse the assumptions that you will have the following app seetings in your App.config: "ClientID", "RedirectURL", "ClientSecret", and "oAuthToken" with all of these fields populated, with the exception of the "oAuthToken" field, from the information in your Yammer app settings.

I have included the complete class to perform the login here and to implement it you just need to call the constructor. 

  1. var yammerLogin = new YammerLogin.YammerAuthentication();
  2. string token = yammerLogin.oAuthToken;

The oAuthToken will be stored in the App.config and it will only re-auth if the token has failed to login. 

I hope this will help save a few hours of work for a few people out there.

Migrating Drupal from MySQL to MSSQL

So I am going to start by ranting a little bit about Microsoft Azure cloud services.  Something odd about Azure is that when you are creating a new Drupal or WordPress site it will automatically crate a MySQL database but if there is already a MySQL database it will not create it automatically.  You can however create a MySQL database then install your CMS and associate it with said database.  The second part of my rant is that with a MSSQL database the smallest size is 2 GB and it is less expensive than the 20 MB MySQL database, which just seems odd to me.


Okay, now that I have that over with, let me get to the heart of what I wanted to talk about and discuss installing Drupal on a MSSQL and migrating (if need be) from MySQL in Azure.  So, to start there is one very easy config change to allow Drupal to run on MSSQL.  Download the drivers from https://www.drupal.org/project/sqlsrv then extract the files.  With the files extracted copy the sqlsrv folder and drop it in the includes/databases directory in the root of your Drupal installation.  After the files are uploaded update the configuration settings in the settings.php file and update the connection for the $databases to have the driver “'driver' => 'sqlsrv'”.  Something to note, if you are performing this on Azure the PHP MSQL Drivers are already installed but if you are running this install on a different system you may need to install PHP MSSQL drivers.


One thing that was missing on my installation was a SUBSTRING function that is used for Drupal when performing a post.  The site worked fine but when I tried to post as an authenticated user I received an error.  To solve the problem I needed to add the function.  Click Here for the specific TSQL code.  (UPDATE)Even with the substring SQL function installed I was still having trouble.  So I ended up hitting the install.php page and then running a database update.  This seems to have fixed my missing SQL functions, but only time will tell if I am not missing anymore.  I have included all of the functions that are created in the link referenced in this paragaraph.


So if you need to migrate from MySQL to MSSQL there are a few steps that you need to take.  After creating the new database in MSSQL download the MSSQL Migration Assistant for MySQL from http://www.microsoft.com/en-us/download/details.aspx?id=42657.  Point the MySQL to your old MySQL instance and point the MSSQL to the new MSSQL instance.  Start by changing the target schema to dbo (this will make the migration easy).  Then right click on your database and click “Convert Schema” this will create the local metadata of the schema.  Once the schema conversion is done, on the target database right click and select “Synchronize with Database.”  Once the synchronization is complete (this will take a while) your tables will be created but without data.  Lastly right click on your original MySQL database and select “Migrate Data.” 


If you have not updated your connection in your settings.php file, you can update that now to point to the MSSQL instance and you should be good.  I tested my change by making a small change to my site and seeing how it reflected in the database (specifically I changed my locale on my user account). 


I hope this will help the few that run Azure sites under your MSDN and do not want to pay for additional MySQL storage.

SharePoint Login as a Different User

What the hell Microsoft? Why did you decide to remove the ability for a user to logon as a different user in SharePoint 2013? It was there in 2010, but this feature is not there in 2013.

Microsoft's recommendation is to right-click on Internet Explorer and then select run-as and then enter your username and password. This is not a good option for most users and in our environment it is not practical. So to get this functionality back, we will create a SharePoint feature that is deployed at the farm level. There are some sites out there that detail this but there is one problem with all of them, they do not redirect the user back to the same site where they did the logout / login function. I have included the specific code to solve the problem but I am not including the details on how to create the feature. Once the feature is created add an empty element to the project and paste in the code below. The LogoffAndLogin() javascript function will get the current subsite and pass it in as a parameter to then allow the logoff webservice to redirect the user back to the original page after login.

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  3. <CustomAction
  4. Id="LoginScript"
  5. ScriptBlock="function LogoffAndLogin(){ if (typeof SP != 'undefined') { var siteCollUrl = '';SP.SOD.executeFunc('SP.js', 'SP.ClientContext', function(){var clientContext = new SP.ClientContext.get_current();var site = clientContext.get_site();clientContext.load(site);clientContext.executeQueryAsync(Function.createDelegate(this, function(){siteCollUrl = site.get_url();var fullURL = siteCollUrl + '/_layouts/closeConnection.aspx?loginasanotheruser=true&amp;amp;Source=' + siteCollUrl; window.location = fullURL;}))})} else { alert('An error occurred during the logoff process, please try again.');}};"
  6. Location="ScriptLink">
  7. </CustomAction>
  8. <CustomAction
  9. Id="LogInAsUser"
  10. GroupId="PersonalActions"
  11. Location="Microsoft.SharePoint.StandardMenu"
  12. Sequence="998"
  13. Title="Sign in as a Different User"
  14. Description="Sign Out and Login as a Different User">
  15. <UrlAction Url="javascript:LogoffAndLogin();"/>
  16. </CustomAction>
  17. </Elements>

I have the code all in one line and I realize that it might be a bit difficult to follow. I have included the code broken out as multiple lines to help you follow what is going on.

  1. LogoffAndLogin(){
  2. if (typeof SP != 'undefined') {
  3. var siteCollUrl = '';
  4. SP.SOD.executeFunc('SP.js', 'SP.ClientContext', function(){
  5. var clientContext = new SP.ClientContext.get_current();
  6. var site = clientContext.get_site();
  7. clientContext.load(site);
  8. clientContext.executeQueryAsync(Function.createDelegate(this, function(){
  9. siteCollUrl = site.get_url();
  10. var fullURL = siteCollUrl + '/_layouts/closeConnection.aspx?loginasanotheruser=true&amp;amp;Source=' + siteCollUrl;
  11. window.location = fullURL;
  12. }))
  13. })
  14. }
  15. else {
  16. alert('An error occurred during the logoff process, please try again.');
  17. }
  18. };

This has worked well in our environment. This is a stupid problem that MS created, but this solution should work well to solve it. Good luck !!

SharePoint security trimmed site list

Okay, so it has been a bit since I have posted anything, mainly due to that I have not anything notable to post.  This is not due to me not doing anything notable but that the things that I have done that were notable were proprietary and I did not feel comfortable disclosing in a public forum. 

So the problem that I was given was that when people landed on the root site within the root site collection of our managed path / application the user hit a page that was essentially blank.  From there they did not know where they should go and did not really know what they would be able to access.  So here comes a webpart that can help solve the problem.  What the goal of this webpart was is to create a security trimmed list of sites the user would have access at the root level.  I am not going to go through each and every step for creating a webpart but I will give some high level info since these are steps that I struggled with when creating the webpart.


  1. So first off, when you are creating this webpart select the "Visual Web Part option" in the project type.
  2. You MUST select DEPLOY AS FARM SOLUTION.  The sandboxed solution will not have access to the necessary objects needed for this webpart.  Specifically getting all site collections in a web application (managed path).
  3. To start we will create two classes.  SiteLookup and SPSiteInfo. 


    1. using Microsoft.SharePoint;
    2. using System;
    3. using System.Collections.Generic;
    4. using System.Linq;
    5. using System.Text;
    6. using System.Threading.Tasks;
    8. namespace SPSiteListing.ListSPSites
    9. {
    10. class SiteLookup
    11. {
    12. private Boolean _EnableTrimming;
    13. private SPContext _Context;
    14. private string _CurrentUserName;
    15. public SiteLookup(SPContext context, Boolean enablePermissionTrimming)
    16. {
    17. _EnableTrimming = enablePermissionTrimming;
    18. _Context = context;
    19. _CurrentUserName = context.Web.CurrentUser.LoginName;
    20. }
    21. public List<SPSiteInfo> GetSites()
    22. {
    23. if (IsRootInApplication())
    24. {
    25. var list = new List<SPSiteInfo>();
    26. list.AddRange(GetSitesUnderCurrentWeb());
    27. list.AddRange(GetSitesUnderManagedPath());
    28. return list;
    29. }
    30. else
    31. return GetSitesUnderCurrentWeb();
    32. }
    33. public Boolean IsRootInApplication()
    34. {
    35. if (_Context.Site.RootWeb.Url != _Context.Site.Url)
    36. return false;
    37. if (_Context.Site.WebApplication.Sites[0].Url != _Context.Site.Url)
    38. return false;
    39. return true;
    40. }
    41. public List<SPSiteInfo> GetSitesUnderManagedPath()
    42. {
    43. var sites = new List<SPSiteInfo>();
    44. var applicationSites = _Context.Site.WebApplication.Sites;
    45. foreach (SPSite item in applicationSites)
    46. {
    47. //you must set disable catching access exceptions to prevent sharepoint from catching it
    48. item.CatchAccessDeniedException = false;
    49. try
    50. {
    51. if (item.RootWeb.DoesUserHavePermissions(_CurrentUserName, SPBasePermissions.Open) || !_EnableTrimming)
    52. sites.Add(new SPSiteInfo(item));
    53. }
    54. catch (UnauthorizedAccessException)
    55. {
    56. //The user does not have access to check their access. So an exception will be thrown.
    57. //This will not cause a problem to not do anything with it, since we are security trimming
    58. //we do not want this one listed anyway.
    59. }
    60. }
    61. return sites;
    62. }
    63. public List<SPSiteInfo> GetSitesUnderCurrentWeb()
    64. {
    65. var sites = new List<SPSiteInfo>();
    66. if (_EnableTrimming)
    67. {
    68. foreach (SPWeb item in _Context.Web.GetSubwebsForCurrentUser())
    69. {
    70. sites.Add(new SPSiteInfo(item));
    71. }
    72. }
    73. else
    74. {
    75. foreach (SPWeb item in _Context.Site.AllWebs)
    76. {
    77. sites.Add(new SPSiteInfo(item));
    78. }
    79. }
    80. return sites;
    81. }
    82. }
    83. }


    1. using Microsoft.SharePoint;
    2. using System;
    3. using System.Collections.Generic;
    4. using System.Linq;
    5. using System.Text;
    6. using System.Threading.Tasks;
    8. namespace SPSiteListing.ListSPSites
    9. {
    10. class SPSiteInfo
    11. {
    12. public string SiteName { get; private set; }
    13. public string SiteUrl { get; private set; }
    14. public string HTMLLink
    15. {
    16. get
    17. {
    18. return String.Format(@"<a href=""{0}"">{1}</a>",
    19. SiteUrl,
    20. String.IsNullOrEmpty(SiteName) ? SiteUrl : SiteName);
    21. }
    22. }
    23. public SPSiteInfo(SPSite site)
    24. {
    25. try
    26. {
    27. SiteName = site.RootWeb.Title;
    28. }
    29. catch (UnauthorizedAccessException)
    30. {
    31. //since the user does not have access to get the title, we can do something
    32. //here if we want with demonstrating that. but it isn't necessary
    33. }
    34. SiteUrl = site.Url;
    35. }
    36. public SPSiteInfo(SPWeb site)
    37. {
    38. SiteName = site.Name;
    39. SiteUrl = site.Url;
    40. }
    41. }
    42. }
  4. So now that we have the classes written to get the data, we need a something to display the data in the webpart. To do that we will use the ascx file created already (just adding a ul with an id and a runat) and add some code in the page_load method.

    ASCX file

    1. <ul id="siteList" runat="server">
    3. </ul>

    ASCX code behind

    1. protected void Page_Load(object sender, EventArgs e)
    2. {
    3. SiteLookup query = new SiteLookup(SPContext.Current, true);
    4. foreach (var item in query.GetSites())
    5. {
    6. var li = new HtmlGenericControl("li");
    7. li.InnerHtml = item.HTMLLink;
    8. siteList.Controls.Add(li);
    9. }
    10. }

So that is basically it. There are some details that are not covered in this post, but this should get you past the things that I struggled with when I created my webpart.

Good Luck !!