End User Filter Option

I am using RS 2005. Here is my questionâ?¦ I have two different date fields
(FileDate and TransDate) in my query and I want to give end user an option to
filter either one i.e on filedate or on Transdate and also a date range in
where clause. Is this possible? If so. How can I implement this
functionality?manipulate the information on the datasource side.
your datasource must be a stored proc created in such a way that if
filedate is null then use transdate.
eg.
create proc mydataset @.filedate datetime = null, @.transdate datetime =null
as
if @.filedate is null
begin
select fields from table where transdate = @.transdate
end
else
begin
select fields from table where filedate = @.filedate
end
Reddy wrote:
> I am using RS 2005. Here is my question... I have two different date fields
> (FileDate and TransDate) in my query and I want to give end user an option to
> filter either one i.e on filedate or on Transdate and also a date range in
> where clause. Is this possible? If so. How can I implement this
> functionality?

encryption optimization

Hi,
I have an application that requires the storing of personal data, name,
address, ssn with a requirement that key fields, first name, last name,
address1, city, zip, ssn be encrypted. Currently, there are over 100,000
records, and growing fast. Searches need to be done based on these key
fields (lastname like 'A%' for instance). I am loading the return set into
SqlDataSource and GridView using .net version 2.0.
I'm using a stored procedure to return the data.
When the page first loads (which filters on lastname = 'A%', it loads
slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it times
out.
Is there a best practice to follow in a case like this that would result in
the best performance possible. I realize with all the encryption that it is
very processing intensive, but with a first load at least usable, but the
second load timing out, there may be some things I should do (clear buffers
or something) that I do not know about doing.
Can you help on this?
Thanks.The bottom line is that you can't efficiently search (use indexes) on these
encrypted key columns because you are searching using the decrypted value
and this value is not stored in the database. During the search, not only
must each value be decrypted, a scan of all table rows is required. This is
a very expensive operation.
For an equality search, you can store a hash in clear text, index the hash
value and add the hash search criteria to your search. This will greatly
reduce the number of qualifying rows and usually result in acceptable
performance. See Laurentiu Cristofor's blog
(http://blogs.msdn.com/lcris/archive.../22/506931.aspx) for a more
complete discussion.
Unfortunately, there is no way to perform efficient wildcard/range searches
on encrypted data. If you must have this functionality, you'll need to take
a different approach like encrypting at the file level (EFS) rather than
column level. I don't know if that's an option in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerhard" <acsla@.community.nospam> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@.microsoft.com...
> Hi,
> I have an application that requires the storing of personal data, name,
> address, ssn with a requirement that key fields, first name, last name,
> address1, city, zip, ssn be encrypted. Currently, there are over 100,000
> records, and growing fast. Searches need to be done based on these key
> fields (lastname like 'A%' for instance). I am loading the return set
> into
> SqlDataSource and GridView using .net version 2.0.
> I'm using a stored procedure to return the data.
> When the page first loads (which filters on lastname = 'A%', it loads
> slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it
> times
> out.
> Is there a best practice to follow in a case like this that would result
> in
> the best performance possible. I realize with all the encryption that it
> is
> very processing intensive, but with a first load at least usable, but the
> second load timing out, there may be some things I should do (clear
> buffers
> or something) that I do not know about doing.
> Can you help on this?
> Thanks.|||Hi Acsla,
I am interested in this issue. Would you mind letting me know the result of
the suggestions?
I noticed that you bind the query result to a Gridview, so I guess that the
loading time (20s and time out) refers to the whole loading process when
all data has been displayed in your GridView. Does the Gridview have a
paging function? According to my experience, if the query result is also
huge, the render time of the control may also spend a long time.
Basically I agree with Dan's suggestions on SQL. Additionally, I would like
your checking how long it will spend if you separately run the SQL
statement in Query Analyzer.
If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Encryption on data fields

Dear all,
I wonder if there is any encrypting function that I can use to encrypt the
data on the fields I want. Can anyone advise?
Thanks.
IvanNot built-in. Either do it in the client application, or wait two months for
SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:228E7755-733D-4000-BE6E-8FAF788C79F8@.microsoft.com...
> Dear all,
> I wonder if there is any encrypting function that I can use to encrypt the
> data on the fields I want. Can anyone advise?
> Thanks.
> Ivan|||Hi Tibor
> Not built-in. Either do it in the client application, or wait two months
> for SQL Server 2005.
It should be november 7, should not it?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23$Kszx0wFHA.612@.TK2MSFTNGP10.phx.gbl...
> Not built-in. Either do it in the client application, or wait two months
> for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:228E7755-733D-4000-BE6E-8FAF788C79F8@.microsoft.com...
>|||Yep, that's the plan. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uEhCrA1wFHA.2656@.TK2MSFTNGP09.phx.gbl
..
> Hi Tibor
> It should be november 7, should not it?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23$Kszx0wFHA.612@.TK2MSFTNGP10.phx.gbl...
>

encryption

Hi
Is there any SQL Server encryption to hide the contents of fields
That is I do have a password column and I want to hide/encrpt it from users
to see it.
Is there any way to do it --like turn encrption ON for a column ?
Is there any way SQL Server help us for this ?
Thanks in advanceIs there any SQL Server encryption to hide the contents of
fields
That is I do have a password column and I want to
hide/encrpt it from users
to see it.
Yes. See permissions-sql server in BOL.
>--Original Message--
>Hi
>Is there any SQL Server encryption to hide the contents
of fields
>That is I do have a password column and I want to
hide/encrpt it from users
>to see it.
>Is there any way to do it --like turn encrption ON for a
column ?
>Is there any way SQL Server help us for this ?
>Thanks in advance
>
>.
>|||right, normally encrypt it NOT let the user see it. BUT
they still need to access the column to get verify their
log on.
i am using DES to encrypt it in my user profile password
column.
>--Original Message--
>There is no column level encryption directly in SQL
Server 2000. You can
>use security features in SQL Server to prevent users from
accessing the
>fields at all, however if you give them permission to
access the field then
>they can see the data. If you need to encrypt the data
then you can do it
>in your application or there several 3rd party encryption
packages that will
>handle this for you. For example:
>http://www.netlib.com/sql-server-encryption.htm
>http://www.protegrity.com/pdf/SD_222_SQL_Datasheet_FINAL_v
4.pdf
>
>--
>Hal Berenson, SQL Server MVP
>True Mountain Group LLC
>
>"Abraham" <binu_ca@.yahoo.com> wrote in message
>news:exFZI3ZQDHA.1024@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Is there any SQL Server encryption to hide the contents
of fields
>> That is I do have a password column and I want to
hide/encrpt it from
>users
>> to see it.
>> Is there any way to do it --like turn encrption ON for
a column ?
>> Is there any way SQL Server help us for this ?
>> Thanks in advance
>>
>
>.
>|||Unless there is a complelling need to be able to read the original password,
you're probably better of storing a password hash. You can still use the
hash to _verify_ the password, you just can't _read_ the original password.
This is why very few systems these days store the password using reversible
encryption...there's just no need.
Check out my previous post:
http://groups.google.com/groups?&hl=en&lr=&ie=UTF-8&selm=0uacnTNyHJ5plZuiRTvUqQ%40speakeasy.net&rnum=2
--
Dan Farino
Sr. Systems Engineer
Stamps.com, Inc.
news.danATstamps.com
"leecs" <leecs@.silverlgobe.com> wrote in message
news:04a101c34290$dd7349f0$a301280a@.phx.gbl...
> right, normally encrypt it NOT let the user see it. BUT
> they still need to access the column to get verify their
> log on.
> i am using DES to encrypt it in my user profile password
> column.
> >--Original Message--
> >There is no column level encryption directly in SQL
> Server 2000. You can
> >use security features in SQL Server to prevent users from
> accessing the
> >fields at all, however if you give them permission to
> access the field then
> >they can see the data. If you need to encrypt the data
> then you can do it
> >in your application or there several 3rd party encryption
> packages that will
> >handle this for you. For example:
> >
> >http://www.netlib.com/sql-server-encryption.htm
> >http://www.protegrity.com/pdf/SD_222_SQL_Datasheet_FINAL_v
> 4.pdf
> >
> >
> >--
> >Hal Berenson, SQL Server MVP
> >True Mountain Group LLC
> >
> >
> >"Abraham" <binu_ca@.yahoo.com> wrote in message
> >news:exFZI3ZQDHA.1024@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >> Is there any SQL Server encryption to hide the contents
> of fields
> >> That is I do have a password column and I want to
> hide/encrpt it from
> >users
> >> to see it.
> >> Is there any way to do it --like turn encrption ON for
> a column ?
> >>
> >> Is there any way SQL Server help us for this ?
> >>
> >> Thanks in advance
> >>
> >>
> >
> >
> >.
> >sql

encrypt/ decrypt fields or VB function "StrReverse".

I need to encrypt some fields on insert/ update and decrypt on select.
I have developed a function in visual basic but I need it to be a SQL Server
function. Is there any built-in functions availables to encrypt/ decrypt
fields or a substitution to visual basic's function "StrReverse" .
Kind regards
Khurram ButtThe REVERSE function in T-SQL is equivalent to the StrReverse function
from Visual Basic.
In SQL Server 2000, there are some undocumented functions for one-way
encryption of passwords (and other similar data): pwdencrypt and
pwdcompare. See this page (for example):
http://weblogs.asp.net/bdesmond/arc...8/15/24177.aspx
In SQL Server 2005, things are much better: there are functions for
encryption and decryption (using symmetric or asymmetric keys) and also
for digital signing.
Razvan|||No there are no such built-in functions in SQL Server 2000, probably you
have to handle this in your VB application or by creating extended stored
procedures.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:eFFHjbXrFHA.1236@.TK2MSFTNGP10.phx.gbl...
>I need to encrypt some fields on insert/ update and decrypt on select.
> I have developed a function in visual basic but I need it to be a SQL
> Server function. Is there any built-in functions availables to encrypt/
> decrypt fields or a substitution to visual basic's function "StrReverse" .
> Kind regards
> Khurram Butt
>

Encrypt payroll fields

I need to store payroll information (pay rates) in my data warehouse.
I've search on field level encryption and most the stuff i find is for
passwords, I.E. not reversable. I need to protect the payrate field
from dba's and sa's, but I need to allow the authorized users to see
the contents.
Any ideas? Does 2005 handle this?
I don't have a web app, per se, that access's this information. however
I am using Cognos' Report Net.
thanks in advance
RobThe most common solution is to use some kind of encryption library in
your client application or middle-tier, and encrypt/decrpyt the data as
people enter it or retrieve it. In SQL2005, you could use the .NET
crypto libraries within a .NET stored proc, but you can use them
already in a .NET client application regardless of the database.

So it probably depends on what your development environment and
language are. These links might give you some ideas:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
http://www.aspfaq.com/show.asp?id=2536

Simon|||SQL2005 actually has encryption built-in using either shared key or
certificates (which it can generate). Alternatively, for SQL2000 look
at xp_crypt from http://www.activecrypt.com/.

Encrypt Data Issue

Does SQL Server 2000 provide any data encryption/decryption functionality so that certain fields (e.g. SSN, Age and Salary) will be encrypted before writing into the table and decrypted once loading out of the table?
J827Look up encryption in Books Online

Using Encryption Methods
Encryption is a method for keeping sensitive information confidential by changing data into an unreadable form. Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly. Decryption is the process of changing encrypted data back into its original form so it can be viewed by authorized users.

Microsoft SQL Server encrypts or can encrypt:

Login and application role passwords stored in SQL Server.

Any data sent between the client and the server as network packets.

Stored procedure definitions.

User-defined function definitions.

View definitions.

Trigger definitions.

Default definitions.

Rule definitions.

encrypt data in database

Hi,

I have a .net application and i added a code that encrypts data saved in database. However, there is already data in the fields that was entered before this change.
I know need to check if the values in those fields are encrypted and if not i need to encrypt them.
How can I perform such a check and update the relevant data?
I use TrippleDES in .net to encrypt/decrypt the data.

Thanks

Have a look at this article:

http://aspnet.4guysfromrolla.com/articles/081705-1.aspx