• This may be useful, feedback is certainly most welcome.

    1) How does one perform validation processes in SQL2K?

    Below is a script that demonstrates a column that has accepted encrypted values, it then returns a row based on input that undergoes an encryption.

    THIS IS NOT SUPPORTED BY MICROSOFT >>WAS IT EVER???<<

    <I hope it fits>

    SET NOCOUNT ON

    GO

    /*

    Author:Shaun Tinline-Jones

    Create Date:2003/05/13

    Purpose:

    Testing some logic around the Encrypt function

    */

    USE Northwind

    GO

    IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Test_Encrypt'), N'IsTable') = 1

    DROP TABLE dbo.Test_Encrypt

    GO

    -- We need to store the values in a field that holds the result of the encryption

    CREATE TABLE dbo.Test_Encrypt

    (

    Gambler sql_variant NOT NULL

    --Gambler nvarchar(25) NOT NULL

    )

    GO

    /**************************** We have some gamblers *********************************/

    DECLARE @Name nvarchar(25) --sql_variant

    SELECT @Name = ENCRYPT(N'Shaun')

    INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(@Name))

    INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(N'Grant'))

    INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(N'Jacye'))

    SELECT Gambler FROM dbo.Test_Encrypt

    GO

    /******************************** Now let's get that winner ****************************/

    DECLARE @Winner nvarchar(25)

    SET @Winner = N'Shaun'

    SELECT N'and the lotto winner is.......' + @Winner FROM dbo.Test_Encrypt WHERE Gambler = ENCRYPT(@Winner) --@Encrypted_Winner

    GO

    SET NOCOUNT OFF

    GO

    2) How does one deal with the upgrade?

    The encrypt function is now, correctly stated by Jacye, using the windows CryptoAPI. So yes it is different from other versions of SQL Server and also suffers the same exposure to cracking>>Whatever that may be<< as this API .

    The best is to use a query that takes a first time user, that is first time since upgrade, check it against a SQL Server 7.0 with the password table. If it succeeds, run customer created encryption algorithm, even if it is the straight Windows CryptoAPI, (as opposed to the SQL2K function) and store the result in the SQL Server instance and record that the user has upgraded.

    This will handle the upgrade in a controlled fashion, while remaining transparent to the user community as well as protect the customer from the possible deprecation of the ENCRYPT function.