SQL Server 2000 on Windows 10

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

Seriously, it turns out that 2015 can’t just link to ODBC, that the libc thing that gave me SDL grief is deeply entrenched all over the place.  So in this case you need to link against legacy_stdio_definitions.lib. Fantastic.

I get my DLL, and yes, it’s a Windows 32bit ODBC driver!

FreeTDS Access failure

And yeah, lots of failure.

A red-herring was seeing this in the trace:

net.c:741:Sending packet
0000 01 01 00 2b 00 00 00 00-53 45 4c 45 43 54 20 43 |…+…. SELECT C|
0010 6f 6e 66 69 67 2c 20 6e-56 61 6c 75 65 20 46 52 |onfig, n Value FR|
0020 4f 4d 20 4d 53 79 73 43-6f 6e 66 |OM MSysC onf|

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.

SQL Server 2000 (Dev) on Windows 10

And then I saw this epic thread, Windows 10 & My SQL Server 2000 Personal.

I managed to install following these steps:

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

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.

SQL Server 6.5 on Windows 10 x64

SQL Server 6.5 running on Windows 10

In the same effort as getting SQL Server 4.21a running on Windows 10, I found that SQL Server 6.5 will run as well.  For what it’s worth, SQL Server 6.0 runs, but the enterprise manger will not run, giving this fun error:

sdf

The SQLOLE OLE object could not be registered.

And SQL 7.0 just bombs out with this:

x

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

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?

%SYSTEMROOT%\system32\regsvr32.exe \mssql\binn\SQLOLE65.DLL

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

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:

%SYSTEMROOT%\SysWOW64\regsvr32.exe \mssql\binn\SQLOLE65.DLL

And you should get:

adf

32bit regsvr32 working

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.

SQL Server 4.21a on Windows 10 x64

SQL Server 4.21a!

SQL Server 4.21a!

It’s been 7 YEARS, since I last took the SQL Server 4.21a plunge by getting it running on Windows Vista.  I was thinking with all this Windows NT 3.1 fun, I should get SQL Server 4.21a up and running on my current Windows 10 machine.  However this proved more involved.

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

ListenOn key

However it’s not terribly useful 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:

NTWDBLIB.DLL
OPENDSNT.DLL
SQLEVENT.DLL
SQLTL32.DLL
SSNMPNTW.DLL
SSMSSOCN.DLL

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:

CHARSET.EXE /S . CP437\noaccent.437
isql -Usa -P < ..\INSTALL\INSTMSTR.SQL
isql -Usa -P < ..\INSTALL\INSTNT.SQL
isql -Usa -P < ..\INSTALL\INSTMODL.SQL
isql -Usa -P < ..\INSTALL\INSTCAT.SQL
isql -Usa -P < ..\INSTALL\CONFIG.SQL
isql -Usa -P < ..\INSTALL\INSTPUBS.SQL
isql -Usa -P < ..\INSTALL\ADMIN2.SQL
isql -Usa -P < ..\INSTALL\OBJECT2.SQL
isql -Usa -P < ..\INSTALL\HELPSQL.SQL

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

SQL Admin connect

Remember by default the sa user has no password!

SQL Administrator

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!

ReactOS hits 0.4!

So as a totally unfair test, I thought I’d load SQL server 6.5 on it.

SQL Server 6.5 on ReactOS

SQL Server 6.5 on ReactOS

After copying in some NT 4.0 dll’s regarding licensing it’ll start to install but the service itself doesn’t work.

bummer.

MSDE2000 didn’t work either.

And the setup program from SQL 4.21 just crashed.

Running MS SQL Server 4.21 on OS X.

Yes, you read that right.  Thanks to the power of Crossover (Wine) I’m running SQL 4.21 on OS X.  But the installation is *NOT* straightforward.  Actually it doesn’t install at all.  But you can ‘transport’ a working copy from a Windows machine into Wine, and it’ll run.

The first thing is, if you’ve ever dealt with MSSQL is that it is VERY picky about machine names.  So first install MS SQL 4.21 in a NT 3.1/3.5/3.51/4.0 VM with a name you like. Also remember to set the default client library to TCPIP. Then create a Wine instance on your target box.  Next you’ll need to make a few changes to the registry to force the machine name:

HKEY_CURRENT_USER\Software\Wine\Network

UseDnsComputerName

REG_SZ N

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\ComputerName\ActiveComputerName

ComputerName

REG_SZ MSDE

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\ComputerName\ComputerName

ComputerName

REG_SZ MSDE

I called my machine MSDE, as I have no imagination.  With those keys in place the next thing to do is grab the service keys..

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLServer

and

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLMonitor

Then finally the MS SQL product key:

HKEY_LOCAL_MACHINE\Software\Microsoft\SQLServer

The final step was to copy over the c:\sql directory, and create icons for the SQL Server, and the management tool.

To run, start the server first (SQLSERVR.EXE) , then launch the admin tool (SQLADMIN.EXE), and you should (hopefully) be able to connect!

MS SQL Server 4.21 running on OS X

MS SQL Server 4.21 running on OS X

And there we go! Is it useful? Not really, but I mean it is cool! I haven’t tried this with 6.0 or 6.5 but I imagine they should work as well. 7.0 and beyond are more complicated to transpose as they require a great deal of COM integration. In theory they should work.

Sometimes less is more.

 

The more they overthink the plumbing, the easier it is to stop up the drain.

-Star Trek III

