Get More Out of Your Secure Store Service via PowerShell and C#
sssblog

Get More Out of Your Secure Store Service via PowerShell and C#

Posted by on Thursday, July 10th, 2014  

 

Download: SecureStoreServiceHelper.ps1

In my opinion, one of SharePoint 2010/2013’s unsung heroes is the Secure Store Service Application. I’m sure the vast majority of SharePoint users have never even heard of it. It’s one of those quiet, unassuming service applications that only a farm administrator can love. It is, however, potentially so much more than the place to hold your Excel and PowerPivot unattended account. In this blog, I hope to show you how you can use the Secure Store Service in a new and powerful way.

So What is This Secure Store Service?

The Secure Store Service Application (SSS) was added in SharePoint 2010 as a replacement for 2007’s Single Sign On feature. Its role in life is to map a SharePoint user (domain account) to a different account, such as a legacy system login. It stores the encrypted user information securely inside a farm database, and the service application provides a simple means to manage the accounts (called “credentials”). The administrator creates something called a Target Application. It defines the user configuration (including who has access to use it) and stores the remote user name and password.  

The vast majority of SharePoint farms likely use it primarily for the unattended accounts used by the Enterprise service applications, such as Excel Services, PowerPivot, and PerformancePoint. In order for these service applications to reach a backend system, file share, or website non-interactively, the administrator specifies a login and password that the system can use to access whatever the service needs. Excel Services and PowerPivot use it, for example, to automatically run data refreshes based on a schedule. It is also very heavily used with Business Connectivity Services (BCS). BCS is a really incredible service application which surfaces line of business data and applications inside of SharePoint. SSS provides a login/password to BCS so that it can connect the user to the backend resource (such as with a SQL login or a login/password for a legacy system).

Is it Actually Secure?

The service has the word “secure” in its name, but is it? Can we trust it to protect that login/password? I would say that, yes, you can absolutely trust SSS to protect your sensitive information. An online search showed absolutely no evidence of anyone ever hacking the SSS and compromising the credentials stored in it. The service application is protected with a key that is used to encrypt/decrypt the credentials stored in the database. The administrator generates this encryption key using a passphrase. As long as that passphrase and the encryption key backup are protected, the stored passwords should be safe from unauthorized access.

Additionally, it is possible to restrict access to individual credentials in SSS target applications. This is key to some really great functionality and what we’ll be relying upon for the scenario described below. You can set the target application Members on the final page when configuring a Group-type target application. Only the users listed as Members have access to the credentials stored in that target application; no other user is authorized to decrypt the credentials, not even those listed as Target Application Administrators. In the case of Individual-type target applications, you specify an owner when you create the credential, and only this individual (cannot be a domain group) can use the credential. This gives you a very precise way to protect the credential.

sss1

More Than Just Single Sign-On

So you might be thinking that this is all well and good for stuff like single sign-on. That is, after all, SSS’ purpose (remember, it’s a replacement for Microsoft’s Single Sign-On in SharePoint 2007). But did you know that you can use SSS for more than just BCS and scheduled refreshes?

The SSS provides a pretty complete infrastructure to store encrypted data. So far, it’s all just been logins and passwords. However, nothing says that’s all a credential can hold. When you setup a target application, you configure whatever fields you need for your credential. Each field has a given Field Type. By default, SharePoint will give you a pair of fields with
Windows User Name and Windows Password as their field types, but you don’t have to use these. You can delete these fields and add however many fields you want. You can choose from the following Field Types:

    • Generic
    • User Name
    • Password
    • PIN
    • Key
    • Windows User Name
    • Windows Password
    • Certificate (new to 2013)
    • Certificate Password (New to 2013)

Other than the new Certificate type, the fields simply take whatever text you want to give it. As far as I can tell, the different types of fields are simply there as a helper to the user when they set the credential.

sss2

Our Scenario: Securely Storing an Encryption Key Passphrase

For the sake of this post, we’ll use as our example a scenario that I had at a previous company. There was a customer who needed to store sensitive data in a SharePoint list. Only authorized users were permitted to access the data. Unfortunately, SharePoint doesn’t offer an encrypted field type out of the box, nor does it provide column-level permissions. Long story short, I ultimately decided to create a new custom field type which would store the text in an encrypted format and only decrypt it for authorized users. One of the major problems was the storing of the encryption key. I didn’t want it hard-coded in the application (easily compromised, difficult to manage, inflexible), and I didn’t want to store it someplace on a server or in SharePoint in clear text (never a good idea).

