Migrate from OLEDB to ODBC - or not

August 11, 2011 (2011-08-11) Microsoft announced with the blog post "Microsoft is Aligning with ODBC for Native Relational Data Access" that they recognized ODBC as the de-facto standard for data access and that OLE DB was scheduled for deprecation. The blog post was later updated with links to tools that should help the migration. Also Microsoft made the FAQ "Microsoft is Aligning with ODBC for Native Relational Data Access - FAQ" to help with the details in the migration.
This was at the time big news as Microsoft for several years had promoted OLE DB for ODBC when building applications involving Microsoft technology. And of course this blog post and it's consequences was discussed a lot, e.g. by Dan Guzman in the blog posts "RIP OLE DB" (2011-09-04) and "Deprecated SQL Server Data Access Technologies" (2017-02-04).
If you are interested in some background then Hal Berenson wrote a blog post about "OLE DB and SQL Server: History, End-Game, and some Microsoft “dirt”" where he gives some great insight as a driving force behind OLE DB.

But then in October 2017 Microsoft released a new OLE DB driver for SQL Server. In the blog post "Announcing the new release of OLE DB Driver for SQL Server" (2019-10-06) Microsoft also announced that OLE DB data access technology was un-deprecated.
A somewhat wobbling course on a rather important subject. But actually I do think it makes sense as both technologies are very widespread and we in reality are beyond the point of no return on each technology.

It is was highly recommended to migrate your solutions and applications from the COM-based OLEDB to ODBC to be in support as described in the document "Data Access Technologies Road Map".

A minor detail is that SQL Server Native Client (SNAC) and MDAC/WDAC continues to be deprecated. So when using OLE DB you should still migrate but to another OLE DB driver with msoledbsql.

Unfortunately there is not much literature on neither OLE DB or ODBC, but with some luck you can find the book "Inside ODBC" by Kyle Geiger (1995, Microsoft Press). The book holds a lot of examples on ODBC are in C/C++ as ODBC is designed for native code.
The "Windows Data Access Components SDK" has a lot of interesting documentation on both OLE DB, ODBC and ADO.

OLE DB

OLE DB is a COM technology with low-level interfaces in a provider for at database service like the  SQL Server Database Engine RDBMS. Back in the days before .NET a Microsoft application could either use OLE DB direct or use the high-level interface ADO. There are still a lot of ADO connectivity in the real world. Especially with Office like Excel in VBA solutions I often meet OLE DB by ADO.
There are several OLE DB providers for various data platforms in WDAC, formerly called MDAC. For SQL Server there are also OLE DB providers in SNAC, but as .NET providers was included by default in .NET then SNAC in general was obsolete for .NET.

The blog post "OLE DB Supportability Guidelines" (2017-08-15) on the SQLNCli blog is written before October 2017 but recommend to continue using OLE DB if no blocking issues are experienced. This I think is the first hint on OLE DB continuation with Microsoft.

SQLNCli blog: "Released: Update to Microsoft OLE DB Driver for SQL Server" (18.1)

ODBC

ODBC is a cross-platform interface for data sources. The interface is as CLI defined as ISO standard (ISO/IEC 9075-3:2016).

When ODBC was announced as the de-facto standard on SQL Server connectivity, Microsoft released some material on ODBC and how to convert applications. A general introduction to ODBC was given in the document "ODBC How-to Topics". The technical article "Converting SQL Server Applications from OLE DB to ODBC" gives some details on converting from OLE DB to ODBC e.g. with a description on mapping OLE DB objects to ODBC APIs.

The Microsoft document "Download ODBC Driver for SQL Server" lists the various downloads for the SQL Server ODBC driver. There are several as Microsoft not only deliver a driver for Windows but also for other operating systems like macOS and a collection of Linux.

Microsoft SQLNCli team blog: ODBC Driver 17.2 for SQL Server Released

SNAC

SQL Server Native Client (SNAC) was created to support special SQL Server Features like database mirroring. Actually the installation set is a single dynamic-link library (DLL) with a special version of SQL Server OLE DB provider and ODBC driver. Also the library contain a special version of the ADO API. That is some versions of SNAC...

SNAC is now deprecated, and the document "SNAC lifecycle explained" gives a short description of the SNAC versions, their general contents and support.

Manual update Windows Server 2016

I have to update Windows Server installations manually as they are used for sandbox installations on virtual machines, and some of the installations like ADDC I don't want to put on the internet. Also I don't take the time to build a WSUS installation to each sandbox installation or domain.

