Sign in
 
HomeOnline20132010Other VersionsRelated ProductsLibraryForumsGallery
TechCenter >
> 
> 
MSSQL database not accessible by farm user though he created it?

回答済み MSSQL database not accessible by farm user though he created it?

  • Thursday, September 13, 2012 8:23 AM
     
     
    Sign In to Vote
    0

    Hey everyone!

    This problem really begins to drive me nuts:

    I've got a testing machine, Win SBS 2011 Standard with SP Foundation 2010 included. I've been playing around with SP for a while, testing and designing a concept. Now I want to reset it to vanilla state to have a clean product on which I can apply my organisational concept.

    What I did:

    - Remove SP from the farm via the Configuration Assistant
    - Delete Sharepoint_Config and AdminContent_ databases
    - Uninstall SP, reboot
    - Install SP, apply all updates (SQL Server 2008 R2 was already installed at that point)
    - Create a domain user "DOM\my_spfarmuser", add his credentials to MSSQL
    - Run the Configuration Assistant
    - Create a new farm
    - Supply database and my_spfarmuser's login credentials
    - click through the rest of the wizard
    - wait for the process to finish (no errors so far)
    - open Central Administration for the first time...
    - Boom: Cannot connect to the configuration database.

    I tracked it down to the following:

    2012-09-13 10:17:03.40 Error: 18456, Severity: 14, Status: 38.
    2012-09-13 10:17:03.40 Login failed for user 'DOM\my_spfarmuser'. Failed to open the explicitly specified database [CLIENT: <local machine>]

    But: The user is definitely allowed to access the databases (error 38 states that he has no access rights to the database). But, as far as I'm concerned, he _must_ have access rights to the database because he has created it.
    The SharePoint_Config and AdminContent databases are created with the correct access rights by this user. The IIS application pools which are created by the Configuration Assistant have the correct identity. The account's role is set to WSS_ADMIN_WPG.

    So the Config Assistant seems to set everything absolutely correctly, still I'm unable to start the Central Administration and continue with my configuration.

    What can I still do to fix this?

    Kind Regards
    Nico

     

All Replies

  • Thursday, September 13, 2012 5:36 PM
     
     
    Sign In to Vote
    0
    Do you have SQL Management Tools on the machine? If so, can you log into it with the service account just to make sure its strictly related to SharePoint and not something else.
     
  • Friday, September 14, 2012 11:19 AM
     
     
    Sign In to Vote
    0

    Hello Kyle,

    I tried this (ran the SQL Server Management Studio as the DOM\my_spfarmuser) and could not access the SharePoint databases, i.e. could not expand the corresponding subtree. The same error in the MSSQL log occurred.

    Furthermore, I had wrong that the my_spfarmuser owned the database. It is actually the administrator account under which I ran the SharePoint Config Assistant. But there are all rights on both relevant databases granted to the my_spfarmuser.

    This has most likely something to do with the MSSQL rights management, but I can't get a grip on where to look for this issue.

    Regards
    Nico

     
  • Friday, September 14, 2012 1:36 PM
    Moderator
     
     
    Sign In to Vote
    0
    Does your farm admin account have securityadmin and dbcreator roles on the SQL Server?

    http://sharepoint.nauplius.net

     
  • Friday, September 14, 2012 1:45 PM
     
     
    Sign In to Vote
    0

    Yes and yes. This has also been done by the SP Config Assistant, as far as I can tell.

    The user is also assigned to the two SP databases and has the db_owner, db_securityadmin, SharePoint_ShellAccess and WSS_Content_Application_Pools roles on both.

     
  • Wednesday, September 19, 2012 8:40 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    The DOM\my_spfarmuser which has securityadmin and dbcreator roles does not access the SharePoint database? Are the SQL Server and SharePoint Server installed on the disparate servers? What's the edition of your SQL Server?

    Do you mean that you cannot access the SharePoint database on the SQL Server machine with the DOM\my_spfarmuser account via the SQL Server Management Studio?

    To make sure and find out the owner of the SharePoint database by using the following SQL query.

    "select suser_sname(owner_sid) from sys.databases where name = 'Northwind'"(The Northwind is the name of your SharePoint database.)

    Here is one article can be referred to.

    SQL Server Tip: How to find the owner of a database through T-SQL

    http://invalidlogic.com/2007/03/04/sql-server-tip-how-to-find-the-owner-of-a-database-through-t-sql/

    Hope that helps.

    Ivan-Liu

    TechNet Community Support

     
  • Wednesday, September 19, 2012 9:07 AM
     
     
    Sign In to Vote
    0

    Hi,

    The DOM\my_spfarmuser which has securityadmin and dbcreator roles does not access the SharePoint database? Are the SQL Server and SharePoint Server installed on the disparate servers? What's the edition of your SQL Server?

    That is correct. SQL and SharePoint services are installed on the same machine, the SQL Server is a 2008 R2 Express as included with Windows SBS 2011 Standard.

    Do you mean that you cannot access the SharePoint database on the SQL Server machine with the DOM\my_spfarmuser account via the SQL Server Management Studio?

    This is correct. When I run the SQL Server Management Studio as the my_spfarmuser, I can see the databases, but as soon as I try to expand their subtrees/content, I get an error message stating that I'm not allowed to access the database - plus, the aforementioned entry (Error: 18456, Status: 38) occurs in the Errorlog of the SQL instance.

    To make sure and find out the owner of the SharePoint database by using the following SQL query.

    "select suser_sname(owner_sid) from sys.databases where name = 'Northwind'"(The Northwind is the name of your SharePoint database.)

    The owner is the Domain and Local Administrator account under which I ran the SharePoint Configuration Assistant. But as far as I can tell, all necessary rights for the my_spfarmuser have been assigned by the assistant.

    When I supply the Administrator account to the Assistant instead of the my_spfarmuser account, everything works like a charm - but I basically don't want the whole thing to run under a privileged account.

    Regards
    Nico

     
  • Wednesday, September 19, 2012 2:56 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Go into Management Studio as a sysadmin, find your farm user under Logins, then validate that that user has dbowner on all SharePoint databases.

    http://sharepoint.nauplius.net