So – you guessed it – I turned to the Secure Store Service. I created a target application with a single field of type Key, and I added the authorized users to the Members list (quick tip: if you’re going to use a workflow which might pause, also add the farm account since the run context will be transferred to the Timer Job service and thus it will require access as well). In the custom field definition, I added a Secure Store Service Target Application ID property so that the user, when designing the list, can choose the correct target application which is holding their key (since there might be the need for multiple). Then, in the field’s code, I pulled the encryption key from the target application (under the current user’s context, of course) and, if there was access, encrypted or decrypted the stored value. There was a lot more to the solution, of course, but these are the main points.

 

Creating the Target Application Credential

The first thing that we need to do is create the target application and set the “credential” (just an encryption key passphrase in our example). The process to create a target application is fairly simple. You can follow the instructions here to do so via Central Administration. For the greatest flexibility, I recommend that you just use a Target Application Type of type Group. The key is to make sure you set your field type appropriately, since you will need to know the type when accessing it later. In our example, we will create a target application with a single field of type Key.

You can use PowerShell to create a target application and set its credential. The following will do so using the field type we need. Simply update the three fields at the top as directed and set the $members and $administrators variables with the users who need access (change the IdentityType to WindowsSecurityGroupName if it’s a domain group). Remember, the users in the $members variable have access to the credential, so only add users who need it.


# Initial source: http://technet.microsoft.com/en-us/library/ff607967(v=office.15).aspx
if((Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.PowerShell
}

$passphrase = "OpenSesame" # The passphrase to encrypt/decrypt the text
$targetAppName = "Encrypted Column Passphrase 1" # The name of the Target Application
$localSharePointSite = "https://LocalSharePoint.com" # The URL of a local site which will connect us to the Secure Store Service

# The list of fields defined in the credential
$fields = @()
$fields += New-SPSecureStoreApplicationField –Name "Passphrase" -Type Key –Masked:$true
#$fields += New-SPSecureStoreApplicationField –Name "NewField" -Type UserName –Masked:$false

# The list of users who can access the credential. Equates to the Members section in the GUI. Add as many as needed.
$members = @()
$members += New-SPClaimsPrincipal -Identity "domain\UserWhoCanReadTheCredential1" -IdentityType WindowsSamAccountName
#$owners += New-SPClaimsPrincipal -Identity "domain\UserWhoCanReadTheCredential2" -IdentityType WindowsSamAccountName
#$owners += New-SPClaimsPrincipal -Identity "domain\UserWhoCanReadTheCredential3" -IdentityType WindowsSamAccountName

# The list of administrators for the Target Application. Equates to the Target Application Administrators section of the GUI.
# Add as many as needed.
$administrators = @()
$administrators += New-SPClaimsPrincipal -Identity "domain\AdminUserName1" -IdentityType WindowsSamAccountName
#$administrators += New-SPClaimsPrincipal -Identity "domain\adminUserName2" -IdentityType WindowsSamAccountName
#$administrators += New-SPClaimsPrincipal -Identity "domain\AdminUserName3" -IdentityType WindowsSamAccountName

$sssTargetApp = New-SPSecureStoreTargetApplication –Name $targetAppName –FriendlyName $targetAppName –ApplicationType Group

$sssApp = New-SPSecureStoreApplication –ServiceContext $localSharePointSite –TargetApplication $sssTargetApp –Fields $fields -CredentialsOwnerGroup $members -Administrator $administrators

$sssCredential = (ConvertTo-SecureString $passphrase –AsPlainText –Force)

Update-SPSecureStoreGroupCredentialMapping –Identity $sssApp –Values $sssCredential

Accessing the Credential in PowerShell

Once you have a credential set in the target application, you can easily access it in PowerShell as long as your account is listed as a Member in the target application. We borrow from .Net in order to do so. The first things we need to do is load the necessary assembly like so (for SharePoint 2010, change the “15” in the path to “14”):

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.Office.SecureStoreService.Server.Security.dll"

Once the type is loaded, we can use the SSS objects like we would in C#. Below is a subset of the functions in SecureStoreServiceHelper.ps1, which is attached to this blog.


function Get-SecureStoreProvider
{
<#
    .Synopsis
        Get a [Microsoft.BusinessData.Infrastructure.SecureStore.ISecureStoreProvider] object for the given on-premises site
    .Parameter $contextSiteUrl
        The URL of the on-premises site connected to the Secure store Service
    .Example
        $ssp = Get-SecureStoreProvider -contextSiteUrl $contextSiteUrl
    .Outputs
        [Microsoft.BusinessData.Infrastructure.SecureStore.ISecureStoreProvider] of the secure store provider
    .Notes
        Name: Get-SecureStoreProvider
        Sources:
        Author: Brian Laws, Summit 7 Systems
        Last Edit: 4/29/2014
#>
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true)][string]$contextSiteUrl
    )
    $ErrorActionPreference = "Stop"
    Write-Debug "$($MyInvocation.MyCommand.Name): Begin"

    [Microsoft.BusinessData.Infrastructure.SecureStore.ISecureStoreProvider]$ssp = New-Object Microsoft.Office.SecureStoreService.Server.SecureStoreProvider

    Write-Verbose "$($MyInvocation.MyCommand.Name): Getting the service context for site $contextSiteUrl"
    $spContext = Get-SPServiceContext -Site $contextSiteUrl -Verbose:$false

    Write-Verbose "$($MyInvocation.MyCommand.Name): Getting the Secure Store provider and setting context"
    $ssp.Context = $spContext

    Write-Debug "$($MyInvocation.MyCommand.Name): End"
    return $ssp
}

