CODEWITHSUNDEEP
sql-servervb6oledbadodb
symbiont
symbiont

Reputation: 1554

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:

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.

i would prefer to change as little as possible

Upvotes: 1

Views: 741

Answers (4)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

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.

Also SQLOLEDB was updated to support TLS 1.2. See Is sqloledb actually using MSOLEDBSQL on Windows Server 2019 So legacy codebases shouldn't be breaking on this any more.

Upvotes: 2

Lauren Glenn
Lauren Glenn

Reputation: 11

I went through this a couple of years ago with some legacy code. I can tell you that MSOLEDBSQL was actually "undeprecated" as Microsoft put it. Not only is MSOLEDBSQL still with OLEDB and usable with VB6, they have version 19 coming out. I haven't made any code changes to support it. There was just an updated connection string. Encryption options, etc. that are all supported on the connection string are supported.

I am currently on 16 I believe. That's the version I use because it is not showing up on any vulnerability scans as a concern to where I'd have to replace it: like XML 4 to XML 6, for example.

Only change I had to make on the install of the app with Windows 10 and 11 was to run the application once as an administrator after any registering of components in order for it to be able to register a component that it needed for data access. I was told that it needed to access and/or update a registry key at least once and after that, the app runs without fail.

Upvotes: 0

StayOnTarget
StayOnTarget

Reputation: 12998

You 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.

Upvotes: 0

Moo-Juice
Moo-Juice

Reputation: 38825

Future 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.

Upvotes: 0

Related Questions