locked
Having problems porting encryption to new server ` RRS feed

  • Question

  • Hello All,  

          I've been tasked with moving a SQL Server 2005 database on Windows 2003 to Windows2008R2 and SQL Server 2008.

    It calls the procedure this way:

                    using (adapter = new SqlDataAdapter("proc_GetPersonalTransactions", con))
                    {
                        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                        adapter.SelectCommand.Parameters.AddWithValue("@Login_ID", ename);
                        adapter.SelectCommand.Parameters.AddWithValue("@SDate", sdate);
                        adapter.SelectCommand.Parameters.AddWithValue("@EDate", edate);

                        adapter.Fill(ds);
                    }

    The stored proc looks like this:

         


    AS

    open

    symmetric key CP_Login_Key decryption by password='Password01'



    --select * from dbo.PersonalTransactions



    select



    convert

    (varchar, decryptByKey(FirstName)) as First_Name,convert(varchar, decryptByKey(LastName)) as Last_Name,


    convert

    (varchar, decryptByKey(Account_Holder)) as Account_Holder,Contract_Date,


    convert

    (varchar, decryptByKey(Stock_Name)) as Stock_Name,ccy,


    convert

    (varchar, decryptByKey(Units)) as Units,convert(varchar, decryptByKey(Unit_Price)) as Unit_Price,


    Buy_Sell

    ,Insert_Date



    from

    dbo.PersonalTransactions


    where

    Contract_Date >= @SDate and Contract_Date <= @EDate


    and

    Login_ID = @Login_ID


    When I point web.config to the old server everything works fine. Using the same search, on the same database just on the new server it just returns nothing. It used to fail outright, but I changed web.config to bubble up errors and it said the user was not granted execute permissions on these two procs. So I did a grant, everything seems to work fine. When I issue the above query without parameters I get the same full table unencrypted. On the old server I get results in the UI, in the new server I don't get anything? What am I missing here? Thanks in advance, Bryan.


    Monday, June 20, 2016 5:20 PM

Answers

  • SQL Server has a key encryption hierarchy where keys protect each other. Supposedly this symmetric key is protected by something, maybe the database master key which in its turn is protected by the service master key. Which is not the same on the two instances, unless you export the service master key from the old instance to the new.

    Tuesday, June 21, 2016 9:38 PM