End User can access entire data without any cube roles

Hi Experts,

I have a report model on top of a cube. I had set a role for an end user to view some specific data which was not working, i then deleted the role from the cube. The end user can still see the entire set of data. Have anyone faced this scenario. The security doesnt seem to work at all.

Please advise.

Regards

Neeraj

Hi Neeraj,

Do you have Kerberos authentication set up? If not, and if Analysis Services is not running on the Report Server, the Report Server is likely using a fixed ID (not the end-user ID) to access Analysis Services - you could confirm the ID by tracing the login to Analysis Services in SQL Profiler.

Encryption of Credit Card files

G/Day Forum,
We are working on complying with the Visa/MAsterCard Payment Card Industry
Data Security Standard (PCI DSS). As part of this we need to imply the
following controls on the storage of credit card data:
to encrypt data at a folder level - that is all of the containing folders
and files
to allow for split knowledge of encryption keys and management thereof
to allow for strong encryption support (algorithms like 3DES, AES, etc)
a mechanism for automating the encryption process on a daily basis - this is
coincide with a backup cycle (no clear text credit card files get backed up
onto tape)
We are looking for a File/Folder encryption solution for a Windows 2000
based file server (member of a Windows 2000 Domain) and a Windows 2003 based
FTP Server (Standalone system), that will be used for storing Credit Card
information.
Your thoughts on any products that suit my requirements?
Regards,
Steve."The Poster" <nospam@.nospam_dontyoudare.net> wrote in message
news:eYqW%23rpGGHA.1396@.TK2MSFTNGP11.phx.gbl...
> G/Day Forum,
> We are working on complying with the Visa/MAsterCard Payment Card Industry
> Data Security Standard (PCI DSS). As part of this we need to imply the
> following controls on the storage of credit card data:
> to encrypt data at a folder level - that is all of the containing folders
> and files
> to allow for split knowledge of encryption keys and management thereof
I don't follow. Do you mean so that no single person can decrypt the credit
card information alone? Is that part of the PCI DSS requirements?

> to allow for strong encryption support (algorithms like 3DES, AES, etc)
> a mechanism for automating the encryption process on a daily basis - this
is
> coincide with a backup cycle (no clear text credit card files get backed
up
> onto tape)
Why would you not always encrypt the credit card numbers immediately instead
of on a schedule? I would think this would be highly preferable.

> We are looking for a File/Folder encryption solution for a Windows 2000
> based file server (member of a Windows 2000 Domain) and a Windows 2003
based
> FTP Server (Standalone system), that will be used for storing Credit Card
> information.
Windows EFS will do this. Make sure however that you 1) configure EFS
securely according to best practices and 2) you MUST back up your encryption
keys. You can meet the requirement if necessary of no single person being
able to decrypt by encrypting using an account where two people each know
half of the password. If you want other combinations of people to be able
to decrypt the data, you could encrypt the data in different ways using
different accounts where different people share the password. PGP, GPG and
www.jetico.com are some other popular low-cost encryption programs that work
similarly and may or may not meet your needs.
Or you could have a developer program a custom solution that handles the
data encryption / decryption and has a front-end that manages user
authentication to see the decrypted data.|||You plan on storing this in files ? as opposed to in database ? and,
more surprising to me, on a machine that has FTP active ' !?
Your interpretation of the guidance does not sound right when you say
> a mechanism for automating the encryption process on a daily basis - this
> is
> coincide with a backup cycle (no clear text credit card files get backed
> up
> onto tape)
I thought it says never stored (anywhere) in the clear
"The Poster" <nospam@.nospam_dontyoudare.net> wrote in message
news:eYqW%23rpGGHA.1396@.TK2MSFTNGP11.phx.gbl...
> G/Day Forum,
> We are working on complying with the Visa/MAsterCard Payment Card Industry
> Data Security Standard (PCI DSS). As part of this we need to imply the
> following controls on the storage of credit card data:
> to encrypt data at a folder level - that is all of the containing folders
> and files
> to allow for split knowledge of encryption keys and management thereof
> to allow for strong encryption support (algorithms like 3DES, AES, etc)
> a mechanism for automating the encryption process on a daily basis - this
> is
> coincide with a backup cycle (no clear text credit card files get backed
> up
> onto tape)
> We are looking for a File/Folder encryption solution for a Windows 2000
> based file server (member of a Windows 2000 Domain) and a Windows 2003
> based
> FTP Server (Standalone system), that will be used for storing Credit Card
> information.
> Your thoughts on any products that suit my requirements?
> Regards,
> Steve.
>
>|||I'll echo Roger's comments. Aside from asking for product advice, it maybe
worthwhile to review your architecture/goals.
Storing credit card information implies that it will be retrieved for future
use. Aside from normal retail operations like allowing customers to "save"
payment information for a quicker checkout process on a subsequent sale,
either by themselves online, or via telephone with a rep, the only other
probable use is for some data mining - but I don't think you need the entire
number to run reports based on credit cards.
On a large scale, say you have multiple "local" locations that run their own
localized sales/ops and then "batch" data into a central location (my guess
for your FTP need), the question still remains, what is the purpose for
including credit card information in such a batching process? I'll assume
this is just to allow the scenario I mentioned - allowing customers
easier/faster experience on a subsequent sale, they may have bought an item
from Store A in CA, but can still have the same ease if they ordered through
your web site or call center in NY or anywhere. In this case, the question
which Roger already asked is, why FTP instead of a synchronized database? If
you are at this scale of operations, then it would only be fitting to have
the proper architecture for it.
Cheers,
Ed
"The Poster" wrote:

