Vault without having SQL Server admin rights

cgadd · Post by **cgadd** » Tue Dec 11, 2007 4:33 pm

We had been running vault on a server maintained by my team, with our own internal MSSQL server.

We are being forced to migrate the Vault install to servers maintained by another group, including a Sql Server that we can not get admin rights to.

We have managed to get Vault running in the new environment by installing a local copy of SQL Server Express edition, using it for the Vault install, then performing a SQL backup, and having the backup restored by the group responsible for the MSSQL Server. I then edited the web.config file to point to that Sql server. All worked correctly.

ButI am now attempting to install the latest update (from 4.04 to 4.06), and the install procedure is still demanding the SQL Admin login....

Is there any way around this? Should I be doing something differently?

Beth · Post by **Beth** » Wed Dec 12, 2007 10:51 am

There really is no good way around this. Your SQL admin could give your windows login certain security rights and then you could use windows authentication.

Any work-arounds would end up requiring you to have some level of admin privilege somewhere. For example, if you copied the database off and then upgraded it elsewhere and then copied it back, you would either have to detach the database or stop SQL server, which I think may need admin rights.

cgadd · Post by **cgadd** » Wed Dec 12, 2007 3:33 pm

Can you tell me which rights would need?

cgadd · Post by **cgadd** » Fri Dec 14, 2007 4:54 pm

Can anyone tell me what specific SQL Server rights I would need in order to be able to run the install/upgrade?

Is there a reason that just have DBO rights to the two vault databases is not enough?

Beth · Post by **Beth** » Fri Dec 14, 2007 5:32 pm

I've been trying this out and so far I'm not successful without sysadmin. That's a nuance of SQL server. I can't even log into the SQL server manager with those. When I know what would do it, I'll post.

Would your SQL admin consider a temporary new login that had full sysadmin privileges?

cgadd · Post by **cgadd** » Wed Jan 02, 2008 9:17 am

It's currently unlikely. And unfortunately, it's kind of a wierd scenario, where my group controls the server vault runs on, and another group controls the SQL server. And for a variety of reasons, neither group can be given administrator rights to the other server. We do have full rights to our databases on the SQL server (dbo and such), but no admin rights to SQL itself.

It may become a non-issue, as our group is probably going to be forced to move to MKS like the other dev teams in the company because we can't get Vault running in our environment. The final meeting on the issue is tomorrow morning. If I don't have a solution by then, it's bye-bye vault.

Post by **jclausius** » Wed Jan 02, 2008 10:03 am

Vault requires rights for installation to create SQL Server user accounts, etc. Also, admin rights are required so a master db stored proc can be created. This sproc is used to close connections to the Vault databases so a backup can be made.

Setup of the database is very tedious work, and can be error prone. The installation is the best bet in configuring any Vault system. Another option would have the DBA run the installer.

Other than that, about the only thing you could try is do an install on another machine. Make a backup or detach the necessary database(s) - sgvault, sgmaster, and/or sgdragnet.

Next Have the DBA restore/attach them to the SQL Server, and create an account with dbo and public rights. You'll also need to make modifications within Vault's web.config to modify the connection string to get to that database.

Note, due to restrictions of the ASP.NET process model, you'll need to use Windows Auth using a Custom .NET account that also exists on your SQL Server within the domain or use the plain "PASSWORD" attribute within the string and connect using SQL Authentication.

If you need some more assistance on this configuration, please let us know.

cgadd · Post by **cgadd** » Thu Jan 03, 2008 9:26 am

We initially used the "install on a different machine and copy the sql backup" method. But then on the next minor upgrade of vault, the installer hung/trashed the install because it didn't have DBA rights again. I expected and understood the DBA requirement for the main upgrade (going from the 3.5.x to 4.x), but was suprised by the more minor upgrade also requiring it, which raises the issue of on-going support issues if we attempt to install without having DBA rights.

Post by **jclausius** » Thu Jan 03, 2008 9:32 am

An upgrade may also change SQL authentication, SQL accounts, etc. Full Admin rights are required for upgrades as well.

So for upgrades, you'll have to reverse the process:

1) Backup / Detach the databases from production.
2) Restore / Attach them to the "temporary" machine.
3) Install the Vault server there, so the DB is upgraded.
4) Backup / Detach from the "temp" machine
5) Restore / Attach on the production server.

Another way around this would be to have the DBA provide authentication info during the installation, and run the install against the production server.