Data encryption and keys

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

As long as the user you are trying to protect against is not a dbo or sysadmin, you can also use permissions (i.e. "GRANT CONTROL ON ASYMMETRIC KEY :: MyAsymmKey TO user1") to restrict access rather than through passwords. The advantage is the user then doesn't have to depend on memorizing a password and you don't have to pass any password values in which is safer from a security standpoint.

Sung

|||Fyi, Books online links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.

Data Driven Subscriptions - Recipient List Management

Hi,
We have a need to be able to manage recipient lists for data driven
subscriptions in a user friendly way. (i.e. we need users to be able to edit
these recipient lists, and they are not the type of users that we want to
allow to open enterprise manager !!)
The general plan is to develop some ASP.Net type app that will edit the
table contents.
Before developing something new and possibly reinventing the wheel, I would
just like to know if the Reporting Services team are considering building
some type of recipient list managemtent into future releases of RS ?
Thanks !Not at this time. You will need to write your own application to take care
of this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gavin R" <GavinR@.discussions.microsoft.com> wrote in message
news:691AABAD-DE25-4B3E-9B68-9F3A9814AFD2@.microsoft.com...
> Hi,
> We have a need to be able to manage recipient lists for data driven
> subscriptions in a user friendly way. (i.e. we need users to be able to
> edit
> these recipient lists, and they are not the type of users that we want to
> allow to open enterprise manager !!)
> The general plan is to develop some ASP.Net type app that will edit the
> table contents.
> Before developing something new and possibly reinventing the wheel, I
> would
> just like to know if the Reporting Services team are considering building
> some type of recipient list managemtent into future releases of RS ?
> Thanks !

Data Delivery

I've created a number of .html, pivot tables using Access' Data Page
tools. These work great and by pointing my browser to these files,
users throughout my firm can access these pivot tables. I had assumed
that I could simply publish these pivot tables on the web to outside
users who are configured as active directory users. The .htm pivot
table is linked to a query in Access that is built from a SQL database.
We've separately built a system where users can crawl through our file
server from outside of our network. I placed the .htm file in a folder
that the user has access to and the application shows the file.
However, when the user clicks on the file, s/he gets these messages:
Data Access Pages has detected that your IE security settings will not
allow you to access data from a site considered to be insecure.
[
In order to access the data contained within the Data Access Page, you
need to:
1. Start IE
2. Choose Internet Options from the Tools menu
3. Click on the "Security" Tab
4. Click on the "Trusted Sites" icon
5. Click on the "Sites..." button
6. Uncheck the "Require server verification (https) for all sites in
the zone" checkbox
]
Our technology consultant indicated that there's no way for the data to
"bind" to the browser and it's impossible to expose these .htm, pivot
tables to users outside of our network. Does anyone know a way to
accomplish what I'm trying to do? I feel like it should be possible
since Microsoft created that product to build .htm pages?
Ryan
Any suggestions would be very helpfulRyan.Chowdhury@.gmail.com wrote:
> I've created a number of .html, pivot tables using Access' Data Page
> tools. These work great and by pointing my browser to these files,
> users throughout my firm can access these pivot tables. I had assumed
> that I could simply publish these pivot tables on the web to outside
> users who are configured as active directory users. The .htm pivot
> table is linked to a query in Access that is built from a SQL database.
>
> We've separately built a system where users can crawl through our file
> server from outside of our network. I placed the .htm file in a folder
> that the user has access to and the application shows the file.
> However, when the user clicks on the file, s/he gets these messages:
> Data Access Pages has detected that your IE security settings will not
> allow you to access data from a site considered to be insecure.
> [
> In order to access the data contained within the Data Access Page, you
> need to:
> 1. Start IE
> 2. Choose Internet Options from the Tools menu
> 3. Click on the "Security" Tab
> 4. Click on the "Trusted Sites" icon
> 5. Click on the "Sites..." button
> 6. Uncheck the "Require server verification (https) for all sites in
> the zone" checkbox
> ]
> Our technology consultant indicated that there's no way for the data to
> "bind" to the browser and it's impossible to expose these .htm, pivot
> tables to users outside of our network. Does anyone know a way to
> accomplish what I'm trying to do? I feel like it should be possible
> since Microsoft created that product to build .htm pages?
> Ryan
> Any suggestions would be very helpful
>
First off, you might have better luck asking in an Access group instead
of SQL Server. Second, let me say, YIKES!!! You allow outside access
to your file servers?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Copy

