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 of Data

Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:

> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
>|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane

>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this[vbcol=seagreen]
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA

Encryption of Data

Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
> > Dear All
> >
> > We have a requirement to encrypt data within SQL Server,
> > eg, Bank Account / credit card numbers.
> >
> > I don't believe there is anything in-built within SQL
> > Server 2000, but is there something in-built in 2005 ?
> >
> > Please note I do know about security and SSL but in this
> > case we are trying to encrypt the data in the server
> > itself.
> >
> >
> >|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
>> Hi
>> SQL Server 2005 will support this at column level using
Certificates.
>> Regards
>> Mike
>> "Jane" wrote:
>> > Dear All
>> >
>> > We have a requirement to encrypt data within SQL
Server,
>> > eg, Bank Account / credit card numbers.
>> >
>> > I don't believe there is anything in-built within SQL
>> > Server 2000, but is there something in-built in 2005 ?
>> >
>> > Please note I do know about security and SSL but in
this
>> > case we are trying to encrypt the data in the server
>> > itself.
>> >
>> >
>> >
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBAsql

Encryption of Data

Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:

> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>
|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane

>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back[vbcol=seagreen]
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this
>.
>
|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Encription for MDF files

Dear Friends
I want to use the encryption for the MDF files so that no one can just copy
the data and attach the MDF files to access the database.
When i have done the same the database is in Suspect Mode. Please suggest
what can be the reason.
I also have the following questions.
1. Can another user restore the database backup for the encrypted MDF files.
Thanks in advance.
Best regards
ShaileshI don't believe SQL Server supports encrypted files. If you don't want
someone to get ahold of your files then set the security on the box so that
only the people who need access have it. As for backups you should look at
SQL LiteSpeed from www.imceda.com . It allows you to encrypt the backup on
the fly.
Andrew J. Kelly SQL MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:9667DEDF-C6BD-4817-BC5E-88941ABE10D2@.microsoft.com...
> Dear Friends
> I want to use the encryption for the MDF files so that no one can just
> copy
> the data and attach the MDF files to access the database.
> When i have done the same the database is in Suspect Mode. Please suggest
> what can be the reason.
> I also have the following questions.
> 1. Can another user restore the database backup for the encrypted MDF
> files.
> Thanks in advance.
> Best regards
> Shailesh|||Hi
Did you encrypt the file/directory when logged in as the SQL Server Service
Account? If not, you need to as the encryption/decryption can only be done b
y
one account.
Just be aware, if you change the user's password from Computer Manager / AD
User Manager, the encryption certificate becomes invalid and you loose total
access to the data on the encrypted file/directory.
If you do a SQL Server BACKUP, the resultant backup can be restored by
someone else, as long as it is not encrypted.
Copying the encrypted file to a different location, will result in it being
decrypted.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sharad2005" wrote:

> Dear Friends
> I want to use the encryption for the MDF files so that no one can just cop
y
> the data and attach the MDF files to access the database.
> When i have done the same the database is in Suspect Mode. Please suggest
> what can be the reason.
> I also have the following questions.
> 1. Can another user restore the database backup for the encrypted MDF file
s.
> Thanks in advance.
> Best regards
> Shailesh|||Hi,
You can use the Encrypted File System Support on Windows 2000
Windows 2000 support encrypted file system property.
Below are the steps encrypt the data files:
1) Logon with the SQL Server startup account
2) Stop SQL Server and sql agent service
3) Right click the data files, select properties, click Advance button,
check the "Encrypt contents to secure data"
4) Start the SQL Server service
See the below KB for more information:-
HOW TO: Encrypt Data Using EFS in Windows 2000
http://support.microsoft.com/defaul...kb;en-us;230520
Note:
If you change the SQL Server startup accout you have to redo the same,
otherwise SQL Server service will not start.
"With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account."
Thanks
Hari
SQL Server MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:9667DEDF-C6BD-4817-BC5E-88941ABE10D2@.microsoft.com...
> Dear Friends
> I want to use the encryption for the MDF files so that no one can just
> copy
> the data and attach the MDF files to access the database.
> When i have done the same the database is in Suspect Mode. Please suggest
> what can be the reason.
> I also have the following questions.
> 1. Can another user restore the database backup for the encrypted MDF
> files.
> Thanks in advance.
> Best regards
> Shailesh|||Respectfully, EFS by itself is not a complete solution.
If the box is stolen - including the EFS recovery keys, then you are no
better off as the system administrator password can be hacked, the filestore
accessed and viola!
If you want to protect against this, then research the syskey command and
also read up about EFS recovery agents and make sure you know the full in's
and out's of it as otherwise it could cost you all your data. It is common
for people to lose data by losing EFS keys.
If some of your data is critical - e.g. columns containing say credit card
numbers, then encrypt it before it is stored and decrypt it when retrieving.
It is easy to code using .Net. This is not an easy solution tho' as you also
have to protect the keys used for encryption and decryption here. If you
want to store some type of passwords - use hashing instead - it is a one way
process. Research SHA1, SHA256 etc.
HTH
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ut72iu6kFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You can use the Encrypted File System Support on Windows 2000
> Windows 2000 support encrypted file system property.
> Below are the steps encrypt the data files:
> 1) Logon with the SQL Server startup account
> 2) Stop SQL Server and sql agent service
> 3) Right click the data files, select properties, click Advance button,
> check the "Encrypt contents to secure data"
> 4) Start the SQL Server service
> See the below KB for more information:-
>
> HOW TO: Encrypt Data Using EFS in Windows 2000
> http://support.microsoft.com/defaul...kb;en-us;230520
> Note:
> If you change the SQL Server startup accout you have to redo the same,
> otherwise SQL Server service will not start.
>
> "With EFS, database files are encrypted under the identity of the account
> running SQL Server. Only this account can decrypt the files. If you need
> to
> change the account that runs SQL Server, you should first decrypt the
> files under the old account, then re-encrypt them under the new account."
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
> news:9667DEDF-C6BD-4817-BC5E-88941ABE10D2@.microsoft.com...
>

EnableReportDesignClientDownload

Dear all
I would like to turn of the Report Builder Buttom in the Report Manager. It
worked with the following script out of msdn:
Class Sample
Public Shared Sub Main(ByVal prompt As String)
Dim rs As New ReportingService.ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim props(0) As [Property]
Dim setProp As New [Property]
setProp.Name = "EnableReportDesignClientDownload "
setProp.Value = prompt
props(0) = setProp
Try
rs.SetSystemProperties(props)
Catch ex As System.Web.Services.Protocols.SoapException
Console.Write(ex.Detail.InnerXml)
Catch e As Exception
Console.Write(e.Message)
End Try
End Sub 'Main
My problem is now, when I try to enable the Report Builder Buttom, this does
not work. Any idea why?
Thanks in advance for your answer,
Marc
--
Best regards,
MarcHello Marc,
Users must have permission to the Execute Report Definitions task (normally
obtained via the System Users role) for the Report Builder button to be
displayed.
See Home->Site Settings->Configure system-level role definitions->System
User->Execute Report Definitions
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter
it worked well thanks!
Best regards,
Marc|||Welcome!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.