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

IslandEarth moves to Wordpress

You may have noticed that the IslandEarth website has changed. This is because we have now moved to WordPress.com

We were previously with Squarespace.com but the costs they charge started to become prohibitive.

I hadn’t even considered WordPress when I began looking around for alternative providers due to bad experiences in the past. But that was over 5 years ago – a millennium in IT terms!

The only reason I started looking at WordPress again was that the means of getting your old blog posts from one provider to another always involved using a WordPress formatted file as the intermediary mechanism. I tried that on a few hosting sites without much success – dates would be reset to today’s date, images would not be brought in etc.

Then I realised that WordPress was offering the same functionality, if not better, than the paid for hosting companies I was looking at. So I took the plunge and was pleasantly surprised at the simplicity of using WordPress and the functionality it has to offer. Just looked at the stats today and they are well presented as well.

It was by no means perfect importing my blog posts from the old site – some images did not transfer DNS A records to point at the WordPress server IPs: 192.0.78.24 and 192.0.78.25

So far so good and I highly recommend using WordPress.com not just for Blog style sites but ordinary websites as well.

Breakthepaywall website move to Azure

Since its inception the Breakthepaywall website has been hosted by Fast2Host in the UK.

Yesterday I noticed that the Breakthepaywall download link was not working – a IIS 404 error page not found was being displayed.

After frantically trying to work out why, without success, I suspected Fast2Host had changed something their end. I called support, which answered swiftly as usual – they have always been very responsive and helpful, and was informed that they had indeed blocked .exe files from being downloaded from their IIS servers.

I was asked to email the specifics of my site, which I did, but have not received a response and upon checking today the .exe files were still being blocked.

As mentioned Fast2host have always been very responsive and helpful but they have always had problems with keeping things up and running – the shared web server the site resides on usually goes down once a night for one reason or another and on many occasions during the day. There have been numerous occasions that the site has been down for 10s of minutes rather than a few due to a reboot or restart.

So, this was the final nail in the coffin, particularly as they had not informed me of this change! I decided to move hosting providers – not a task taken lightly as it has proved very difficult in the past.

I needed a free/cheap website hoster that supported classic ASP (the greatest scripting language of all time 🙂 and allowed .exe files to reside and be downloaded from the web server.

I have always been an MSDN subscriber (Microsoft developer network) and heard recently about their free credits to the Azure cloud network for MSDN subscribers.

It turns out they give you £95 per month of credits to use Azure resources as you please on a pay as you go basis.

Intrigued, I decided to give it a go. As an MSDN subscriber I already have a Microsoft username so I simply logged onto the Azure portal at:

http://azure.microsoft.com/en-us/pricing/member-offers/msdn-benefits/

The portal interface is superbly designed and very intuitive. And it supports older technologies like Classic ASP. Within 30 minutes I had a fully functioning website with custom domain and all my files uploaded via FTP. You also get a custom IP which I then punched into my DNS A record entry and voila! my new host was serving the Breakthepaywall website and the .exe setup files could once again be downloaded.

All this for just £6.16 per month – shared plan which is required if you want a custom domain name, otherwise it’s free if you’re ok to have an Azure domain name. For me it was free because of my MSDN free credits – fantastic, i’m sold. Microsoft are definitely going in the right direction with Azure.

Changing the colour of a gradient using photoshop elements

Changing the colour of a gradient using photoshop elements (version 5 used):

 

Whilst designing a website I came across some lovely borders with gradients:

These were not just linear gradients but had been designed to go in corners i.e. the gradient goes round the corner (as in 2nd and last images above).

But I didn’t want red I wanted green. However, if you try and change the gradient colour by reapplying a new linear gradient you lose the corner affect. You can add new gradient layers with different linear angles to create the same effect but that meant you were recreating the whole thing from scratch – all I wanted to do was change the colour.

Here’s how you do it:

1. These were GIF files so I right clicked on the image and chose to open with Adobe Photoshop Elements.

2. Change the colour mode by selecting Image menu, Mode, RGB Color.

3. Choose Layer menu, New Adjustment Layer, Hue/Saturation.

4. Give the new layer a name.

5. Use the sliders to change the colour – you can change the saturation and brightness for different shades but in this case I wanted the same shade just a different colour so I adjusted the Hue to +109 which gave me the green required:

6. Save as a PSD photoshop file so that you can edit again in future.

7. Save as a web file – GIF again: