Vault server 3.1.8 install fails creating stored procs

mitch.wheat · Post by **mitch.wheat** » Thu Apr 06, 2006 10:37 pm

Whilst performing a clean install of vault server 3.1.8, I received the following message:

OK
Configuring your new Vault Installation Requesting Vault Admin user
password...OK Connecting to the SQL Server...OK Verifying the SQL
Server requirements...OK Checking for an existing Vault database...Not
found.
Creating a new Vault database on (local)...
Creating the SourceGear Vault database...OK
Upgrading SourceGear Vault database...OK
Creating SourceGear Vault stored procedures...The variable name
'@dbname' has already been declared. Variable names must be unique
within a query batch or stored procedure.
Vault Setup is exiting due to a failure or cancellation. Error Code =
402

mskrobul · Post by **mskrobul** » Fri Apr 07, 2006 9:08 am

Hmmm...we haven't seen that one before.

What version of SQL are you using? What is your SQL collation?

Have you upgraded or made any changes to SQL server recently?

When installing Vault, did you choose windows or SQL authentication in the connect to database dialog? Did you choose to authenticate with a user who has db_owner permissions on the 'Master' database (this is a requirement for the install)?

Can you send us your vault_install.log file located in your temp (%temp%) directory?

You can use the email button at the bottom to email me directly.

mitch.wheat · Post by **mitch.wheat** » Sun Apr 09, 2006 6:37 pm

SQL Server 2000 SP3

SQL_Latin1_General_Cp1_Cl_AS

No changes as far as I'm aware (all other DBs and apps are running fine)

Installed as a domain user. Tried a second time after ensuring that account had full access to master, then tried as myself (local admin on SQL server box). All to no avail.

From the look of the error it really is complaining about an error in the script.

Attached install.log

BTW When trying to attach the log file, it said the extension .log is NOT ALLOWED !!!!

mitch.wheat · Post by **mitch.wheat** » Sun Apr 09, 2006 11:42 pm

Ooops! My mistake.

I placed the domain user in teh System Admin role assuming this would include dbo. Removed this and explicitly placed user in rol db_owner and DB install worked OK.

mitch.wheat · Post by **mitch.wheat** » Sun Apr 09, 2006 11:47 pm

OK scrub that rather pre-mature post.

I tried it on my local SQL server and that worked; then re-tried on production server (after last post!) and get the same error.

Creating a new Vault database on (local)...
Creating the SourceGear Vault database...OK
Upgrading SourceGear Vault database...OK
Creating SourceGear Vault stored procedures...The variable name '@dbname' has already been declared. Variable names must be unique within a query batch or stored procedure.
Vault Setup is exiting due to a failure or cancellation. Error Code = 402

Does this look like a permissions problem?

mskrobul · Post by **mskrobul** » Mon Apr 10, 2006 8:29 am

It probably is some type of permission problem.

If it was an error in the script it would happen to everyone. That part of the script has been there for a very long time.

Can you try using SQL authentication instead of windows auth? In the Connect to Server dialog during the install choose the SQL Authentication radio button and use the SQL SA name and password to connect (note that this login will only be used during the install).

It may still be helpful to get your log file. If you want to email me your log file you can email maryjo at sourcegear.com.

mitch.wheat · Post by **mitch.wheat** » Tue Apr 11, 2006 6:55 pm

Hi Mary

I attached the log 2 replies up.

Regards
Mitch

Post by **jclausius** » Tue Apr 11, 2006 7:57 pm

Mitch:

MaryJo, I hope I'm not interrupting, but I have a couple of questions for Mitch.

Mitch:

1) You installed successfully to a different SQL Server. What was the version of that SQL Server? What are the differences between the two SQL Servers?

2) How are you connecting to the SQL Server? SQL Server authentication or Windows authentication? If using SQL Server authentication, what account are you using? If using Windows authentication, what priviledges does your Windows account have on the production SQL Server?

3) You seem to be using a Custom .Net account in the install? Is there a reason not to use the default setting for the ASP.Net process?

mitch.wheat · Post by **mitch.wheat** » Tue Apr 11, 2006 9:21 pm

Hi Jeff and Mary

I have just run profiler to try and find which proc is causing the failure. It seems to be this one:

-------------

CREATE PROCEDURE [spkillusers] (@dbname nvarchar(50)) -- NOTE DO NOT ENCRYPT... THIS IS IN THE MASTER DATABASE, AND AS A COURTESY SHOULD BE VIEWABLE
AS
BEGIN

