3

I have a sql database which connection string was hardcoded on a property bag I am changing it to use the secure store service application.

I already created the application id on the store with the required fields and values. I set it to Group.

THe connection string will be the same for all users, and I am trying to read it with elevaged privileges.

I still get access denied

The exception is thrown here:

using (var credentials = secureStoreProvider.GetCredentials(applicationId))

My all code is like this:

/// <summary>
        /// Gets the database server and database name from the property bag
        /// </summary>
        /// <returns></returns>
        public static string GetDMSConnectionStringFromSecureStore()
        {
            var entityBuilder = new EntityConnectionStringBuilder();
            SPSecurity.RunWithElevatedPrivileges(delegate
            {
                try
                {
                    const string providerName = "System.Data.SqlClient";
                    var dic = GetCredentialsFromSecureApp(Constants.SecureStore.ApplicationId);
                    var serverName = string.Empty;
                    var databaseName = string.Empty;
                    var databaseUserName = string.Empty;
                    var databasePassword = string.Empty;

                    foreach (var item in dic)
                    {
                        if (item.Key == Constants.SecureStore.DatabaseServerField)
                        {
                            serverName = item.Value;
                        }
                        if (item.Key == Constants.SecureStore.DatabaseNameField)
                        {
                            databaseName = item.Value;
                        }
                        if (item.Key == Constants.SecureStore.UserNameField)
                        {
                            databaseUserName = item.Value;
                        }
                        if (item.Key == Constants.SecureStore.PasswordField)
                        {
                            databasePassword = item.Value;
                        }
                    }

                    // Initialize the connection string builder for the underlying provider.
                    var sqlBuilder = new SqlConnectionStringBuilder
                    {
                        DataSource = serverName,
                        InitialCatalog = databaseName,
                        UserID = databaseUserName,
                        Password = databasePassword
                    };

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

                    // Initialize the EntityConnectionStringBuilder.
                    entityBuilder.Provider = providerName;
                    entityBuilder.ProviderConnectionString = providerString;
                    entityBuilder.Metadata = @"res://*/DMSModel.csdl|res://*/DMSModel.ssdl|res://*/DMSModel.msl";
                }
                catch (Exception ex)
                {
                    LoggingService.LogError(LoggingCategory.Security, ex);
                }
            });

            return entityBuilder.ToString();
        }

        private static SecureString CreateSecureString(string unsecureString)
        {
            var secureString = new SecureString();
            foreach (var c in unsecureString/*.ToCharArray()*/)
            {
                secureString.AppendChar(c);
            }
            return secureString;
        }

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

            SPSite adminSite;
            var 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;
        }

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

            return null;
        }

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

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

            // Get the default Secure Store Service provider.
            var 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;
        }
4
  • 1
    Dumb question maybe, but does your application pool account have correct permissions on the Secure Store Service? Nov 15, 2013 at 13:06
  • it didnt, I am testing it now Nov 15, 2013 at 13:14
  • This didnt work, I added the Web App Pool account used on the Sharepoint Web Application (IIS), and give all rights, I restarted iis and timer service. Nov 15, 2013 at 13:15
  • I guess you have been inspired by this code: msdn.microsoft.com/en-us/library/ff394459%28v=office.14%29.aspx the difference being that they do not use SPSecurity.RunWithElevatedPrivileges. Can it be that you would need to create a new SPSite in GetCentralAdminSite() for it to be created with elevated privileges? Nov 15, 2013 at 13:21

1 Answer 1

9

I found the problem myself, Robert inspired me :)

  1. On the secure store service application, I selected it and clicked on the administrators button, I added the App Pool account used by the calling application. It did not work.

  2. I added the Web Service App pool account It did not work.

  3. On the target application, I edited it, and on the members section (last step), I added the Web App Pool account This worked.

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.