Error in granting rights to Profile and Social database (User Profile Service Application)

Jan 15, 2013 at 12:22 PM

I'm trying to install SP2013 (on SQL 2012) with AutoSPInstaller and using the lastest Stable build. But unfortunately I encouter 2 errors during the configuration.

"Cannot add contoso\spweb to the SharePoint_Shell_Access role of the database Test_Profile.

A possible cause of this error is that the account name was already added to the database as a login using a differentuser name than the account name."

At C:\AutoSPInstaller\AutoSPInstaller\AutoSPInstallerFunctions.ps1:22-4 char:67+
                Get-SPDatabase | ? {$_.Name -eq $ProfileDB} | Add-SPShellAdm ...
CategoryInfo          : InvalidData: (Microsoft.Share...AddSPShellAdmin:   SPCmdletAddSPShellAdmin) [Add-SPShellAdmin], ArgumentException    +
FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShe   llAdmin

The same error occurs on the Social database.

I'm running the setup as contoso\spsetup (local admin)
Farm admin account is contoso\spfarm
Service account is contoso\spsvc
Web app account is contoso\spweb

So I'm using different accounts for all services.
I found another thread with the same error but there were not resolutions that helped my problem.

Coordinator
Jan 17, 2013 at 3:19 AM

Have not seen this myself. Could you possibly try again with the latest checked-in version? There may have been a fix implemented for this although I don't recall specifically. Are there any errors of note in the SQL server logs?

Brian

Jan 17, 2013 at 8:17 AM

brianlala,

I am using this version:

Source Code AutoSPInstaller 3.2

source code, 78K, uploaded Oct 13, 2012 - 33347 downloads
 
Where can i download the latest check-in version?
Do you mean this version > http://autospinstaller.codeplex.com/SourceControl/changeset/view/97444 ?
 
I will check the SQL logs
Apr 17, 2013 at 12:18 AM
Edited Apr 17, 2013 at 12:19 AM
I am seeing the exact same issue and I'm using version 3.86 of the script.

As a result of this issue, the top right suite bar links (Newsfeed, SkyDrive, Sites) are missing from the Portal web app. I went in to SQL Server and manually add the app pool account of the Portal to the SharePoint_Shell_Access role for the Profile and Social DBs - and this made the suite bar links appear for the Portal web app.

My environment is a single server farm.

I ran the script under a dedicated install account and have specified dedicated app pool accounts for farm admin, web app pool and service app pool.

Many thanks for this great script!

Bernado
Apr 17, 2013 at 2:06 AM
I have done some further investigation. If I login as the Install account and execute the Add-SPShellAdmin cmdlet then I get the same error as the script. If I login as the FarmAdmin account then the cmdlet works fine (I have to add the FarmAdmin account to the local Administrators group though).

Perhaps others are not seeing this error because they are using the same account for Install and FarmAdmin?
Nov 8, 2013 at 2:33 AM
Also getting this error. I was able to add the role to the account manually for the Social and Profile database on the SQL Server.

The error doesn't halt the script.

Using 3.93 of AutoSPInstaller, SharePoint 2013 Enterprise on Windows 2012, connecting to a separate SQL 2008 SP2 server running on Windows 2008 RS SP1 server. Using separate accounts for all services.
Jan 4, 2014 at 11:11 AM
I get this same error on the Social and Profile databases wherein the application pool account SP_PortalAppPool could not be added to the shell admin role on those databases. I'm using:

-v 3.96 of the script
-Windows 2012
-SQL 2012
-SharePoint Enterprise 2013
-Individual accounts for

SP_Admin
SP_Farm
SP_Services
SP_PortalAppPool
SP_ProfilesAppPool
SP_SearchService
SP_CacheSuperUser
SP_CacheSuperReader
SP_ProfileSync
SP_SearchContent

Error messages are:
  • Granting CORP\SP_PortalAppPool rights to SP2013_Profile...
    Add-SPShellAdmin :
    "Cannot add CORP\SP_PortalAppPool to the SharePoint_Shell_Access role of the database SP2013_Profile. A possible
    cause of this error is that the account name was already added to the database as a login using a different user name
    than the account name."
    At \192.168.1.100\f$\Software\SharePoint\SP2013\AutoSPInstaller\SP\AutoSPInstaller\AutoSPInstallerFunctions.ps1:2996
    char:67
  • Get-SPDatabase | ? {$_.Name -eq $profileDB} | Add-SPShellAdm ...
  • ~~~~~~~~~~~~~~
    • CategoryInfo : InvalidData: (Microsoft.Share...AddSPShellAdmin:SPCmdletAddSPShellAdmin) [Add-SPShellAdm
      in], ArgumentException
    • FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin
  • Granting CORP\SP_PortalAppPool rights to SP2013_Social...
    Add-SPShellAdmin :
    "Cannot add CORP\SP_PortalAppPool to the SharePoint_Shell_Access role of the database SP2013_Social. A possible
    cause of this error is that the account name was already added to the database as a login using a different user name
    than the account name."
    At \192.168.1.100\f$\Software\SharePoint\SP2013\AutoSPInstaller\SP\AutoSPInstaller\AutoSPInstallerFunctions.ps1:2998
    char:66
  • Get-SPDatabase | ? {$_.Name -eq $socialDB} | Add-SPShellAdmi ...
  • ~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidData: (Microsoft.Share...AddSPShellAdmin:SPCmdletAddSPShellAdmin) [Add-SPShellAdm
      in], ArgumentException
    • FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin
