Applying Encryption Keys in a Web Farm

I wonder if someone can help clarify this one..?
If Iâ've implemented Reporting Services across a Web Farm, and saved a copy
of the encryption key for each node â' If encryption is broken, which key do I
apply?
Thanks,
MarkThe key is the same for each node. There is only one symmetric key for a
farm. The key is just encrypted differently for each node.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> I wonder if someone can help clarify this one..?
> If I've implemented Reporting Services across a Web Farm, and saved a copy
> of the encryption key for each node - If encryption is broken, which key
do I
> apply?
> Thanks,
> Mark
>|||Thanks Daniel
Does that mean that if I want to recover I run â'rskeymgmt â'a..â' on one (any)
node â' or on all nodes in the Farm?
"Daniel Reib [MSFT]" wrote:
> The key is the same for each node. There is only one symmetric key for a
> farm. The key is just encrypted differently for each node.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > I wonder if someone can help clarify this one..?
> >
> > If I've implemented Reporting Services across a Web Farm, and saved a copy
> > of the encryption key for each node - If encryption is broken, which key
> do I
> > apply?
> >
> > Thanks,
> > Mark
> >
>
>|||You would need to do that to each node that was failing with an error about
not be able to unencrypt data. You should rarely have to use this tool.
Only if any passwords get reset or users that the service are running as
change.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
news:6865252C-DD9F-425F-8F4A-498139C9BDDC@.microsoft.com...
> Thanks Daniel
> Does that mean that if I want to recover I run 'rskeymgmt -a..' on one
(any)
> node - or on all nodes in the Farm?
>
> "Daniel Reib [MSFT]" wrote:
> > The key is the same for each node. There is only one symmetric key for
a
> > farm. The key is just encrypted differently for each node.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> > news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > > I wonder if someone can help clarify this one..?
> > >
> > > If I've implemented Reporting Services across a Web Farm, and saved a
copy
> > > of the encryption key for each node - If encryption is broken, which
key
> > do I
> > > apply?
> > >
> > > Thanks,
> > > Mark
> > >
> >
> >
> >|||OK - Thanks Daniel - Appreciate your swift response.
"Daniel Reib [MSFT]" wrote:
> You would need to do that to each node that was failing with an error about
> not be able to unencrypt data. You should rarely have to use this tool.
> Only if any passwords get reset or users that the service are running as
> change.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> news:6865252C-DD9F-425F-8F4A-498139C9BDDC@.microsoft.com...
> > Thanks Daniel
> >
> > Does that mean that if I want to recover I run 'rskeymgmt -a..' on one
> (any)
> > node - or on all nodes in the Farm?
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > The key is the same for each node. There is only one symmetric key for
> a
> > > farm. The key is just encrypted differently for each node.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> > > news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > > > I wonder if someone can help clarify this one..?
> > > >
> > > > If I've implemented Reporting Services across a Web Farm, and saved a
> copy
> > > > of the encryption key for each node - If encryption is broken, which
> key
> > > do I
> > > > apply?
> > > >
> > > > Thanks,
> > > > Mark
> > > >
> > >
> > >
> > >
>
>

Applying CASE in Stored Procedures

I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:

ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)

AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.

Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys

But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.

I'm using SQL Server 2000, connecting with Visual Studio.NET 2003

Moving to the Transact-SQL forum, which is best suited to this type of questions.|||

What is the value of @.keys?

If you hard-code the values do you get results?

|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Well, the CASE in the WHERE does works

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.

|||

This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:

http://www.sommarskog.se/dyn-search.html

It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.

All of this is nicely covered in that article.

|||

Hi,

In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..

Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys