Error when installing

[koolworld]

Hi, get this error when installing Vault 4_1_1_18060 on Win2003 server with SQL2000 SP4 installed on same machine:

Looking for IIS Version
Configuring your new Vault Installation
Requesting the IIS Process User...Requesting Vault Admin user password...OK
Connecting to the SQL Server...OK
Verifying the SQL Server requirements...OK
Checking for an existing databases...Found.
Asking for database's fate...Dropping database
Dropping Existing database...OK
Creating a new Vault database on (local)...
Creating the SourceGear master database...OK
Creating the SourceGear source control database...OK
Creating SourceGear master stored procedures...'with ENCRYPTION' is not a recognized option.
Vault Setup is exiting due to a failure or cancellation. Error Code = 402

[Beth]

We ran a quick test here to ensure success when installing on Windows 2000 sp4. That went ok.

In what you posted, it said it found a database and that you chose not to keep it. If there was an existing database, did you make a backup first?

[koolworld]

Hi, the log was from my 2nd attempt to install, the first fresh install came up with the same error.

Did you try it on SQL2000 SP4?

[Beth]

I'm sorry, that's what I meant. It was tried with SQL 2000 sp4, though the machine was Windows 2000 also. It shouldn't make a difference on the OS of the server. I tried it with a Windows 2003 server with SQL 2000 sp4.

Were any errors logged in your event viewer?

[Beth]

Let's see if your database will allow use to even create a stored procedure. Please run the following in query analyzer.

Code: Select all

CREATE PROCEDURE [dbo].[sourcegeartestsp] WITH ENCRYPTION AS

SELECT count(*) from sysusers
GO

Please let me know if there is a success or fail from that.

[koolworld]

Thanks for the quick response. This seems to be the problem, the output from the above is:

Code: Select all

Server: Msg 195, Level 15, State 1, Procedure sourcegeartestsp, Line 1
'ENCRYPTION' is not a recognized option.

[Mike Dimmick]

I believe WITH ENCRYPTION was added in Microsoft SQL Server 2000, so if it's not recognized, that would imply that the server is older. What's the result of running SELECT @@VERSION?

WITH ENCRYPTION isn't as secure as you might think. SQL Server 2000 reuses the same key when a stored procedure is altered with the ALTER PROCEDURE function, and it uses the RC4 stream cipher, which generates a bitstream of data derived from the key called the keystream, which is then XORed with the plaintext to produce the ciphertext. If you use ALTER PROCEDURE ... WITH ENCRYPTION to change the procedure text to a known plaintext of sufficient length, you can retrieve the new procedure ciphertext and XOR it with the known plaintext to retrieve the keystream. You can then XOR the original ciphertext with the retrieved keystream to reveal the original plaintext.

Example decryption script

This still works in SQL Server 2005, but you now have to be logged in using the Dedicated Administration Connection to access the procedure text. Source

WITH ENCRYPTION is best thought of as an anti-tampering mechanism rather than a mechanism to protect source code. (Even if this feature didn't have this key reuse error, the text still has to be submitted to SQL Server as plaintext to begin with.)

[koolworld]

Hi, results are:

Code: Select all

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

Is the encryption something which needs enabling?

[jclausius]

WITH ENCRYPTION is best thought of as an anti-tampering mechanism rather than a mechanism to protect source code.

Yes.

[koolworld]

why is it necessary to encrypt your stored procs? Surely it doesn't matter if people see the code.

[jclausius]

Since a stored proc is not exactly like a binary piece of code (which can still be decompiled), the ENCRYPTION flag keeps people from accidentally changing code which could corrupt the actual data. See my post above.

To be honest, I've never seen this error before, and an internet search provides no data on an error of this type. I'll see if I can get some kind of response on Microsoft for this error.

In the meantime, another option we could try is to temporarily install SQL Server Express (http://www.microsoft.com/sql/editions/e ... fault.mspx). By default this will create a named instance Server (SQLEXPRESS). Once installed, you could run the installer against ( .\SQLEXPRESS ) and see if that creates the Vault database stored procs.

[koolworld]

Thanks for looking into this - i too looked on Google and couldn't find anything which is really surprising. Will look at getting another instance of a database installed other than SQL2000.

[jclausius]

A thought...

Does the following script work?

Code: Select all

-- a test db
CREATE DATABASE testdb
GO
-- switch to the db
USE testdb
GO
-- a stored proc
CREATE PROCEDURE dbo.sourcegeartestsp AS
BEGIN
SELECT count(*) from sysusers
END
GO
-- execute the stored proc
EXEC dbo.sourcegeartestsp
GO
-- switch db context
USE master
GO
-- drop database
DROP DATABASE testdb
GO

[koolworld]

yes, returned 12.

[jclausius]

OK. That was of no help. :>)

I'll see if the MS newsgroups turn up any issues on the original error.

I know I suggested SQL Server Express, but the DB formats for that are not backwards compatible with SQL Server 2000. You may want to try MSDE 2000 (http://support.microsoft.com/kb/324998), so if we can get to the bottom of this problem, the MSDE 2000 database can be moved over to the SQL Server 2000 machine.