Project Server 2007 DB provisioning fails in locked down SQL envir

[GarthP]

I have question regarding SQL permissions when setting up Project Server 2007
on WSS3 in a shared, locked dwon SQL environment.

I'm currently trying to setup Project Server 2007 for a customer and have
been given a Dev environment, which consists of a Web/App server (W2K8 SP1)
and a shared SQL 2005environment.

The customer's DBA has created the required DBs and assigned the domain
level setup account to the dbowner role.

I'm at the stage where I've managed to setup the WSS3 site after a lot of
work and am ready to create a Project Server site.

Each time I try and create the site either via the Web interface or STSADM
projcreatepwainstance I get the following error:

Provisioning '/Projects': Failed to provision databases. An exception
occurred: Database provisioning failed. User must be in both dbcreator and
securityadmin server roles or be dbowner on each of the databases..

Is it sufficient for the setup account to only have dbowner privileges or
does it really require dbcreator and securityadmin rights on the SQL server?
It has been made clear to me that there is no way that this elevated level
will be assigned to the account, so if it's required I'm not sure how I can
finish the implementation.

The dbowner privilege was enough to setup the Config, Content and SSP DBs,
so I would presume the same is true for the 4 Project Server DBs (Archive,
Draft, Published, Reporting).

Any help would be appreciated, it's had me stumped for a couple of days, and
I reckon I've trawled the Internet and read enough technical articles to
think that something is missing.

To confuse matters even more the DBA assigned the user to the dbcreator
fixed server role and I still get the "User must be in both dbcreator and
securityadmin server roles or be dbowner on each of the databases.." error.

Note that the securityadmin fixed server role has not been assigned.

GarthP

 

[Ray McCoppin]

This article describes the least-privilege for account using sharpoint. I
have seen sharepoint operate using less but there are time when sharepoint
will try to change the schema and the system will fail.
http://technet.microsoft.com/en-us/library/cc263445.aspx



If the DBA's and secuirty cna't live with this you may have to look for a
different tool.


Hope this helps

 

[GarthP]

Thanks Ray,

I discovered the following through gradually adjusting privileges on the
shared SQL environment :-
Installing the WSS related databases only requires dbowner privileges on DBA
created databases.
To install the 4 Project Server related databases (Archive, Draft,
Published, Reporting) you need to have dbcreator and securityadmin privileges
on the box, dbowner is not enough even though the Microsoft documentation
says so!
There must be a piece of code in the 'stsadm projcreatepwainstance' command
that checks for a return message from SQL to say that the setup user has
dbcreator and securityadmin privileges, if not it doesn't proceed.
If this is the case then Microsoft need to modify this command to allow the
lower level dbowner privilege to write to existing DBs.

The projcreatepwainstance also had a few undocumented feature when
privileges were raised for the setup user.
The DBA created databases were located on a SAN share with separate volumes
for .mdf and .ldf files, each database has its own directory (e.g.
Projects_Draft would reside at
M:\MSSQL\Data\Projects_Draft\Projects_Draft.mdb and
O:\MSSQL\Data\Projects_Draft\Projects_Draft.ldf).

When the 'projcreatepwainstance' command was run it created the databases at
the root of each of the volumes (M:\Projects_Draft.mdb and
M:\Projects_Draft.ldf)!!
Not only that but it dropped the existing WSS_Config database and created it
at the root of each volume!!!!

This behaviour is not acceptable in a locked down environemt, it's going to
make it very difficult to deploy the product into the customer's production
environment if I can't explicitly state the steps that
'projcreatepwainstance' carries out.

I would really like to know if there is a document that details the code
steps.