AKA accessing SQL Server 4.21 via Access 2016
I’ve been pretty dismayed for the longest while that newer versions of Windows bundle newer versions of ODBC that refuse to talk to any version of SQL Server prior to 2000 (at the moment). Of course if I were ‘professional‘ Id be upgraded to the latest version, maybe even running on Linux. But I’m not, and you didn’t come here for how to upgrade/update but rather how can you use tools from the 90’s in the 10’s.
If you try to use the current SQL Server driver, you’ll get this message. Don’t be fooled, SQL Server 7.0 isn’t supported either (probably because of the SUN vs Microsoft Java debacle), it will only work with SQL Server 2000 or later versions.
It’s been this way since Windows 7, and I’ve just given up and gotten used to having to have a VM to access older databases. That is, until today.
While I was dumping data and moving stuff for my blog (something I need to write about another BCP adventure), I wanted to do a simple Access database to make sure it’s looking sane. And I figured I’d jump down to Windows 95, and load up Office 95. Well sure enough there was no proper SQL Server ODBC driver. Popping in the SQL Server 6.5 CD, naturally there is no ODBC drivers for Windows 95, but rather a 16bit driver for Windows 3.1, and 32bit drivers for Windows NT. I installed the NT version, and was still unable to connect until I installed the SQL client which then let me run WINDBVER.EXE so I could configure the appropriate transport DLL (TCP/IP) and then I could connect.
And this got me thinking, is it possible to just take those DLL’s and move them onto Windows 10? Well naturally that won’t work as the driver sqlsrv32.dll is a system protected file, and you can’t overwrite it without a lot of pain. However this got me to thinking that it should be possible to just tell the system it’s a new driver with a different name.
Looking through the registry I notice that HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC is where the 32bit ODBC stuff is stored, so this is where I’ll make my ‘OLD SQL’ driver. I simply used the existing one and renamed the driver/setup DLL’s to sql65-sqlsrv32.dll so that way the 2 DLL’s can co-exist without freaking out.
With that done, the key Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers will need a new SZ string saying the ‘OLD SQL’ driver is installed.
I also copied the following files from the SQL 6.5 client into a subdirectory of SysWOW64 so I could configure the transport:
DBMSADSN.DLL
DBMSDECN.DLL
DBMSRPC3.DLL
DBMSRPCN.DLL
DBMSSOC3.DLL
DBMSSOCN.DLL
DBMSSPX3.DLL
DBMSSPXN.DLL
DBMSVIN3.DLL
DBMSVINN.DLL
SQLGUI32.DLL
SQLSVC32.DLL
ntwdblib.dll
WINDBVER.EXE
Now if you are going to use named pipes it’ll just work out of the box. Running WINDBVER you’ll see that it’s set that way by default.
to change it to TCP/IP I coped the files DBMSSOC3.DLL & DBMSSOCN.DLL into the SysWOW64 directory and re-ran WINDBVER, and then selected TCP/IP.
Now we can go ahead and add a SQL DSN. Remember to use the 32-bit ODBC Data Source tool
If everything is setup correctly you should see the ‘OLD SQL’ Database driver. Scrolling to the right you’ll see that it’s version 2.65.02.01 dated 7/7/1997
Now we just need to configured the driver. Be sure to hit the Options button and type in the database name, otherwise it’ll just go to the default database. Since I’m just using the ‘sa’ user that would be the master database, but I want Access to instead use the ‘pubs’ database.
Sadly these old drivers don’t have any test functionality. So there is no real way to know if it is working at this point, however I would imagine it should as we have added a regsitery key for the driver, added it to the available driver list, selected (if needed) a transport DLL, and populated the needed fields. The next thing to do is to try to use it.
Start Access, and select a blank database. Go to the External Data tab, and then choose the New Data Source, and the From Other Sources option. This will bring up the ODBC database wizard.
I’m going with linked, as I want the data to be used from the SQL Server all of the time. You will be then prompted to choose the Data Source
You can select the DSN we setup earlier, and then it’ll prompt you to login.
Again I’m using SA because… why not. And now for the moment of truth, if everything is correct you’ll be presented with the list of tables! This means that it’s working!
You can choose to save the password, and if needed select unique fields, or just leave it as it is. Now you should be able to access your SQL 4.21 database!
And there we have it.
If anyone is brave/crazy enough I extracted the driver here:
sql65odbc-files.7z SQL Server 6.5 should not only be able to access 4.21, and 6.0 but I think it ought to be able to access SQL Server 7.0, although I haven’t tested it out yet.
I wonder if odbcconf.exe /A {INSTALLDRIVER “Old SQL Server | Driver=c:\sql65\sql65-sqlsrv32.dll | Setup=c:\sql65\sql65-sqlsrv32.dll | APILevel=1 | ConnectFunctions=YYY | DriverODBCVer=02.01 | FileUsage=0 | SQLLevel=1”} work.
It certainly sounds easier but I’ve had no luck running it.
I started a Windows 2016 VM on Azure for testing.
I extracted the 7z archives into c:\sql65 and moved all files into this directory.
I also grabbed msvcrt40.dll from Windows XP SP3 installer (available from https://ftp.gnome.org/mirror/archive/ftp.sunet.se/pub/security/vendor/microsoft/winxp/Service_Packs/WindowsXP-KB936929-SP3-x86-ENU.exe (thanks winetricks from the url)):
C:\sql65>expand c:\Users\nico\Downloads\WindowsXP-KB936929-SP3-x86-ENU\i386\msvcrt40.dl_ msvcrt40.dll
So my c:\sql65 folder was this:
Directory of C:\sql65
09/23/2018 02:48 PM .
09/23/2018 02:48 PM ..
07/07/1997 12:48 AM 16,384 DBMSADSN.DLL
07/07/1997 12:48 AM 9,216 DBMSDECN.DLL
07/07/1997 12:48 AM 23,920 DBMSRPC3.DLL
07/07/1997 12:48 AM 13,824 DBMSRPCN.DLL
07/07/1997 12:48 AM 10,576 DBMSSOC3.DLL
07/07/1997 12:48 AM 10,752 DBMSSOCN.DLL
07/07/1997 12:48 AM 21,744 DBMSSPX3.DLL
07/07/1997 12:48 AM 17,408 DBMSSPXN.DLL
07/07/1997 12:48 AM 18,620 DBMSVIN3.DLL
07/07/1997 12:48 AM 11,264 DBMSVINN.DLL
04/14/2008 12:00 AM 61,440 msvcrt40.dll
08/05/2000 05:51 PM 274,489 ntwdblib.dll
09/20/2018 04:22 PM 1,000 odbc32.reg
07/07/1997 12:48 AM 240,640 sql65-sqlsrv32.dll
07/07/1997 12:48 AM 466,944 SQLGUI32.DLL
07/07/1997 12:48 AM 51,712 SQLSVC32.DLL
07/07/1997 12:48 AM 41,984 WINDBVER.EXE
Then I run odbcconf without space:
C:\sql65>c:\windows\syswow64\odbcconf.exe /A {INSTALLDRIVER “Old SQL Server |Driver=c:\sql65\sql65-sqlsrv32.dll |Setup=c:\sql65\sql65-sqlsrv32.dll |APILevel=1 |ConnectFunctions=YYY |DriverODBCVer=02.01 |FileUsage=0 |SQLLevel=1”}
And I verified from odbcad32 that the driver was registered.
C:\sql65>c:\windows\syswow64\odbcad32.exe
Now i just need an old SQL Server to test it.
Email sent!
Hey neozeed,
Can you pelase provide me the sql 6.5 and sql 7 install?
Check out WinWorld.
Look like one of my comments was filtered by the antispam.
I was explaining the right syntax for odbcconf INSTALLDRIVER (remove the space after the | separator).
I hate having to run that, but I get so many spam things every day it’d be a full time job to stay on top of, which wouldn’t leave time for my full time job. Or doing this. Sigh. sometimes the internet sucks.
Windows 2003 R2 isn’t available anymore on Azure.
So I used sql65.zip to start a SQL 6.5 server (with the pubs database) on a Windows 2008 R2 VM.
Then I tried to use the ODBC driver on the Windows 2016 VM.
It failed because sql65-sqlsrv32.dll wasn’t able to find its DLL.
So I started Access 2016 with a overridden PATH:
C:\sql65>set PATH=%PATH%;c:\sql65
C:\sql65>”\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE”
Then I was able to select data from dbo_authors without any issue.
So the whole ODBC drivers can be installed outside %windir%\SysWoW64 🙂
https://pix.milkywan.fr/lSgCrM7l.png (local mirror)
ALSR and CFG are missing on these ancient DLL so exploiting this instance of Access would be a lot easier than vanilla Access.
I was not able to use LibreOffice Base to get data from this ODBC source (invalid column name on every request). But the ODBCTest from the Microsoft Access Data SDK 2.8 was able to do a SELECT @@VERSION;
I didn’t know that the name of the client software was sent to the SQL Server:
https://pix.milkywan.fr/ZuSSEmZH.png (local mirror)
I tried to use direct SQL query in LibreOffice base but it still failed with the same Invalid column number.
From the SQL Entreprise Manager, I found that the last T-SQL request was sp_cursorfetch so I believe LibreOffice is calling incorrectly the ODBC driver …
Nice! Even better to no pollute syswow64!
I haven’t tried using libre office as I have MS office so it hasn’t really been a thing to worry about. I know when I tried to use the OpenTDS based ODBC driver that access had the hardest time with it, where it was looking for tables that don’t exist. Maybe it’s the API level? I didn’t dig too deep on that as it seemed such a can of worms.
I was surprised that Access 97 can talk to 4.2 just fine, and really surprised that 2016 can as well. In my original test VM with Windows 95 I was using Office 95.
You may want to test to see how SQL 2000 deals with Libre Office. Or set the API version higher or lower.
Hi neozeed,
I’m trying to download the 6.5 drivers but the page is asking for username and password, could you please send me the file with the drivers?
Thx.
it should be on the 404 page? I am in the process of moving stuff to save money so things may be down at the moment.
I’ve added it to archive.org:
https://archive.org/details/sql65-odbc
I’m trying to download the 6.5 drivers but the page is asking for username and password, could you please send me the file with the drivers?
Okay, Here comes another person that is trying to get the SQL 6.5 drivers.
I tried using the SQL Server 6.5 installer from winworldpc but there seem to be files missing (the DB{{something}}3.DLL ones mainly). And I cannot proceed, Please re-upload them
Sorry didn’t see this comment, but I’ve uploaded the driver set to archive.org.
https://archive.org/details/sql65-odbc
Modern office 365 is all 64bit, so you will want something old like Office 97?