SQL Server: can I save binary data in nvarchar column?

Question

I know that you can convert binary data to nvarchar and save in the nvarchar column, and then return it back to binary when it is extracted. Will this work correctly all the time to provide you with raw binary data? Or can something be mixed up in translation?

I ask because we have an nvarchar column that stores passwords in plain text. I would like this to change to save PW in ciphertext. If I use the EncryptByPassPhrase function, it returns varbinary . I am wondering if I can just convert the output from EncryptByPassPhrase to nvarchar and save the "Password" in the same column. This would be easier than creating a new varbinary column to save the encrypted PW.

Therefore, I suggest converting current passwords as follows:

 UPDATE Users SET Password = CONVERT(nvarchar(200), EncryptByPassPhrase('whatever', Password)) 

Then I would decrypt and extract the PW as:

 SELECT CONVERT(nvarchar(200), DecryptByPassPhrase('whatever', Password)) AS PW FROM Users 

Will this work correctly?

Thanks in advance!

Answer 1

Ignoring whether this is a good idea, the following query seems to indicate that all two byte mills are rounded forward.

 WITH T1 AS ( SELECT TOP 65536 CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 32769 AS SMALLINT) RN FROM master..spt_values v1, master..spt_values v2 ), T2 AS ( SELECT CAST(CAST(RN AS BINARY(2)) AS NCHAR(1)) AS NC, RN FROM T1 ) SELECT * FROM T2 WHERE CAST(CAST(NC AS BINARY(2)) AS SMALLINT) <> RN