SQL Native Client and ODBC

We still use OLE DB throughout our Microsoft products – Data connections from website applications, SQL SSIS etc. And it seems Microsoft still encourages its use – default choices in many cases. There are other data connection choices – ODBC, ADO.NET. But we have always found OLE DB to be the recommended technology by developers for compatibility with all functionality, speed and ease of use.

The OLE DB drivers were always included in the SQL Native Client package which also contained ODBC drivers and you needed to install it on the SQL server and match up your connection strings with the correct version you were using.

I was starting to evaluate SQL 2016 and was looking for the latest SQL Native client update for that version. It became a big surprise when I learnt that the native client driver has not been updated since the release of SQL 2012 and that Microsoft announced in 2011 (just before the release of SQL 2012) that they were deprecating OLE DB in 2018, that no new versions would be released and were recommending the use of ODBC instead:

https://blogs.msdn.microsoft.com/sqlnativeclient/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e696d0ac-f8e2-4b19-8a08-7a357d3d780f/microsoft-is-aligning-with-odbc-for-native-relational-data-access-faq?forum=sqldataaccess

So, you may find that in future OS and IIS versions the OLE DB connection is broken. I decided a comprehensive review of all the drivers available needed to be performed, the results of which you will find listed below. You will notice that there has been 3 new releases of the ODBC drivers now named ‘Microsoft ODBC Driver for SQL Server’.

The main problem that I have found is with nText and Nvarchar(max) fields – you should be using Nvarchar(max) as Ntext was deprecated in SQL 2005. But even then none of the ODBC drivers (except the vintage SQL 2000 driver using Driver={SQL Server}) will read large blob type data in nText/Nvarchar(max) type fields – you just get blank. So the only way around this is to use OLEDB driver (SQLNCLI) or limit the field to nvarchar(4000) which is the max length you can set (nvarchar(max) is blob type storage which can store up to 2GB). This was one incompatibility that we found – there may well be more!

So, let me re-iterate, Microsoft have dropped OLE DB in their latest driver releases, they recommend you use ODBC instead but ODBC does not work with all SQL functionality! I did pose a stack overflow question which didn’t come up with any viable solutions:

http://stackoverflow.com/questions/38608879/oledb-odbc-ntext-and-nvarcharmax

But we have also noticed that the default in SQL 2016 SSIS is to use OLE DB! In fact it took us a while to work out that ODBC has been included – you have to choose other options. Also, if you go to any conference where SSIS is being discussed they will all be using OLE DB, or ADO.NET when using multiple parameters as it is more manageable, certainly not
ODBC.

We also asked Microsoft execs about the situation at the same conferences and their typical response was, and i’m paraphrasing, ‘well we announced it is deprecated but there is such a huge base of customers using it that we don’t see it dissapearing anytime soon’. Indeed the main thrust of their deprecation announcement seems to be to do with SQL Azure – I have no experience with Azure but it sounds like you can only use ODBC with Azure and therefore, they are getting on premises people to change over to ODBC because that will make migrating to Azure much easier – of course in Microsoft’s eyes everyone will migrate to Azure so the issue over OLE DB becomes academic to them.

Anyway, we decided to stick with OLE DB in Native client rather than having to test everything with the latest ODBC driver as it looks like there will be no problem when we upgrade to SQL 2016 with continuing to use OLE DB.

SQL Native Client:

The SQL Native Client installs both 32bit and 64bit OLE DB and ODBC drivers.

The versions that are currently available are:

SQL Native Client V9 (SQL 2005):

OLEDB Provider code: SQLNCLI
OLEDB User/Password connection string:

objname.Open "Provider=SQLNCLI;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;User ID=Username;Password=Password;"

OLEDB Trusted Connection connection string:

objname.Open "Provider=SQLNCLI;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;Trusted_Connection=yes;"

ODBC Driver code: {SQL Native Client}

ODBC User/Password connection string:

objname.Open "Driver={SQL Native Client};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Passwor"

