We have one SQL Server 2012 database (on a remote server) that we are connecting to from three different client computers via an Excel Add-in. The original connection string we used worked fine for two of the computers. However, for the third computer, for some reason we had to use a different connection string to the same exact database.
The first two computers are operating on Windows 7 Pro, 64-bit and the original connection string we use for them is:
Provider=SQLNCLI11;Server={myserver};Database={mydb}; ...
The third computer is operating on Windows 7 Enterprise, 64-bit and the original connection string we had to use for it is:
Provider=SQLOLEDB;DataSource={myds};InitialCatalog={mydb}; ...
Why would I have to use two different connection strings to the same database? Besides the type of database itself, what determines which connection string and provider to use? Is it something to do with the drivers installed on each client computer? If so, how do we account for that? Or... is it something else?
Any help and advice is greatly appreciated!
Provider basically tells the client machine which network/connection library to use when connecting to the database server. To use a provider, you have to have it installed. you would use a provider for performance, functionality and support reasons. For example, SQLNCLI11 comes with SQL Server 2012. If a client machine does not have SQL 2012 client libraries installed, then they cannot use that provider, nor the client connection features that it supports, such as AlwaysOn (application intent readonly, etc.)
SQLOLEDB is old school stuff, I don't know if it is installed by default nowadays or not. If you have that, you won't have access to above mentioned functionality, or any other specific SQL related client connectivity functionality (like mirroring's failoverpartner parameter) in the connection string.