Create Identical Symmetric Keys On Two Servers?

Apr 17, 2015

why my script is not allowing me to DECRYPTBYKEY once I restore my DB from PRODUCTION BACKUP....

-- SET Staging to Single User Mode to be able to RESTORE DB---

-- STEP 1 (Works No Problems Here)

USE master;
GO
ALTER DATABASE Staging
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE Staging
FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMyDBRestore.bak' ;
GO

-- STEP 2 - USE ONLY IF THE ABOVE IS UNSUCCESSFUL ||| FAILURE ****** RESTORE RUN THE FOLLOWING SCRIPT -----

--If the above is successful the DB sets itself back to MULTI_USER

--ALTER DATABASE Staging
--SET MULTI_USER;
--GO
--ALTER DATABASE Staging
--SET READ_WRITE
--GO

-- STEP 3 (Works No Problems Here)

--------------- @@@@@@@@@@@@@@ IMPORTANT UNCOMMIT AND RUN @@@@@@@@@@@@@@@@@@@@@@@@@ MANUAL STEPS ----------------------
-- RBD - Recreate Security ID'S AND PERMISSIONS FOR Stored Procedure EXECUTE RIGHTS, because PASSWORDS are different on lower
-- environments
--USE [Staging]
--GO
--/****** Object: User [WebUser] Script Date: 4/13/2015 11:15:51 AM ******/
--DROP USER [WebUser]

[code]....

View 1 Replies


ADVERTISEMENT

Moving Symmetric Keys Between Servers

Apr 24, 2007

I'm having some issues restoring a backup of database that uses native encryption onto another server. I know there are a couple of articles on this but I seem to be missing something. Any help would be greatly appreciated.

Current Server Windows 2000
Destination Server Windows 2003


The original key setup for the current server was achieved by something like this:

CREATE SYMMETRIC KEY HR01 WITH algorithm=DES encryption BY password = 'HRpassword'


Running the command select * from sys.symmetric_keys on the current server I get the following:






name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid

HR01
1
256
56
D
DES
2006-11-22 16:36:01.883
2006-11-22 16:36:01.883
BBD80500-338F-47D7-B336-85D46E00F2F0
So I restored the database onto the new server and ran this script:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

I don't have a master key... so I go back to the original server and ran this script and did another backup:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Running select * from sys.symmetric_keys on the current server now looks like this:





name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid

##MS_DatabaseMasterKey##
1
101
128
D3
TRIPLE_DES
2007-04-23 16:03:09.183
2007-04-23 17:02:46.630
1CB0D800-0173-4A1E-B841-362B454E60AC

HR01
1
256
56
D
DES
2006-11-22 16:36:01.883
2006-11-22 16:36:01.883
BBD80500-338F-47D7-B336-85D46E00F2F0



I then restored the new backup onto the 2003 server and ran this script:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

The alter script now runs fine but I get the error message: The decryption key is incorrect when trying to open the HR01 key




View 1 Replies View Related

Which Algorithm To Be Used With Symmetric Keys

Mar 28, 2006



Hi,

I want to create a symmetric key that will be encrypted by certificate key. Can u guide me which algorithm is best out of the following:

DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, AES_256.

I tried using AES_128, AES_192, AES_256 but it says 'the algorithm specified for the key are not available in this installation of Windows.'

Pls tell me which else algorithm is best to use and pls specify why.

Thanks

Gaurav

View 5 Replies View Related

Replacing Symmetric Keys

Feb 2, 2007

I'm trying to develop a procedure for re-encrypting data with a different symmetric key, but I need the new key to use the old key name. I want to do this without leaving the data in clear text at any point. I would think the process would work like this:

1.Create a new key
2.Decrypt data w/old key and encrypt with new key.
3.Drop old key.
4.Rename new key using the old key name.


However, I can't find that there's a way to rename a symmetric key. Is this correct? If that is the case, I believe I have to do an additional round of encryption:

1.Create a temp key
2.Decrypt data w/old key and encrypt with temp key.
3.Drop old key.
4.Create a new key with the old key name.
5.Decrypt data w/temp key and encrypt with new key.
6.Drop temp key


Thanks for any insight.

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

Retrieving Symmetric Keys From Database.

Apr 9, 2007

I've played with various configurations of the MS SQL Server encryption functionality, and come across an embarrassingly easy question that I cannot seem to resolve. How do I retrieve the actual symmetric and asymmetric keys out of the database?



