Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2010-01-29 : 09:38:50
|
I am working on database encryption and while am testing this, i came across an issue where i am not able to insert data after the column is being changed from Varchar to VARBINARY.Let me give you the details:I have a following table where i am going to encrypt the CustSSN column.CREATE TABLE Customer (CustID int identity(1,1) PRIMARY KEY,CUSTSSN VARCHAR(11),CUSTFNAME VARCHAR(25),CUSTLNAME VARCHAR(25),)I have inserted the data before the encryption, it worked fine I was able to insert the records, but after the encryption with the following column, I am not able to insert a record. I have created a Master key, Certificate and a Symmetric key for the encryption. After this, I have altered the column CustSSN as below:ALTER TABLE Customer ADD EncryptCustSSN VARBINARY(256) GOInsert Statement is: INSERT INTO CUSTOMER VALUES ('555-55-5555', 'ABC', 'XYZ')I get the following error: Msg 257, Level 16, State 3, Line 1Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.How can I use the convert function to conver from varchar to varbinary for EncryptCustSSN record. I am trying to convert within the INSERT statement. Thanks for your help and let me know if the question is not clear. |
|
agiotti
Starting Member
37 Posts |
Posted - 2010-01-29 : 23:33:20
|
Try something like this.DECLARE @X varbinary(256)Set @X = CAST('555-55-5555' As varbinary(256))INSERT INTO CUSTOMER VALUES(@X, 'ABC', 'XYZ') |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 03:02:41
|
I think you can put the CAST directly into a VALUES list (saves the working variable)If not then:INSERT INTO CUSTOMER SELECT CAST('555-55-5555' As varbinary(256)), 'ABC', 'XYZ' should save that step |
|
|
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2010-02-01 : 09:40:49
|
kristen, I still get the following error. I used the second statement. Msg 257, Level 16, State 3, Line 1Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 09:51:24
|
The examples here are only providing 3 columns, and the table has four columns, is that right?How does EncryptCustSSN get populated from CUSTSSN? I expect that is the problem (i.e. put an explicit CAST in that process as per the above examples) |
|
|
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2010-02-01 : 10:06:30
|
Yes, there are 4 columns in the table but the whole concept is, i am going to drop the CUSTSSN field from the table, i have populated the EncryptCUstSSN from CUSTSSN field using the UPDATE STATEMENT.USE TestDBGO OPEN SYMMETRIC KEY DBTestSymmKeyDECRYPTION BY CERTIFICATE DBEncryptionCertUPDATE Customer SET EncryptCustSSN = ENCRYPTBYKEY(KEY_GUID('DBTestSymmKey'), CustSSN)GO--Drop the CustSSN column, if needed, or I can keep this column for the reference to compare.ALTER TABLE CustomerDROP COLUMN CUSTSSN After i drop the CUSTSSN column, EncryptCustSSN column is the one which has the encrypte SSN and i want to insert records into this table (CustID,EcnryptCustSSN, FName, LName), where CustID is an Identity Column. Let me know if this is still not clear or still confusing. Thanks for your help. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 10:13:55
|
My thinking is that after you ADD EcnryptCustSSN it will be the last column in the table.If you don't use a Column List after insert then the columns must be presented in Ordinal sequence; this will explicitly match Columns and Data values:INSERT INTO CUSTOMER(EcnryptCustSSN, FName, LName)SELECT CAST('555-55-5555' As varbinary(256)), 'ABC', 'XYZ' although presumably you have to encrypt "555-55-5555", rather than just Cast it to varbinary? |
|
|
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2010-02-01 : 10:24:52
|
Yes, I have to encrypt '555-55-5555'. |
|
|
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2010-02-01 : 10:27:03
|
Ok, that statment work, at least to insert the record, so how can i encrypt it now? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 10:45:32
|
Sorry, no idea about how to encrypt it. Is it not similar to how you have above?... get encryption key etc ...INSERT INTO CUSTOMER(EcnryptCustSSN, FName, LName)SELECT ENCRYPTBYKEY(KEY_GUID('DBTestSymmKey'), '555-55-5555'), 'ABC', 'XYZ |
|
|
sql_newbie121
Yak Posting Veteran
52 Posts |
Posted - 2010-02-01 : 11:41:31
|
Kristen, Thank you very much, it solved the problem. I did use the following command to encrypt the record on EncryptCustSSN field. Use TESTDBGOOPEN SYMMETRIC KEY DBTestSymmKeyDECRYPTION BY CERTIFICATE DBEncryptionCertINSERT INTO CUSTOMER(EncryptCustSSN,CustFName, CustLName)SELECT ENCRYPTBYKEY(KEY_GUID('DBTestSymmKey'), '555-55-5555'), 'ABC', 'XYZ'GO |
|
|
|
|
|
|
|