function Get-CredentialsFromSecureStore
{
<#
    .Synopsis
        Gets a [Microsoft.BusinessData.Infrastructure.SecureStore.SecureStoreCredentialCollection] object containing the credentials stored in a Secure Store Service Target Application
    .Parameter contextSiteUrl
        The URL of the on-premises site connected to the Secure store Service
    .Parameter ssTargetAppName
        The Secure Store Service target application ID/name containing the credentials to retrieve
    .Outputs
        [Microsoft.BusinessData.Infrastructure.SecureStore.SecureStoreCredentialCollection] containing the stored credentials
    .Example
        $ssCreds = Get-CredentialsFromSecureStore -contextSiteUrl "http://onpremises" -ssTargetAppName "Some Target App Name"
    .Notes
        Name: Get-CredentialsFromSecureStore
        Sources:
        Author: Brian Laws, Summit 7 Systems
        Last Edit: 4/29/2014
#>
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true)][string]$contextSiteUrl,
        [Parameter(Mandatory=$true)][string]$ssTargetAppName
    )
    $ErrorActionPreference = "Stop"
    Write-Debug "$($MyInvocation.MyCommand.Name): Begin"

    Write-Verbose "$($MyInvocation.MyCommand.Name): Getting the Secure Store Provider from ""$contextSiteUrl"""
    $ssp = Get-SecureStoreProvider -contextSiteUrl $contextSiteUrl

    Write-Verbose "$($MyInvocation.MyCommand.Name): Getting the Credentials for Secure Store Target Application ""$ssTargetAppName"""
    [Microsoft.BusinessData.Infrastructure.SecureStore.SecureStoreCredentialCollection]$ssCreds = $ssp.GetCredentials($ssTargetAppName)

    Write-Debug "$($MyInvocation.MyCommand.Name): End"
    return $ssCreds
}

function Convert-SecureStringToString
{
<#
    .Synopsis
        Convert a SecureString object into a standard string
    .Parameter secureString
        SecureString object to convert
    .Outputs
        String containing the converted text
    .Example
        $password = Convert-SecureStringToString -secureString (ConvertTo-SecureString "password" –AsPlainText –Force)
    .Notes
        Name: Convert-SecureStringToString
        Sources:
        Author: Brian Laws, Summit 7 Systems
        Last Edit: 4/29/2014
#>
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true)][System.Security.SecureString]$secureString
    )
    Write-Debug "$($MyInvocation.MyCommand.Name): Begin"

    $plainString = [Runtime.InteropServices.Marshal]::PtrToStringBSTR([Runtime.InteropServices.Marshal]::SecureStringToBSTR($secureString))

    Write-Debug "$($MyInvocation.MyCommand.Name): End"
    return $plainString
}

