Missing Column Encryption checkbox on ODBC setup on Win 11

[MathiasB]

I have a new Win 11 laptop and the checkbox to enable column encryption is missing. Anyone seen this. It doesn't work in code either. To be clear, the checkbox is present on a machine upgraded from win 10 to 11.

lcConnStr = [Driver={ODBC Driver 13 for SQL Server};Server=LAPTOPXXXX\SQLEXPRESS;UID=xxxxxx;PWD=xxxxxx;APP=app name;DATABASE=bdname;ColumnEncryption=Enabled;]
nConn = SQLStringConnect(lcConnStr)

With column encryption on table, a select query on this connection will return plain data. Again it works on all 3 Win 10 machines. But they all have the Column Encryption checkbox just below Transparent Network IP Resolution.

I have ruled out the certificate being invalid because even SQL server errors if I delete the certificate. SQL server happily decrypts the encrypted columns when the certificate is re-imported back. It appears to be a win 11 issue. I have inspected the components of ODBC Data Sources app. They are identical on the 2 win 11 machines. It works as expected on the one upgraded from win 10.

I have attached the screenshot

Thanks in advance

Mathias

 

[Chris Miller]

I can't tell you why the ODBC driver dialog has this checkbox in one case and not the other, but do you have the exact same ODBC driver version on both?
And then, does it work to add the ColumnEncryption=Enabled option in the connection string or is that ignored?

I just can compare this to the ODBC configuration dialog of MySQL drivers that differ by version a lot and also don't have UI for every single option you can encode in the MySQL options parameter. So lack of a checkbox in a dialog is not necessary lack of that feature.

Without Win11 I can't help you further than that, i.e. to recommend looking into the exact ODBC Driver versions you have on each computer. ODBC Manager will tell you which DLLs are used and Windows Explorer properties will tell the file version information, if not ODBC Manager already tells you enough about the driver version.

What could change from win 10 to win 11 and could also differe depending on whether you upgrade to win11 or install win 11 on a blank machine is how network settings are configured regarding many details as are usually stored in the registry. So you could look into that. I'm not a network expert to tell you which configurations settings reflect where in the registry, if at all.

Chriss

 

[tomk3]

Hello,

using Win11pro with "odbc driver 17 for sqlserver" I have the checkbox encrypt.
(ODBC Driver for SQL Server Version 17.10.0005)

Regards
tom

 

[MathiasB]

Thanks guys,

The ODBCData Sources application shows 6 components on the About tab.
On both systems where it works and the one where it doesn't work the versions show as follows:
Description Version File
Administrator 10.0.22621.1 \system32\odbccp32.dll
Control Panel Startup 10.0.22621.1 \system32\odbcad32.exe
Cursor Library 10.0.22621.1 \system32\odbccr32.dll
Driver Manager 10.0.22621.3085 \system32\odbc32.dll
Localized Resource DLL 10.0.22621.1 \system32\odbcint.dll
Unicode Cursor Library 10.0.22621.1 \system32\odbccu32.dll

I can confirm, those details are identical on both systems. I am assuming they're the files that drive the app. As far as I know the process is achievable in code. I have:
========
Local lcConnStr, lnResult, lcTSQL, lcAlias, llReturn
lcConnStr = [Driver={ODBC Driver 13 for SQL Server};Server=xxxx\yyyy;UID=xxxx;PWD=xxxx;APP=app name;DATABASE=dbname;ColumnEncryption=Enabled;]
gnConn = SQLStringConnect(lcConnStr)
lcAlias = '_system'
Use In Select(lcAlias)
lcTSQL = "Select Top (1) * From [tblwithEncrypedtColums]"
lnResult = SQLExec(gnConn, lcTSQL, lcAlias)
If lnResult <> -1
llReturn = Used(lcAlias)
EndIf
If llReturn
Select (lcAlias)
Browse normal
EndIf
=====
Code runs successfully on both just the encrypted columns are still encrypted, they come through as memos as expected

Mathias

 

[Chris Miller]

I wasn't awsking about the DLLS of the ODBC Data Source Administrator application.

Within it you find a list of drivers here:

It's these drivers that provide the dialog you mention, not Windows, the dialogs to configure settings are individual per driver, not per Windows. But since SQL Server is also a Microsoft product it can of course differ which drivers are available.

If you don't find the same SQL Server driver versions - and there are a lot of drivers and even families of drivers, then that explains why you sometimes have some features and sometimes not. It would surprise me if you find the same versions and they offer the different dialogs. In general I'd say with every new Windows version the list of standard drivers is updated, but I also know you find quite old drivers listed, no matte which Windows version. It's clear an upgraded Windows version will keep the drivers you already used before, while not upgrading but installing Windows on new systems, you can't expect all drivers in all versions to be available, the current version of ODBC Driver for SQL Server is number 18, so I'd surely not expect version 13 preinstalled on Windows 11.

To add the driver you need you find listings of drivers older releases for SQL Server at

https://learn.microsoft.com/en-US/s...ndows?view=sql-server-ver16#previous-releases

That also includes the driver version 13 and 13.1. Maybe - I'm quote sure, but you never know - after you install those, you'll find the feature again.
PS: I'm aware you do have a version 13 driver installed already, otherwise you wouldn't even get a connection, but as said there is a 13 and 13.1 version and I would even bet there are different DLL versions of each driver vetrsion available, i.e. file version can differ from driver version.

Chriss

 

[MathiasB]

Thank you so co much Chris, that was it. It was a lower version of 13 (2015.130.811.168) installed.

Upgraded it (2015.131.4414.46) the checkbox is now available and it works in code as expected

Mathias

 

[Chris Miller]

Okay, not sureif 2015.131.4414.46 is also 13 or 13.1, but good to know there are different 13 vesions. It's bad if you have different version drivers with the same driver name, because you, of course, expect drivers of the same name to have the same features. Even if one driver was just a bugfix of the other.

Unfoprtunately MS Windows Update feature doe update Windows itself and MS Software, also maybe some hardware drivers, although that's often done with a secondary update software, for example Acer does provide such things for their PCs and laptops. ODBC drivers are not within that category, also because an automatic update exceeding version numbers could break a DSN from working. Unfortunately also the other way around, sometimes not upgrading to at least the latest version of the same major version could cause the trouble you had.

There's no easy way to see which server is used, even though a DSN using a driver gives a mean of making the conection to the server and finding out the server version to pick the latest driver. Well, all in all it's only doable in a Windows upgrade situation, anyway, where you already have a defined DSN, and in such a situation keeping the drivers mostly means there's no such problem anyway. If you install a system new, you have to install ODBC drivers yourself, I guess you just didn't know how to find out which version was the exactly necessary version. For future readers, the list of drivers helps, but in the end you may also need to look into the DBC dlls and that requires finding them, the ODBC drivers are installed in different places, there's not a Windows system directory for ODBC drivers, they can go into one of the 32bit or 64bit Program Files directries, they can be part of a software or standalone. Unfortunately the ODBC manager does only show the DLL file names (not sure drivers could also be sys files or exes), but not their location.

The essence of this could be a recommendation to ODBC driver vendors, that's not limited to MS, to make driver names unique and only ever name two different driver versions the same, if they have the exact same feature sets, so such situations would point out a failure early on. The behavior of ignoring options given in a connection string a driver doesn't support also would be helpful, a warning instead of an error would already help to figure that out.

Chriss