So Close: DB Error

Alright, I've been at this for about a week with little success, but I think I've narrowed down my problems to just a couple of things. I've searched the forums for solutions, but what I happened to find didn't give me much help (though I did find some other minor fixes I'm sure will help once I can get this up).

I'm trying to set up a "personal private server" for my own use (to play around) and hopefully set it up for some friends of mine to join in as well (via Hamachi), but I'd love to just get myself in there right now. >_<

I'm using a router-ed LAN/WAN, SQL2000, the CentOS 4.6 "pre made" server; VM is set to Bridged, IPs are as follows:

DB Computer (Windows XP): 192.168.1.116
Linux VM: 192.168.1.20
Client Computer: 192.168.1.116

Have been following chumpy's guides for most of this (originally used Mahi's video guide, but soon changed to this). Only difference is the 2000/2005, but I think Mahi's guide gave me enough understanding to make the 2005 work well enough to set up 2000.

My logs and a photograph of the results of chumpy's "is your SQL set-up right?" query script can be found here for review. (My sa pw has been edited out of the logs. >_>)

My theory (based on the script) is that I somehow don't have "remote connections" allowed to my DB files, but I can't seem to find the option to change that anywhere in SQL2000. Of course, I could have a totally different problem that my inexperienced mind doesn't see.

------------------------------------------

A side question: When using the "sa" login, should "sa's" master database be "master" or "ACCOUNT"?

When using your logins, use 'ACCOUNT' as the main database.

;-)

Quote:

---

Originally Posted by Daman2009

When using your logins, use 'ACCOUNT' as the main database.

;-)

---

I assumed that, but even after changing that my problem still persists (though I had very little hope that would be the fix, it's another one of those "extra" things I've found out before it may become a problem :3 ).

SQL2000 has remote connections on by default so it won't be that and you don't have a "named instance" in SQL so the usual culprits are out. The logs are indicating a problem contacting the database during auth though if you check the global db agent log. I'm going to guess at xp_md5 here as that is the one thing my sql script doesnm't check (note to self: why doesn't it??). Run this query on any db:

select account.dbo.fn_md5('test')

If you get letters and numbers back then md5 support is working and if you get a "null" it's broken. If it's broken logins can't be created and login attempts will fail because the SQL sever can't encrypt passwords. If this is the case then find the xp_md5 entry in the "extended procedures" folder in the "master" database and check it's properties. Make sure the path to the .dll is correct and also check the file permissions on the .dll itself as i've seen that cause problems before.

If it isn't that then try pinging the windows ip from centos to make sure it can reach it and check firewalls are off. Something is stopping it getting through if it isn't xp_md5 at fault.

Great job on the info supplied by the way, if more people put that amount of effort in my job would be so much easier!

Alright, let me run down what you've suggested:

xp_md5: Appears to be working fine as the query brought back a string of letters/numbers. I checked it in the database ntl and saw no real issue (as far as I can tell).

Pinging: Both "computers" can ping each other successfully... <_<

Firewall: Well, my XP uses "Sunbelt Firewall" but I have that disabled while I do server work (as it messed up my first installations of VMWare) so it can't interfere I hope... The Linux should have no Firewall I assume since it's the 4.7 Pre-made server, so that just leaves my router (Linksys WRT54GS, firmware 7.50) I guess.

That being said, do I have to do port forwarding for LAN connections (it's already setup for the CentOS IP ntl) and is there any known issues with my router and trying to run a CabalPS? (I'm gonna go search for topics on this, but I'll ask ntl.)

And yeah, after reading tons of the more "noobish" cries for help, figured I might as well do it right. xD Ntl, thanks for undertaking this as your "job" chumpy. ;D

EDIT: Actually, I thought of something. I've been remaking the DB and Server a number of times already, but is it mandatory so have SQL2000 upgraded to SP4 and would that cause any issues? I don't think I did that on this last "remake" of mine...

Not sure if installing SP4 would help with your issue, but from all guides that I have looked at, they have used this update so give it a go!

Will upgrading to SP4 cause issues with an already setup (restored DBs, setup logins, etc) Database or will it upgrade the server with no loss of data?

I get the same errors ..
nickmasterinu if you fixed your problem , help me fix my too !..

Quote:

---

Originally Posted by hackalin

I get the same errors ..
nickmasterinu if you fixed your problem , help me fix my too !..

---

I'm still waiting for any answer on the possibility of the router blocking my connections. Short of port forwarding, I'd rather not mess with it too much on my own. >_<

Quote:

---

Originally Posted by nickmasterinu

Will upgrading to SP4 cause issues with an already setup (restored DBs, setup logins, etc) Database or will it upgrade the server with no loss of data?

---

No it wont change any files that have been created ( i think )!!
BUT, I would start fresh. Delete or backup databases already created, then install SP4 the create and/or restore your databases.

Because Im not sure what the SP4 update includes. Could be something to do with creating databases which might be why you cant run your cabal.

SP4 isn't strictly needed (i ran for ages on SP3 only) but it is a good idea. Installing a service pack won't affect existing data of configuration.

Some routers aren't capable of properly hosting as the port forwarding only applies to connections from outside not ones from inside the network. The simplest way to find out is to configure the server using the WAN IP and getting somebody external to try logging in, if they can and you can't it's likely your router is one that can't.

If you want to be absolutely sure the server config is working right then run cabal_config and when asked for the IP players will connect to use the centos IP. Also use the same ip in your internal.txt and if that works the the server is fine and the problem is at the router end somewhere. Using a port checker like www.canyouseeme.org is useful to work out if the ports are actually open (remember they will still show as closed if the server is not running).

Quote:

---

Originally Posted by chumpywumpy

Some routers aren't capable of properly hosting as the port forwarding only applies to connections from outside not ones from inside the network. The simplest way to find out is to configure the server using the WAN IP and getting somebody external to try logging in, if they can and you can't it's likely your router is one that can't.

If you want to be absolutely sure the server config is working right then run cabal_config and when asked for the IP players will connect to use the centos IP. Also use the same ip in your internal.txt and if that works the the server is fine and the problem is at the router end somewhere. Using a port checker like www.canyouseeme.org is useful to work out if the ports are actually open (remember they will still show as closed if the server is not running).

---

The ports are successfully open and forwarded, but I don't quite see how a testing of a "WAN" setup will help fix a connection between my server instance (CentOS) and the DB files (Windows). If the CentOS can't see the DB, having someone try to log in would prove pointless like any and all of my attempts, or would checking to see if a WAN connection, regardless of success, can try to mark the login logs show it's not a router problem...or is there something I'm missing here?

Ntl, once my friend gets up today I'll see if he'll test it for me. <_<

Ah you are right, i read through too fast and focused on the port forwarding comments (it was lunchtime when i posted and didn't have much time).

I'll think up a way of testing the sql connection from centos on my way home and update shortly.

Alright then, I shall be waiting.

I think I'll try to play around and see if I can find anything else that might help...or maybe just mess with some of the DB files, lol.

Ok, lets try this. Create a text file called test.sql with the following in in and copy to /root/

Code:

---

select account.dbo.fn_md5('test')

---

Now from a terminal or putty type:

Code:

---

isql -v CabalAccount sa password < /root/test.sql

---

That should run the contents of the .sql file using the CabalAccount DSN from etc/odbc.ini for the sql server IP, port and default database. It should fetch 1 row which is an md5 hash. If not maybe it will give some clue why the connection is failing.