> G/Day Forum,
> We are working on complying with the Visa/MAsterCard Payment Card Industry
> Data Security Standard (PCI DSS). As part of this we need to imply the
> following controls on the storage of credit card data:
> to encrypt data at a folder level - that is all of the containing folders
> and files
> to allow for split knowledge of encryption keys and management thereof
> to allow for strong encryption support (algorithms like 3DES, AES, etc)
> a mechanism for automating the encryption process on a daily basis - this
is
> coincide with a backup cycle (no clear text credit card files get backed u
p
> onto tape)
> We are looking for a File/Folder encryption solution for a Windows 2000
> based file server (member of a Windows 2000 Domain) and a Windows 2003 bas
ed
> FTP Server (Standalone system), that will be used for storing Credit Card
> information.
> Your thoughts on any products that suit my requirements?
> Regards,
> Steve.
>
>

Encryption in MDF file possible?

I am working on a distributed application that will use a SQL Express 2005 MDF file for the primary application data storage. The program will be storing sensitive data and I would like to encrypt the data in it.

I have searched through the forums and cannot find any reference to how to enable encryption for an MDF file.

I thought about writing my own encrypt / decrypt functions, however, I'm using databound controls and do not have a home-grown data access layer that I can tap into to implement the encryption.

When I originally added the MDF file to my application, under the "Advanced" settings, I saw that I could switch an "Encryption" property to "True"... figuring that would do the trick. However, when the MDF file is created I get an error stating, "The instance of SQL Server you attempted to connect to does not support encryption".

What is the trick for encrypting data in an MDF file that is being used with databound controls? Remember that this will be a distributed app - so if there are any customizations that are required on the SQL Express side to support this - I will need to find some way to configure SQL Express on the target machines during my bootstrap install of it.

Any help would be greatly appreciated! Thanks!

I think you actually want to encrypt the data and not the file. Here is a link to a starting point:

http://msdn2.microsoft.com/en-us/library/ms190357.aspx

|||

Yes - that is what I meant... encrypting *data* in the MDF file.

As I wrote in my original post, I'm using data-bound controls, so I do not have a data layer that have created to pass data through - it is all being handled by the magic of Microsoft. That being said, I'm not sure how to implement the information contained in the link you provided. That seemed in line with me writing my own encryption functions and passing the data through them during read / writing to the database - which isn't an option for me using databound controls. Or is it?

|||

I've never tried using the the SQL Server 2005 encryption with databound controls but it should work as long as you can edit the query. You basically extend the query to include encryption and decryption information.

The pointer I sent you is just one of several BTW

Encryption for SSAS

I wonder if someone could give some pointers here.

I do have to offer cubes through a firewall to the outside for colleagues working at clients' locations. Therefore, I chose to use the HTML feature of SSAS, I use IIS to host the msmdpump.dll and secure the access through Windows User Accounts with password. There is no encryption on this channel.

I know how to set up certificates in Windows2003 and IIS to protect Websites and access them through HTTPS (SSL). I would really want to use the same for my SSAS, but whenever I shift to SSL on Port 443 I cannot connect with my Excel to the SSAS. A web page in the same folder as the msmdpump.dll is still accessible, so the certificate is installed and distributed to the client correctly, and the IIS setup should be fine as well.