Hi,
Currently, we are running CRM application for 125 users on SQL 2000/Windows
NT. Due to some unforseen reasons, we could not perform
capacity planning before rolling the application to the connected and remote
users. As result, our current database server is running on low memory
and most important is shortage of disk space on the C drive. We decided to
replace the existing server with another robust server, taking into
current and future requirement. With regards to server replacement, couple o
f questions and require your technical expertise.
a) The current CRM users are connected to existing server using configuratio
n file. This configuration file is stored on the users client machine
and and we like to ensure the same configuration file is used by the users.
Due to this constraint, the naming convention of new machine name
will be exactly the same name as used in our current database server machine
. We like to ensure, when the existing box is replaced with new server
the users will be connected to new server.
b) Is it possible to copy the entire SQL data lying on the existing server t
o another server. Following set of data to be copied. Can you
please let us know, which tool to be used for copying the following set of d
ata.
The data to be copied to the new server will be (Master, MSDB, Pubs, CRM app
lication data i.e (Tables, Users, SP, etc)).
DTS Packages from existing to newly built server
All the security Logins
Can you please let us know, are there any other steps which is missed out fo
r copying data from server to server. We will be testing this
process on test box before rolling out to production users.
Need your technical advise on how to proceed.
Thanks
ThomasI've used the following approach: http://www.support.microsoft.com/?i..._sql_server.htm .

Data Copy

Hi,
Currently, we are running CRM application for 125 users on SQL 2000/Windows NT. Due to some unforseen reasons, we could not perform
capacity planning before rolling the application to the connected and remote users. As result, our current database server is running on low memory
and most important is shortage of disk space on the C drive. We decided to replace the existing server with another robust server, taking into
current and future requirement. With regards to server replacement, couple of questions and require your technical expertise.
a) The current CRM users are connected to existing server using configuration file. This configuration file is stored on the users client machine
and and we like to ensure the same configuration file is used by the users. Due to this constraint, the naming convention of new machine name
will be exactly the same name as used in our current database server machine. We like to ensure, when the existing box is replaced with new server
the users will be connected to new server.
b) Is it possible to copy the entire SQL data lying on the existing server to another server. Following set of data to be copied. Can you
please let us know, which tool to be used for copying the following set of data.
The data to be copied to the new server will be (Master, MSDB, Pubs, CRM application data i.e (Tables, Users, SP, etc)).
DTS Packages from existing to newly built server
All the security Logins
Can you please let us know, are there any other steps which is missed out for copying data from server to server. We will be testing this
process on test box before rolling out to production users.
Need your technical advise on how to proceed.
Thanks
Thomas
I've used the following approach: http://www.support.microsoft.com/?id=314546 . Here the master and msdb databases are not copied to the new server. Instead you transfer the logins, jobs, packages etc as outlined here. If you plan on using the old serv
er's name, you need to run sp_dropserver and sp_addserver (after renaming the server) to change @.@.servername ie:
1. sp_dropserver 'currentservername'
2. sp_addserver 'desiredservername' , 'local'
3. restart the service and check @.@.servername
I've seen description sof the approach you're asking about but have never tried it myself - it assumes that your drive set up for example is the same. Here is a link that describes this http://vyaskn.tripod.com/moving_sql_server.htm .

Data Caching in reports?

I wrote an invoice report that gets its data from an Oracle database. This is working fine except when users change something on the server (like adding a new charge to the invoice) then when they re-run the SSRS report the new line doesn't show up. They wait 15 minutes and re-run the SSRS report and then it appears.

It is like the SSRS report is caching the old data of the report.

Points to note though...

