SQL Server 20XX-“客户端不支持加密"使用链接服务器查询编译存储过程时 [英] SQL Server 20XX - "encryption not supported on the client" when compiling stored procedure with linked server query

问题描述

试图在SSMS中编译存储过程，该存储过程包含对链接服务器的打开查询.链接的服务器已连接，并且在我的服务器对象/链接的服务器"文件夹中.我可以在查询选项卡中执行打开查询，但是在过程中运行相同的查询时，出现错误

Attempting to compile a stored procedure in SSMS, which contains an open query to a linked server. The linked server is connected and in my 'Server Objects/Linked Servers' folder. I can do the open query in a query tab, but when the same query is ran within the procedure, I get the error

客户端不支持加密

Encryption not supported on the client

这只是昨天的工作，我知道并没有改变.

This was just working yesterday, with no changes on my end, that I'm aware of.

我在计算机上本地使用SQL Server 2014，并且该过程正尝试使用位于其他位置的普及"数据库连接到链接服务器.不知道哪个版本.

I am using SQL Server 2014 locally on my machine, and the procedure is attempting to connect to a linked server with a "Pervasive" DB, located elsewhere. Not sure which version.

到目前为止，我已经尝试过重新启动，而我们的SQL Server DBA尚未意识到任何其他人的计算机上的问题.

So far, I've tried rebooting and our SQL Server DBA is unaware of an issue on anyone else's machine.

推荐答案

我在其他地方找到了解决问题的方法...不在堆栈溢出中，因此我在这里希望它很方便...

I found the answer to this elsewhere... Not on Stack Overflow, so I wanted it here for convenience...

当发生这种情况时，对于链接服务器，这是由于以下事实:目标服务器所需的加密与请求服务器的加密不匹配，因此无法建立连接.

When this occurs, in regards to a linked server, it is due to the fact that the encryption required by the target server doesn't match that of the requesting server, so connection could not be established.

要解决此问题，您可以尝试卸载与计算机有关的所有SQL并重新安装...，也可以尝试以下操作:

To remedy this issue, you can attempt to uninstall everything SQL related on your machine and reinstall... Or you can attempt the following:

1. 使用命令提示符/Powershell/Cortana，键入"regedit"，然后按Enter.
2. 在注册表编辑器"中，导航到此位置..."HKEY_LOCAL_MACHINE \ SOFTWARE \ WOW6432Node \ Microsoft \ MSSQLServer \ Client \ SNIxx.x"("x代表您的SNI版本..."Sql网络接口" )
3. 首先通过单击文件/导出"将文件夹备份到以下位置，然后将其保存在"HKEY_LOCAL_MACHINE \ SOFTWARE \ WOW6432Node \ Microsoft \ MSSQLServer \ Client \"的任何位置
4. 在您使用的SNI版本为11.0的情况下，您都将进行文件级搜索，并在下面的文件数据中替换SNIxx.x，将版本号替换为您的版本号.
5. 现在在注册表编辑器中，单击文件/导入"，然后导航到保存新文件的文件夹，双击该文件，或突出显示该文件，然后单击打开".

这是该文件中需要存在的数据...

Here is the data which needs to exist in that file...

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\DB-Lib]
"AutoAnsiToOem"="ON"
"UseIntlSettings"="ON"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x]
"ProtocolsSupported"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,  
00,00,00,76,00,69,00,61,00,00,00,00,00
"ProtocolOrder"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,00,  
00,00,00,00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\GeneralFlags]
"NumberOfFlags"=dword:00000002

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\GeneralFlags\Flag2]

"Label"="Trust Server Certificate"
"Value"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\LastConnect]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\np]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000001
"ProtocolName"="Named Pipes"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\np\Property1]
"Name"="Default Pipe"
"Value"="sql\\query"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\sm]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000000
"ProtocolName"="Shared Memory"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\tcp]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000003
"ProtocolName"="TCP/IP"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\tcp\Property1]
"Name"="Default Port"
"Value"=dword:00000599

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\tcp\Property2]
"Name"="KEEPALIVE (in milliseconds)"
"Value"=dword:00007530

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\tcp\Property3]
"Name"="KEEPALIVEINTERVAL (in milliseconds)"
"Value"=dword:000003e8

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\VIA]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000002
"ProtocolName"="VIA"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\VIA\Property1]
"Name"="Default Server Port"
"Value"="0:1433"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNIxx.x\VIA\Property2]
"Name"="Default Client NIC"
"Value"="0"

这篇关于SQL Server 20XX-“客户端不支持加密"使用链接服务器查询编译存储过程时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！