a *FUTUREPROOF* Project reference, to connect to SQL Server?
Background
i have legacy VB6 code that accesses SQL Server. it produces an error code 0x80004005 when TLS 1.0 is disabled for SQL Server, because the code still uses the provider SQLOLEDB:
[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
it does not explicitly use TLS, but TLS is always used for credentials according to Microsoft documentation.
Possible Solution
after looking around i have found that Microsoft released the new provider MSOLEDBSQL as a replacement for SQLOLEDB. MSOLEDBSQL supports TLS 1.2 and that will be kept updated, according to their documentation:
https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15
i've tested MSOLEDBSQL after installing the drivers, and changing the (ADODB.Connection) connection string from:
c.ConnectionString = "Provider=SQLOLEDB;Data Source=" & svr & ";Initial Catalog=" & db & ";User Id=" & u & ";Password=" & p & ";"
to:
c.ConnectionString = "Provider=MSOLEDBSQL;DataTypeCompatibility=80;Data Source=" & svr & ";Initial Catalog=" & db & ";User Id=" & u & ";Password=" & p & ";"
and this fixes the problem.
Questions
however, i'm not sure, that what i'm doing is futureproof.
- is this the provider MSOLEDBSQL indeed future proof, or would you recommend another one?
- should my VB6 project keep referencing ADODB ("msado28.tlb" Microsoft ActiveX Data Objects 2.8 Library), or is there a more futureproof reference?
- for example: would this work with TLS 1.3 in the future?
i would prefer to change as little as possible
symbiont3 Answers
MDAC and the VB6 runtime are Windows components, and MSOLEDBSQL is current, and will continue to be maintained. So that is your best combination for running this legacy codebase now and in the future.
David Browne - MicrosoftFuture proofing is a bit of a "Crystal Ball" kind of thing. There's lots we can do in code to make our lives easier, but at the end of the day - when we're using 3rd party libraries - we don't know when a library will be deprecated, what it will support or unsupport.
When it comes to code that we own as developers, we can write abstractions and do our best to be able to be backwards compatible when we suddenly realise there's a better way of doing things (or, in fact, just doing a code-refactor and don't want to break our clients).
With third-party code, this is not practical. At the end of the day, your database connectivity requires a connection string and a provider that supports it. Expect that can possibly change in the future, and your roadmap should be to keep an eye on it. At some point, you'll have to perform an update, as you just have done.
Moo-JuiceYou might consider refactoring the code responsible for establishing the DB connection into its own VB6 DLL. This DLL would manage the connection string, contain the essential references, and would be responsible for establishing the actual connection and returning the correct object.
Set this DLL up with binary compatibility.
In the future should the connection details change, only this single DLL would need to be affected, minimizing retesting & re-deployment.
In addition you could store data like the connection string in a configuration file. In the limited case where only that text would need to be changed, conceivably a user could do that themselves given good instructions. But otherwise I don't think the added complexity of a config file would be worth it; it also creates more pitfalls and points of failure.
StayOnTargetUser contributions licensed under CC BY-SA 3.0