To execute the report I am using the SSRS 2005 webservice. I understand that there is 2 cachings, 1 server and 1 IIS side. I have not cached the server and don't know how to clear the IIS side. I see there is a rs:ClearSession=true parameter that you can pass in but by using the SSRS 2005 webservice this is not an option (I don't see how you can pass query strings in to the call).

Does SSRS cache the Oracle DataSet at all?

Thanks heaps for any help,

Craig

I'm having issues with caching too, but it may be a different problem than yours. Check the settings on your report server to make sure it is set to not caching.

Under the Properties tab, execution left menu there is a "Render this report with the most recent Data" radio button. Make sure the "Do not cache temporary copies of this report" radio button is checked.

Cycling Through Subreports

I have a master report which contains several subreports. My end users want these reports to cycle through for a list of clients in a loop. So, for example, given a list of three clients, the users want to see all subreports run for client 1, then all subreports run for client 2, then all subreports run for client3. (I hope this is making sense)

Currently the report uses an "in" statement so the users get subreport 1 for clients 1, 2, and 3, followed by subreport 2 for clients 1, 2, and 3, etc., which is not what they want.

I've tried placing the subreports inside a table and creating a group, but that just seems to crash the Report Designer.

Is there any to create a loop outside of the report that will call the report once for each client? Or is there some other way I can get it to run the subreports one client at a time?

Any help would be greatly appreciated.

Creating a group by client and putting the subreports inside of the group should be the right way of doing it. I'm not sure why the designer crashes when you are doing this using a table. But you can also try using a list instead.

cycles or multiple cascade paths error

Hi there.
I've been searching for this error specifically but I haven't found anything yet.

I have these two tables (USERS and REQUESTS):

USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)

where LOGIN is the primary key.

The problem comes when I try to create the "REQUESTS" table.
In these requests there's one user who types the request. After one or two days, there's other user who aproves the request. The problem is that I need two foreign keys referencing the table "USERS".

CREATE TABLE REQUESTS (
[ID] [numeric](5, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL ,
[APROVED BY] [varchar] (10) NULL
) ON [PRIMARY]
GO

ALTER TABLE REQUESTS ADD
CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE REQUESTS ADD
CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY
(
[TYPED_BY]
) REFERENCES [USERS] (
[LOGIN]
) ON UPDATE CASCADE ,
CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY
(
[APROVED_BY]
) REFERENCES [USERS] (
[LOGIN]
) ON UPDATE CASCADE

And SQL returns:

Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table 'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Ok, after that, I tried creating a new table to store aprovals (Table with two fields: "REQUEST_ID" and "APROVED_BY").
So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.
The same error comes up.

I don't think this structure goes into "cycles" or "multiple cascades".

How can I do this?
Thanks in advance
Regards

RolandYou can't have multiple dependency paths for CASCADE UPDATE/DELETE operations. If you want to enforce referential integrity between these tables, one of the relationships will need to be enforced with a trigger.|||Blindman, thanks for your reply.
I was afraid that I had to mess with triggers to solve this.
Oh man, I'll have to do it (I've never done triggers before).

Best Regards|||They aren't very difficult. The code you will need is very simple. Just read the sections in Books Online about the virtual "inserted" and "deleted" tables. The only other thing you need to make sure of is that your trigger works for transactions involving multiple records. Many people make the mistake of assuming the only one record will be inserted, deleted, or updated at a time, and this is what leads to problems.
If you run into anything you don't understand, post another thread on this forum.

Cutting and Pasting from PDF output

Hi

We have some reports that are published as PDF files. The layout of the report is fairly free, using lists within lists.

Some of our users want to cut and paste some of the data. When they do so, the order and layout of the fields does not resemble the layout of the report. Any way around this.

Note that when the report is published as mhtml or excel, cutting and pasting from these formats is much better.

thanks

Peter

No, there is no way around that. If the user is going to reuse the figues in another format then it is best to stick to Excel output. If only specific users need this then you could set them up with an subscription that has Excel output, and leave everyone else with the PDF version.