Data Encryption And Keys

Jan 25, 2007

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

View 3 Replies

Encryption - Importing Keys

Oct 12, 2006

Is it possible to load data into MSSQL 2005 that has been encrypted externally with a symmetric key algorithm, such as AES, and then import the key to SQL Server? After browsing through Books Online, I don't see any way to import a symmetric key from an external file, but maybe I'm missing something.

View 3 Replies View Related

Scheduled Backup For Encryption Keys

Oct 29, 2007

Is there a way to schedule the backup of encryption keys periodically?

Thanks
Karthika

View 1 Replies View Related

Symmetric Encryption By Asymmetric Keys

Nov 9, 2007

i've getting ready to implement encryption on a rather large database. I'd read that if performance is of utmost concert, you should use symmetric keys. I want to encrypt those keys by asymmetric keys. My code is working, but i'm just not sure if there is a quicker way? do you have to open and close the key each time you select/update/insert in a stored procedure that references an encrypted column, or is there a way to just modify the code by adding the encryptbykey/decryptbykey functions?

has anyone implemented encryption on columns in large tables? any suggestions for me?

Thanks,
Pete

here's my code to create the keys:

create asymmetric key ASK_Auto_Encrypt
with algorithm = RSA_512;

create symmetric key SK_AE
with algorithm = TRIPLE_DES
encryption by asymmetric key ASK_Auto_Encrypt;

here's my code to test this:

create table encryption_test (test varchar(50));

open symmetric key SK_AE
decryption by asymmetric key ASK_Auto_Encrypt;

insert into encryption_test
select encryptbykey(key_guid('SK_AE'),'test');

select convert(varchar(max),decryptbykey(test)) from encryption_test;

close symmetric key SK_AE;

View 14 Replies View Related

2005 Encryption - Symmetric Keys

May 29, 2006

Hi There

We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.

We do the followign steps:

Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.

But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.

Once you have dropped the key you cannot decrypt the data anymore?

Also the key defults the expiration date to 1 year.

WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?

You can backup master keys nd certificates but not symmetric keys?

It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?

Thanx

View 6 Replies View Related

SQL 2005 Encryption - Symmetric Keys

Feb 14, 2007

I have a question about the storage of symmetric keys in SQL Server 2005 due to the fact that I have read two conflicting statements on this.

In Laurentiu's blog located at http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx, in regards to preventing symmetric key loss he makes the statement that "...Because the keys are stored in the database, they will be saved with the database....".

But in the white paper Improving Data Security by Using SQL Server 2005, which is located at http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx, in regards to symmetric keys the statement is made "...Note: The symmetric key is not stored in the database. Only the encrypted values of the symmetric key are stored in the database. Therefore, users who can access the database cannot decrypt the data without first decrypting the symmetric key....".

So I am just wondering which statement is correct, are symmetric keys stored in the database or not?

Thanks!

Ginny

View 1 Replies View Related

Encryption Keys Backup Option Is Disabled

Jan 23, 2008

I just installed Reporting Services (2005) in a "distributed installation" mode. That is, I have sql server 2005 on a separate server. I installed the Report Server on its own server where I already had IIS running.

That all seemed to go well and I didn't get any errors or anything. After the install process was done, I ran the RS Configuration Manager tool. Since "Install but do not configure" option was automatically selected for me, I started working through all the pages from top to bottom. Again everything worked, and I have green checkboxes next to all of the nodes in the left except...

When I get to the Encryption Keys page, it has a blue exclamation icon next to its icon in the left pane. On the page itself, the Backup and Change buttons are disabled, greyed out.

And, the next item down, Initialization, is shown with a greyed out "x" icon. It's not red, but greyed out.

So, I'm not sure what this all means, but I'm guess it means that I don't have RS set up yet...! Any help would be greatly appreciated.

-- M Noreen

View 3 Replies View Related

Primary/Foreign/Identity Keys && Encryption

Nov 2, 2006

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

View 1 Replies View Related

Managing And Rotating Keys For Encryption For Many SQL Servers

Aug 15, 2007

