Upvote Upvoted 3 Downvote Downvoted
SQL Database Connections w/Sourcemod & DigitalOcea
posted in Q/A Help
1
#1
0 Frags +

I know there's definitely some smarter people who browse these forums and I'm hoping someone might be able to assist me with my issue.

I'm currently running a digital ocean droplet with ubuntu 16.04 running a simple LAMP stack and hosting my mumble server on it. I've got a functional installation of MySQL and phpMyAdmin for database management. Accessing the database locally on the server via SSH gives me no problems, nor does directly editing database schema via phpMyAdmin, however when I supply the database user information for sourcemod in databases.cfg, my server can not make a connection regardless of my efforts. I'm essentially wanting to have support for more robust admin management using the sql-threaded plugin which is loading properly as well as the sql-admin-manager plugin.

On the droplet, I also have firewall rules to support I/O traffic on default mysql port, ssh, http/https, and mumble default port.

I know there's definitely some smarter people who browse these forums and I'm hoping someone might be able to assist me with my issue.

I'm currently running a digital ocean droplet with ubuntu 16.04 running a simple LAMP stack and hosting my mumble server on it. I've got a functional installation of MySQL and phpMyAdmin for database management. Accessing the database locally on the server via SSH gives me no problems, nor does directly editing database schema via phpMyAdmin, however when I supply the database user information for sourcemod in databases.cfg, my server can not make a connection regardless of my efforts. I'm essentially wanting to have support for more robust admin management using the sql-threaded plugin which is loading properly as well as the sql-admin-manager plugin.

On the droplet, I also have firewall rules to support I/O traffic on default mysql port, ssh, http/https, and mumble default port.
2
#2
1 Frags +

Have you changed the

mysqld.cnf

file in

/etc/mysql/mysql.conf.d/mysqld.cnf

(i think) and changed the

bind-address

to 0.0.0.0? (Restart the mysql service after that change) Spent ages trying to do the same. Also need to create a user that listens to all addresses so

CREATE USER '<username>'@'%' IDENITIFIED BY '<password>';

then

GRANT ALL ON *.* TO '<username>'@'%';

or whatever. I think that should fix it.

Have you changed the [code]mysqld.cnf[/code] file in [code]/etc/mysql/mysql.conf.d/mysqld.cnf[/code] (i think) and changed the [code]bind-address[/code] to 0.0.0.0? (Restart the mysql service after that change) Spent ages trying to do the same. Also need to create a user that listens to all addresses so [code]CREATE USER '<username>'@'%' IDENITIFIED BY '<password>';[/code] then [code]GRANT ALL ON *.* TO '<username>'@'%';[/code] or whatever. I think that should fix it.
3
#3
0 Frags +

Thanks for the quick reply Thermite, I believe I've gone through these steps as recommended by a stack overflow thread but I'll give it another shot in a bit. If anyone else has other suggestions feel free to keep the coming.

Thanks for the quick reply Thermite, I believe I've gone through these steps as recommended by a stack overflow thread but I'll give it another shot in a bit. If anyone else has other suggestions feel free to keep the coming.
4
#4
0 Frags +

I've had issues with sourcemod plugins that do things outside of TF2 being functionally disabled by selinux and it is very possible that the ubuntu solution of apparmor is doing the same. Try disabling apparmor if #2 doesn't work.

As an aside, google compute offers a free low-end vm that is more than enough to run a mumble server on, which may save you some money.

I've had issues with sourcemod plugins that do things outside of TF2 being functionally disabled by selinux and it is very possible that the ubuntu solution of apparmor is doing the same. Try disabling apparmor if #2 doesn't work.

As an aside, google compute offers a free low-end vm that is more than enough to run a mumble server on, which may save you some money.
5
#5
serveme.tf
3 Frags +

If everything runs on the same machine, please do not set the bind-address to 0.0.0.0.

Do you get an error in sourcemod? Have you checked the sourcemod logs, have you checked the mysqld logs?

If everything runs on the same machine, please do not set the bind-address to 0.0.0.0.

Do you get an error in sourcemod? Have you checked the sourcemod logs, have you checked the mysqld logs?
6
#6
1 Frags +
ArieIf everything runs on the same machine, please do not set the bind-address to 0.0.0.0.

Do you get an error in sourcemod? Have you checked the sourcemod logs, have you checked the mysqld logs?

This is the sourcemod error I recieve:

L 12/04/2018 - 19:26:11: [sql-admin-manager.smx] Could not connect to database: [1045]: Unknown error 1045
[SM] Could not connect to the database.

This error is complaining about not being able to establish a database connection using the provided user/pass combination, but I have verified it multiple times, and used it to log directly into mysql seperately.

The sourcemod logs don't give me many clues, I'll include a pastebin dump:
Sourcemod log file

