从SQL Server 2012迁移到SQL Server 2019:使用EncryptByPassPhrase问题.&

Question

SQL团队将服务器从SQL server 2012迁移到SQL server 2019 UC8后，开始出现问题。问题发生在用户维护期间。基本上是在插入或保存密码时。有一个存储过程包含以下内容:

UPDATE TableOfCreds
SET Password = EncryptByPassPhrase('ThisPassword', @Password)
WHERE User_ID = @UserID

对密码的插入是相同的。在SQL Server 2012中，它按预期运行，在SQL Server 2019中发生以下错误:

Msg 8152, Level 16, State 30, Line 7
字符串或二进制数据将被截断

在微软网站上，它声明SQL Server 2017以后使用AES256密钥，密码短语使用128密钥位长度的TRIPLE DES。不确定这是否是原因，如果是，如何解决它。其他一些细节

• 表中密码列类型为varchar(50)
• 解密似乎按预期工作

在过程之外执行上述脚本会导致相同的错误。

还有一些研究揭示了UDF中的内联问题，但是我们不使用函数，并且将过程转换为函数可能比我们希望的要长。

加密字符串并保存到表中而不接收错误的最佳方法是什么?

Answer 1

返回类型varchar 8000

https://learn.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-ver16

select len(EncryptByPassPhrase('ThisPassword','Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.'))

返回612年