MAC Used As Search Index For Encrypted Data: How Secure?

Aug 17, 2006

I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?



TIA,



Barkingdog

View 5 Replies


ADVERTISEMENT

Secure A Single SQL Server Database + Make Float Fields Encrypted

Mar 21, 2007

Hi all,

I am developing an application for a big office which uses SQL Server 2000.
Apart from my database, on that server, there are two databases by other companies.
The administrator also has access to server but the client only wants him to backup the database.

I have two questions:

1) First of all (if it is possible) I would like to protect my own database from the other companies.

I don't want them to:


see the data in the tables (around 20 tables)
make changes to the stored procedures (more than 100 stored procedures)
be able to backup the database

2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden.
I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.



Thanks for your patience reading this!

Would really appreciate some help on any of these

Nicolas

View 4 Replies View Related

Wildcard Search On An Encrypted Column?

May 16, 2007

Is it possible to do a wildcard search on an encrypted column?



Many thanks!

View 6 Replies View Related

Index Of Encrypted Field

Jan 15, 2008



I created Encryption by passphrase . The data type of the encrypted field is varbinary(MAX). Now I want to make one Index on this filed. Name of the encrypted colum is ''Encrypted Account Number' " and the name of the table is "S3schema-Test". The value of ''Encrypted Account Number" is unique. I wrote the following T-sql code :-


CREATE CLUSTERED INDEX [IDX_Encrypted Account Number1]

on [S3schema-Test] ([Encrypted Account Number])

But I am getting following errors:-
Column 'Encrypted Account Number' in table 'S3schema-Test' is of a type that is invalid for use as a key column in an index.

Any Idea, how to fix this error?

View 1 Replies View Related

Full-text Search On Encrypted XML Datatype

May 25, 2007

That's right. My developers want full-text searching implemented on an encrypted XML datatype...on SQL Server Express, no less.
Whether this can be done seems to boil down to four questions:
1) Can the XML datatype be encrypted?
2) Can the XML datatype be full-text indexed?
3) Can encrypted data be full-text indexed?
4) Can encrypted XML data be full-text indexed?
So I'm diving into the documentation now, but if anybody out there already knows whether the answer to any of these questions is "No", please save me some time.
Oh, and they also want to substitute potato nuggets for fries, and biggie-size the whole thing.

e4 d5 xd5 Nf6

View 6 Replies View Related

How To Extract Data From SQL Server With The Index Server Search Result?

Jan 14, 2006

I am developing a Recruitment Agency WebsiteI am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.Is it anyway possible to extract all the CV Details data by matching the path.I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.Thank you very much in Advance
            string strCatalog = "TestCatalog", strsearchstrings="";            string strQuery="";             strsearchstrings = txtsearch.Text;
            strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')";            // TextBox1.Text is the word that you type in the text box to query by using Indexing Service.            string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog;
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring);            conn.Open();                                   System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn);            System.Data.OleDb.OleDbDataReader objRdr;
            objRdr = objcmd.ExecuteReader();            DataGrid1.DataSource=objRdr;            DataGrid1.DataBind();            objRdr.Close();            conn.Close();

View 1 Replies View Related

Secure/selective Data Access

Jul 23, 2005

Dear netters,We are looking to build a SQL Server database that will be hooked up toa Web server (WebLogic or a .NET).The database will house data for multiple customers, and therequirement is to have no customer see other customer data. Web serverwill be responsible for authenticating users (ids and passwords will bemaintained there/possibly stored in the database, but will not beactual database logins) -- and establishing pooled connections to thedatabase, using some sort of a proxy login.Ideally, we do not want to have the database itself visible to "theworld", it should reside behind a firewall, and be accessible throughthe Web server, which will do all authentication and routing.We want to have either tables or views defined in such a way that:1. Users can only see their own data and NEVER can see anybody else's.2. Users' access to the database is logged (this includes updates aswell as queries).3. We would like to provide some sort of a reporting mechanism -- whereusers can form their own queries. So, something like a Crystal or anAccess front end, that users are typically familiar with... The problemis that this requires users having direct access to the database (?),and opens up tables (unless, there is a way to create parameterizedviews that will always restrict access to a subset of records).Any advice on how to best implement this is greatly appreciated.Thank you in advance!

View 4 Replies View Related

DB Engine :: Secure Deletion Of Data

Nov 16, 2015

