This post is a little off track, but after spending quite some time trying to figure a technical problem out, I thought it might help out another lost soul, so I’m posting under Technical Architecture.
If you need to use an OLE style connection string to connect to a SQL Server database (for example, you’re using pre-written code that relies on that provider type), but you’re having trouble connecting, you can use the SQL Native Client Provider instead.
So rather than:
Provider=SQLOLEDB;Server=myhost;Database=mydatabase;Persist Security Info=False;User ID=xxxx;Password=yyyy
you can use:
Provider=SQLNCLI;Server=myhost;Database=mydatabase;Persist Security Info=False;User ID=xxxx;Password=yyyy
Bear in mind, the provider should match your version of Native Client, so in my case (I’m running SQL Server 2008 R2), it’s actually SQLNCLI10, as per:
If you can, avoid SQLOLEDB altogether and use SQLClient.
Hope that helps someone.