I understand that having your mysql bind address to 0.0.0.0 is pretty insecure and I do have it set to such atm while I attempt to reduce as many hindrances as possible to get this functional. I haven't checked the mysqld logs but I will not and update my post shortly.

Edit:

I followed this guide to set up logging files for mysql as I suppose they aren't enabled by default. I'll test a bit and see if I can have some generated.

[quote=Arie]If everything runs on the same machine, please do not set the bind-address to 0.0.0.0.

Do you get an error in sourcemod? Have you checked the sourcemod logs, have you checked the mysqld logs?[/quote]

This is the sourcemod error I recieve:
[code]L 12/04/2018 - 19:26:11: [sql-admin-manager.smx] Could not connect to database: [1045]: Unknown error 1045
[SM] Could not connect to the database.[/code]

This error is complaining about not being able to establish a database connection using the provided user/pass combination, but I have verified it multiple times, and used it to log directly into mysql seperately.


The sourcemod logs don't give me many clues, I'll include a pastebin dump:
[url=https://pastebin.com/VAzPgHTe]Sourcemod log file[/url]

I understand that having your mysql bind address to 0.0.0.0 is pretty insecure and I do have it set to such atm while I attempt to reduce as many hindrances as possible to get this functional. I haven't checked the mysqld logs but I will not and update my post shortly.


Edit:

I followed [url=https://stackoverflow.com/questions/5441972/how-to-see-log-files-in-mysql]this guide[/url] to set up logging files for mysql as I suppose they aren't enabled by default. I'll test a bit and see if I can have some generated.
7
#7
0 Frags +

Here are my logs files from /var/log/mysql/

mysql_error.log

error.log

Nothing too interesting from what I can gather, the IP's that I removed from error.log are not from services that I recognize the IP from, and not from the TF2 server I am trying to establish the database connection from. However, I did see that there is an issue setting up SSL with an attached library error.

[Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

I'm not sure if this is enough to prevent a remote connection, but I feel like I'm grasping at straws.I am running https on my server with LetsEncrypt, something that I didn't mention in my OP.

Here are my logs files from /var/log/mysql/

[url=https://pastebin.com/ZH7QSzfb]mysql_error.log[/url]

[url=https://pastebin.com/StCZ29V9]error.log[/url]

Nothing too interesting from what I can gather, the IP's that I removed from error.log are not from services that I recognize the IP from, and not from the TF2 server I am trying to establish the database connection from. However, I did see that there is an issue setting up SSL with an attached library error.
[code][Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key[/code]
I'm not sure if this is enough to prevent a remote connection, but I feel like I'm grasping at straws.I am running https on my server with LetsEncrypt, something that I didn't mention in my OP.
8
#8
serveme.tf
0 Frags +

MySQL 1045 is an authentication error. So your sourcemod can connect to MySQL server, it just doesn't have the right credentials. You probably didn't set up the database user correctly in MySQL (Thermite's post has the right commands) or made a typo in your sourcemod configuration.'

Also, please bind to 127.0.0.1 if everything runs locally. Else you'll be mining bitcoins for someone soon.

MySQL 1045 is an authentication error. So your sourcemod can connect to MySQL server, it just doesn't have the right credentials. You probably didn't set up the database user correctly in MySQL (Thermite's post has the right commands) or made a typo in your sourcemod configuration.'

Also, please bind to 127.0.0.1 if everything runs locally. Else you'll be mining bitcoins for someone soon.
9
#9
0 Frags +

Thanks to everyone for the help. I have established a database connection through my tf2 server now, the issue was having my mysql user account set to 'user'@'localhost' instead of @'%' and I feel like a bit of a fool. What is a safe way to establish remote connections through mysql? Do I just bind access for specific IP's/services I want connected?

Thanks to everyone for the help. I have established a database connection through my tf2 server now, the issue was having my mysql user account set to 'user'@'localhost' instead of @'%' and I feel like a bit of a fool. What is a safe way to establish remote connections through mysql? Do I just bind access for specific IP's/services I want connected?
10
#10
serveme.tf
1 Frags +

Here are a few ways
- Limit MySQL access with iptables to certain IPs
- Put the servers in a VPN, bind MySQL to the VPN address. (Has DO launched their VPC solution yet?7)
- Set up tunnels to the server hosting the MySQL server
- Restrict the MySQL user to a certain IP: 'foobar'@'123.123.123.123' (I like this one the least, since your mysql server is still reachable from the outside and bound to 0.0.0.0)

Here are a few ways
- Limit MySQL access with iptables to certain IPs
- Put the servers in a VPN, bind MySQL to the VPN address. (Has DO launched their VPC solution yet?7)
- Set up tunnels to the server hosting the MySQL server
- Restrict the MySQL user to a certain IP: 'foobar'@'123.123.123.123' (I like this one the least, since your mysql server is still reachable from the outside and bound to 0.0.0.0)
Please sign in through STEAM to post a comment.