encrypt sensitive data

How do you encrypt sensitive data that may be stored in a database such as
passwords, creditcard numbers,etc.. ?
3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
Any info that you can provide would be valuable. Using SQL 2000Hi
Do it at application level using the Windows API's of if you use .NET, use
the Crypto classes.
SQL server 2005 will support encryption at row level.
Regards
Mike
"Hassan" wrote:

> How do you encrypt sensitive data that may be stored in a database such as
> passwords, creditcard numbers,etc.. ?
> 3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
> Any info that you can provide would be valuable. Using SQL 2000
>
>|||Hassan,
If you use passwords for user authentication purposes only (i.e. you do not
need to know their plaintext values, but want to make sure that the user
logging on submits the right password) use hashing instead of encryption.
This way you don't have to deal with key protection.
For values that need to be decrypted (e.g. SSN or credit card number), use
symmetric key encryption, such as Rijndael algorithm, with strong keys.
Alternatively, you can use public-private keys, but the performance can be
bad.
Whether you use hashing or encryption, implement it in C#, C/C++, VB or
whatever your application is written in, not in SQL Server (T-SQL).
Although, I have to say that there are tools which allow encryption directly
in the database (such as nCipher: http://www.ncipher.com/dbe/, netlib:
http://www.netlib.com/, etc), I do not have much experience with them, so I
cannot comment.
SQL Server 2000 (and earlier) has very limited cryptographic features, so
they are of little use. As Mike mentioned, the next version of SQL Server
offers much better cryptographic features, but the main problem I see is
that if you decrypt a value on the database side and pass it to the
application which resides on a different machine (which is a typical case in
most enterprise environments), a simple network sniffer will reveal all your
secrets. That is unless you use SSL between the application server and the
database server, which you can do, but it would require more administrative
hassles and cause performance degradation since all payloads will have to be
encrypted and decrypted.
A couple of notes. If you use encryption in application (not T-SQL), you may
need to store the same value twice: one hashed (without salt), the other
encrypted. Let me illustrate. Say you store SSNs encrypted. Normally, you
would need to use an initialization vector (IV) or a pseudo-IV with
encryption, so depending on the IV value the result of the encryption can be
different. In this case, how do you perform a search for a record with a par
ticular SSN? Unless you use the same IV for all records (which kinda defeats
the purpose), you cannot just encrypt the submitted value and run a select
query (SELECT * FROM ... WHERE SSN = <encrypted value> ), because you do not
know which IV to use to generate the encrypted result. In this case, when
storing SSN, you may need to keep two values: a hash of the SSN and an
encrypted value, so in your SELECT statement you can use the hash. (Note:
You can generate hash using SQL Server's PWDENCRYPT function, but then again
you will be passing the value in plain text over the network.)
Finally, when you use encryption, you have to protect the key (or a
passphrase - and other characteristics - from which the key is derived).
Basically, your application "owns" the key, so when the application is
installed on a server, you - or a server admin - must "install" the key in
some way and store it in a secure manner so that the application can
retrieve it and use for encryption and decryption (the idea here is to allow
different key to be used in different environments - development, test,
production - and have a person responsible for the key access: i.e.
developers should not know which key is used in production).
Unfortunately, there are no totally secure ways of doing this and better or
worse options depend on the type of your application. You can use third
party tools (e.g. CipherSafe: http://www.obviex.com/ciphersafe) or implement
your own protection mechanism using DPAPI (which is the best option,
although with some limitations, partially in the context of ASP.NET apps),
or something else. If you want to learn more about this topic, check this
article: "Protect It: Safeguard Database Connection Strings and Other
Sensitive Settings in Your Code" at
[url]http://msdn.microsoft.com/msdnmag/issues/03/11/ProtectYourData/default.aspx.;[/url
]
it covers several related aspects.
And, yes, whichever method you choose, expect encryption and decryption to
cause performance degradation, but depending on the method you choose it can
be anything from negligible to non-acceptable.
Good luck,
Alek
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OJsFSrEMFHA.3076@.TK2MSFTNGP14.phx.gbl...
> How do you encrypt sensitive data that may be stored in a database such as
> passwords, creditcard numbers,etc.. ?
> 3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
> Any info that you can provide would be valuable. Using SQL 2000
>

Empty Report manager page

When I go to http://localhost/Reports I see the page (subscription,
home, ...) but I don't see any tools nor reports.
I have anonymous authentication set to off in IIS for that directory,
and only have integrated authentication turned on. I logged in with
administrator account.
Is there any other way of setting users and roles without using the
'manage reports' page? That way I could make sure administrators has
the correct role.
Thanks in advance,
Stijn Verrept.Are you an administrator on the actual server machine?
Chris
Stijn Verrept wrote:
> When I go to http://localhost/Reports I see the page (subscription,
> home, ...) but I don't see any tools nor reports.
> I have anonymous authentication set to off in IIS for that directory,
> and only have integrated authentication turned on. I logged in with
> administrator account.
> Is there any other way of setting users and roles without using the
> 'manage reports' page? That way I could make sure administrators has
> the correct role.
>
> Thanks in advance,
> Stijn Verrept.|||Chris McGuigan wrote:
> Are you an administrator on the actual server machine?
> Chris
Thanks for the reply, I'm getting desperate :). Yes I am, the server
is a domain controller BTW. I had access earlier but now I don't have
access anymore for some reason, maybe I set something incorrect?|||It does sound like the RS server doesn't recognise you as an
administrator.
I have to say that I did a recent installation on a domain controller
and it went terribly. The main problem seemed to be that the
installation couldn't create the ASPNet user which is essential.
I had to set ASPNet up by hand and had to raise it to administrator
level.
Having said that, your issue seems different. Is it possible that you
changed the role of the administrator in RS so that it didn't have full
rights anymore? If thats a possibility then you will probably have to
reinstall.
Have you had reports running on this installation. If not and you are
trying to set it up for the first time, I would strongly advise you NOT
to put it on a domain controller.
Chris
Stijn Verrept wrote:
> Chris McGuigan wrote:
> > Are you an administrator on the actual server machine?
> >
> > Chris
> Thanks for the reply, I'm getting desperate :). Yes I am, the server
> is a domain controller BTW. I had access earlier but now I don't have
> access anymore for some reason, maybe I set something incorrect?|||Try this url;
http://SERVERNAME/Reports/Pages/SystemSecurity.aspx
If you get access denied, this will prove whether or not you are an
administrator as far as RS is concerned.
Chris
Chris McGuigan wrote:
> It does sound like the RS server doesn't recognise you as an
> administrator.
> I have to say that I did a recent installation on a domain controller
> and it went terribly. The main problem seemed to be that the
> installation couldn't create the ASPNet user which is essential.
> I had to set ASPNet up by hand and had to raise it to administrator
> level.
> Having said that, your issue seems different. Is it possible that you
> changed the role of the administrator in RS so that it didn't have
> full rights anymore? If thats a possibility then you will probably
> have to reinstall.
> Have you had reports running on this installation. If not and you are
> trying to set it up for the first time, I would strongly advise you
> NOT to put it on a domain controller.
> Chris
>
> Stijn Verrept wrote:
> > Chris McGuigan wrote:
> >
> > > Are you an administrator on the actual server machine?
> > >
> > > Chris
> >
> > Thanks for the reply, I'm getting desperate :). Yes I am, the
> > server is a domain controller BTW. I had access earlier but now I
> > don't have access anymore for some reason, maybe I set something
> > incorrect?|||One other thing to consider. Did you enable anonymous access on your
webserver? If anonymous access is enabled then all users are the same (they
are anonymous) and RS does not know you are the administrator. Disable
anonymous access.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:OmiivhMpFHA.272@.TK2MSFTNGP15.phx.gbl...
> It does sound like the RS server doesn't recognise you as an
> administrator.
> I have to say that I did a recent installation on a domain controller
> and it went terribly. The main problem seemed to be that the
> installation couldn't create the ASPNet user which is essential.
> I had to set ASPNet up by hand and had to raise it to administrator
> level.
> Having said that, your issue seems different. Is it possible that you
> changed the role of the administrator in RS so that it didn't have full
> rights anymore? If thats a possibility then you will probably have to
> reinstall.
> Have you had reports running on this installation. If not and you are
> trying to set it up for the first time, I would strongly advise you NOT
> to put it on a domain controller.
> Chris
>
> Stijn Verrept wrote:
>> Chris McGuigan wrote:
>> > Are you an administrator on the actual server machine?
>> >
>> > Chris
>> Thanks for the reply, I'm getting desperate :). Yes I am, the server
>> is a domain controller BTW. I had access earlier but now I don't have
>> access anymore for some reason, maybe I set something incorrect?
>|||I get "You do not have permission to access this page." and I'm logged
in as administrator.|||Bruce L-C [MVP] wrote:
> One other thing to consider. Did you enable anonymous access on your
> webserver? If anonymous access is enabled then all users are the same
> (they are anonymous) and RS does not know you are the administrator.
> Disable anonymous access.
No I only have integrated authentication turned on (mentionned this in
original mail), however, the browser doesn't popup the login screen. I
immediatly get: "You do not have permission to access this page." when
trying to access the page Chris gave me.|||I went back and looked at your original message. You only mentioned the
Reports website, what about Reportserver web. The Reports (report manager)
web is a portal into the reportserver. You need anonymous off for both.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:OMadnSiPYfnzbJjeRVnytA@.scarlet.biz...
> Bruce L-C [MVP] wrote:
>> One other thing to consider. Did you enable anonymous access on your
>> webserver? If anonymous access is enabled then all users are the same
>> (they are anonymous) and RS does not know you are the administrator.
>> Disable anonymous access.
> No I only have integrated authentication turned on (mentionned this in
> original mail), however, the browser doesn't popup the login screen. I
> immediatly get: "You do not have permission to access this page." when
> trying to access the page Chris gave me.|||Bruce L-C [MVP] wrote:
> I went back and looked at your original message. You only mentioned
> the Reports website, what about Reportserver web. The Reports (report
> manager) web is a portal into the reportserver. You need anonymous
> off for both.
YES!!! Good job mate! That did it, nice observing :). I put it to
anonymous so that anyone can look at report.
Thanks a lot!
Stijn Verrept.