Jan 6, 2014 at 10:39 AM
It turns out that in SQL these databases :

SP2013_SecureStore
SP2013_Profile
SP2013_Sync
SP2013_Social

are owned by the farm account (sp_farm) whereas the rest of the databases are owned by the setup account (in my case sp_admin). This explains why the command throws the error. Not sure why these databases are owned by the sp_farm account. Thoughts anyone?
Jan 7, 2014 at 9:15 AM
Found that there is a part of the script that launches with the farm account and creates the UPS. It is likely that this part of the script therefore creates those specific databases with the farm account as owner. To workaround this I simply ignored the errors for now and used these commands to add the required permissions:

Login as the setup account and launch a powershell window with elevated permissions and execute:
net localgroup administrators /add corp\sp_farm
This will add the farm account to the local administrators group. Then login as the farm account and execute these commands using an elevated PowerShell window:
Add-PSSnapin Microsoft.SharePoint.Powershell
Get-SPDatabase | ?{$_.Name -eq "SP2013_SecureStore"} | Add-SPShellAdmin –Username corp\sp_admin
Get-SPDatabase | ?{$_.Name -eq "SP2013_Profile"} | Add-SPShellAdmin –Username corp\sp_admin
Get-SPDatabase | ?{$_.Name -eq "SP2013_Sync"} | Add-SPShellAdmin –Username corp\sp_admin
Get-SPDatabase | ?{$_.Name -eq "SP2013_Social"} | Add-SPShellAdmin –Username corp\sp_admin
Make sure you replace the databases with your database names and the account with your setup account.

Again login with the setup account and execute this command to remove the farm account from the local admin group:
net localgroup administrators /delete corp\sp_farm
That's it, you're done!
Apr 16, 2014 at 5:39 AM
Same issue.

I did similar to what @sharepointbloke suggested but added the PortalAppPool account as per the error.

I'm not entirely confident that this will resolve the problem.
Nov 26, 2014 at 12:42 AM
Edited Nov 26, 2014 at 12:43 AM
Hi

I have seen this issue when building a multi server farm on using SQL Server 2012 as the back end database. I am running as best practice (separate install and farm accounts). The install account has public, dbcreator and security admin roles in the database. As pointed out in this script the four databases in question are all owned by the farm account, whereas the other databases are owned by the install account. At the point the script attempts to add the user users (managed accounts) to the profile database it fails as the install account does not have the necessary permissions in these databases. It will also fail (as pointed out above) if you try and run the power shell manually as the install account (for the same reason). Now, many users will not see this error, particularly if they run the install (incorrectly?) under the farm account credentials - in this case all databases will be owned by the farm account and the script has the necessary permissions to add the other users. I believe script will also work correctly if you give the install account sysadmin permission in the SQL database for the install (and then remove it afterwards).

In this case the database server was prepared in advance by another team in readiness for the SharePoint install. While we do not believe there is any special policy or server role customisation in play I cant rule it out.

I'll leave it to better minds than me to work out which is the more sensible approach or to consider if there is actually something that needs updating in the script / approach here.

Cheers

Adrian
Jan 15, 2015 at 1:26 AM
Hi there!

I had the same problem using SPAutoInstaller (Configuration Version="3.96") on a multi-server farm.
All service account are unique and have separate passwords. and the input XML has been filled using SPAutoInstallerGUI.

Error Message:
  • Granting domain\serviceaccountname rights to Prefix_Content_MyPortal...
  • Granting domain\serviceaccountname rights to Prefix_Profile...
    Add-SPShellAdmin :
    "Cannot add domain\serviceaccountname to the SharePoint_Shell_Access role of the database Prefix_Profile. A
    possible cause of this error is that the account name was already added to the database as a login using a different
    user name than the account name."
    At F:\AutoSPInstaller\SP\AutoSPInstaller\AutoSPInstallerFunctions.ps1:3141 char:67
  • Get-SPDatabase | ? {$_.Name -eq $profileDB} | Add-SPShellAdm ...
  • ~~~~~~~~~~~~~~
    • CategoryInfo : InvalidData: (Microsoft.Share...AddSPShellAdmin:SPCmdletAddSPShellAdmin) [Add-SPShellAdm
      in], ArgumentException
    • FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin
    • Granting domain\serviceaccountname rights to SP2013DEV_Social...
      Add-SPShellAdmin :
      "Cannot add serviceaccountname to the SharePoint_Shell_Access role of the database PRefix_Social. A
      possible cause of this error is that the account name was already added to the database as a login using a different
      user name than the account name."
      At F:\AutoSPInstaller\SP\AutoSPInstaller\AutoSPInstallerFunctions.ps1:3143 char:66
  • Get-SPDatabase | ? {$_.Name -eq $socialDB} | Add-SPShellAdmi ...
  • ~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidData: (Microsoft.Share...AddSPShellAdmin:SPCmdletAddSPShellAdmin) [Add-SPShellAdm
      in], ArgumentException
    • FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin
Feb 23, 2015 at 9:35 PM
Feb 23, 2015 at 9:46 PM
So what are the implications of this going forward without taking any action? What impact can we expect if the Portal account does not have Shell Admin role on the Social and Profile Databases?

Thanks