Well as of late we’ve been fighting this aging SQL server at work. It was originally a NT 4.0 server with SQL 6.5 upgraded to 7.0 then 2000 with Windows 2000. It also had to do some work with Oracle, and the dba’s were using MSDTC to make sure their transactions were getting committed into Oracle. Oh and to keep it ‘fresh’ the Oracle client was version 7 as it originally was talking to an Oracle 7 DB.

Well as the years go by, that Oracle 7 DB became Oracle 9i (already obsolete!), and we suddenly hit a transaction wall.

And along the way we virtualized the server to go into our VMWare ESX 2.x cluster, and it’s been since migrated to VMWare ESX 3.5

The server was dropping tens of thousands of these XA???????????????????.trc files, into the \winnt\system32 directory.

Well naturally you’ll eventually hit this wall of how many 8.3 translations you can do before the system CRAWLS. And boy oh boy did we hit that wall. So at first my idea was to delete all of these trc files, and let it live, but that’s not such a ‘great’ idea… As this reeks of a fundamental problem.

So the ‘first’ step in all of this madness was to up the OS to Windows 2003 enterprise (it was 2000 Advanced server before) And see how things were doing. The OS upgrade went smoothly I had slipstreamed sp2 into the update, so I only had some 90+ updates needing to be done once the OS had been upgraded. And for ‘good’ measure I thought I’d take the server from 768MB of ram up to 2GB, and set VMWare to allow 4 cpu’s for the database server. The node it was running on wasn’t doing terribly much so what the hell right?

Wrong…

The server was now performing markibly SLOWER… And yes, still dropping TRC files like there was no tomorrow.

After a bunch of digging around, I found out that in 2003 you have to click a box in the component manager to allow XA (cross architecture) transactions! Well now it wasn’t dropping as many XA trc files, but after watching it for a while, when two went to run at the same time, the SQL server would crash with a hex code saying it was out of memory.

Out of memory? I’d just given it more!?

So I did the ‘logical’ thing and gave the system 5 GB of ram, and enabled the /3GB flag in boot.ini

Same crashes.

I moved SQL server up to using 2GB of ram (out of 5, sure why not?). Same error.

Well this sucked, so we tried to update the Oracle client from 7 to 9i. In the process I found I couldn’t un-install the 7 client, nor could the 9i thing just ‘upgrade’ it, 9i kind of installed in parallel. Which led us to our next major fault, after swapping out the new client, removing the Oracle product key from the registry, and re-linking the Oracle servers using a new registration string, now ALL of our transactions against the oracle servers were failing.

Thank goodness for google, as we were able to deduce that the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsfot\MSDTC\MTxOCI was populated with all the old Oracle 7 values….

OracleOciLib -> oci.dll
OracleSqlLib -> orasql9.dll
OracleTraceFilePath -> c:\oratrace
OracleXaLib -> oraclient9.dll

So changing it to reflect Oracle 9i and suddenly our transactions were running! Even two at a time!!

But there was no doubt about it the transactions were slower then hell. We had gone from 1 minute to 11 minutes on one, and 5 minutes to just under an HOUR.

I added MORE memory to only find the SQL server couldn’t see the network card. So I added another one, and it got even SLOWER.

So in a minute of panic, I reduced the ram back to 768MB, took the VM from 4 cpu’s to 2 cpu’s and forced SQL server to use a single processor.

And our timings are now fantastic! That 1 minute process can complete now in 12 seconds!!! The other process finishes in about a minute give or take, but it’s tremendously faster.

From what I can gather, since SQL is so IO bound the more ‘top’ hardware you give it, the harder it pushes the IO stalling itself… Naturally it’s different on a physical machine, but sometimes it’s interesting to see what happens.

And may this be a lesson, just because it can emulate multiple CPU’s doesn’t mean it’ll run parallel things ‘better’…

Polling around….

This weekend has been kind of crappy as we had some cisco switches flake out, but nobody knew anything happened… I’d been pulled away on so many things over the last few years that monitoring them kind of fell by the waaaayside.

Well in this economic environment nobody is going to shell out tens of thousands of dollars for some simple syslog/polling system that pages people. I needed one for free.

And years ago, I wrote a simple one that revolved around SQL Server 7/MSDE 1.0

Anyways I’ve made it as 2 CD images, and if you are bored, or in the need to poll devices with a basic TCP connect you can download it here:

https://sourceforge.net/projects/simplemssqlnetw/

What may be useful for people is that I’ve put some impossible to find software on the prerequisite CD…

Namely:

NT 4.0 sp6a
MSDE 1.0
Option Pack (IIS4)
SQL 7 sp4

For some reason all the above stuff is getting dammed near impossible to find on Microsoft’s site. I guess it is end of the road for the NT 4.0 heyday. So at the least, this is my way to keep a location to download this software, as I’m sure someone will need it someday.

MSDE 1.0 for those who don’t know was a redistributable version of SQL Server 7.0 that had a 10 user limit, and a 2GB database size limit. Also there was no GUI management but it was great to use, because unlike SQL Express, it included the SQL Agent. The agent can run tasks at certain times, say like run a TSQL script that dumps a list of machines from a table into the hosts file, then tries to connect to each machine and record the state into a database…..

It was VERY useful stuff for the time.

There is no exciting screnshot, as I never did make an interface to the thing, instead I opted to configure everything through Access.

Other then that, I’ve been playing with a BackOffice 1.5 CD set I got on ebay… NT 3.51, MS Mail 3.5 & SQL 6.0!! It’s been so long, but MSSQL 6.0 was the first SQL server that I ever was payed to manage… It’s amazing how far we’ve come as an industry, and at the same point how things stay the same, although the installation of NT 3.51 is SO FAST!!!