There is all kinds of great info out there about the mechanics behind column level encryption in SQL2005, but it all seems to assume I only have 1 or 2 database servers. If I am using an X509 certificate to encrypt my data, it looks as if I can script the administration of this fairly easily.

But what if I have 1000 SQL Servers?

Is there any guidance/best practices/tools out there that will help me manage the 1000 certificates that I would need to deploy in such a scenario. Also, what if I need to 'rotate' the certificates for some reason. Can a PKI for the domain help me to automate and manage this?

It seems as if the management of these certificates is purely 'manual' at this point.

Thanks for any help,

...Andrew

View 4 Replies View Related

SQL Server Admin 2014 :: Using Column Encryption With Symmetric Keys

Jun 25, 2015

I am trying to implement the column encryption on one of the tables, have used the below link as the reference and got stuck at the last step.

[URL] ....

I have completed the following steps so far.

- CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’

- CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'

- CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,

[Code] .....

Example by using the function

EXEC OpenKeys

-- Encrypting
SELECT Encrypt(myColumn) FROM myTable

-- Decrypting
SELECT Decrypt(myColumn) FROM myTable

When I ran the last command :

-- Decrypting
SELECT Decrypt(myColumn) FROM myTable

I get the following error :

Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.

Where will I use the convert function, in decrypt function or in select statement?

View 9 Replies View Related

SQL Security :: Encryption 2005 - User Defined Function For Encryption And Decryption

Oct 7, 2015

I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.

best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.

SELECT ID FROM table WITH (NOLOCK)
WHERE dbo.DecodeFunction(column) = 'value'

When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.

View 3 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

Data Encryption

Jul 20, 2007

i have to encrypt my data in sql2005 database using assymetric key encryption which i have done properly.But i have a doubt that while writing stored procedure i have to provide key information in it , that will be visible to everyone then whats the use of taht encryption. Can anyone tell me how can i write stored procedure without providing key information in it.

Divya

View 7 Replies View Related

Data Encryption

Jun 14, 2001

I am 99% sure SQL Server 7.0 cannot encrypt data in an individual column. Can anyone tell me whether I am right or wrong?

View 1 Replies View Related

Data Encryption

May 3, 2000

Does anybody know how can I implement data encryption on the sql7.0 database?
I need to do this with some of the fields, like credit card number. My sql server acts as backend database server for IIS servers.

Your help is greatly appreciated!

Donald Ye

View 1 Replies View Related

Data Encryption

Apr 8, 1999

Hi,

I am looking for a way to encrypt a column in my SQL Server Table. I have been looking in the books and have not found anything. Any ideas of how to do it?

View 4 Replies View Related

Data Encryption

Jun 15, 2001

I need assistance on how to encrypt the data in a column.

View 2 Replies View Related

Data And SP Encryption

Apr 20, 2007

hi,
I am a new user to SQL convert my business application from VFP. I am bulding my new application depending on stored procedures. My questions as follow:-

- Is there a way to stored procedures encryption?
- How to products my data structure

regards

View 1 Replies View Related

Data Encryption

Jun 14, 2006

Hi,

We need to set up a data export process from a SQL DB.

The output (be it XML, Text Files or whatever) needs to be encrypted before it is FTPd somewhere.

Is there support for encrption in SSIS? How / where in the package designer would you achive this?

Thanks in advance.

Martin

View 5 Replies View Related

Generate Script For Primary Keys And Foreing Keys

May 16, 2008

Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.

Also how I script default and other constraints of a table?

View 2 Replies View Related

'Use Encryption For Data' Is Not Supported

Sep 4, 2001

The error in the subject line "The property 'Use Encryption for Data' is not supported" is encountered when running a DTS package via a job step in SQL Server 7.0. The problem is not encountered when executing the DTS package interactively via Enterprise Manager from my PC. When I view the Advanced Properties of the OLE DB driver from Enterprise Manager on my PC, I notice a Use Encryption for Data Property. When I view the Advanced Properties of the OLE DB driver from Enterprise Manager on the Server, the Use Encryption for Data Property does not appear. Service Pack 3 for SQL 7.0 is installed on both my PC and on the SQL Server. I suspect this Property difference for OLE DB could be related to the fact that I installed Enterprise Manager for SQL 2000 on my PC at one time and then uninstalled. Any ideas how to correct this situation?