We have been asked whether it is possible to completely delete sensitive data. When data is deleted it is not actually completely removed from the database but is marked as deleted. It is possible to zero out that data using sp_clean_db_free_space but this doesn't affect the transaction log.

We had hoped that CHECKPOINT would clear the data from the log but we are not completely certain of this. So the question becomes is there a built-in command or function in SQL Server 2008, or subsequent versions, that will completely remove deleted data, both from the transaction log and the database itself? Or is there a 3rd party tool to accomplish the same thing?

View 25 Replies View Related

Search Index

Aug 18, 2004

Dear Experts

I had created an index on of my table in SQLSERVER 2000 !
and after some time i had added a field on the table for which
i had created index now i want to reindex the table ,
but now i don;t know the name of the indextable which i had made,
I tried to search it on the ENTERPRISE MANAGER
but i can;t get !
Can any one help me to find out the list of all the
TABLE NAME / and the INDEX


With Regards
Manish Kaushik

View 1 Replies View Related

Is Data Sent Via DTS Encrypted?

Jan 27, 2005

Hi all,

I may have a requirement to send data from a SQL Server at site A to an Oracle server at site B. These sites have no network connection between them, and the current suggestion is to use ftp, but the transfer (or username and password) will not be encrypted.

If I create a DTS package transferring data from site A, will that transfer be encrypted?

If not, is there an option with SQL Server DTS to ensure that the data is sent in an encrypted form?

Thanks in advance.

View 9 Replies View Related

Encrypted Data

Feb 23, 2004

Hi,

How can I save encryped data in SQL2000? Also, how can I retrieve it?

View 2 Replies View Related

Searching Encrypted Data; Using MAC Secret Data

Aug 10, 2006

I just finished reading an article on how to search encrypted data efficiently and they suggested creating a new column with a Message Auhtentication Code. To be honest, reading the aritcle makes my head hurt. I can hardly understand what they were doing myself and I can't begin to explain it to a developer.

Are there any easier ways to search encrypted columns for a speciifc match? If not, does any have some stored procs that implement this messy MAC stuff?



TIA,



Barkingdog

View 5 Replies View Related

MS SQL Full-text Index Search

Jan 16, 2006

First of all I’m new to MS SQL, I did work with mySQL
 
Table name db (real db has 12 columns)
Id         c1                    c2        c3
1          tom                  john      olga
2          tom john           olga      bleee
 
I enabled full text index on all columns
 
Problem when I do search like this:
SELECT * FROM db WHERE CONTAINS(*,'�tom� AND “john�')
 
It will return only one row (id 2) – I understand that the full text search does look only at one column at a time because it did not return row #1
 
Anyway I thought that I can add extra column c4 and when user enters new data it will save data from columns c1, c2, c3 to c4 (varchar(750)) and then I will do search only on c4 – this way it will work the way I want.
 
1)       Is there any better way to do this?
2)      How do I sort results by “rankâ€? with SQL

View 1 Replies View Related

Problem With Full Index Search

Jan 18, 2007

I have implemented Full Text Search for my MSSQL Database and got stuck with this problem:

When i try to search for the exact word 'Function 1' using the CONTAINS keyword, the query retrieving the following results too.... :confused:

1. Function 1
2. Function 2
3. Function 3
4. Function 4

Where i want the first answer alone. But it works good with the strings (ex: Broken Wire) where both are strings. :)

Wont it work with the text which is a combination of a string and a number like 'Function 1'

Kindly guide me!!

SqlHunt

View 1 Replies View Related

Weighted Search On Fulltext Index

Aug 20, 2007

SQL Server 2005:
Anyone know how best to rewrite this SQL string to perform a weighted search on my table?
here's my code:
SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')"
the idea is to get a list of recipes most pertinant to the ingredients entered.
table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.

at the moment it returns the correct recipes but not in the right order.
If I use 'CONTAINS' it returns nothing.
hope that's not too vague!

View 10 Replies View Related

DB Design :: How To Use Encrypted Data In Oracle DB

Jun 26, 2015

I am using a sql server 2012 and having a database "Test".In this database , having a table "Employee". I have encrypted the employe table data with the password. How can is use these employee data in oracle server. ie;How can I show the employee data as the encrypted  and decrypted form of data in Oracle server( The actual encryption happened only in SQL DB).

View 8 Replies View Related

SQL 2005, Replicating Encrypted Data?

Jun 1, 2006

