Connecting to JSS DB

[mqh7]

I downloaded SequelPro. When I launch it I am asked for the HOST name. I enter our server name (where JSS is installed and it's also the server I installed SequelPro on) and it says It can't find the host. "Unable to connect"

how do you normally connect using this tool? I know I'm an Admin so it's not a rights issue.

[jarednichols]

Yes, it is a rights issue. You're connecting to MySQL, not the box.

https://jamfnation.jamfsoftware.com/article.html?id=114

[mqh7]

Interesting, thanks. I've tried what you said and I still can't connect. I am NOT using a domain ID. I'm trying to use a Local account. I RDC into our Mac/JAMF server using ADMIN and the local ADMIN password. So maybe I'm doing the GRANT SELECT syntax wrong. What should I type giving the following information.

User name: Admin Server name: JSS-ADMIN1 (admin is a local account on this box)
password: JSS1ADM2

[mqh7]

I notice I get this error: MySQL said "Host JSS-ADMIN1 is not allowed to connect to this MySQL server.

[donmontalvo]

http://www.jamfsoftware.com/libraries/pdf/white_papers/Manually_Installing_the_JAMF_Software_Server....

[mqh7]

Donmontalvo, our JSS environment is up and running. I can access it all from the web portal. So the DB is configured. I just need to create my own custom reports. I've tried installing sequelpro and tried to have it connect to our DB but it does not connect.

How can I confirm what my issue is? Is there a log file sequelpro creates? Evenlog information on the Mac?

[jarednichols]

Did you create a SQL user or are you trying to log in with the OS user?

[mqh7]

OS user.

[donmontalvo]

See step 5 on page 9:

http://www.jamfsoftware.com/libraries/pdf/white_papers/Manually_Installing_the_JAMF_Software_Server....

[mqh7]

Ok, I used localhost and user name-jamfsw03 and I was able to connect. The only DB I saw was called Information_Schema, but it does not look like the JSS DB. What is the default JSS DB name?

[donmontalvo]

That would be step 4. :)

[mqh7]

Thanks I read that but we have a DB already created. JSS is installed and up and running. we have machines inventoried and can create reports against these machines all from the JSS web portal. I just need to access this already existing DB from a GUI tool so I can create my own custom SQL reports.

How is that done?

[donmontalvo]

You mentioned only seeing Information_Schema database, what do you see when you get in and show databases via command line?

Don

$ mysql -u <your-mysql-username> -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 246
Server version: 5.0.92-log Source distribution

Cannot read termcap database;
using dumb terminal settings.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| jamfsoftware       | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

mysql> use jamfsoftware;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_jamfsoftware                               |
+------------------------------------------------------+
...[snip, snip, snip]...
+------------------------------------------------------+
162 rows in set (0.00 sec)

mysql>

[mqh7]

when I do that I see what you see......I see Information_schema, jamfsoftware, mysql, performance_schema and test.

But from the sequelpro tool I don't see the jamfsoftware DB.

[donmontalvo]

Ya, I don't have experience with the GUI application...have you pinged JAMF Support? If they help you fix the problem, would be great to see the answer. :)

[jarednichols · Accepted solution]

Try MySQL Workbench instead. It's actually from MySQL instead of some other third party.

[mclinde]

Hi anyone. I'm attempting to do this with SQL Studio 2012 and the MySQL ODBC Connector. I haven't found great information about this, but here's where I'm at (hopefully someone can help?).

I've installed the MySQL ODBC connector on my PC workstation where I have SQL Studio.
I'm attempting to set up the ODBC connection using the following information:
Data Source Name (server name)
TCP/IP Server (server by IP) Port 3306
User (the jamf software user and password as per JSS setup)
Database: jamfsoftware

I have confirmed the user has the GRANT ALL command completed, and access to the full database, but when I attempt to test the ODBC connector I get the following error:
Connection Failed
[MySQL][ODBC 5.2(w) Driver] Host 'itpcwin8' is not allowed to connect to this MySQL server.

So I need to do something else to allow my local PC to access the JSS via ODBC, but I am not a database guy by nature. Can anyone assist me?

[kitzy]

Hi @mclinde,

My suspicion is that the issue you're running into deals with the way MySQL handles rights. You not only have to specify a username and password, but you must also specify from where that user will be connecting.

For example, if my grant was

GRANT ALL ON jamfsofware.* TO 'jamfsofware'@localhost

then user jamfsofware would only be able to connect to the jamfsofware database from the local host. It sounds like you're trying to connect from another host, so you would have to add a grant that specifies that host, something like

GRANT ALL ON jamfsofware.* TO 'jamfsoftware'@10.x.x.x

Make sure you substitute the IP address of the machine you want to initiate the connection from.

Hope that helps!