I'd like to explore the possibility of off-loading the encryption/decryption work from the database server to a load-balanced pool of servers. For this model to work the pool would need access to the keys. The symmetric keys currently are generated with the command...



create symmetric key EncryptionKey211

with algorithm = AES_256

ENCRYPTION BY certificate CreditCardCert



Am I missing something obvious here?

View 4 Replies View Related

Max Number Of Symmetric Keys In A Database

Sep 26, 2007



Is there a limit on number of symmetric keys/asymmetric keys/certificates that can be stored in a database?

Is there any effect on performance of the SQL Server if I have too many (few hundreds) symmetric keys in the database?

thanks

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

Data Encyption Using Symmetric Keys Outside SQL Server

Jan 30, 2007

Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.

I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.

Please advise me if my approach is correct and what steps I need to take.

View 5 Replies View Related

Opening Symmetric Keys In Stored Procedures

Apr 26, 2007

I can't seem to use symmetric keys in stored procedures. I am trying to decrypt a column in my stored precedure but the values are all NULL. First I tried a symmetric key that required a password but I kept getting simple syntax errors when I tried to send the password to the stored precedure as a parameter. Now I am trying a symmetric key that uses a certificate.



I also tried using WITH EXECUTE AS SELF and WITH EXECUTE AS CALLER but that made no difference.

View 1 Replies View Related

Reporting Services :: Recovering Symmetric Key From Keys Table

Jul 29, 2015

We had a server give out on us completely. All that is left is a back up of the SSRS database. We have been able to get a new SSRS environment working but the symmetric key doesn't seem to work.  Reports will run if we delete all sensitive data through the Config manager and reenter the connection strings into the datasource but if we can get the symmetric key saved in the ReportingServices database to work correctly it will save us a ton of effort. Can we make use of the symmetric key saved in the ReportServer.dbo.Keys table?

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

Our Report Servers Are Constantly Getting The Symmetric Key Error - Why Does This Happen.

Feb 6, 2007

Our report server are constantly getting the below error.

What causes this - I know how to fix it, in fact, I've automated it but why does it constantly happen on some servers? I guess I'd like to know what causes it to try and fix it at those points instead of having to fix it here. Proactively.



Reporting Services Error



The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help



The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)



The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)



