SQL Server 20XX - "encryption not supported on the client" when compiling stored procedure with linked server query

Question

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.

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.

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

Answer 1

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.

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

1. Using Command Prompt / Powershell / Cortana, type, 'regedit' and hit enter.
2. In 'Registry Editor,' navigate to this location... 'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SNIxx.x' (The 'x's represent your version of SNI... "Sql Network Interface")
3. Backup the folder at the following location first, by clicking, 'File/Export' and save it wherever, 'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\'
4. Whatever version of SNI you have, in my case 11.0, you will do a file level search and replace, in the file data below, for SNIxx.x, replacing the version number with your's.
5. Now in Registry Editor, click, 'File/Import' and navigate to the folder where you've saved your new file, double click it, or highlight it and click, 'open'.

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"

Answer 2

I had a similar problem. The issue was that I had old versions of the SQL tools installed and the computer PATH was still setup to reference the 100 version before the 110 version.

For example, both 2008 and 2012 were installed.

C:\Program Files\Microsoft SQL Server\100\Tools\Binn

C:\Program Files\Microsoft SQL Server\110\Tools\Binn

Reference folder versions here: SQL Server file names vs versions

I simply referenced the newer version and the problem was resolved.