Once more again I’m confronted with a situation where I needed a SQL, but I don’t have direct access to the data. The machine I’m able to run some stuff on is not only insanely out of date (yay!) but doesn’t have enough disk space for even something like SQL Server 2000.
Enter SQL 4.21a
I “installed” 4.21a on this 32bit 2003 server in much the same way I transplanted 4.21a onto Windows 10. However I did use the srvany utility to load up the SQL Server service, much like how I used it to run an instance of Qemu in the background elsewhere. Now I have my intermediary SQL Server running like a normal service, and set a password for the SA user.
Now for the fun.
First be sure to set your target database for ‘bulk/load’ and I’d also set it for ‘truncate log on checkpoint’. If you don’t set the bulk/load then you cannot BCP data into the database.
Using the SQL Explorer tool I could view the tables I wanted, and export them as ‘SQL CREATE’ giving me the table layout. I then quickly converted them into something acceptable for 4.21a. Now it’s a matter of establishing a connection to the old server from the new.
First I tested with the ISQL command. I needed to copy the DLLs DBMSSOCN.DLL & NTWDBLIB.DLL into the directory to get the command to fire up. Since my strategy here is to do a BCP dump/BCP load the first thing I need to do is purge the data.
This of course assumes that the server address is 192.168.1.42 and that in this case I’m deleting the firewall_mapping table from the network database. If you’ve made it this far that means we are 1/4th of the way there!
I found this ‘one trick’ to get the BCP command from the SQL 4.21a tools to connect to the 2016 server and dump the table as a trusted connection. I’m not sure how much longer this kind of thing will work, but I was pretty amazed it did. I didn’t even bother trying to see if the 4.21a BCP tool could read a 2016 BCP dump. Maybe it would if you keep the formatting the same, but I find ‘like to like’ much easier. I renamed the old BCP.EXE to BCP42.EXE so that they won’t collide in any way causing weirdness. At the same time I run them from a directory that is NOT in the system path.
bcp42 "[Network Database].[dbo].[firewall_rules]" out c:\temp\1.csv -t, -r= -P
The notation looks weird, as my source database name has a space in it. This initially caused endless frustration, but it was just a matter of using the fully qualified name, which is in quotes
"[database].[role].[table]"
I set the field delimiter as a comma, and the row terminator as an equal sign. I tried not setting it but I was getting ‘spiraling data’ as it was not picking up the end of row correctly at first.
The first time you run the BCP without a format file it’ll walk you through the specifics of the fields. I just blindly accepted the defaults, and saved the file as firewall_rules.fmt . Now on subsequent runs, I can run the export like this, which uses the saved formatting:
bcp42 "[Network Database].[dbo].[firewall_rules]" out c:\temp\1.csv -t, -r= -P -ffirewall_rules.fmt
Great so if everything is going well, we have no exported our data! Now the next step is to import the data into our old server. Since we have that format file, this “should” go pretty smoothly. Notice the server is an IP address which implicitly has it connecting by TCP sockets, not named pipes. As such there is no implicit ‘Trusted connection” as there was when talking to the local 2016 server.
bcp42 network.dbo.firewall_rules in c:\temp\1.csv -Usa -PPASSWORD -S192.168.1.42 -t, -r= -ffirewall_rules.fmt
Naturally change PASSWORD to whatever password you have for the SA user.
Starting copy...
1000 rows sent to SQL Server.
1892 rows copied.
Network packet size (bytes): 512
Clock Time (ms.): total = 2216 Avg = 1 (853.79 rows per sec.)
And there you have it, all being well you’ll see the program update every 1,000 rows as it inserts data.
Originally I wanted to use the data transformation wizard thing (whatever they renamed DTS to) however the ODBC is limited to the newer .NET 4 stuff, which won’t use the old SQL Server 6.5 ODBC drivers. I really didn’t think the SQL Server 4.21a BCP command to work on a modern server against a new(ish) version of SQL Server, but it did!
I guess you could neaten it up with a command file to drop tables/re-create if you wanted, or at the least delete data/checkpoint and set the load options, dump/load data, and then turn off the load state for the database. I’m not doing reports or anything fancy, just visualizing data as they say.
Although things like ODBC have drifted out, it’s still kind of interesting that ancient BCP can still communicate over named pipes as an implicit trust.
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.
SQL Server version 6.5 and all previous versions are no longer supported
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.
Access 95 using the SQL 6.5 NT ODBC Driver + Client
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.
OLD SQL Driver
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.
Add the new driver to the available list.
I also copied the following files from the SQL 6.5 client into a subdirectory of SysWOW64 so I could configure the transport:
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.
Named pipes, and notice it’s version 8.0.1.85 from 2016!
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.
TCP/IP via DBMSSOCN.DLL
Now we can go ahead and add a SQL DSN. Remember to use the 32-bit ODBC Data Source tool
Note this is the 32-bit version!
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
Use the ‘OLD SQL’ driver
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.
Configure the old driver
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.
The ODBC option is in the ‘Other’ sources now.
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.
Link to the data source
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
The dbtest ODBC connection should be under the Machine listings
You can select the DSN we setup earlier, and then it’ll prompt you to login.
Login to the SQL Server
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!
The pubs database from SQL 4.21
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!
The Authors table from the pubs 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 didn’t do anything to set this up. I just searched for ISQLW and for some reason this ancient one showed up in the search path, and it connected. I didn’t notice it at first until it didn’t like the newer shift insert/delete operations, as back then you needed to use Control C/V ..Â
Not being able to stop there, I fired up the admin tool. It complains that the stored procedure sp_MSAdmin_version is missing. However you can go ahead and create it…
create procedure sp_MSAdmin_version as select “Microsoft SQL Administrator script version 4.20.22.1” go
And it’ll connect.
Yes you can track stats in sort of real time
Oddly enough things that talk to the server work okay. Things related to the databases don’t work at all.
SQLServer 2017 on Linux users
I even can admin users from 4.21’s admin program.
I guess the sp_MSAdmin_* scripts could be fixed up for 2017, allowing for a more robust experience, but I really can’t think of any reason why to do it. I’m more surprised that all the new ODBC drivers since Vista won’t talk to SQL Server 4.21, 6.0, & 7.0, but it seems the client tools can talk to the new server.
I’ve even created the infamous ‘PUBS’ database from the 4.21a script as well. Again not very useful, but all the more fun!
PUBS
Installation wasn’t too hard, but a little weird to re-produce. Anyways you’ll need to trust the MS key
And then I added this into the /etc/apt/sources.list:
deb [arch=amd64] https://packages.microsoft.com/debian/9/prod stretch main
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial main
And then run the following to download MSSQL & the needed bits. It’ll prompt a few times to agree to the License:
And if everything goes correctly you will then be prompted for the edition to use, the SA password, and then you can start the server with:
systemctl restart mssql-server.service
And away you go.
My output was like this:
# cat /etc/issue
Debian GNU/Linux 9 \n \l
root@Junk:/# apt-get update;apt-get upgrade
Hit:1 http://security.debian.org stretch/updates InRelease
Ign:2 http://debian.uchicago.edu/debian stretch InRelease
Hit:3 http://debian.uchicago.edu/debian stretch Release
Hit:4 https://dl.yarnpkg.com/debian stable InRelease
Hit:5 http://ftp.debian.org/debian stretch-backports InRelease
Hit:7 https://deb.nodesource.com/node_8.x stretch InRelease
Hit:8 https://packages.microsoft.com/debian/9/prod stretch InRelease
Hit:9 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial InRelease
Reading package lists... Done
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
root@Junk:/# apt-get install mssql-tools mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libc++1 libodbc1 libsss-nss-idmap0 libunwind8 msodbcsql17 odbcinst odbcinst1debian2 unixodbc
Suggested packages:
clang libmyodbc odbc-postgresql tdsodbc unixodbc-bin
The following NEW packages will be installed:
libc++1 libodbc1 libsss-nss-idmap0 libunwind8 msodbcsql17 mssql-server mssql-tools odbcinst odbcinst1debian2 unixodbc
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/181 MB of archives.
After this operation, 932 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Preconfiguring packages ...
Selecting previously unselected package libc++1:amd64.
(Reading database ... 53362 files and directories currently installed.)
Preparing to unpack .../0-libc++1_3.5-2_amd64.deb ...
Unpacking libc++1:amd64 (3.5-2) ...
Selecting previously unselected package libodbc1:amd64.
Preparing to unpack .../1-libodbc1_2.3.4-1_amd64.deb ...
Unpacking libodbc1:amd64 (2.3.4-1) ...
Selecting previously unselected package libunwind8.
Preparing to unpack .../2-libunwind8_1.1-4.1_amd64.deb ...
Unpacking libunwind8 (1.1-4.1) ...
Selecting previously unselected package odbcinst1debian2:amd64.
Preparing to unpack .../3-odbcinst1debian2_2.3.4-1_amd64.deb ...
Unpacking odbcinst1debian2:amd64 (2.3.4-1) ...
Selecting previously unselected package odbcinst.
Preparing to unpack .../4-odbcinst_2.3.4-1_amd64.deb ...
Unpacking odbcinst (2.3.4-1) ...
Selecting previously unselected package unixodbc.
Preparing to unpack .../5-unixodbc_2.3.4-1_amd64.deb ...
Unpacking unixodbc (2.3.4-1) ...
Selecting previously unselected package libsss-nss-idmap0.
Preparing to unpack .../6-libsss-nss-idmap0_1.15.0-3_amd64.deb ...
Unpacking libsss-nss-idmap0 (1.15.0-3) ...
Selecting previously unselected package msodbcsql17.
Preparing to unpack .../7-msodbcsql17_17.2.0.1-1_amd64.deb ...
Unpacking msodbcsql17 (17.2.0.1-1) ...
Selecting previously unselected package mssql-server.
Preparing to unpack .../8-mssql-server_14.0.3037.1-2_amd64.deb ...
Unpacking mssql-server (14.0.3037.1-2) ...
Selecting previously unselected package mssql-tools.
Preparing to unpack .../9-mssql-tools_17.2.0.1-1_amd64.deb ...
Unpacking mssql-tools (17.2.0.1-1) ...
Setting up libsss-nss-idmap0 (1.15.0-3) ...
Setting up libodbc1:amd64 (2.3.4-1) ...
Setting up libunwind8 (1.1-4.1) ...
Processing triggers for libc-bin (2.24-11+deb9u3) ...
Processing triggers for man-db (2.7.6.1-2) ...
Setting up libc++1:amd64 (3.5-2) ...
Setting up mssql-server (14.0.3037.1-2) ...
Setting up odbcinst1debian2:amd64 (2.3.4-1) ...
Setting up odbcinst (2.3.4-1) ...
Setting up unixodbc (2.3.4-1) ...
Setting up msodbcsql17 (17.2.0.1-1) ...
Setting up mssql-tools (17.2.0.1-1) ...
Processing triggers for libc-bin (2.24-11+deb9u3) ...
root@Junk:/# /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a product key to enter.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409
Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.
Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]:yes
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /lib/systemd/system/mssql-server.service.
Additionally you may not want to listen on every single IP address, but rather only on the loopback. So you would run this to configure the listening address:
/opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1
I also use the SQL Agent, to enable that just simply run this:
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
systemctl restart mssql-server
I kept on getting this error which I didn’t see any way to cleanly resolve to fix for running MSSQL on Debian. The best hint is the OpenSSL is either too new (unlikely) or too old (far too likely). Instead I just changed distros as that is what people do, they don’t troubleshoot problems in Linux, just change distros so why bother fighting it?
# /opt/mssql-tools/bin/sqlcmd -Usa -PMYPa55w0rd!# -S127.0.0.1
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
OpenSSL?
Going further though, as much as I liked Debian it really does run better on Ubuntu. So as an addendum, use these sources (at the moment!). Since the SQL Agent wouldn’t run, and I couldn’t connect locally it was worse than useless.
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04//prod xenial main
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial main
Now the first time I tried to do anything on Ubuntu I got this lovely error:
# /opt/mssql-tools/bin/sqlcmd
terminate called after throwing an instance of 'std::runtime_error'
what(): locale::facet::_S_create_c_locale name not valid
And it just hung the process. I had to control-Z & kill -9 %1 it to get it out of the way. Well it turns out that this VM didn’t have it’s locale set. Fixing that was pretty simple, once you know how:
Another thing that really bugs me is the lack of cryptography by default. So I found this nice recipie to for setting it up quickly. Just watch your hostname!
So as part of my fun day job stuff involves moving data around and from time to time, there is that proverbial server in the corner (in this case it’s even physical!) and in this case I needed to move stuff into an old version of SQL Server as I needed to remember how to use the older BCP syntax. Obviously I guess I could have just installed 6.5 into a VM, did the SQL transfer, then the whole upgrade to 7/2000 and up to something modern, but per requirements I needed to check data in the middle, so the quickest way is to just BCP it out into something human readable, and BCP it into something new.
SQL Server Books Online on Windows 10
So it turns out the books are easily copied off the install CD, and just run the viewer application directly. And even better the ‘massive and optional’ install of all the online documentation turns out to be absolutely trivial by modern standards.
As you can see it’s really not that much in the world of terabyte disks. Anyways just copy out all the SQLBOOKS* files and then from the binn directory grab the INFOVIEW program. To view the books just run “INFOVIEW SQLBOOKS.MVB” and you’ll be set!
Some of the other older tools like Visual C++ 4.x also use this InfoViewer format, and you can copy all the other associated files, in the off chance you want to keep this ancient stuff handy.
Ugh so I was forced to setup something with JDBC. It’s been like forever since I have messed with Java in forever. So I thought I’d try something simple first. I found this very simple program to query against the NorthWind database from here.
// Import the SQL Server JDBC Driver classes
import java.sql.*;
class Example
{
public static void main(String args[])
{
try
{
// Load the SQLServerDriver class, build the
// connection string, and get a connection
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://ServerName;" +
"database=northwind;" +
"user=UserName;" +
"password=Password";
Connection con = DriverManager.getConnection(connectionUrl);
System.out.println("Connected.");
// Create and execute an SQL statement that returns some data.
String SQL = "SELECT CustomerID, ContactName FROM Customers";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);
// Iterate through the data in the result set and display it.
while (rs.next())
{
System.out.println(rs.getString(1) + " " + rs.getString(2));
}
}
catch(Exception e)
{
System.out.println(e.getMessage());
System.exit(0);
}
}
}
As you can see it’s pretty simple. The server I’m using is on the default instance so I don’t need the instance name. So first thing off, compile the program, and run it, right?
# ./javac sql.java
# ./java Example
com.microsoft.sqlserver.jdbc.SQLServerDriver
Well that’s great. No doubt we actually need a driver from Microsoft, which surprisingly wasn’t too hard to find. I’m sure the link will drift over the years, but right now here is the Microsoft JDBC Driver 6.2 for SQL Server. From what I remember you would just use the jar flag, and be on your way.
# ./java -jar mssql-jdbc-6.2.2.jre8.jar Example
no main manifest attribute, in mssql-jdbc-6.2.2.jre8.jar
Great. What’s this crap?
Well it turns out that you now need a Manifest.txt file. Oh and the best part is that it needs a blank line at the end of the file. So much time spent trying to figure that one out.
Manifest-Version: 1.0
Class-Path: mssql-jdbc-6.2.2.jre8.jar
Main-Class: Example
Ok, now to make my life easier I’m just going to throw this thing into a jar.
jar -cfm Example.jar Manifest.txt Example.class mssql-jdbc-6.2.2.jre8.jar
and now we get to the real fun, trying to get it to run. My main testing SQL server is an ancient SQL Server 7.0 SP4 which I really need to just finally get around to upgrading. While it’s served it’s time as a good base test instance, time has finally come to that point where nothing is going to talk to it anymore. But while I was crazy enough to try to talk to it I got this fun error:
WARNING: ConnectionID:1 Prelogin error: host ServerName port 1433 Unexpected end of prelogin response after 0 bytes read
I guess the hint is the Prelogin, as it’s failing the higher security checks for the authentication. So I quickly installed a 2003 server along with SQL Server 2005. And oddly enough it was lacking the Northwind database, but I did find this great site, northwinddatabase.codeplex.com with a handy SQL script to generate the database.
Update the java file to point to the new server, and …
# ./java -jar Example.jar
Connected.
ALFKI Maria Anders
ANATR Ana Trujillo
ANTON Antonio Moreno
AROUT Thomas Hardy
….
WILMK Matti Karttunen
WOLZA Zbyszek Piestrzeniewicz
I have to admit it, that when I first heard about this I was HIGHLY skeptical, but sure enough it actually works.
Enterprise Manager looking at the infamous PUBS database
Although I have gotten SQL Server 4.21a & 6.5 running on Windows 10 (The core from 6.0 works, but it’s pre-release COM objects for the Enterprise manager don’t like Windows 10) There were two stumbling blocks I never could get around. The first one turned out to be something trivial, which is SQL 4.21 would never listen on TCPIP.
Fixing SQL 4.21
It turns out that this actually was a simple fix.
17/09/21 19:40:24.00 server server name is ‘JADERABBIT’ 17/09/21 19:40:24.00 server Recovering database ‘model’ 17/09/21 19:40:24.00 server Recovery dbid 3 ckpt (45,26) 17/09/21 19:40:24.00 server Clearing temp db 17/09/21 19:40:24.03 kernel Using ‘SQLEVENT.DLL’ version ‘4.21.00’. 17/09/21 19:40:24.83 kernel Using ‘OPENDSNT.DLL’ version ‘4.21.09.02’. 17/09/21 19:40:24.83 kernel Using ‘NTWDBLIB.DLL’ version ‘4.21.00’. 17/09/21 19:40:24.83 ods Using ‘SSNMPNTW.DLL’ version ‘4.21.0.0’ to listen on ‘\\.\pipe\sql\query’. 17/09/21 19:40:24.83 ods Using ‘SSMSSOCN.DLL’ version ‘4.21.0.0’ to listen on ‘1433’. 17/09/21 19:40:26.04 server Recovering database ‘pubs’ 17/09/21 19:40:26.06 server Recovery dbid 4 ckpt (469,25) 17/09/21 19:40:26.06 server Recovering database ‘ultimate’ 17/09/21 19:40:26.06 server Recovery dbid 5 ckpt (524295,12) 17/09/21 19:40:26.06 server Recovery complete. 17/09/21 19:40:26.12 server SQL Server’s default sort order is: 17/09/21 19:40:26.12 server ‘bin_cp850’ (ID = 40) 17/09/21 19:40:26.12 server on top of default character set: 17/09/21 19:40:26.12 server ‘cp850’ (ID = 2)
The DLL for TCP/IP is SSMSSOCN.DLL, and it turns out it really wants to be located in the C:\Windows\SysWOW64 directory (aka the system path for libraries). Well that’s all great now, isn’t it?
Not really.
ODBC Hell
The ODBC drivers in Windows 10 finally made a magical cut off point that they will not talk to any old and ‘vulnerable’ SQL Servers. This means that the oldest version you can connect to is SQL Server 2000. Even SQL 7 didn’t make the cut. Trying to connect to a SQL 7 server, you just get:
Attempting connection [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
And then I saw this post, about using FreeTDS to connect to MSSQL. So I followed their instructions, and got nowhere fast just lots of crashing. Turns out the bloodshed environment’s included G++ just fails 100% of the time for me, with a nice crash. So I pointed it to the TDM GCC install, and then had to link the DLL manually and… nothing. No configuration point. In a fit of rage, I took the exist msvc project, opened it in Visual Studio 2015, and built it, except for one issue…
odbccp32.lib(dllload.obj) : error LNK2019: unresolved external symbol __vsnwprintf_s referenced in function _StringCchPrintfW
So I was thinking that SQL 4.21 & 6.5 are just too old to have this weird table, and as mentioned over here people would just create it, to get Access to shut up, and get on with their lives.
So, I put in some SQL
CREATE TABLE MSysConf(CREATE TABLE MSysConf(Config  int NOT NULL,chValue char(255) NULL,nValue  int NULL,Comments char(255) NULL) GO INSERT INTO MSysConf(Config,nValue,Comments)VALUES(101,1,’Prevent storage of the logon ID and password in linked tables.’) GO
And yes, it creates the table, Access get’s it’s result then obviously doesn’t like it and up and dies. Maybe I can burn more cycles on it later, or break down and ask.
Extract SP4 Copy ..SP4\x86\other\sqlredis.exe to ..\originalinstallpath\x86\other (this avoid mdac insall freezing) Create this folder structure (any place): Microsoft SQL Server\80\Tools\Binn Microsoft SQL Server\MSSQL\Binn Find out sqlunirl.dll on SP4 path and copy to Binn folder above Copy dll files on ..SP4\x86\setup to Microsoft SQL Server\MSSQL\Binn (folder above) Copy folder structure (created on step 3) to C:\Program Files (x86) Give full access to user logged to **Microsoft SQL Server** folder Change install compatiblity ..\originalinstallpath\x86\setup\setupsql.exe XP SP2 Run as administrator
**UPDATE
Newer versions of Windows 10 don’t include the old Visual C++ runtime, and the install will fail. Just copy the DLL’s into the syswow64 directory.
copy SP4\x86\system\msvc?71.dll \Windows\SysWOW64
This is tested on 17134.112 Version 1803 of Windows 10.
Could that really be it? For some reason I had a file held in the Computer\HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager\PendingFileRenameOperations registry key, preventing me from installing, but zapping the key & stub program, and I was able to follow the steps (I’m still not sure if you copy the dlls into the MSSQL\Binn or Tools\BInn directories, so I copied them to both!) and yes, it worked. I even could run the SP4 update.
And now I can use Access 2016 with this fine ancient database.
Access 2016 with SQL 2000 via ODBC
And here we are. As always there is no larger over reaching point to this. I did have to create a linked SQL login for myself to get ODBC to login properly but it’s somewhat simple, and honestly if that sounds bizarre to you, why are you even thinking about something like this?
For me, I’m interested in the DTS of all things. Sure the new ones are fancier, and all that jazz, but I paid good money back in the day for old MS dev tools, and being able to use them without any virtualization, aka running on bare iron is all the more appealing.
Your SQL Server installation is either corrupt or has been tampered with (unknown package id).
Which clearly means I’m missing something in trying to transplant settings. Â However for some reason SQL 6.5 I can register the SQLOLE type, and boom!
SQL 6.5 in action
SQL Server 6.5 running on Windows 10
On Win64 vs Win32 and COM objects
I should mention that when registering a COM object you typically run something like this:
regsvr32.exe \mssql\binn\SQLOLE65.DLL
Which picks up the one in the default path. Â What about system32?
Well it turns out that this ‘system32’ directory is actually the 64bit system directory! Â And attempting to do this will just result in the error:
64bit regsvr32 on a 32bit COM object
The module was loaded but the call to DllRegisterServer failed with the code 0x80040005. Well great. Â This typically goes back to a permissions issue, or the wrong regsvr32.exe being called.
However on a Win64 based OS, you actually need to specify the Win32 version of regsvr32 which actually lives in the SysWOW64 directory, and run the command prompt at administrator! So you would run it like this:
With this COM object registered, you can now launch the Enterprise manager!
Also I found a semi fun way to rename the SQL server:
sp_configure ‘allow updates’, 1
go
reconfigure with override
go
delete sysservers
go
sp_addserver YOURSERVERNAME,local
go
shutdown
go
Running this and it renamed the local SQL instance, and shut it down.  Restarting and it connected to itself just fine.  Naturally change YOURSERVERNAME to whatever your hostname is.  SQL server always wants to be called whatever the actual hostname is, otherwise things break in strange and confusing ways.
Thoughts
Is this terribly useful? Â Probably not. Â But I think it’s kind of interesting to run 90’s era server software in the 21st century. Â Sure I wouldn’t want to run any of it in any type of production environment, but it shows at it’s core how Win32 has not drifted. Â However looking at the Microsoft Management console of SQL Server 7.0, and how it will not either run on Windows 10, nor will the snapin run show just how fragile the house of COM turned out to be, and meanwhile good old fashioned Sybase/Win32 Â code still runs from 1993 onward.
I suppose the next thing to do is to try it on Wine, or a fun enough debugger/syscall trace to see what on earth SQL 7.0’s problem is. Â I don’t have any doubt that it’s nothing that can’t be fixed, although back to the root point, would you really want SQL 7.0 in 2016… or even SQL 2000 for that matter.
Unlike Windows Vista, the setupdll.dll from Windows NT 4.0 will not work. Â I used the one from Windows NT 3.1, and it will run the setup program fine, but the file copy bombs out. Â I went crazy and modified the setup.inf to not actually copy files, take an xcopy of the raw files from an old install, and it won’t even try to install the service, it just builds a master database, and exits.
Trying to run the SQL Server directly, and you get this fun error:
16/10/13 21:19:08.40 kernel  Unable to start due to invalid serial number.
Well isn’t that great. Â So naturally you either have to install it, or just import an existing registry key setup like this SQL.REG file.
If you are so inclined, you can even remove named pipe support, and have it listen only on TCP/IP. Â Or the other way around. Â Or even change the TCP port.
In the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer\Server key there should be a Multi-String key called ListenOn … and it’s just the network transport DLL, and how they listen. Â As you can see SSNMPNTW is the named pipe transport, and SSMSSOCN is the TCP/IP transport.
ListenOn key
SQL Server really wants to load the TCP/IP trasnport DLL from the Windows\System32 directory. On 64bit versions of Windows, however that means copying the SSMSSOCN.DLL file to the SysWOW64 directory, in order to have TCP/IP networking.
SQL server is not terribly useful in it’s current state as it turns out that master database is actually empty. Â So without stored procedures or much of anything you really can’t do anything with it. Â However looking at the install directory there is a bunch of SQL scripts. Â Even better on the VM where I’ve installed it, there is some output files, that by their date & time tell me in what order to run them!
First things first, I copy the following files from the C:\SQL\DLL diredctory to the C:\SQL\BINN directory so I don’t have to mess with paths:
Now I can run the SQL server from the C:\SQL\BINN directory
START SQLSERVR.EXE
And now it’s running.  If there is any issues, or your master database is either damaged, or just plain doesn’t exist, you can create one with the BLDMASTR.EXE program.
C:\sql\BINN>BLDMASTR.EXE Buildmaster 4.20a NT : Wed Jan 26 12:37:00 1994
Master disk name? (default is master.dat) :
Master disk size (in 2k blocks)? (default is 6144) :
Configuration only? (default is N) (y or n) : n
Databases only? (default is N) (y or n) : n Master device: master.dat writing configuration area writing the MASTER database writing the MODEL database writing allocation pages for remaining 7 MB, (3584 pages) 7 MB Buildmaster complete
It’s that simple! Â Move the master.dat file into the C:\SQL\DATA directory and if the server doesn’t find it by itself, you can just tell it where it’s located:
START SQLSERVR.EXE -d ..\DATA\master.dat
And it should start.
Now the file CONFIG.SQL needs to be modifed (if you have a prior config) or created.
The two key lines are:
update master.dbo.sysdevices set phyname=’C:\SQL\DATA\MASTER.DAT’ where name = ‘master’ sp_addserver YOURMACHINE,local
Which as you can imagine simply sets where the master database lives, and what the machine name is, as SQL server LOVES to know the correct machine name. Â With that done, here is my simple script for populating the master database:
And once this has finished it will have populated all the tables in your master database. Â Hit CTRL+C in the SQL Server window, and it’ll shut down. Re-launch it, and it’ll be initialized.
Assuming this all went according to plan, you can now launch SQLADMIN, the SQL Administrator, and then you can get a connect screen like this (I only got it running with the named pipes transport…)
SQL Admin connect
Remember by default the sa user has no password!
SQL Administrator
And we are good to go! Â Feel free to grab SP4, and apply it by unzipping, and copying the files & dll’s to their respective places. Â So there we go, from January of 1994, to October of 2016, SQL Server still running!
