This guide uses the Terminal almost all the way. This is not for everyone, you need some understanding of your Linux!
There are 2 versions of SQL available on Linux: 2017 (evaluation - but it's for 64 bit apps only) and 2000. This guide will follow the installation of 2000 - the game can only be run 32 bits...
Installing SQL 2000 under wine:
Note for 64 bits systems: for any wine/winetricks related instruction to SQL server and your top server, you have to add this:
env WINEARCH=win32 WINEPREFIX=~/.wine32
This is because Winetricks prevents dotnet20sp2 from installing into a 64 bits prefix - it is 32 bits only! Examples:
env WINEARCH=win32 WINEPREFIX=~/.wine32 winetricks winxp
env WINEARCH=win32 WINEPREFIX=~/.wine32 winetricks dotnet20sp2
env WINEARCH=win32 WINEPREFIX=~/.wine32 wine MSDE2000A
First, make sure you have wine and winetricks installed. Then, download MS SQL 2000 from here: https://www.microsoft.com/en-us/download/details.aspx?id=22661.
Then, to configure Wine to support SQL 2000, set it to win xp mode and install Net Framework 2.0 SP 2 and MDAC 2.8 (SP 2 is not needed but it is recommended):
Then, cd to folder where MSDE2000A.exe is and install it - Remember to use .wine32 folder instead of .wine to use the 32 bits prefix on a 64 bit system:
wine ~/.wine/drive_c/MSDERelA/setup.exe DISABLEROLLBACK=1 BLANKSAPWD=1 DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL
The installer will fail! When time out failures start appearing, do a ctrl+c to abort, then copy the remaining files over - - Remember to use .wine32 folder instead of .wine (on both parameters) to use the 32 bits prefix on a 64 bit system, and to use your user name instead of "myusername":
cp ~/.wine/drive_c/users/myusername/Temp/SqlSetup/Temp/*.* ~/.wine/drive_c/windows/system32/
Now installation is finished. Restart wine or computer (otherwise SQL service might not run). Then MSSQLSERVER service will start automatically when first running a program on wine on 32 bits prefix. You can also do the following to start it manually directly:
wine net start MSSQLSERVER
To start the program that controls the service, you can do the following:
wine "C:/Program Files/Microsoft SQL Server/80/Tools/Binn/sqlmangr.exe"
Sources: http://mynixworld.info/2012/12/22/ms-sql-server-on-linux-yes-it-works/ https://appdb.winehq.org/objectManager.php?sClass=version&iId=11016
Before we continue, the dbs have to be in the same environment of the installed SQL server. That means, you are using the ~/.wine32/drive_c folder on 64 bits and ~/.wine/drive_c on 32 bits.
These folders are usually hidden, so you might need to unhide them first (you need to configure that in your system). Then unpack the db files to a folder inside that drive_c folder.
This guide will show how to setup SQuirreL SQL client. SQuirrel SQL and similar gui clients should be used for fully setup gui Linux environments and ease of use. This guide will assume you already installed the SQL server.
Setting up SQuirreL SQL client and connecting to server:
Download it using your package manager - on Arch Linux it's named squirrel-sql.
After installing it, download MSSQL JDBC driver depending on the SQL version installed:
SQL 2017: version 6+: sqljdbc_6.0.8112.100_enu.tar.gz. Extract sqljdbc41.jar (enu/jre7 folder) if SQL client uses Java 7, or sqljdbc42.jar (enu/jre8 folder) if using Java 8.
SQL 2000: version 3.0: sqljdbc_3.0.1301.101_enu.tar.gz. Extract sqljdbc4.jar.
Setting up the SQL client:
Start the client, click the drivers tab on the left side of the window, then right click "Microsoft MSSQL Server JDBC Driver", then click modify.
Click "Extra Class Path" tab, then click "Add" button.
Now search for that file you just extracted. Then click "OK". On the bottom of window, you'll see "Driver class com.microsoft.sqlserver.jdbc.SQLServerDriver successfully registered for driver definition: Microsoft MSSQL Server JDBC Driver", that means it worked.
Now click the "Aliases" tab on left side of window. Then click the "+" sign button (create new alias).
Put any name, select "Microsoft MSSQL Server JDBC Driver". Now for the url, it's going to be like this:
localhost can be the ip of your SQL server, if not local, and the 1433 can be changed to another if you have another SQL 2000 instance running or are using another port for the SQL server.
The user name is sa, and:
SQL 2017: put the sa password given at installation.
SQL 2000: leave the pass blank.
Then click OK.
Connecting to SQL server:
Now start SQL server if not started yet:
SQL 2017: it already starts on system start.
SQL 2000: starts only if you manually run a wine command or run the SQL manager.
Then click "Aliases" tab again, select the connection you created, and click the Connect button.
To run SQL commands, there's a set of tabs: Objects, SQL, Hibernate, Monitor. Click the SQL tab. Now the torture will start. Type the commands in the SQL commands section and then click the first line, and click the "running guy" button. If you did any mistakes, red text is going to appear on the bottom.
Now continuing, this is the tricky part for those who don't know the SQL language (like me) - you gotta attach using the SQL language (specifically, TSQL for SQL 2000)! The scripts that follow will be the same in all and any SQL clients.
Attaching the databases (using SQL 2000 language):
EXEC sp_attach_db @dbname = 'dbname',
@filename1 = 'dbpathfile.mdf',
@filename2 = 'dbpathfile.ldf';
Where dbname is the name of db (either 'accountserver' or 'gamedb'), the dbpathfile.mdf is the path to the db .mdf file in wine, like 'C:\folder\db.mdf' and dbpathfile.ldf is the path to the db .ldf in wine too. So for example:
EXEC sp_attach_db @dbname = 'accountserver',
@filename1 = 'C:\games\top-server-db\accountserver_data.mdf',
@filename2 = 'C:\games\top-server-db\accountserver_log.ldf';
EXEC sp_attach_db @dbname = N'gamedb',
@filename1 = N'C:\games\top-server-db\gamedb_data.mdf',
@filename2 = N'C:\games\top-server-db\gamedb_log.ldf';
Creating SQL logins (using SQL 2000 language):
Now you have to create the logins needed by the server. Type in:
EXEC sp_addlogin 'account', 'pass', 'db';
EXEC sp_addlogin 'pko_account', 'Y87dc#$98', 'AccountServer';
EXEC sp_addlogin 'pko_game', 'Y87dc#$98', 'GameDB';
Assigning ownership of databases to a SQL login (using SQL 2000 language):
Of course, changing the account names/passwords depending on server file cfgs. Then add the users to the dbs as owners, by typing in:
EXEC sp_adduser 'account', 'account', 'db_owner';
EXEC sp_adduser 'pko_account', 'pko_account', 'db_owner';
EXEC sp_adduser 'pko_game', 'pko_game', 'db_owner';
Creating in-game accounts:
Using one of the sql clients above, use the guide below:
But just in case the forums get deleted for some reason, it's a good idea to keep a summary of it here - first part to create account, second part to make it gm or not:
INSERT INTO account_login (name, password) VALUES ('<Login>', '<PasswordMD5>')
INSERT INTO account (act_id, act_name, gm) VALUES ((SELECT MAX(act_id) + 1 FROM account), '<Login>', <GM-level>);
Getting the server up and running:
Now you also need to unpack the server files to a folder in the:
SQL 2000: wine environment used by SQL server.
SQL 2017: anywhere as long as you own the folder. It doesn't matter which wine prefix you use either. Databases need to be in the certain folder specified in SQL 2017 guide part.
When you do, run them. Now, if you run gameserver, you might run into some errors that usually won't happen in Windows:
"Bad allocation" error: Your server is using too much memory. 32 bit programs can use at most 4 GB of memory, but on Linux, for some reason, it can't use that much. Maybe even 2 GB is too much. I'm still not sure how to fix this, but the workaround is to load less things into 1 game server. Running linux with just barebones installed might help (which you would usually do on a dedicated server).
"Access Violation" error on GateServer: Regular IP's put at IP key on [ToClient] will make this error to appear. You have to put a long and invalid string on that field. 1231234h25giftuihfi223 is good, hdf8duity357hruasdklhfj is good too. Anything is good, as long as it's not an IP and is long (maybe over 16 bytes long).
(EXTRA) Running client on Linux notes:
The client can run on a 64 bits prefix wine as well, and performance is like on Windows for me. There are some things to take note that are as follows:
It's best to install wine-staging-nine - it has patches that improve dx 9 games. I didn't test the client with it yet, but it should work. Configure it at winecfg at Staging tab.
Some private server launchers will require wine-mono to be installed. Net Framework 4.0 might work too (look here: https://wiki.archlinux.org/index.php/wine#Installing_.NET_Framework_4.0). When installing SQL 2000, mono gets uninstalled from that prefix. You might want to create another prefix just for that.
The flash bug on the server selection screen has the workaround which is on wine online database. But there's another workaround found by me which only applies to a Linux with borderless and titlebar-less window management - just move the window to the top-left corner until it snaps there. Now Flash will draw normally like on Windows!