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


ADVERTISEMENT

Protect SQL 7 Data From IS

Aug 7, 1999

My client maintains its HR data in an application that uses Oracle as its backend. This highly-sensitive data is basically off limits to all but a select few. Presently, I use a program in Access 97 that allows one high level HR person to pass their login to linked Oracle tables and copy a large chunk of this data to Access tables. From there I can morph it as needed for the Personnel, Safety, EEOC and other areas. The client sees this PW-protected, encrypted Access DB as safe because, being "only an Access DB", it falls below the radar of IS. This basically means IS can't get to the data. However, accessibility and scalability are non-existent. I'd like to reduce the Access DB to a shell that simply links to Oracle and SQL Server 7 tables and performs a straight pipe of the raw data between DBs. However, now IS will be very interested (since it's SQL 7) and have Admininstrator rights, therefore causing the HR people to squash the deal. How can I lock SQL 7 up so tight that IS can't get to the data and yet be able to maintain the DB? If this is not feasible, are there any other options that might provide a solution?

View 1 Replies View Related

Protect Data From DBA

Jan 22, 2008

Hi all, We have sql server 2000 on Windows server 2003.Is there anyway in sql server 2000 to protect some crucial data, even from the DBA. Thanks in advance...

View 14 Replies View Related

How To Protect Data Of One Table?

Dec 13, 2006

I have a DB on my SQL Server Express 2005. In this db I have one table and I DON'T want any user can modify data on this table but I want only show this data (only select statements allowed). If I install this db on one of my customers' machine, I can see that he can modify data into this table If he log in into the database with windows authentication and not with the "USERLOGIN" that I have created with sql server authentication. What can I to to remove dbo access in Windows authentication in my db and "transfer" the dbo in another user access (like MYUSER with Sql Server authentication)?

Thank You

Mirko

View 4 Replies View Related

Protect Data And Schema SQL SERVER 2005 EXPRESS

Aug 17, 2006



We have a commercial VB.NET winforms client/server application that utilizes SQL Server 2005 express edition. The schema and data that the application utilizes is proprietary and could be very damaging if it got into a competitors hands.

Is there any way to protect the data and schema of a sql server 2005 express edition database?

Will this functionality ever be added?



Thanks

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

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

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

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

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

SQL 2012 :: Encrypted Data In Project Manifest Failed To Load

Sep 8, 2015

An inherited SSIS (2012) solution displays the following messages when I try opening it in VS 2012:

"One or more projects in the solution were not loaded correctly."

Then another message displays

"The encrypted data in the project manifest failed to load. The project manifest is corrupted or the project was encrypted by another user."

View 8 Replies View Related

How Can I Retrieve Sql 2000 Encrypted Column Data From SQL 2005 Stored Proc?????

Aug 4, 2006



Hi...

I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.

select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a

The Following Error I get When I execute the above statement.



Msg 195, Level 15, State 10, Line 1

'Encrypt' is not a recognized built-in function name.



Thank you.



Bal.

View 9 Replies View Related

Problem Restoring Database With Encrypted Columns To Different Database Or Server With Encrypted Columns.

Jan 23, 2006

I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.

First, here's how i created the symmetric key and encrypted data in the original database:

create master key
encryption by password = 'testAppleA3';

create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

open symmetric key sk_Test decryption by certificate test;

insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));

select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees

close all symmetric keys

Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).

Then if i attempt to open the key in the new database and decrypt:

open symmetric key sk_Test decryption by certificate test;

I get the error: An error occurred during decryption.

Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then I try opening the key again and get the error again:

An error occurred during decryption.

So then it occurs to me, maybe i need to drop and recreate it so i do

drop symmetric key sk_test

then

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

and then try to open it.

Same error!

So then i decide, let's drop everything, the master key, the certificate and then symmetric key:

drop symmetric key sk_test
drop certificate test
drop master key

Then recreate the master key:

create master key
encryption by password = 'testAppleA3';

Restore the certificate from a backup i had made to a file:

CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'

Recreate the symmetric key again:

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

And now open the key only to get the error:

Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.

I also tried running the command

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

again but this does not resolve the issue.



Thx.

View 6 Replies View Related

How Can I Protect My Parameters

Oct 25, 2007

Hello.
I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter.

When the report is runnig the parm1 value is "false".
How can I protect this parameter from a change by the user?
I mean - the user can run the report and then add to the url "¶m1 = true".

Can I do anything against that?
I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked.

Any ideas?

Thanks.

View 2 Replies View Related

Protect From SQL Injection?

Jun 25, 2007

Hi,



Just read about SQL injection, and tested it out with sample database, and it does hack my database, the article show to prevent SQL injection by using application code to remove those keywords and change single quote to double quote, is there any method to prevent SQL injection directly using the database system itself, maybe stored procedure or anything?



Thanks.

View 6 Replies View Related

How To Protect My Parameters

Oct 25, 2007

Hello.
I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter.

When the report is runnig the parm1 value is "false".
How can I protect this parameter from a change by the user?
I mean - the user can run the report and then add to the url "&param1 = true".

Can I do anything against that?
I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked.

Any ideas?

Thanks.

View 4 Replies View Related

Sql Injection - Are Parameters Enough To Protect You.

Feb 8, 2008

 Hi all,I have been learning .net and creating a public facing site. I am therefore worried about SQL injection.My question is...Is enclosing customer input inside .net SQLParameters enough to protect you from SQL injection?If not why not?I have seen people saying that SQLParameters alone is not enough but not an explanation why?Can anyone help?If I use code to remove words like drop or characters like '%' I'm limiting what my users can enter, but if I have to I will. taC 

