What's the difference between SQLNCLI and .NET Framework Data Provider for SQL Server

Question

I have an application which uses a UDL file where the connection string specifies "Provider=SQLNCLI.1" which doesn't work on a specific machine because sqlncli.dll is not installed.

Another .Net app however, where the connection string is in app.config and does not specifically show the provider does worrk. The "Change Data Source" dialog in Visual Studio shows data provider as ".NET Framework Data Provider for SQL Server". This app works, even though sqlncli.dll is not installed.

Why? What provider is it using? Does it somehow fall back to sqloledb.dll?

Answer 1

The SQL Server Native client is an independent data access API that was introduced in SQL Server 2005.

From MSDN

It also provides new functionality above and beyond that supplied by the Windows Data Access Components (Windows DAC, formerly Microsoft Data Access Components, or MDAC). SQL Server Native Client can be used to create new applications or enhance existing applications that need to take advantage of features introduced in SQL Server 2005, such as multiple active result sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.

Read more about it and how it compares to MDAC/WDAC which is used by the ADO.Net SQL Server providers here.

Answer 2

So if I don't specify the provider name in the connection string but choose ".NET Framework Data Provider for SQL Server" in Visual Studio what provider dll am I using?