Checking 32-Bit Leftovers: Linked Server Providers and ODBC Drivers

The new server starts, but the old import job cannot load its provider. A 32-bit dependency can survive unnoticed inside a job or connection. Inventory the caller and driver architecture before moving the workload.

Identify Which Process Loads the Driver

A driver must match the process that loads it. A modern SQL Server process cannot load a legacy provider built only for the other architecture. A separate application or package runtime can have its own architecture.

That is why checking the operating system alone isn't enough. Trace each dependency from the calling process to the installed provider or driver it actually uses.

I start with the failing job step rather than the machine's label. A successful application connection doesn't prove a linked server has the same provider path. Likewise, a working linked server doesn't certify an SSIS package.

Keep those consumers separate in the inventory. A move needs to reproduce supported dependencies for each caller, including credentials, configuration, and architecture choices.

Find 32-Bit Providers Behind Linked Servers

The sys.servers view records the provider and data source for linked servers. Read those values on the source and intended target. Compare the provider registrations without printing secrets.

The inventory doesn't establish that authentication or execution works. It identifies what to test. A familiar linked-server name can hide a different provider installed underneath it after an upgrade or rebuild.

The sys.sp_enum_oledb_providers procedure lists the OLE DB providers registered on the instance host. Its availability list isn't an architecture compatibility test for every consumer. Inspect the actual provider and supported deployment.

The next queries are read-only starting points. Record which applications rely on each linked server before changing its configuration. Removing an unused-looking provider can break a job that runs only at month-end.

SELECT name, product, provider, data_source, catalog
FROM sys.servers
WHERE is_linked = 1
ORDER BY name;
EXEC sys.sp_enum_oledb_providers;

Read Agent Steps and Their Launchers

Agent jobs can launch SSIS, PowerShell, or command-line programs. Each subsystem adds another process boundary. Read the command and execution account with the job owner during the inventory.

A hard-coded path can select an older executable even when a newer runtime is installed. Don't assume the program found in your interactive session is the same one launched by the service account.

I look for executable paths, architecture flags, and package execution settings. The query below lists relevant steps for review. Commands can contain connection information, so keep the output in the controlled local audit.

Check the step's advanced configuration in SSMS as well. A command-text search alone doesn't prove every runtime option. The checkbox for a package's runtime needs an explicit review.

SELECT j.name AS JobName,s.step_id,s.step_name,s.subsystem,s.command
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE s.subsystem IN (N'SSIS',N'CmdExec',N'PowerShell')
ORDER BY j.name,s.step_id;

Open the 32-Bit and 64-Bit ODBC Administrators

On a 64-bit Windows installation, System32 contains the 64-bit ODBC administrator. SysWOW64 contains the 32-bit administrator. Those names are memorable for precisely the wrong reason.

A data source created in one administrator doesn't establish the required driver in the other. Check the application's architecture, then inspect the matching administrator and driver list rather than testing whichever dialog you opened first.

The commands below open the two Windows tools individually. Run the one needed for the caller under review. Compare driver names, DSN scope, and authentication configuration.

A user DSN available to your desktop account isn't automatically available to an Agent service or proxy. A system DSN also needs the correct architecture. Architecture and account scope are separate checks with separate failure paths.

REM Command line
C:\Windows\System32\odbcad32.exe

REM Command line
C:\Windows\SysWOW64\odbcad32.exe

Use Windows Inventory Without Guessing

The Windows ODBC PowerShell module can list drivers and data sources by platform. That keeps both inventories visible without navigating two similar dialogs. The output is an inventory, not a connectivity test.

A listed driver can still fail because of credentials, permissions, or unsupported configuration. Test the actual application path afterward and keep sensitive connection information out of shared results.

Run these commands locally on the relevant Windows host with appropriate access. Compare source and target results under the same platform filters. Don't install an obsolete dependency merely because it appears in the old inventory.

Confirm a supported replacement with the application owner. The move is a chance to remove an architectural assumption, but it still has to preserve the workload's required behavior.

# PowerShell
Get-OdbcDriver -Platform '32-bit'
Get-OdbcDriver -Platform '64-bit'
Get-OdbcDsn -DsnType System -Platform '32-bit'
Get-OdbcDsn -DsnType System -Platform '64-bit'

Test 32-Bit SSIS Runs in Their Real Context

An SSIS package's design-time success doesn't prove its scheduled execution path. Review the selected runtime architecture for Agent and catalog execution where supported. Confirm the driver on that path and the account performing the work.

A package reading a local file also needs file permissions and the same path assumptions. Keep the architecture test attached to the actual deployment method.

Which job still depends on a runtime choice nobody has recorded? Start there before a cutover. Test representative package execution on the target, including the source connection and destination write.

Record the selected architecture and why it is required. A fallback to the older runtime remains a dependency. Manage it under a supported migration plan rather than hiding it.

Close the Inventory With a Rehearsal

Create one dependency record per consumer, including architecture, driver, DSN scope, and execution account. Add the target test result and unresolved replacement decisions. Test the same launch method used in production.

A query window and an interactive administrator are useful tools, but neither reproduces an unattended Agent step automatically. The rehearsal needs to follow the path that will run after the move.

Keep the 32-bit review separate from capacity and database compatibility checks. They address different risks. Don't declare the migration ready until every required consumer has a supported target path.

Save a reversal plan for the workload, including its connection endpoints. A faster machine won't fix a connector that cannot fit the process loading it. The matching architecture still matters.

Record who owns each remaining legacy dependency and when it will be replaced. A documented exception is easier to operate than an invisible fallback. Keep the supported installation source and version with the local inventory. The next rebuild needs a reproducible dependency plan, including the account and architecture that passed the rehearsal.

Related reading on this blog: The OLE DB Provider "Microsoft.ACE.OLEDB.12.0" for Linked Server "(null)" Reported an Error. Access Denied and SSIS: Package Error: "ODBC Source" Failed Validation and Returned Validation Status "VS_NEEDSNEWMETADATA".

A driver inventory is not a working job, it is the dependency map for a real execution test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.