2

I have some application pages which connect to a DAL done in Entity Framework and connects to sql.

However, the developers of this used property bags to store the username, db, password and dbname.

I want to use the secure store service application. I created a target application, and I created 4 keys. Username (generic username) DB Name (Generic) Server (Generic) password (password), masked.

How can I read those settings from my Entity Framework layer to get the values ?

  public static void Invoke<TConnection>(Action<TConnection> action)
            where TConnection : DbConnection, IDisposable, new()
        {
            var connection = new TConnection();

            try
            {
                //connection.Open();

                action(connection);

                connection.Close();
                connection.Dispose();
            }
            catch (SqlException)
            {
                connection.Close();
                connection.Dispose();
                throw;
            }
            catch (TimeoutException)
            {
                connection.Close();
                connection.Dispose();
                throw;
            }
            catch (Exception)
            {
                connection.Close();
                connection.Dispose();
                throw;
            }
        }

        public static void CreateEntityConnection(EntityConnection connection, bool openConnection)
        {
            connection.ConnectionString = GetDMSConnectionString();

            if (openConnection)
                connection.Open();
        }

        public static string GetDMSConnectionString()
        {
            // Specify the provider name, server and database.
            string providerName = "System.Data.SqlClient";
            string serverName =
            string databaseName = 
            string userName = 
            SecureString password = 
            password.MakeReadOnly();

            // Initialize the connection string builder for the
            // underlying provider.
            SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

            sqlBuilder.DataSource = serverName;
            sqlBuilder.InitialCatalog = databaseName;
            sqlBuilder.UserID = userName;
            sqlBuilder.Password = 

            // Build the SqlConnection connection string.
            string providerString = sqlBuilder.ToString();

            // Initialize the EntityConnectionStringBuilder.
            EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

            //Set the provider name.
            entityBuilder.Provider = providerName;

            // Set the provider-specific connection string.
            entityBuilder.ProviderConnectionString = providerString;

            // Set the Metadata location.
            entityBuilder.Metadata = @"res://*/DMSModel.csdl|
                                        res://*/DMSModel.ssdl|
                                        res://*/DMSModel.msl";


            return entityBuilder.ToString();
        }
5

You can Access to the secure store service programmatically

Retrieve the Central Administration Site

public static SPSite GetCentralAdminSite()
{
    SPAdministrationWebApplication adminWebApp = SPAdministrationWebApplication.Local;
    if (adminWebApp == null)
    {
        throw new InvalidProgramException("Unable to get the admin web app");
    }

    SPSite adminSite = null;
    Uri adminSiteUri = adminWebApp.GetResponseUri(SPUrlZone.Default);
    if (adminSiteUri != null)
    {
        adminSite = adminWebApp.Sites[adminSiteUri.AbsoluteUri];
    }
    else
    {
        throw new InvalidProgramException("Unable to get Central Admin Site.");
    }

    return adminSite;
}

Decrypt the secure string

private static string GetStringFromSecureString(SecureString secStr)
{
    if (secStr != null)
    {
        IntPtr pPlainText = IntPtr.Zero;
        try
        {
            pPlainText = Marshal.SecureStringToBSTR(secStr);
            return Marshal.PtrToStringBSTR(pPlainText);
        }
        finally
        {
            if (pPlainText != IntPtr.Zero)
            {
                Marshal.FreeBSTR(pPlainText);
            }
        }
    }

    return null;
}

Now we can retrieve all credentials stored in the Secure Store Application

public static Dictionary<string, string> GetCredentialsFromSecureApp(string applicationId)
{

    var credentialMap = new Dictionary<string, string>();

    // Get the default Secure Store Service provider.
    ISecureStoreProvider provider = SecureStoreProviderFactory.Create();
    if (provider == null)
    {
        throw new InvalidOperationException("Unable to get an ISecureStoreProvider");
    }

    var providerContext = provider as ISecureStoreServiceContext;
    if (providerContext != null)
        providerContext.Context = SPServiceContext.GetContext(GetCentralAdminSite());

    var secureStoreProvider = new SecureStoreProvider
                              {
                                  Context = SPServiceContext.GetContext(GetCentralAdminSite())
                              };

    using (var credentials = secureStoreProvider.GetCredentials(applicationId))
    {
        var fields = secureStoreProvider.GetTargetApplicationFields(applicationId);
        for (int i = 0; i < fields.Count; i++)
        {
            var field = fields[i];
            var credential = credentials[i];

            var decryptedCredential = GetStringFromSecureString(credential.Credential);

            credentialMap.Add(field.Name, decryptedCredential);
        }

    }

    return credentialMap;
}

Add this references to your project :

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

To do a simple test, I created a console application with this code:

static void Main(string[] args)
{
    Dictionary<string, string> dic = GetCredentialsFromSecureApp("Secure App ID");

    foreach (var VARIABLE in dic)
    {
        Console.WriteLine(VARIABLE.Key +" : "+ VARIABLE.Value);
    }
    Console.ReadKey();
}

enter image description here

  • for a sql connection string, I was thinking that I should create 2 fields, user and password, and store the database server name and database name somewhere else. Or do you think that all connection string should be stored into one credential type password? – Luis Valencia Oct 15 '13 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.