function Get-ValueFromSecureStoreCredentials
{
<#
    .Synopsis
        Extracts a value out of a Secure Store Service target application credential for a given field type
    .Parameter ssCredentials
        Secure store service credential object to extract from
    .Parameter ssCredentialType
        [Microsoft.BusinessData.Infrastructure.SecureStore.SecureStoreCredentialType] - The type of field/credential to extract from
    .Outputs
        Unencrypted string value
    .Example
        $destUser = Get-ValueFromSecureStoreCredentials -ssCredentials $ssCreds -ssCredentialType UserName
    .Notes
        Name: Get-ValueFromSecureStoreCredentials
        Sources:
        Author: Brian Laws, Summit 7 Systems
        Last Edit: 4/29/2014
#>
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true)]$ssCredentials,
        [Parameter(Mandatory=$true)][Microsoft.BusinessData.Infrastructure.SecureStore.SecureStoreCredentialType]$ssCredentialType
    )
    $ErrorActionPreference = "Stop"
    Write-Debug "$($MyInvocation.MyCommand.Name): Begin"

    [Microsoft.BusinessData.Infrastructure.SecureStore.ISecureStoreCredential]$value = $ssCredentials | Where-Object {$_.CredentialType -eq $ssCredentialType} | Select-Object -First 1
    $valueDecrypted = Convert-SecureStringToString -secureString $value.Credential

    Write-Debug "$($MyInvocation.MyCommand.Name): End"
    return $valueDecrypted
}

Once the above scripts have been loaded, we can then pull our passphrase like so:

$creds = Get-CredentialsFromSecureStore -contextSiteUrl “https://LocalSharePoint.com” -ssTargetAppName "Encrypted Column Passphrase 1"
$passphrase = Get-ValueFromSecureStoreCredentials -ssCredentials $creds -ssCredentialType Key
Write-Host "The secret passphrase is '$passphrase'"

For another example of how to access the Secure Store Service from PowerShell, please see my blog Sync a Managed Metadata Term Group with SharePoint Online or On-Premises.

 

Accessing the Credential in C#

The process of retrieving credentials from the Secure Store Service in C# is quite similar to doing so in PowerShell. The below code is a quick-and-dirty, super-simple console application that I ripped off from the Office Dev Center (I modified it a little to save space). Please refer to the original code for a more complete example. You will need to add references to the following libraries: Microsoft.BusinessData.dll, Microsoft.SharePoint.dll, and Microsoft.Office.SecureStoreService. The first two are found in the ISAPI folder under C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\[14/15] folder (depending on your SharePoint version). We get a bit of a curve ball in with the Microsoft.Office.SecureStoreService.dll. You actually need to pull it from here: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.Office.SecureStoreService\v4.0_15.0.0.0__71e9bce111e9429c\Microsoft.Office.SecureStoreService.dll.

The primary difference between this and the PowerShell method is that we’re using the SecureSToreProvider factory and setting its context via the SPServiceContext. When you call GetCredentials() on the SSS provider object (passing in the name of your target application), you get back a SecureStoreCredentialCollection. This object is a collection of all of the fields you created when you setup the target application. The naming in the object model is a little confusing, since each individual field (as defined in Central Admin UI) is considered a “credential.” Simply iterate through the collection and check the CredentialType property to identify the field you want (the SecureStoreCredentialType enumeration makes this easier). The text itself is in the Credential property. Please make sure you properly dispose of the SecureStoreCredentialCollection object, since it implements the IDisposable interface. You’ll get nasty memory leaks if you don’t.


using System;
using System.Security;
using System.Runtime.InteropServices;
using Microsoft.SharePoint;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;

// Source: http://msdn.microsoft.com/en-us/library/office/ff394459(v=office.14).aspx
namespace SP2013_Console_App {
    class Program {
        static void Main(string[] args) {
            string spSiteUrl = "https://LocalSharePoint.com";
            string sssTargetApplication = "Encrypted Column Passphrase 1";
            string passphrase = string.Empty;

            using (SPSite spSite = new SPSite(spSiteUrl)) {
                // Get our connection to the Secure Store Service via the default service context of our site.
                ISecureStoreProvider sssProvider = SecureStoreProviderFactory.Create();
                ISecureStoreServiceContext providerContext = sssProvider as ISecureStoreServiceContext;
                providerContext.Context = SPServiceContext.GetContext(spSite);

                try {
                    // Get the credential collection object for the target application. The object contains our collection of fields that we defined
                    using (SecureStoreCredentialCollection sssCreds = sssProvider.GetCredentials(sssTargetApplication)) {
                        if (sssCreds != null) {
                            // We should only have one field in the collection
                            foreach (SecureStoreCredential sssCred in sssCreds) {
                                // Look for the Key field and retrieve its value
                                if (sssCred.CredentialType == SecureStoreCredentialType.Key) {
                                    passphrase = GetStringFromSecureString(sssCred.Credential);
                                }
                            }
                        }
                    }
                }
                catch (SecureStoreException e) {
                    Console.WriteLine(e.Message);
                    throw;
                }
            }

            Console.WriteLine("The Key stored in the '" + sssTargetApplication + "' target application is: " + passphrase);
            Console.WriteLine("Done. Press any key to continue");
            Console.ReadKey();
        }

