Share via

Compatibility Issue - SQL Azure connectivity using the SQL Native Client may break after the July release of the Windows Azure Guest OS

  • Article

 

Prior to July 2013 the Windows Azure Guest OS included version 10 of the SQL Native Client libraries (commonly named SQLNCLI or SNAC). The July release of the Windows Azure Guest OS (1.24, 2.16 and 3.4) now ship version 11 of SQL Native Client libraries. This change can cause exceptions being thrown by client applications connecting to SQL Azure (or any other database accessed by SNAC).

 

Symptoms:

  • The most common symptom is a failure in connectivity to the database starting mid-July (July 16th is the date when July releases started shipping) or when you manually upgrade to the latest Windows Azure Guest OS version (1.24, 2.16 or 3.4).
  • Error messages will depend on the language or framework you are using, but will generally be thrown when opening a connection to the database. Here are some variations of the errors you may see:
     

With SNAC ODBC:  

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

 

With SNAC OLEDB:

                The 'SQLNCLI10' provider is not registered on the local machine.

 

With PHP:

Error establishing a Database Connection
The connection has timed out – the server at XXXXXXXX.database.windows.net is taking too long to respond.

 

Cause:

Prior to July 2013 the Windows Azure Guest OS shipped with the Microsoft SQL Server 2008 Native Client (version 10 of the SQLNCLI – sqlncli10.dll), but after July 2013 the Windows Azure Guest OS ships with the Microsoft SQL Server 2012 Native Client (version 11 of the SQLNCLI - sqlncli11.dll).

ODBC/OLEDB Connection Strings generally hardcode the SQLNCLI version as in the following examples:

SQL Server Native Client 10.0 ODBC Driver

Driver={SQL Server Native Client 10.0};Server=tcp:[serverName].database.windows.net;Database=myDataBase;Uid=[LoginForDb]@[serverName];Pwd=myPassword;Encrypt=yes;

SQL Server Native Client 10.0 OLE DB Provider

Provider=SQLNCLI10;Password=myPassword;User ID=[username]@[servername];Initial Catalog=databasename;Data Source=tcp:[servername].database.windows.net;

Since the sqlncli10.dll no longer exists on the OS, the calls to its methods will fail.

 

 

Impacted OS Versions

Guest OS Version

Configuration String

Release Date

SQL Native Client Version

1.24

WA-GUEST-OS-1.24_ 201306-01

July 16 2013

SQL 2012 (v11)

2.16

WA-GUEST-OS-2.16_201306-01

July 16 2013

SQL 2012 (v11)

3.4

WA-GUEST-OS-3.4_201306-01

July 16 2013

SQL 2012 (v11)

 

 

How to check the version of the SQL Native Client Libraries on your VM?

  • You can login to your Virtual Machine with RDP and look in D:\Windows\System32.
    • If sqlncli10.dll is present then you are using version 10.
    • If sqlncli11.dll is present then you are using version 11.
  • You can check the Programs and Features List via the control panel.
    • If Microsoft SQL Server 2008 Native Client is present then you are using version 10.
    • If Microsoft SQL Server 2012 Native Client is present then you are using version 11.

 

How to determine if you are impacted?

  • If you are using a database connection string check for the presence of “Driver= ” or “Provider= ”. This value will indicate if you are using version 10 or version 11.
  • If you are using PHP/Wordpress, you can look at PHP logs – where you can search for the string "sql". Calls will generally indicate it is expecting version 10.

 

Note: If you have a .NET application which directly uses System.Data.SqlClient.dll you should not be affected as this assembly doesn't use SNAC.

 

Solutions:

The latest Windows Azure Guest OS includes both SNAC 10 and SNAC 11 installed side-by-side. If you had rolled back to a previous Guest OS version you can now upgrade to the latest one (201308-01 (3.6, 2.18, 1.26) or newer).

  • For a quick, temporary solution you can roll back to the previous Guest OS (June release – versions 1.23, 2.15, 3.3) which contains SNAC 10. This can be done via the Azure Management Portal or by uploading a new service configuration file with the correct OS configuration string. This should be a temporary solution since the June Guest OS releases will be deprecated.
  • Upgrade your code or connection string to use SNAC version 11. This could be as simple as changing "10" into "11" (see SQL Azure ConnectionStrings samples on https://www.connectionstrings.com/sql-azure/) – but this of course needs testing.
  • If you are using PHP, you can use the latest version 3.0 of the PHP SQL drivers – available on https://www.microsoft.com/en-us/download/details.aspx?id=20098
  • You can install SQLNCLI10 x64 (SQLNCLI version 10 in x64) side-by-side with version 11. Note that you will need to deploy SQLNCLI version 10 with a Startup Task and redeploy your application. You can redeploy your application using an In Place Upgrade or VIP swap in order to prevent downtime.

 

 

A special thanks to Axel Guerrier with our Windows Azure Technical Support team for providing the technical information.