-- Copyright 2002-2005 SourceGear LLC. --
-- All rights reserved. See Vault License Agreement --
-- for more information. --

SET NOCOUNT ON
DECLARE @@strsql nvarchar(255)
CREATE TABLE #tmpusers
(
spid int,
eid int,
status nvarchar(30) COLLATE DATABASE_DEFAULT,
loginname nvarchar(50) COLLATE DATABASE_DEFAULT,
hostname nvarchar(50) COLLATE DATABASE_DEFAULT,
blk int,
dbname nvarchar(50) COLLATE DATABASE_DEFAULT ,
cmd nvarchar(30) COLLATE DATABASE_DEFAULT
)

INSERT INTO #tmpusers EXEC sp_who
DECLARE logincursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpusers WHERE dbname = @dbname
DECLARE @spid nvarchar(10)
DECLARE @dbname2 nvarchar(40)
OPEN logincursor
FETCH NEXT FROM logincursor INTO @spid, @dbname2
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @@strsql = N'KILL ' + CAST(@spid AS nvarchar(10))
EXEC sp_executesql @stmt = @@strsql
END
FETCH NEXT FROM logincursor INTO @spid, @dbname2
END
CLOSE logincursor
DEALLOCATE logincursor
DROP TABLE #tmpusers

SET NOCOUNT OFF
RETURN @@ERROR
END

----------------

I've tried installing as both the sa and a domian account with full rights.

The only reason I'm using an impersonated Domain account is because We have set up a specific user for vault to run under. Is this not the preferred way to install?

Regards,
Mitch

mitch.wheat · Post by **mitch.wheat** » Tue Apr 11, 2006 9:37 pm

I should add that I checked the DB after it fails, and there is no sign of the stored proc spkillusers.

Also, even weirder, I can run that stored proc in query Analyser and it works fine!

MItch

mitch.wheat · Post by **mitch.wheat** » Tue Apr 11, 2006 9:42 pm

Just tried re-running as the Network\Service option: still fails.

SQL Server 2005 is also installed on the Box. Would this affect it?

What's the best way to find out all the differences between my local server (that works) and the production server?

Regards,
Mitch

Post by **jclausius** » Tue Apr 11, 2006 9:45 pm

Mitch:

Yes. The stored procedure works fine. Unfortunately, any searches on the error message indicates a failed SQL Server installation.

How confident do you feel about your SQL Server? Is it being used without any problems? If this a brand new SQL Server installation, could you uninstall / reinstall SQL Server? The posts to this problem indicate uninstalling / reinstalling SQL Server might do the trick.

Additionally, did you find the answers to my post?

mitch.wheat · Post by **mitch.wheat** » Tue Apr 11, 2006 10:00 pm

Hi Jeff

1) Both Servers are 2000 SP3. My local one does not have a copy of SQL 2005 installed. What's the best way to enumerate all the differences?

2) Used sa account, domain account as local admin, and myself as local admin, all with no result. Both Windows account were granted db_owner permissions.

3) Tried using custom and the default Network Service account.

BTW. this box has IIS 6.0 on it and also Sharepoint. We have tried removing Sharepoint, but still doesn't work.

Regards,
Mitch

Post by **jclausius** » Tue Apr 11, 2006 10:38 pm

What about the SQL Server installation itself? Could it be at fault? How solid is the SQL Server installation? Can an uninstall / re-install be done?

Other than that, about the only other option I can think of is to install Vault against a different SQL Server, move the database, configure the new SQL Server with the correct permissions for sgvault, and then change the connect string in Vault's web.config to point to the original SQL Server machine name.

If you go this other route, you'll need to make sure the authentication found in the connection string in Vault Service's web.config matches your SQL Server setup.

If you are connecting in SQL Server authentication mode, the default is to use sgvaultuser. You'll need to create an sgvaultuser in the new database, and then run the sp_change_users_login to map the new sgvaultuser to the sgvaultuser found in the database.

If you're using Windows authentication, you'll have to create a SQL Login for your ASPNet Process account, and then grant that account db_owner and public in the sgvault database.

BTW - I'm signing off for now, but I'll be back in 8 hours or so.

Post by **jclausius** » Tue Apr 11, 2006 10:49 pm

mitch.wheat wrote:The only reason I'm using an impersonated Domain account is because We have set up a specific user for vault to run under. Is this not the preferred way to install?

The preferred way is to use the default .Net process (NETWORK_SERVICE account)

If you do create an account, you will need to configure it to run as a Custom .Net account. Also, within IIS 6.0, I would look at the application pool for Vault. Make sure it is running as this account.