        private static string GetStringFromSecureString(SecureString secStr) {
            if (secStr == null) {
                return null;
            }

            IntPtr pPlainText = IntPtr.Zero;
            try {
                pPlainText = Marshal.SecureStringToBSTR(secStr);
                return Marshal.PtrToStringBSTR(pPlainText);
            }
            finally {
                if (pPlainText != IntPtr.Zero) {
                    Marshal.FreeBSTR(pPlainText);
                }
            }
        }
    }
}

 

Limitations

Of course, nothing is perfect. The following are some limitations that I’ve been able to identify.

  • The code above only work on-premises. Additionally, you cannot access the SSS of another on-premises farm.
  • From what I can tell, there is a built-in security mechanism which restricts SSS access to on-box
  • I believe it is currently not possible to pull credentials via CSOM or REST. I do, however, believe that it might be possible to access it via SOAP calls to a web service. This is outside my area of knowledge, though. The protocol is documented here.
  • Because of the above, these solutions will not work in SharePoint Online (even though it has an SSS)
  • The SSS is not accessible from within Sandboxed Solutions
  • Since the 2013 App Model relies upon remote or client-side solutions, I do not believe the SSS would be available from a 2013 app. I do not know this for sure, though.

 

Additional Uses for the Secure Store Service

Now that we know the basics of how to interact with the Secure Store Service, there are any number of ways that we can take advantage of what Microsoft gave us. Again, any kind of text can be stored in one of these fields (except for the new Certificate field type, which does actually check to make sure it’s a valid cert). Really, your imagination is the limit! Here are some possible uses that I came up with (and yes, some of them might be rather nuts):

  • Store your farm’s passphrase (used to add servers to the farm)
  • As our example describes, store a passphrase in order to create a secured custom column type which also provides column-level permissions
  • Store any passphrases or passwords (such as for the farm account)
  • Securely store anything needed in automation scripts that you don’t want to hard-code or leave in plain text (again, like passwords)
  • Store a login/password for non-BCS access to other systems, such as Office 365. You can see this in action in my blog on synchronizing an MMS term group with Office 365.
  • Build a full-featured service account password vault with or without a SharePoint interface (like a custom web application) using the Member functionality to restrict who has access to the account
  • In 2013, like above, build a kind of certificate storage vault with user-level restrictions
  • For third-party solution providers, store a secured license key which your custom code can then use for license validation
  • When building custom solutions, add user or permissions validation anywhere not normally available (like column-level permissions) by checking whether or not the current user has access to a target application
  • Create a web service or some other proxy which can provide encrypted data to other non-SharePoint systems.

 

Final Thoughts

As a standard service application, when you use the Secure Store Service to store sensitive data, you get all of the benefits that come along with being part of the farm. This includes backups, automatic deployment across application services, service load balancing, and the SharePoint security model. You basically get an advanced password storage vault for free. However, as with all things SharePoint, keep in mind the very important mantra: In SharePoint, just because you can it doesn’t mean you should. Consider the impact to the farm, the manageability of the solution, how easily the solution can be upgraded to the next version of SharePoint, the security architecture, etc. Enter carefully if you have a large solution depending upon it. However, if you do your homework, test, and monitor, you might find another really great, free tool to add to your belt.

I hope this has been helpful! Please feel free to share any ways that you’ve also used the Secure Store Service in a novel way. Thanks for reading!

 

Disclaimer
The sample scripts are not supported under any Summit 7 Systems standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Summit 7 Systems further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Summit 7 Systems, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Summit 7 Systems has been advised of the possibility of such damages.

Posted by on Thursday, July 10th, 2014  

Subscribe to RSS Feed

Sign Up for Newsletter

3

3

comments

    Feb 19
    2016

    lwsrbrts

    Great stuff. I wanted to find out which user account I had configured as the PowerPivotUnattendedAccount and the GUI wouldn’t even give the name but the PowerShell script worked great by changing the ssCredentialType parameter passed to the Get-ValueFromSecureStoreCredentials cmdlet to “WindowsUserName” instead of “Key”.

    Thank you!

    Reply
      Feb 19
      2016

      Brian Laws

      Exactly. Like you, I use the same cmdlets to retrieve the information in case I need to document the farm. I’ve had to do this when I’m building a new farm to migrate into. I’m really glad it was helpful!

      Reply

Leave a Reply