Phidiax Tech Blog

Recently, at an enterprise client, we were practicing Disaster Recovery with BizTalk Server 2016.

While running the UpdateDatabase.vbs script provided under the %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2016\Bins32\Schema\Restore folder.

We immediately starting getting errors that didn't seem to make sense.

C:\Users\...\Documents\Restore-POC2 to POC1\UpdateDatabase.vbs(277, 5) Microsoft OLE DB Provider for SQL Server: Invalid connection string attribute
Info: Attempting to connect to server "RANDOMSERVER.CORPDEV.COM,1434", database "BizTalkMgmtDb"...

At first, I thought the issue was that the OLE DB Provider didn't accept a port number after the server name, but that became obvious that it was fine. I then opened the actual UpdateDatabase.vbs and had a look at the problem, narrowing it down to the GetConnection function call. It turns out that the connection provider is hard coded to SQLOLEDB...

The OLD DB connection provider was deprecated in SQL Server 2016. But, as things happen, it was decided to undeprecate the driver and continue to support it, but with some changes.

The documentation recommends using MSOLEDBSQL provider moving forward. You must download and install the latest version of the Microsoft® OLE DB Driver 18 for SQL Server® Then, update the script with MSOLEDBSQL to use the long term supported driver. Please see the full documentation at Microsoft Documentation about restoring your databases

'===============================================================
'  GetConnection(cnString)
'  This function accepts a connection string and opens
'  a connection by using it. It returns the connection
'  back
'===============================================================
Function GetConnection(cnString)
    dim conn : set conn = CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 30
'    conn.Provider = "SQLOLEDB" ' CHANGE from this to the following line
    conn.Provider = "MSOLEDBSQL"
    conn.Open cnString
    Set GetConnection = conn
End Function