The otes on manually updates to Windows Server 2016 are personal and strictly coupled to my given installations. Experiences are then also rather personal, and the notes should not be used without review and test.

Updates are downloaded from Microsoft Update Catalog. A search for "Windows Server 2016" (link) and a sort on latest update date gives a nice list to pick from.
Usually updates are released to the catalog at the same time as to Windows Update. That is 2nd Tuesday of the month. Also called Patch Tuesday.


2018-07 Cumulative Update (KB4346877) fail with error message on the update not being "applicable".
Searching the error message I found this short and precise description from PDQ.com (link). It points to KB4132216 on a "Servicing stack update for Windows..." where the text mention stability improvements to "... an issue that might sometimes lead to incorrect checks for applicability during installation of Windows Updates...". The text is at first sight on Windows 10, but a lot of Windows Server 2016 updates are named Windows 10 in the KB articles and even the files in the installations.
The update can be downloaded from Microsoft Update Catalog (link). Installing the servicing stack update takes a few seconds, and after that the Cumulative Update runs as expected.
This takes several minutes and requires a server restart. The restart itself also takes several minutes.

SSMS installation on off-line computer

In secure networks I have several times found it necessary to install a tool station computer without internet access. Most tools like editors can easily be downloaded from another computer and the installed on the tool station. But Microsoft SQL Server Management Studio (SSMS) has some issues when installing without internet access.

This is an example on the type of error message I usually get. In this case it is from installing SSMS 17.2 on a danish Windows 7. But I have had similar messages on previous SSMS versions and on other Windows version.
I have not found a official description from Microsoft on this situation. One of the first times I ran into this I was actually sitting with several Microsoft SQL Server consultants and PFE's, and they came up with a solution by some mails.

When a SSMS Setup Failure happens it is also logged in a SsmsSetup log file. This log file is in the folder "C:\Users\<User Name>\AppData\Local\Temp\SsmsSetup".
(** Insert example **)

The short description is that SSMS is missing two certificates, that it is used to download during SSMS installation. The two certificates are

• MicRooCerAut2011_2011_03_22 (link)
• MicRooCerAut_2010-06-23 (link)

You will have to install the certificates before installing SSMS.
After you have downloaded the certificates as cer-files you will have to import the certificates into Windows. It does not matter where you place the cer-files.
I know there are several guides on  importing certificates into Windows, but I go through the details anyhow to avoid doubt.

1. Start Microsoft Management Console (mmc.exe)
2. Add the snap-in "Certificates" (Ctrl + M)
3. When you mark the snap-in to be added to MMC you will have to select which account type the snap-in should manage. Select Computer Account 
4. You will also have to select which computer the snap-in should manage. Select Local Computer
5. After adding the Certificates snap-in you should expand Certificates > Root Key Centers... (or Trusted Root Certification Authorities) > Certificates
6. Right-click the item Certificates and select All Tasks > Import...
7. This will start the guide Certificate Import
8. Select to place all certificates in the Certificate Store named Root Key Center... (or Trusted Root Certification Authorities)
9. When both certificates are imported with success you can close MMC

All this I am sure can to automated by a script, but as the computer is off-line there are also some issues getting the script file to the computer.

Windows 10 on ThinkPad T440s

The other day I wanted to install Windows 10 on a Lenovo ThinkPad T440s.
It seemed like a simple task:

1. Download Windows 10 image from Visual Studio subscription.
2. Extract installation files to USB stick.
3. Boot ThinkPad on USB stick.
4. Install Windows 10.

Not quite.
The installation set is not specially large and fits easily on a medium (8 GiB) USB stick.
But the file "install.wim" is larger than 4 GiB in the latest Windows 10 image. And when a USB usually is formatted with FAT32, it can't hold a file larger than 4 GiB.
My first thought was to format the USB stick with exFAT, but the T440s couldn't boot on the USB stick with the installed BIOS level.

My solution was:

1. Format the USB stick with FAT32.
2. Download the RTM image of Windows 10 where the file "install.wim" is smaller than 4 GiB.
3. Extract installation files to the USB stick.
4. Boot the ThinkPad on the USB stick.
5. Install Windows 10 RTM.
6. Update Windows 10 to current release - with several reboots.

How to install Windows 10 later when RTM is not available on Visual Studio subscription might be a larger challenge in the future.
If I meet that challenge then I will update this post.

