1

I want to use Always Encrypted feature introduce in SQL Server 2016 SP1. In order to do that, I need to use the new ODBC Driver 13.1 for SQL Server instead the current one sqloledb.

It seems it is breaking my application, for example, when XML is returned. Here, it is said that:

In order to take advantage of new features introduced in SQL Server 2005 such as multiple active result sets (MARS), query notifications, user-defined types (UDTs), or the new xml data type, existing applications that use ActiveX Data Objects (ADO) should use the SQL Server Native Client OLE DB provider as their data access provider.

So, that's this mean that I need to rewrite code, where XML is used?

I know that, Microsoft undeprecated the deprecated sqloledb driver, but its first version coming in March 2018 will not support Always Encrypted - so, I do not want to wait.

gotqn
  • 42,737
  • 46
  • 157
  • 243

2 Answers2

2

Classic ADO has no notion of the SQL Server XML type. The legacy SQLOLEDB provider and legacy SQL Server ODBC driver return type adLongVarWChar (203) for XML so it's a string on the client side.

The newer SQL Server Native Client OLE DB provider returns type 141 for XML to ADO but there's no matching ADO DataTypeEnum (https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum). The provider will return XML data as adLongVarWChar when the DataTypeCompatibility=80 connection string keyword is specified for ADO compatibility.

Unfortunately, there is no equivalent DataTypeCompatibility connection string keyword for ODBC drivers. The newer ODBC drivers return XML data as ADO type adLongVarBinary (205) when accessed via the MSDASQL provider classic ADO uses for ODBC drivers. So the XML will need to either be cast to/from nvarchar(MAX) in SQL queries or the adLongVarBinary value converted on the client side.

I can't say if the yet unreleased Microsoft OLE DB Driver for SQL Server will provide relief by supporting the DataTypeCompatibility keyword but I hope it does, similarly to Native Client. Hopefully, we'll know more details soon. I doubt ADO will be beefed up to support the newer SQL types natively since it's hardly been touched in the last 15 years but I've been wrong before.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thanks. I can use a combination of two connections in order to have the legacy code and always encrypted working (one using `OLEDB` and one using `ODBC` where `AE` is needed). Are you saying that there is a possibility that the unreleased Microsoft OLE DB Driver to require re-writing legacy, code? – gotqn Feb 07 '18 at 12:54
  • @gotqn, yes it is possible legacy ADO classic code might require changes. I'll see if I can find out more info. – Dan Guzman Feb 07 '18 at 13:03
  • Yes. It's kind of hard to find details or road map about the new `OLEDB` driver. Actually, I have read your blog (http://www.dbdelta.com/tag/odbc/) to find more details. – gotqn Feb 07 '18 at 13:06
  • @gotqn, I think the roadmap problem is more with ADO than OLE DB now that OLE DB is undeprecated. ADO is all but deprecated so you'll need to be especially thorough in testing. I know this is not always feasible with legacy apps but your life will be easier going forward if you can ditch classic ASP/ADO. – Dan Guzman Feb 10 '18 at 12:35
  • Are you sure it is deprecated? Do you mean it might be not supported well with the new MSSQLOLEDB driver? – gotqn Feb 10 '18 at 13:34
  • 1
    @gotqn, ADO is not officially deprecated, although it probably should be given the little attention it's gotten. So be aware ADO might not work as desired with newer SQL Server features/drivers since it's still living in the SQL 2000 world. Mind you, this isn't to say it won't work but code changes might be required and support for AE will be iffy. See my [related answer about ADO and AE](https://social.msdn.microsoft.com/Forums/en-US/92966405-6325-4b54-a2ce-f40b9f7e985d/not-possible-to-update-value-of-encrypted-column?forum=sqldataaccess). – Dan Guzman Feb 10 '18 at 15:19
  • Funny. That's me again. – gotqn Mar 06 '18 at 09:04
0

I would not use the ODBC driver; it has a number of "wontfix" bugs.

Instead you should use the currently supported MSOLEDB driver: Microsoft OLE DB Driver for SQL Server

And you will have to include in your ConnectionString:

DataTypeCompatibility=80

for the reason's Dan said.

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219