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
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;
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
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...
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 ?
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?
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
--------------- @@@@@@@@@@@@@@ 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]
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.
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.
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?
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?
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.
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,
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,
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?
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?
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?
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
Respectd sir thanks for your reply but my issue is different. In my database have lots of table, In each table have different datatype field. we want to just encrypt these data. As example i am declare one table
TableCD --------- CDID Integer Not Null VolID Integer NotNull CDRegDate DateTime CDData1 BigInt CDAmtInWord Varchar(100) not null
Data Is CDID VolID CDRegDate CDData1 CDAmtInWord 1201/10/2008102014521 cr. 1302/10/2008102014531 cr.
our requirement is just encrypt this data with symmetric key and decyrpt this data. Please immediate reply me. Thanks Asit Sinha
Hi - this is a repost of a question that I originally posted in Security. Ok, I'm very new to this topic. I'm working on an application that requires that some information in the db be encrypted and then decrypted when retrieved. I have everything set up and it works fine except for one thing. I can't seem to be able to pass a parameter into the sp that is used to decrypt the key. It only seems to take the string when typed in. I really think I'm missing something here. It doesn't seem all that great to have your password hard-coded into the stored procedure. Maybe I'm just screwing something up? Anyway, I can't get it to work if it looks like this:OPEN SYMMETRIC KEY Key_NameDECRYPTION BY PASSWORD = @pwdThis does work:OPEN SYMMETRIC KEY Key_NameDECRYPTION BY PASSWORD = 'password'This has to be some goof on my part right? If the db machine is compromised you're giving the keys to decrypt the data away as well - they just haver to open your stored proc. You should keep them separate imo and I hope someone can set me straight. Also, encrypting the stored procedure is an option, but it's very easy to decrypt from what I've read. Can someone help point me in the right direction? Thanks! And thanks to the mod that suggested moving this post. Any help will be appreciated.
I am working to set up encryption of my database. am trying to implement a process proposed by the DoD Standard Technical Implementation Guide (STIG) and cannot figure it out. I am using SQL 2012 and the requirement is to
A: Run the following to create a certificate: USE <'database name'> CREATE CERTIFICATE <'certificate name'> ENCRYPTION BY PASSWORD = '<'password'>' FROM FILE = <'path/file_name'> WITH SUBJECT = 'name of person creating key', EXPIRY_DATE = '<'expiration date: yyyymmdd'>'
This script did not work – I did not know what file it was referencing and it kept erroring out, so I used my own process as follows
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' CREATE CERTIFICATE certificate_name WITH SUBJECT = 'Certificate for my database'
[code]....
This also error out, but my questions are the following:
-What would the symmetric key be encrypting? -The application that is using the database is IIS, so would there be a problem with it communicating with the database? -Does the key need to be installed on that IIS server? -Do I need to apply the encryption to the database, column or table.
Hi I am using sql server 2005, I want to encrypt data and i am using Symmetric key. In Symmetric key encrypt the varchar , varbinary data encrypt but for integer it show the error. so please suggest me can we encrypt the integer data. if yes then how ? In below example if we use integer then encrypt it will show error.
I have symmetric key named "PasswordKey" in a database in a default instance. The key works correctly in the default instance. However, I have a report server in an instance named "sqlexpress" that also needs to decrypt a column using the same key. I get an error that states: Cannot find the symmetric key 'PasswordKey', because it does not exist or you do not have permission.
What permission does the symmetric key need? Someone suggested granting permission to the database role but what role in the named instance needs permission?
grant references on symmetric key :: PasswordKey to user
Who is the user if the report server is in an instance named sqlexpress?
I have symmetric key named "PasswordKey" in a database in a default instance. The key works correctly in the default instance. However, I have a report server in an instance named "sqlexpress" that also needs to decrypt a column using the same key. I get an error that states: Cannot find the symmetric key 'PasswordKey', because it does not exist or you do not have permission.
What permission does the symmetric key need? Someone suggested granting permission to the database role but what role in the named instance needs permission?
grant references on symmetric key :: PasswordKey to user
Who is the user if the report server is in an instance named sqlexpress?
Please be gentle...I am very new to working with SQL.
I have the need to encrypt my columns in SQL 2005. I have created a symmetric key 'SecureKey' as well as a secure certificate 'SecureCert'
I have ran the script to create the key and the certificate successfully. When I run "select * sys.symmetic_keys;" the key shows up...when I run "select * sys.certificates;" the cert shows up.
Here is my issue, in the security folder under my database, these two things do not show up in the appropriate folders. Also when I run my encryption scripts, I am getting an error of
"msg 15151, Level 16, State 1, Line 3
cannot find the symmetric key 'SecureKey', because it does not exist or you do not have permission"
I create a certificate and a symmetric key encryption by it. Then i create a user and grant the view definition permission of both object to the user.
But it return a error message "Cannot find the certificate 'cert1', because it does not exist or you do not have permission" when execute the open symmetric sql statement as the user.
It' very surprised that it return a string when execute encryptbyCert function ,but it return null when execute decryptbyCert function...
Code Block create certificate cert1 encryption by password='P@ssw0rd1' with subject='cert protected by self' create symmetric key key1 with algorithm=rc2 encryption by certificate cert1 create user user1 without login grant view definition on certificate::cert1 to user1 grant view definition on symmetric key::key1 to user1 execute as user='user1' open symmetric key key1 decryption by certificate cert1 with password='P@ssw0rd1'--return a error /* Msg 15151, Level 16, State 1, Line 0 Cannot find the certificate 'cert1', because it does not exist or you do not have permission. */ declare @n varchar(100) set @n='aa' declare @c varbinary(1000) select @c=encryptbycert(cert_id('cert1'),@n)---return a encrypt data select convert(varchar,decryptbycert(cert_id('cert1'),@c,N'P@ssw0rd1'))--return null
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.
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
SSRS had been working fine on my comp till the time i insatlled VS 2005. I have started getting following error since VS2005 install
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 and then restart the service. Check the documentation for more information. (rsReportServerDisabled) Get Online Help Bad Data.
Why does this error cropped and how can i fix it? I am using SQL Server 2000.
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)
Hey I had a table with a column of data encrypted in a format. I was able to decrypt it and then encrypt it using Symmetric keys and then updating the table column with the data. Now, there is a user sp which needs to encrypt the password for the new user and put it in the table. I'm not being able to make it work. I have this so far. Something somewhere is wrong. I dont know where. Please help Thanks. I used the same script to do the encryption initially but that was for the whole column. I need to see the encrypted version of the @inTargetPassword variable. But it's not working. It doesn't give me an error but gives me wrong data...