Hello,

I've been searching for information on this for awhile unsuccessully. I am using SQL Server 2005 Enterprise Edititon and transactional replication with separate publisher, distributor and subscribers. I need to encrypt data on our publisher and then I need to replicate that data out (i.e., encrypted data, not encrypted transmission). Since the keys are server specific, I imaging there might be some difficulty in doing this, but I figure I can't be the only one with this problem. Does anyone have a resource or know what must be done to get this accomplished?

Thanks,

Aaron Lowe

View 8 Replies View Related

Backup And Resore Encrypted Data

Mar 27, 2007

I have two databases, one production and other a production copy.

One column of the production table is encrypted by the following syntax.



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'



CREATE CERTIFICATE cert_demo

WITH SUBJECT = 'encryption demo'



CREATE SYMMETRIC KEY symkey_demo

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE cert_demo

-----------------------------------

I have stored procedure to add records to a production table like this.

ALTER Procedure AddRecord

@id int,

@var1 varchar(100)

AS

BEGIN

DECLARE @var2 varbinary(256)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

SET @var2 = EncryptByKey(

key_guid( 'symkey_demo' ),

@var1 )

insert into dbo.Test(id,Data,EData)

values(@id,@var1,@var2)

CLOSE SYMMETRIC KEY symkey_demo

END

---------------------------------



Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.



Create procedure ViewData

@id int

AS

BEGIN

DECLARE @var1 varbinary(256)

DECLARE @var2 varchar(100)

OPEN SYMMETRIC KEY symkey_demo

DECRYPTION BY CERTIFICATE cert_demo

select @var1=EData from Test where id=@id

Set @var2 = convert( varchar(100), DecryptByKey( @var1))

print @var2

END



It doesn't print the enctypted data. Please suggest what goes wrong.



View 5 Replies View Related

Handling Encrypted Data In SSIS

Mar 11, 2008



Hi, I have source data in encrypted format. How should i handle it in SSIS?
I have found no information for such situation.

Anybody have any idea about it.

Bhakti

View 2 Replies View Related

How To Protect Encrypted Data When DB Is Stolen

Jun 25, 2007

I got a problem concerning encryption. The thing is I have decided to use symmetric key protected by certificate to encrypt certain information. Certificates are protected by database masterkey and by service key.



But I also want to be sure that if someone steals my database with all its data he wont be able to decrypt it with his own SQL Server Management Studio where he has all the permissions.



Also after some time I will need to take my database and set it up on another PC.



Has anyone ideas how to solve this??



P.S. As far as I know if symmetric key is protected by certificate which is protected by DB master key and service master key then you cant decrypt data if database is moved to another workstation and opened with another Management Studio. Please can anyone explain how this works( if its true). And if this is true then how can i move my DB without loosing access to encrypted data???

View 9 Replies View Related

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

Multiple Parameters Using Full Search Index

Nov 26, 2014

Using a full search index with the following query works with just one parameter.

declare @P0 varchar(50) = '"First*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0)

How do I make it work if I have two parameters, while also protecting the parameters from injection attacks?

declare @P0 varchar(50) = '"First*"'
declare @P1 varchar(50) = '"Second*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0 AND @P1)

If they weren't parameters, you put single quotes around the ' @P0 AND @P1' to get this query to work.

View 2 Replies View Related

Full Text Search - Index Files

Mar 8, 2008

history.ix, index_a.ix, index_d_1.ix, index_di_1.ix, index_i_2.ix,
index_k_2.ix, index_kl_1.ix, index_klh_2.ix, index_n.ix,
index_r_l.ix, index_sv.ix, index_v.ix, index_v_ix.log, indexlog.dat.

This files are generated durin full text search.
now i have doubts regarding this,
1) Can we referrence this files directly
2) Where it will be located in our system?
3) is it loaded for each Full Text Index we created for the table.
4) How this file are used in Full Text Search.

View 1 Replies View Related

Searching For Encrypted Fields In Data Columns

Jul 20, 2005

I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s

View 10 Replies View Related

Backup Database With Encrypted Column Data

Jan 25, 2007

Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.

How could I backup/restore master key, symmetric and asymmetric key created for my database?

Thank you

View 6 Replies View Related

SQL Server 2012 :: Search For Maximum Value Of A Specified Index On Dataset

Oct 16, 2014

I have a dataset that I need to search for a maximum value of a specified index.

