we were recently using the agent configuration 'vs2017-win2016' to run our automated tests in Azure DevOps. However, we've had to upgrade to the configuration 'windows-latest' recently due to this being deprecated, and we've suddenly started getting the error 'The 'SQLNCLI11.1' provider is not registered on the local machine' when trying to connect to a SQL Database to pull back data.
I've done a bit of googling, and it sounds like these agents are missing the Microsoft Access Database Engine which is why it's failing. Has anyone found a workaround for this? Using Self-Hosted agents isn't really an option as we require resources to set them up that aren't available to us, along with a budget we don't have for it. Any help would be greatly appreciated!
The connection string I am using to connect is:
"Server=tcp:" + server + ",1433;Initial Catalog = " + initialCatalog + ";Persist Security Info=False;User ID = " + username + ";Password=" + password + ";MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Provider=SQLNCLI11.1"
where 'server', 'initialCatalog', 'username' and 'password' are parameters we pass in.
Looks like SQLNCLI is deprecated and you should use MSOLEDB
https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15
I imagine it has been removed from the build agent to encourage migration
Here are some sample connection strings
https://www.connectionstrings.com/ole-db-driver-for-sql-server/
Does this connection string work:
There are more sample sqloledb connection strings here
https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/