Bad Data. (Exception from HRESULT: 0x800900



Why

View 3 Replies View Related

SQL Script To Compare Two Identical Tables On Two SQL Servers

May 23, 2007

Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.

I like to compare them and find and view the differences in records.


Thanks for any help.

Abrahim

View 3 Replies View Related

Create Script To Create/Refresh Identical Database

Mar 26, 2008



I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.

I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.

Any help would be appreciated. Thanks!

View 9 Replies View Related

Key_source Parameter In Create Symmetric Key

Apr 6, 2006

Hi Laurentiu

I was reading a blog entry of yours http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx and in the section "How to prevent a symmetric key loss", you mentioned key_source.

I am trying to understand how this is used because when you open a symmetric key, the only options for decrypting the key are password, symmetric key, asymmetric key and certificate.

Thanks

View 3 Replies View Related

Unable To Create A Function By Using Symmetric Encryption

Jan 29, 2007

Msg:

Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.

"open symmetric keys" is not allowed in a function?

if I want to encrypt a string in a function by sql2005's internal functions ,how can I do ?

View 3 Replies View Related

How To Find A Symmetric Key That Protected Other Symmetric Key

Jan 22, 2008



Say i have some symmetric keys encrypted by other symmetric keys ,
is there some way to find the relationship of these key?

I know i can find the certificate or asymmetric key through sys.key_encryptions catalog view by "thumbprint" column,
but the thumbprint column is null when the key was encrypted by a symmetric key.


any suggestions?


thanks in advance

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

Identical Database W/ Identical Stored Procedures?

Oct 25, 2005

We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel

View 4 Replies View Related

DTS - Primary Keys Drop When Transferring Tables Between Servers

Sep 7, 2000

Hello All,

Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.

Many thanks in advance for any and all help,

Gurmi

View 1 Replies View Related

Create Table With Foreign Keys

May 30, 2007

How do I create a table with a foreign key

View 6 Replies View Related

Create An Indexes/Keys Property With T-SQL

May 19, 2004

is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property

thanx

View 5 Replies View Related

T-SQL (SS2K8) :: Combining Keys To Create A Master List?

Mar 5, 2014

I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:

Original Data
key1 key2
647942600014
647942285437
2324662490640
2324662285437
2324662066128
2222 2285437
2222 1111111
9999 1111111
9999 2222222

Should look like:

22222600014
22222285437
22222490640
22222066128
22221111111
22222222222

I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.

I tried playing with self joins between the tables but have had no success.

I am using Sql Server 2008 and the number of records could 500K to 1MM.

View 2 Replies View Related

SQL 2012 :: Create Table With Both Keys As Foreign (error)

Dec 15, 2014

I'm trying to create a table in Microsoft Server Management Studio 2012. The table has two fields which are both foreign keys.

I created the following:

create table tblRoomEquipment(
RoomID nvarchar(8),
EquipmentType nvarchar(1),
foreign key (RoomID) references tblRoom(ID),
foreign key (EquipmentType) references tblEquipment(Type)
)

Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!

Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.

View 6 Replies View Related

How To Create Apps That Write Code To Retrieve Data With Foreign Keys?

Apr 6, 2007

Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks

View 2 Replies View Related

Update Rows To Resolve Issues About Duplicate Keys On Create Unique Index

May 7, 2008





Hi there ...here comes a tricky one.

I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.
This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!

I can find those rows by

SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0
WHERE EXISTS (
SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1
WHERE table0.ParentREF = table1.ParentREF
AND table0.UniqueName = table1.UniqueName
AND table0.OID != table1.OID
)
ORDER BY ParentREF, UniqueName, ModifiedUTC desc

...but I struggle to make the required SQL (SP?) to update the "invalid" rows.
Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!

ATM the preferred (cause easiest) way is to rename the invalid rows with
UniqueName = OID
because if I use any other name I risk to create another double entry.


Thanks in advance to whoever can help me

View 4 Replies View Related

Trying To Create A View Across Two SQL Servers

May 20, 2008

I have two machines running SQL server. I need to create a view that contains data from both servers on Server 2.

ex:
Server 1 contains:
database (Employees)
Table (EmpMaster)
Column (EmpNumber)
column (EmpName)


Server 2 contains:
database (Training)
Table (TrainingEntry)
Column (EmpNumber)
Column (TrainingDate)
Column (TrainingDescription)

If the two tables were on the same server and in the same database, I'd just write the following:

select
TrainingEntry.EmpNumber
,Employee.EmployeeName
,TrainingEntry.TrainingDate
,TrainingEntry.TrainingDescription
from TrainingEntry
Left Outer Join Employee on TrainingEntry.EmpNumber =
Employee.EmpNumber

The question is how do I handle going between two databases, and more importantly, how do I handle linking between two servers?

All examples are appreciated.

Thanks

View 6 Replies View Related

I Want To Create Link With 2 Servers

Jul 27, 2006

I want to create link with 2 servers is that possible to do that. for example is like this,
----------------------------------------------------------------------------------------------------------------
SELECT Products.ProductID,
Products.ProductName,
Products.CategoryID,
Categories.CategoryName
FROM [SERVER1].[Northwind].[dbo].[Products]
INNER JOIN
[SERVER2].[Northwind].[dbo].[Categories]
ON [Products].[CategoryID] = [Categories].[CategoryID]


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

Products table has SERVER1 and Categories table has SERVER2

View 6 Replies View Related

How To Create And Schedule A Job Using Linked Servers?

Apr 4, 2006

There are 2 sql servers : A and C.
I have to grab data on server A and update records with this data on server C. It has to run every night, so it has to be scheduled as a job.
I created linked server on server A and ran query select... It works.
I can create Update query.
What are my steps next? I read how to create a job, but not understand how to incorporate the query into it. I am totaly unexpirenced user, please explain step by step or give me a link to a good explanation. I also do not understand how and whether DTS should be used in all this.
Appreciate any help.

Ann2

View 2 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Create View With Data From Multiple Servers

Jul 23, 2005

Hi everyone,I have 5 servers, all with identical databases just different data. Ihave a rather lengthy SQL statement (in a View) to hit one database andpull-in certain data, but I'd like to somehow run this same SQLstatement within the view but hit all 5 servers so we don't have 5different versions of this data to mess with.I'm not opposed to creating an update query in a stored procedure tohit all 5 databases and update a table or even do this within a DTS,but I'd prefer to keep it as simple as possible and as dynamic so theusers can simply run the view and get live data anytime based on all 5tables.Is this possible ???Thanks,rlangly

View 2 Replies View Related







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