SQL 2012 :: Using Symmetric Key Encryption
Nov 2, 2015
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.
View 3 Replies
ADVERTISEMENT
May 6, 2008
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.
View 2 Replies
View Related
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
Jun 21, 2007
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"
Am I missing something?
Thanks
View 7 Replies
View Related
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
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
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
May 4, 2006
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...
declare @thePassword as varbinary(128)
,@inTargetPassword as varchar(255)
,@pwd3 as varchar(255)
,@theUserId bigint
set @theUserId= 124564
set @inTargetPassword = 'test'
OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE sqlSecurity;
Select @pwd3=EncryptByKey(Key_GUID('Key1')
, @inTargetPassword, 1, HashBytes('SHA1', CONVERT( varbinary, [UserObjectId])))
from table1 where UserObjectId= @theUserId
close symmetric key Key1
View 6 Replies
View Related
Nov 29, 2007
I currently have a login page in asp.net 2.0 linked to a SQL 2005 database table that holds the usernames and passwords. At present, I am on an "honor system" where I do have access to the passwords of the other users but would like to change it so that I cannot know what the users' passwords are. Thank goodness that there is no personal information within the pages and the logins were created to keep a log of who logs in and what not. However, I would like to soon hold more personlized information, hence the need to encrypt each user's password even from myself.
I have read up on Symmetric Encryption for SQL 2005 but I would like to know if there is anything else available, any good proven methods that someone else has already tried.
Also, while testing out Symmetric Encryption, I noticed that I have to supply the encryption password for the decryption. However, if I know what the password for the encryption/decription is, does it not defeat the purpose of having the encryption at all, in terms of the "Admin" having access to sensitive information? Just curious if I understood the concept correctly or not.
Thanks in advance to all.
View 2 Replies
View Related
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
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
Sep 15, 2015
I have a SQL Server 2012 Standard and needed to fulfill a requirement to set Force Encryption on the Server with a DoD Certificate.
I will be altering my client connections to use Encrypt=True; TrustServerCertificate=True (The client is a .Net Web Application).
The OS is where SQL Server 2012 Standard resides is Windows 2008 R2 DataCenter Service Pack 1 64-bit.
What I am needing to know is where do I get the DoD Certificate from? and Do I just install it on the server where SQL Server resides?
View 0 Replies
View Related
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
Nov 3, 2015
I have setup ssl encryption for SQL connections by using a self-signed certificate (not the best I know) and setting "Force Encryption" to Yes. Now I am trying to show that the connection is indeed encrypted and I like to see which the ssl-key is used for a connection; preferably the one that I provided. How can I do this? I am just not comfortable trusting the OS or SQL to do what I ask.The query "SELECT encrypt_option FROM sys.dm_exec_connections" Shows incrypted but no extra info.
View 0 Replies
View Related
Jul 10, 2014
In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
View 0 Replies
View Related
Aug 7, 2014
In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
View 5 Replies
View Related
Sep 22, 2014
How can I create encryption on database ?. When I try to apply encryption on my database then it showing error message below....
View 9 Replies
View Related
Jun 3, 2015
We are planning to encrypt few fields using asymmetric encryption. Tyring share public key with users and retain private key with us. How to generate keys? Haven't found any solid document on how to generate these keys.
View 0 Replies
View Related
Apr 1, 2015
I have a customer who is looking to encrypt their applications connection to the back-end SQL server. I've gotten a valid certificate for the server from a trusted CA, and it's installed on the server (shows up in Personal certificates in the Certificate MMC when selecting the computer) and I can choose it in the drop down on the certificates tab of the SQL Network protocols properties in Configuration Manager.
Now, my question: If I select the certificate but do NOT enable the "Force Encryption" option, the client will be able to request encryption on their end, and the connection will then be encrypted. If the client (other applications) do NOT request encryption, they will still be able to connect, but their connection will be un-encrypted. URL....
Lastly, I could've swore I had found some information indicating that some methods of establishing a connection to SQL can fail when Force Encryption is enabled, as the method doesn't support SSL encryption.
View 1 Replies
View Related
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
Oct 22, 2014
I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
View 1 Replies
View Related
May 30, 2008
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
View 1 Replies
View Related
May 29, 2008
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.
--1) Create master key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Asit'
--2) Create certificate.
CREATE CERTIFICATE CertificateTest2
WITH SUBJECT ='CertificateTest2'
--3) Create SYMMETRIC KEY .
CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertificateTest2
--4) Open SYMMETRIC KEY .
OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE CertificateTest2;
--5) create table.
create table SYMMETRIC1
(
id varchar(100) not null primary key ,
name varbinary(MAX) not null,
card_num varbinary(MAX) not null
);
--6)SEA THE RECORDS
SELECT * FROM SYMMETRIC1
--7)INSERT RECORD IN TABLE
INSERT INTO SYMMETRIC1(ID,NAME,CARD_NUM)VALUES (
EncryptByKey( key_guid('TestSymKey'),'2',1,'SQL Server') ,
EncryptByKey( key_guid('TestSymKey'),'Asit sinha',1,'SQL Server'),
EncryptByKey( key_guid('TestSymKey'),'11',1,'SQL Server') )
--8)FETCH ENCRYPTED RECORD FROM TABLE AND DECRYPT THE VALUE
SELECT
convert(varchar(max),DecryptByKey(id,1,'SQL Server')) as ID,
convert(varchar(max),DecryptByKey(Name,1,'SQL Server')) as Name,
convert(varchar(max),DecryptByKey(card_num,1,'SQL Server')) as Card_Num
FROM SYMMETRIC1
--9)SEA THE RECORDS
SELECT * FROM SYMMETRIC1
Asit Sinha
View 1 Replies
View Related
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
Aug 6, 2007
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?
View 3 Replies
View Related
Aug 6, 2007
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?
View 3 Replies
View Related
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
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
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
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
Nov 4, 2007
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
any suggestions?
Thanks
View 5 Replies
View Related
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
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
View Related