Getting and using PowerShell AzureRM

PowerShell

Check if PowerShell is available
powershell.exe
and what version that is installed.
prompt> $Host
or
prompt> $Host.Version

If PowerShell is an old version or not installed, then the installation is done by installing Windows Management Framework (WMF) in the latest version. Take a look at the PowerShell site (link) on MSDN or use your favorite search engine to find the latest version of WMF.

PowerShellGet

PowerShellGet is a PowerShell module that is used to get modules form a repository like PowerShell Gallery.

Check if the module PowerShellGet is installed and if then in what version:
Get-Module -Name PowerShellGet -ListAvailable
The output is a ModuleInfoGrouping object, where the Version property holds a Version object.

Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 1.0.0.1 PowerShellGet {Install-Module, Find-Module, Save-Module, Update-Module...}

Check what version that is the latest in the repository:
Find-Module -Name PowerShellGet -Repository PSGallery

Version Name Repository Description
------- ---- ---------- -----------
2.0.1 PowerShellGet PSGallery PowerShell module with commands for discovering,...

Compare the Version elements in the outputs.
The output is a ModuleInfoGrouping object, where the Version property holds a Version object like the output from the Cmdlet Get-Module.
You might have several versions of the same module installed. Then you should compare on the latest version.

Install the latest version of the PowerShellGet module:
Install-Module -Name PowerShellGet -AllowClobber -Force
I have seen this warning (error?) some times:
WARNING: The version '1.1.4.0' of module 'PackageManagement' is currently in use. Retry the operation after closing the applications.
A PowerShell restart does not help. I have not looked deeper in this issue.

A new compare shows to versions of PowerShellGet installed side-by-side.

Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 2.0.1 PowerShellGet {Find-Command, Find-DSCResource, Find-Module, Find-RoleCap...
Script 1.0.0.1 PowerShellGet {Install-Module, Find-Module, Save-Module, Update-Module...}
In general PowerShell will use the highest version number available when called without a specific version number.

Update the PowerShellGet module to the latest version:
Update-Module -Name PowerShellGet
I have seen that version 1.0.0.1 of PowerShellGet can't be updated, but the update doesn't fail.

Uninstall the module in all versions installed:
Uninstall-Module -Name PowerShellGet -AllVersions -Force
Again PowerShellGet in version 1.0.0.1 stand out as it is not uninstalled, but the uninstall doesn't fail.
If I try a version specific uninstall
Uninstall-Module -Name PowerShellGet -RequiredVersion '1.0.0.1'
then there is an error
PackageManagement\Uninstall-Package : No match was found for the specified search criteria and module names 'PowerShellGet'.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.1.3.2\PSModule.psm1:2252 char:21
+ ... $null = PackageManagement\Uninstall-Package @PSBoundParameters
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Microsoft.Power...ninstallPackage:UninstallPackage) [Uninstall-Package], Exception
+ FullyQualifiedErrorId : NoMatchFound,Microsoft.PowerShell.PackageManagement.Cmdlets.UninstallPackage
where it looks like a newer version is installed, but that does not show through Get-Module -ListAvailable. It might be the installation on my computer that has broken during several Install-Update-Uninstall sessions. I have not looked deeper in this issue.

Import the PowerShellGet module in the current PowerShell session with this statement:
Import-Module -Name PowerShellGet
This is usually not needed as the module is loaded when installed.

List functions available in module:
Get-Command -Module PowerShellGet

Remove the PowerShellGet module with
Remove-Module -Name PowerShellGet
but why???

AzureRM

It is the same Cmdlets as used with PowerShellGet above, but I go through the phases anyway to see the differences.

Check the installed and available version:
Get-Module -Name AzureRm -ListAvailable
Find-Module -Name AzureRM -Repository PSGallery

Install the latest version:
Install-Module -Name AzureRM -AllowClobber -Force
It will usually take several seconds before the installation begins. Until then no activity is shown in the PowerShell console - you just have to be a little patient.

Update to the latest version:
Update-Module -Name AzureRM

Uninstall the module:
Uninstall-Module -Name AzureRM -AllVersions -Force

Import the module in the current PowerShell session:
Import-Module -Name AzureRM

List functions available in module:
Get-Command -Module AzureRM

Remove the module from the current PowerShell session:
Remove-Module -Name AzureRM

Context

Windows 7 (Enterprise) and 10 (Pro), WMF 5