Example of dataset:

Heat Index Heat # Frame Window
1 584 110 110
1 584 102 109
1 584 95 106
1 584 190 112
2 586 100 100
2 586 150 120
2 586 170 130
2 586 112 126

I need to find the maximum value of Frame for each heat and the maximum value of Window for each heat. As you can see each heat has several data points.

Is there a way I can send the dataset to a function? I could then use a loop to test for the maximum of Frame and the maximum of Window for each heat.

Could I use an array?

View 3 Replies View Related

T-SQL (SS2K8) :: Multiple Parameters Using Full Search Index?

Dec 1, 2014

Using a full search index with the following query works with just one parameter.

declare @P0 varchar(50) = '"First*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0)

How do I make it work if I have two parameters, while also protecting the parameters from injection attacks?

declare @P0 varchar(50) = '"First*"'
declare @P1 varchar(50) = '"Second*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0 AND @P1)

If there weren't parameters, you put single quotes around the '@P0 AND @P1' to get this query to work.

In addition, while "where contains((Col1,Col2),@P0) and contains((Col1,Col2),@P1)" works, it appears to increase the execution time.

View 0 Replies View Related

Index Files Created During Free Text Search

Mar 8, 2008

history.ix, index_a.ix, index_d_1.ix, index_di_1.ix, index_i_2.ix,
index_k_2.ix, index_kl_1.ix, index_klh_2.ix, index_n.ix,
index_r_l.ix, index_sv.ix, index_v.ix, index_v_ix.log, indexlog.dat.

This index files are created in my hidden folder. Now, my problem is,
1) Is this files associated with Full Text Search.
2) when the file will be created.
3) For what purpose, this files are stored.

View 4 Replies View Related

TSQL - Full Text Search / Index / Catalog

Aug 20, 2007

Hi guys,
What should I do in order to make a Full Text Index / Catalog and then using the Contain predicate?
Thanks in advance,
Aldo.

View 3 Replies View Related

SQL Search :: Truncate And Load A Table With FullText Index?

Jun 4, 2015

I am using SSIS to replace set of tables daily. One of the table has primary, unique, foreign keys and full-text index. Before truncating, I am dropping the foreign key constraints (to truncate the parent table), truncating the tables and recreating the foreign keys.

I have few questions:

1) Do I need to drop and recreate the unique key as well? (I am not dropping the primary key) - Unique key is identity column created just for the full-text indexing since it was mentioned that key on integer is better than key on varchar and my pk is a varchar.

2) Do I need to drop and recreate the full-text index or just rebuild/repopulate it every time the table is loaded.

This is the first time i am using  full text index and I was able to learn a lot about it from the sites. I would like to understand the correct approach while loading the tables.

View 4 Replies View Related

Reporting Services :: Error While Deleting Encrypted Data

Sep 15, 2011

I am using SQL Server 2008 R2.I have opened the Reporting Service Configuration Manager to delete the encrypted data.In the encryption key section, when I click on the 'Delete' button to delete the encrypted data, it gives me below error. Microsoft.Reporting Services. WmiProvider. WMIProvider Exception: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.

 ---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.DeleteEncryptedInformation()
   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()

View 4 Replies View Related

Howto Reinstall With Local Encrypted Data, No Domain?

Aug 17, 2005

This is a question more about EFS, I think, than SQL itself, so apologies for that.

View 1 Replies View Related

Full-text Index Search Not Returning Expected Results

Apr 11, 2007

Hello,

My full-text search isn't working at all! I have a temporary table with full-text indexing enabled where files are scanned for social security numbers. If it has one, the user will see a message that it believes it's found a SSN and won't upload it. There is only ever one row in this table, as we overwrite the contents upon each upload.

I'm testing this search, and it doesn't work. The table has the following columns:
attachemtId (int) - primary key
fileContent (image) - contents of the file
fileExtension (varchar) - extension of the file (this is always either ".pdf" or ".doc")


I created a .doc file that simply says "ssn", and then run the following query:

SELECT * FROM TempAttachment
WHERE CONTAINS(fileContent,'ssn')


and nothing is returned! I tried the same thing with a .pdf file, and same results.

I'm not sure if this is related, but earlier I had this issue where I had to reset permissions for the directory. I've tried removing the full-text index and adding it again, but that didn't do anything. I also checked error logs on the server, and there were no messages. Any help would be appreciated! Thank you!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved