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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Converting VARCHAR to VARBINARY Issue

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)
GO


Insert Statement is:
INSERT INTO CUSTOMER VALUES ('555-55-5555', 'ABC', 'XYZ')

I get the following error:
Msg 257, Level 16, State 3, Line 1
Implicit 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')
Go to Top of Page

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
Go to Top of Page

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 1
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
Go to Top of Page

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)
Go to Top of Page

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 TestDB
GO
OPEN SYMMETRIC KEY DBTestSymmKey
DECRYPTION BY CERTIFICATE DBEncryptionCert
UPDATE 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 Customer
DROP 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.
Go to Top of Page

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?
Go to Top of Page

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2010-02-01 : 10:24:52
Yes, I have to encrypt '555-55-5555'.
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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 TESTDB
GO
OPEN SYMMETRIC KEY DBTestSymmKey
DECRYPTION BY CERTIFICATE DBEncryptionCert
INSERT INTO CUSTOMER(EncryptCustSSN,CustFName, CustLName)
SELECT ENCRYPTBYKEY(KEY_GUID('DBTestSymmKey'), '555-55-5555'), 'ABC', 'XYZ'
GO
Go to Top of Page
   

- Advertisement -