View 1 Replies View Related

SQL Data File Encryption

Jan 4, 2007

Is there any way to encrypt the sql server mdf and ldf data files in SQL Server 2005 without using EFS or creating an additional system user? Only one application which has the password embedded (i know this is a security risk) should be able to connect to the database and access the mdf and ldf files.

Greetings
Roland Mayr

View 1 Replies View Related

Encryption Of Data In SQL05

Jan 19, 2006

My current experience is with access and sql2000. In my current application, I compress/encrypt my data prior to storing in my database. Does the new verion of sql support compression/encryption and if so can you please point me to any links which discuss.

thanks,

Fred Herring

View 5 Replies View Related

Urgent !!!!! Nee Explanation On Primary Keys And FK Keys

Jul 15, 2002

Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.

Is there an article on primary keys/Pk ?

View 1 Replies View Related

Foreign Keys - On Which Kind Of Keys Do The Base On?

Nov 22, 2007

Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz

View 6 Replies View Related

SQL Server Data Encryption And Decryption

Feb 19, 2008

Hi.
I have a SQL Server 2000 database that contains information I would like to encrypt. The information is a field inside a table, and I would like to encrypt this information using a key, and decrypt it in my asp.net application using that key and use the decrypted data.
Please tell me how this can be done, or direct me to an article or a link on the subject.
Thanks in advance.

View 2 Replies View Related

Data Encryption In SQL Server 2000

Mar 13, 2007

I know there is no native encryption in SQL2K, but what 3rd party encryption tools would other forum members recommend from experience? I am required to encrypt data for PCI compliance.

Thanks
Lempster

View 1 Replies View Related

SQL 2012 :: Is Transparent Data Encryption Useful

Oct 12, 2015

We have a large VMWare environment with HP SAN, and 2 datacenters - primary and DR, with SAN based replication between them.We also backup databases to tape via Commvault and send them off-site using a vendor.TDE protects data at 'rest', meaning, (that if it wasn't implemented, and) if someone got a hold of our physical .mdf/.ldf files, or our .bak's, or tapes, they could recover the data.

View 6 Replies View Related

Transparent Data Encryption In SQL 2008

Mar 31, 2008

Hi,
I need to encrypt certain fields of tables with minimized changes required to my application. I want the database only available to my application so I want to use password to protect the encryption key.

Currently, in SQL 2005, I created view and use INSTEAD OF trigger to encrypt/decrypt data to underlyer table. I also having code at application startup, pass my password to a stored proc to open these key so that my view can encrypt/decrypt data accordingly.

I read some posts mentioned that SQL 2008 supports transparent data encryption. May I know how does it help in my case?

Please advice.

Thank you

View 4 Replies View Related

Data Encryption (SQL Server 2005)

Jan 25, 2008

Hello,

I store data in an .mdf file (I use SQL server 2005), because this way it's easier to move the application from one machine to another.

I've faced a problem with the encryption of the database.

Is there a possibility/way to encrypt a database file so that, if someone else finds/copies the mdf, he/she won't be able to read it.

I thought about encrypting the string values of the tables itself and decode them inside the application and encide when Inserting, but why inventing somehing that might already exist.

Thank you.

View 7 Replies View Related

Analysis :: Data Encryption For Cubes

Apr 30, 2015

One of my client requires TDS for all databases. My BI product has reports with OLAP Cube as data source.

How about using BitLocker for the HDD where the abf is physically located.

View 6 Replies View Related

Report Builder And Data Encryption

Apr 16, 2008

Hi,

I have an encrypted SQL Server 2005 database. Data is encrypted and decrypted using the encryptbykey and decryptbykey statements. I would like to allow the end user to be able to build adhoc reports using the Report Builder. I can't figure out how to decrypt the fields as they are dragged onto the report layout. Does anyone have any insight on how to accomplish this, or is it even possible?

Thanks,

Mark

View 5 Replies View Related