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.