Can anybody tell me if that IS possible at all and I am missing anything to configure, or if SSAS through IIS + SSL is not possible at all?

In a related topic, I read that SSAS does support encryption in native, I looked up the property in my standard version and it is enabled. However, our Sysadmin was able to read quite clear text when we communicate internally with SSAS on its native 2386 port.

I have not looked into that since the solution above would be far better for me, but I wonder if there is an explanation of the behaviour.

For the record: That is Win2003 Server with SQL 2005 and SP1 installed. German language version of both OS and SQL, and SQL is the standard Edition.

I read that SSAS does support encryption in native, I looked up the property in my standard version and it is enabled. However, our Sysadmin was able to read quite clear text when we communicate internally with SSAS on its native 2386 port.

I doubt your sysadmin was able to read clear text in the default installation. All communications are encrypted by default. See this blog post for more details: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/02/as2005_protocol.aspx

Can anybody tell me if that IS possible at all and I am missing anything to configure, or if SSAS through IIS + SSL is not possible at all?

Yes it is possible - I saw it working before. Of course, troubleshooting such issues could be very difficult, because they depend a lot on the environment setup :(

HTH,

Mosha (http://www.mosha.com/msolap)

Encrypting data within the DB

Hello,
I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have
been working with SQL Server, Building ASp WebSites for many years now.
I am by no means an expert - nor have I had ANY formal training. So ebar
with me if my questions seem elementary...

I have some questions regarding sensitive data and encryption.

There is a project that is headed my way were the social security number
is being used as the unique identifier for an account. I have always
used as identity column as a unique identifier. What would be the pros
and cons of using the SSN as a unique identifier?

#1 How do I go about encrypting the number to store in the DB. Is this
done within SQL Server? Or before the data is inserted?

#2 Is it possible to use an encrypted field as a unique identifier?

My gut tells me to use the identity column , encrypt the SSN and not use
it as any part of an identifier.

Thank You for your Help.. Happy New Year!

Please Reply to the Newsgroup.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!It is not common but it is possible for a Social Security number to change
given time. It should certainly be a way for people to quickly find
individuals however I dont think it it should be a primary key --

As far as encryption goes check out the source code for the forums that are
available at www.webwizguide.com -- there is a very nice encryption system
built into the software that I have used many a time and I cant knock it --

"kimi" <kim@.kimmyXSPAMX.com> wrote in message
news:3ffd9826$0$70302$75868355@.news.frii.net...
> Hello,
> I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have
> been working with SQL Server, Building ASp WebSites for many years now.
> I am by no means an expert - nor have I had ANY formal training. So ebar
> with me if my questions seem elementary...
> I have some questions regarding sensitive data and encryption.
> There is a project that is headed my way were the social security number
> is being used as the unique identifier for an account. I have always
> used as identity column as a unique identifier. What would be the pros
> and cons of using the SSN as a unique identifier?
> #1 How do I go about encrypting the number to store in the DB. Is this
> done within SQL Server? Or before the data is inserted?
> #2 Is it possible to use an encrypted field as a unique identifier?
> My gut tells me to use the identity column , encrypt the SSN and not use
> it as any part of an identifier.
> Thank You for your Help.. Happy New Year!
> Please Reply to the Newsgroup.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||"kimi" <kim@.kimmyXSPAMX.com> wrote in message
news:3ffd9826$0$70302$75868355@.news.frii.net...
> Hello,
> I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have
> been working with SQL Server, Building ASp WebSites for many years now.
> I am by no means an expert - nor have I had ANY formal training. So ebar
> with me if my questions seem elementary...
> I have some questions regarding sensitive data and encryption.
> There is a project that is headed my way were the social security number
> is being used as the unique identifier for an account. I have always
> used as identity column as a unique identifier. What would be the pros
> and cons of using the SSN as a unique identifier?
> #1 How do I go about encrypting the number to store in the DB. Is this
> done within SQL Server? Or before the data is inserted?
> #2 Is it possible to use an encrypted field as a unique identifier?
> My gut tells me to use the identity column , encrypt the SSN and not use
> it as any part of an identifier.
> Thank You for your Help.. Happy New Year!
> Please Reply to the Newsgroup.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

1. There is no built-in encryption mechanism in MSSQL, but there are a
number of third-party products available:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Alternatively, use the Windows CrpytoAPI from your front end application to
encrpyt/decrypt as you access the data, so the database only ever stores the
encrypted value.

2. Any column which has guaranteed unique values could be a key, but your
encryption solution might produce something unsuitable, such as a very long
value which would be awkward to work with.

Personally, I would go with your proposed solution, and avoid the SSN as a
key. For example, if your solution ever goes international, then basing
everything on an SSN will suddenly become an issue. An artificial key of
some sort is a useful solution. But ultimately you have to decide based on
your requirements.

Simon|||>As far as encryption goes check out the source code for >the forums
that are
>available at www.webwizguide.com -- there is a very nice >encryption
system
>built into the software that I have used many a time and I >cant knock
it --

Thank you for the link above. I will check this out first chance I get.
I am somewhat familiar with this forum. Thanks.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>but there are a number of third-party products available:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Have you used any of the third party software to encrypt?

>Alternatively, use the Windows CrpytoAPI from your front >end
application to
>encrpyt/decrypt as you access the data, so the database >only ever
stores the
>encrypted value.

Well this is what I want - but this brings up antoher concern. If the
value stored is encrypted - Would this mean I could never search by SSN?

>Personally, I would go with your proposed solution, and >avoid the SSN
as a
>key. For example, if your solution ever goes >international, then
basing
>everything on an SSN will suddenly become an issue. An >artificial key
of
>some sort is a useful solution. But ultimately you have to >decide
based on your requirements.

I agree - I think I am going to push for this - and your point about
international possibilities will be my defense.

Thank you for your help.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Nah it just means you would have to decript the SSN field as part of the
where clause -- probably not that fast as this would lead to indexes being
thrown off but hey --

What I am not quite sure of is why the SSN has to be encrypted in the first
place? Unless somebody is going to have access to the server that should
not I just dont get it -- maybe I am not as cautious as some however

"kimi" <kim@.kimmyXSPAMX.com> wrote in message
news:3ffdbb52$0$70306$75868355@.news.frii.net...
> >but there are a number of third-party products available:
> >http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
> Have you used any of the third party software to encrypt?
>
> >Alternatively, use the Windows CrpytoAPI from your front >end
> application to
> >encrpyt/decrypt as you access the data, so the database >only ever
> stores the
> >encrypted value.
> Well this is what I want - but this brings up antoher concern. If the
> value stored is encrypted - Would this mean I could never search by SSN?
>
> >Personally, I would go with your proposed solution, and >avoid the SSN
> as a
> >key. For example, if your solution ever goes >international, then
> basing
> >everything on an SSN will suddenly become an issue. An >artificial key
> of
> >some sort is a useful solution. But ultimately you have to >decide
> based on your requirements.
> I agree - I think I am going to push for this - and your point about
> international possibilities will be my defense.
> Thank you for your help.
>
> Please Reply to the Newsgroups..
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||because customers are more willing to give you their SSN if they know that
it is secure.

"J00 Moo" <notreallyarealemailaddress@.hotmail.com> wrote in message
news:btkn5l$8fvta$1@.ID-68406.news.uni-berlin.de...
> Nah it just means you would have to decript the SSN field as part of the
> where clause -- probably not that fast as this would lead to indexes being
> thrown off but hey --
> What I am not quite sure of is why the SSN has to be encrypted in the
first
> place? Unless somebody is going to have access to the server that should
> not I just dont get it -- maybe I am not as cautious as some however
> "kimi" <kim@.kimmyXSPAMX.com> wrote in message
> news:3ffdbb52$0$70306$75868355@.news.frii.net...
> > >but there are a number of third-party products available:
> > >http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
> > Have you used any of the third party software to encrypt?
> > >Alternatively, use the Windows CrpytoAPI from your front >end
> > application to
> > >encrpyt/decrypt as you access the data, so the database >only ever
> > stores the
> > >encrypted value.
> > Well this is what I want - but this brings up antoher concern. If the
> > value stored is encrypted - Would this mean I could never search by SSN?
> > >Personally, I would go with your proposed solution, and >avoid the SSN
> > as a
> > >key. For example, if your solution ever goes >international, then
> > basing
> > >everything on an SSN will suddenly become an issue. An >artificial key
> > of
> > >some sort is a useful solution. But ultimately you have to >decide
> > based on your requirements.
> > I agree - I think I am going to push for this - and your point about
> > international possibilities will be my defense.
> > Thank you for your help.
> > Please Reply to the Newsgroups..
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||kimi (kim@.kimmyXSPAMX.com) writes:
> Well this is what I want - but this brings up antoher concern. If the
> value stored is encrypted - Would this mean I could never search by SSN?

Searching for a single value should not be much of a problem. You encrypt
the input and then look up the encrypted value. Of course, you must use
an encryption method which always gives the same encrypted value for the
same input.

Search for a range, like all SSN that start on 500 would be more
problematic.

Going back to your original post:
>What would be the pros and cons of using the SSN as a unique identifier?

The pro would be that you would get a tap on your shoulder by Joe Celko.

But as noted by others, SSN is a typical example of something that looks
like a natural key, but when you look closer to it, does not live up to
the strict requirements for a primary key in a relational database. A
fact which is true many natural look-a-like keys.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>But as noted by others, SSN is a typical example of >something that
looks like a natural key, but when you look >closer to it, does not live
up to the strict requirements >for a primary key in a relational
database. A
>fact which is true many natural look-a-like keys.

Would you consider a phone number to be a good choice for a primary key?

As I understand the data that is to be stored right now.
The only options I have are ssn, phone number, or identity column.

I am so leaning toward the identity.
Thank you for your replies

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Upon further ingestion of coffee I realize that a phone number as a
primary key would be a BAD Move...

I am sticking with the identity column - i see it as my only option
right now.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"kimi" <kim@.kimmyXSPAMX.com> wrote in message
news:3ffebf76$0$70303$75868355@.news.frii.net...
> Upon further ingestion of coffee I realize that a phone number as a
> primary key would be a BAD Move...
> I am sticking with the identity column - i see it as my only option
> right now.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

The identity column is probably the best approach. The fundamental issue as
I see it is that there is no such thing as a natural key for human beings,
or at least no key that is practical in relational database terms. In some
specific contexts, it might be possible to use something like an email
address, but even then a surrogate key might be required purely for
performance.

You could try to make a composite key out of numerous fields (names, phone
numbers, addresses etc), but it would be completely impractical, even if you
could be sure that everyone in your database will always have an email
address and a phone. I suppose you might theoretically be able to produce a
unique hash value from someone's DNA, but even if it could be made small
enough to work in a practical implementation, you'd still have a problem
when you get your first set of identical twin customers/employees/whatever.

SSNs, passport numbers, employee IDs etc. are essentially artificial keys at
the end of the day, so there's no reason to be reluctant to use one
yourself, at least in my view.

Simon|||>> The only options I have are ssn, phone number, or identity column.
<<

Go down the list and ask yourself which of the three can be verified
in the reality you are modeling. I can call someone and ask for his
phone number. I can call the government and verify his SSN. IDENTITY
by definition is never a key -- just put the same data into the table
1000 times on a table that uses such a pseudo-key and trash your data
integrity.

What is the nature of the application? Do you require the phone
number or can someone qualify for membership in the set without one?
Probably. Do you require the SSN or can someone qualify for membership
without one? Probably not. If I hire a foreign national, I have to
get a tax number for him in a business environment. So you allow
sloppy SSNs only if you want to go to jail under INS, IRS and Homeland
Security laws. You not only have to have it on file as a matter of
law, you have to verify it as a matter of law.

The bad news is that simple verification is a pain with SSN. Buy a
package or find some freeware to save yourself the pain.|||Joe,

> I can call someone and ask for his phone number.

And don't forget to ask "Is this the party to whom I am speaking?"

Linda

Encrypting confidential data including foreign keys

Hi,
I am currently working on a project for a client. The project is to create
an intranet site to maintain confidential employee data. There is a master
employee table, containing name, date of birth etc. However, the table also
links to some look-up tables, such as grades, ethnicity, citizenship etc.
I was wondering what was the best solution for encrypting the data in SQL
Server 2000, so that someone with database access cannot read the
confidential data. Encrypting the free text data is fairly simple, but I am
not sure how to encrypt the foreign keys while preserving referential
integrity.
For example, if an employee has citizenship = 5, it is quite easy to workout
the citizenship from the look-up table. Even if 5 is encrypted to ABC, it is
quite easy to see the pattern on the table.
Also, the client wants to do some reporting on the tables, so run queries
with filters (such as Citizenship=UK) on encrypted data.
We looked at xpcrypt, but it appears to create automatically a view
containing the decrypted data for the duration of the SQL session, so I am
not sure how appropriate it is for a web application, maintaining pretty
much a constant connection/session to the DB.
Any advice welcome!
Thanks,
TomOn Fri, 28 Oct 2005 21:29:31 +0100, "Tom" <Tom@.nospam.com> wrote:

> but I am
>not sure how to encrypt the foreign keys while preserving referential
>integrity.
>
Hmm, I doubt you'll find any encryption product that can encrypt a
foreign key column.
Scott
http://www.OdeToCode.com/blogs/scott/|||Hi,
well try our software for SQL server side encryption for that , visit and
refer :
http://database-encryption.com/ it will serve your purpose
Regards
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hi,
> I am currently working on a project for a client. The project is to create
> an intranet site to maintain confidential employee data. There is a master
> employee table, containing name, date of birth etc. However, the table als
o
> links to some look-up tables, such as grades, ethnicity, citizenship etc.
> I was wondering what was the best solution for encrypting the data in SQL
> Server 2000, so that someone with database access cannot read the
> confidential data. Encrypting the free text data is fairly simple, but I a
m
> not sure how to encrypt the foreign keys while preserving referential
> integrity.
> For example, if an employee has citizenship = 5, it is quite easy to worko
ut
> the citizenship from the look-up table. Even if 5 is encrypted to ABC, it
is
> quite easy to see the pattern on the table.
> Also, the client wants to do some reporting on the tables, so run queries
> with filters (such as Citizenship=UK) on encrypted data.
> We looked at xpcrypt, but it appears to create automatically a view
> containing the decrypted data for the duration of the SQL session, so I am
> not sure how appropriate it is for a web application, maintaining pretty
> much a constant connection/session to the DB.
> Any advice welcome!
> Thanks,
> Tom
>
>|||http://www.sqlservercentral.com/col...oolkitpart1.asp
You can't really encrypt foreign key columns to any useful degree, since
they'll have to be encrypted on the referenced table as well and once again
you've got an easily visible link... What you can do is encrypt the
descripition data in the referenced table to make it unreadable. So instead
of storing "NATIVE AMERICAN" in plain text on the ethnicity table, you could
encrypt that description and store it.
"Tom" <Tom@.nospam.com> wrote in message
news:raydnZJ8BOyXF__eRVnyjw@.pipex.net...
> Hi,
> I am currently working on a project for a client. The project is to create
> an intranet site to maintain confidential employee data. There is a master
> employee table, containing name, date of birth etc. However, the table
> also links to some look-up tables, such as grades, ethnicity, citizenship
> etc.
> I was wondering what was the best solution for encrypting the data in SQL
> Server 2000, so that someone with database access cannot read the
> confidential data. Encrypting the free text data is fairly simple, but I
> am not sure how to encrypt the foreign keys while preserving referential
> integrity.
> For example, if an employee has citizenship = 5, it is quite easy to
> workout the citizenship from the look-up table. Even if 5 is encrypted to
> ABC, it is quite easy to see the pattern on the table.
> Also, the client wants to do some reporting on the tables, so run queries
> with filters (such as Citizenship=UK) on encrypted data.
> We looked at xpcrypt, but it appears to create automatically a view
> containing the decrypted data for the duration of the SQL session, so I am
> not sure how appropriate it is for a web application, maintaining pretty
> much a constant connection/session to the DB.
> Any advice welcome!
> Thanks,
> Tom
>|||you can use built in database security to accomplish your goal|||What SQL 2000 function is that?
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1147967032.280063.193550@.u72g2000cwu.googlegroups.com...
> you can use built in database security to accomplish your goal
>

Emps working Hrs. <CrossTab> ..

hello all

i'm really confused .. i use VS 2005, VB.NET, Crystal Reports and SQL Server Studio Managment as my DB..

My problem is i dont know where do i have to implement this report i want to produce .. its working hours for all employees showing by month ...

ex:

Jan-07 Feb-07 March-07 ......HrsSUM

joe 54 50 88 sum(hrs)

Sam 20 20 50 sum(hrs)

note : it doesnt always starts at jan-07 ... we have a "cut off date" which is the calculations start date till the end of the project (2010)

so its a crosstab query ... should i do that using crystal report , writing a function there to generate the months and create a crosstable ?(no idea how to do that).... or writing a VB code and pass it to the report ?

or just create a stored procedure in the DB to do that ?

i think the last one is wt should do it ... but i still dont know how

any help plz ,,,,,,,,

I would create a query to sum all the hours for each employee by month.

Then use crystal reports to generate the crosstab report and summation.