ODBC Trusted Connection connection string:

objname.Open "Driver={SQL Native Client};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

SQL Native Client V10 (SQL 2008):

OLEDB Provider code: SQLNCLI10
OLEDB User/Password connection string:

objname.Open "Provider=SQLNCLI10;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;User ID=Username;Password=Password;"

OLEDB Trusted Connection connection string:

objname.Open "Provider=SQLNCLI10;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;Trusted_Connection=yes;"

ODBC Driver code: {SQL Server Native Client 10.0}

ODBC User/Password connection string:

objname.Open "{SQL Server Native Client 10.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "{SQL Server Native Client 10.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

SQL Native Client V11 (SQL 2012):

Download Location: https://support.microsoft.com/en-us/kb/2726013
OLEDB Provider code: SQLNCLI11
OLEDB User/Password connection string:

objname.Open "Provider=SQLNCLI11;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;User ID=Username;Password=Password;"

OLEDB Trusted Connection connection string:

objname.Open "Provider=SQLNCLI11;Data Source=ServerIP;Failover Partner=MirrorServerIP;Initial Catalog=YourDatabase;Trusted_Connection=yes;"

ODBC Driver code: {SQL Server Native Client 11.0}

ODBC User/Password connection string:

objname.Open "{SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "{SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

Microsoft ODBC Drivers:

Microsoft ODBC Driver 11 for SQL Server:

Download path: https://www.microsoft.com/en-gb/download/details.aspx?id=36434
OLEDB Provider code: n/a
With this version Microsoft dropped OLEDB support
This version appears to be a driver wrapper to enable new functionality (like connecting to Azure DBs) you can then supply the underlying driver or use the ODBC driver that comes with it (Unfortunately you can’t use the old OLE DB driver).
So, in order to use Microsoft ODBC Driver 11 with SQL Native Client V11 you would need to install both Microsoft ODBC Driver 11 for SQL Server AND SQL Native Client V11 and use the following connection strings:

Provider code: MSDASQL
ODBC Driver code: {SQL Server Native Client 11.0}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={SQL Server Native Client 11.0};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

But you would be better off using the driver that comes with it (I hesitate to use the word native driver – that would confuse things even further!). These are the connection strings to use if you just want to use Microsoft ODBC Driver 11 for SQL Server:

Provider code: MSDASQL
ODBC Driver code: {ODBC Driver 11 for SQL Server}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 11 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 11 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

Microsoft ODBC Driver 13 for SQL Server:

Released 25th July 2016

Download path: https://www.microsoft.com/en-us/download/details.aspx?id=50420

This added support for SQL 2016.

Provider code: MSDASQL
ODBC Driver code: {ODBC Driver 13 for SQL Server}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

Microsoft ODBC Driver 13.1 for SQL Server:

Released 1st August 2016

Download path: https://www.microsoft.com/en-us/download/details.aspx?id=50420

This added functionality for always encrypted connections.
See: https://msdn.microsoft.com/en-us/library/jj730314(v=sql.1).aspx

Provider code: MSDASQL
ODBC Driver code: {ODBC Driver 13 for SQL Server}

ODBC User/Password connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;User ID=Username;Password=Password;"

ODBC Trusted Connection connection string:

objname.Open "Provider=MSDASQL;Driver={ODBC Driver 13 for SQL Server};Server=ServerIP;Failover Partner=MirrorServerIP;Database=YourDatabase;Trusted_Connection=yes;"

 

In all cases we are connecting to an SQL 2012 server with database mirroring. Strings may be different for other scenarios. A good website for connection strings is:
https://www.connectionstrings.com/sql-server/
Although they are not up to date with MSDASQL connections at time of writing.

Note: Certain keywords seem interchangeable between drivers e.g. ‘Failover Partner’ and ‘Failover_Partner’; ‘Trusted_Connection’ and ‘Integrated_Security’

Advertisements

Author: James

IT Manager - Network, Web coding, MS SQL and Online Mapping expert

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s