View 1 Replies View Related

HOW CAN I PROTECT SQL DB IN AN EXISTING SERVER?

Mar 14, 2008

Dear Colleagues,

I have designed a Microsoft SQL Server 2005 database application using Visual Basic 2005. I want to control access to the database programmatically, without the End-User opening the database in SQL Server.

I want to protect the database structure such as my tables, code, etc. This restriction should include all the Administrators of the Computers on which my application will be deployed. Any modification of my database or code should be implemented only by me.

What is the best way to do this using (a) Windows Authentication Login? (b) SQL Server Login? How do I configure the User-Login?

NEW: In addition to above question, how best do I achieve this protection if installing the DB with other databases in an already existing server, is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.

Thanks and best regards,

Peter

View 6 Replies View Related

How To Protect DataBase Using Password?

Sep 6, 2007

I want only SQL Server Authentication not Windows Authentication Because If some one copy database and attach at some other place using Windows Authentication then they can see each and everything.

I want something like Access (I know its password can be broken very easily)


I want to protect Table & SP Schema, Data is not much important.

Its urgent

View 1 Replies View Related

About Database And Table Protect

May 13, 2007

hello,

We wrote an erp,and provide a platform to participator to extend my erp system,so I will give my participator database dictionary,but I only want to give partial database dictionary,I will hide some table and some field, I want they cann't open the database thouth sql server management studio or other tools,only can using our interface to access database,how can I do?

-- I use sql server 2005

View 5 Replies View Related

Protect Intellectual Property

Jul 25, 2006



Dear All,

How is it possible to protect the Intellectual property for a integration / analysis project?

For instance if I build a complex solution €“ how do I avoid others to view and copy the solution.

Best Regards, T

View 1 Replies View Related

Encrypted Data Size By Original Size, Algorithm ?

Jan 24, 2006

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

View 3 Replies View Related

Tables Protect In Sqlserver 2000

Jul 31, 2003

I have some tables in the employee database, this database created from sql sever 2000. I build a employee management application by C# and sqlserver 2000.

My goal is after design complete the empployee database by sqlserver 2000, any users can not modify my tables and unkonw table's structure.
help me please

thanks and reagards

View 2 Replies View Related

How To Protect A Frequently Real Time DB

Jul 2, 2001

Hi Everybody:

We have a table which needs to be updated 2 million times per day. It hosts all real time transaction. There are 200K records in this table. Would you please to share your experience with me about how to protect/save such table in SQL 2000 from any possible damage?

We plan to use point-in-time backup (every 5 minutes). It still takes at half an hour to recover the whole database. Any new technology from Microsoft or SQL 2000 you can recommend?

Thank you very much.

Joan

View 3 Replies View Related

How To Protect Errorlogs To Tables From Rollbacks

Apr 5, 2002

Hey!

This post contains the code for this thread: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14475

It deals with the problem how to prevent log actions in long running batch jobs from being rolled back. It was heavily inspired by Andy Pope´s approach to error handling (http://www.sqlteam.com/item.asp?ItemID=2290) and in fact you will see much of his code here.

The code:

This procedure dynamically opens a second connection in parallel to the existing connection of the calling procedure using SQL-DMO. So the second connection runs without the scope of transaction of the calling procedure. So no action you take here is rolled back in case the calling proc fails. So be careful! Keeping data integrity is your job here and you could do many weird things to your database.
The procedure dynamically adds a user function that if called just would return the object token of the new DMO connection. So any piece of code in the same batch could reuse the exisiting connection.

LogConstructor
CREATE PROCEDURE LogConstructor AS

if exists (select * from sysobjects where id = object_id (N'dbo.MFF_GetLogObject')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function dbo.MFF_GetLogObject

DECLARE @Error INT
DECLARE @ErrorMsg VARCHAR(255)
DECLARE @oSQLServer INTEGER
DECLARE @Source VARCHAR(255)
DECLARE @Return INTEGER
declare @dynsql nvarchar(3000)

-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
IF @Error <> 0
GOTO OA_Error

-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
GOTO OA_Error

-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME
IF @Error <> 0
GOTO OA_Error

-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
GOTO OA_Error
IF @Return = 0
GOTO OA_Error

-- Create Function with server object
select @dynsql = N'CREATE Function MFF_GetLogObject () RETURNS INT AS BEGIN RETURN ' + cast(@oSQLServer as varchar) + N' END'
EXEC sp_executesql @dynsql

return


OA_Error:
-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
@ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'
print @ErrorMsg
return
GO
The next procedure just drops the DMO connection and also drops the user function as the token is invalid by now. This proc should be called within the same batch as the constructor to clean things up properly.

LogDestructor

CREATE PROCEDURE MFP_LogDestructor AS

declare @lo int
select @lo = dbo.MFF_GetLogObject()
exec sp_OADestroy @lo

if exists (select * from sysobjects where id = object_id(N'dbo.MFF_GetLogObject')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function dbo.MFF_GetLogObject
GO

View 2 Replies View Related

How To PROTECT SQL Server Database Files ?

Jul 23, 2005

Hello,How to protect structures(Tables,SP,Views and Functions) of a SQLServer Database?(Password protect a database file)I have a SQL database that will distribute with my application, I wantto protects it's structure from my appliction users. Only myapplication can access the database.Thanks

View 3 Replies View Related







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