2

I am running a flask web server on server A, and I need to access a SQL database in remote server B.

I am getting this error.

pymysql.err.OperationalError: (1045, "Access denied for user 'MYID'@'localhost' (using password: NO)")

Can someone help me??

Below is my code:

from flask import Flask, render_template, request
from flask.ext.mysql import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST']='IP OF SERVER B'
app.config['MYSQL_PORT']='SERVER B PORT NUMBER'
app.config['MYSQL_USER']='MYID'
app.config['MYSQL_PASSWORD']='MYPW'
app.config['MYSQL_DB']='DBNAME'
mysql=MySQL(app)
mysql.init_app(app)
@app.route('/')
def index():
    cur = mysql.get_db().cursor()
    cur.execute('''SQLQUERY;''')
    rv=cur.fetchall()
    return str(rv)
user9578771
  • 19
  • 1
  • 1
  • 2

2 Answers2

1

Using a mysql client or console you should execute something like this: 

grant all privileges on DBNAME.* to MYID@localhost identified by 'MYPW';

using a user with grant privilege (usually root).

Of course you can narrow down the privileges that you grant from: all privileges to let's say: select,insert,update,delete, depending on the case.

To access the console with root open a terminal window and write: 

mysql -uroot -p

and provide the password or for passwordless root:

mysql -uroot

In case you do not know the root password follow this guide: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

In case you are on a company/university workstation with no privileges to perform the above operation, ask from the administrator to grant the privileges.

Marinos An
  • 9,481
  • 6
  • 63
  • 96
  • thanks for the help ! when I try this, `grant all privileges on DBNAME.* to MYID@localhost identified by 'MYPW';` i get the following error **ERROR 1044 (42000): Access denied for user 'MYID'@'%' to database 'DBNAME'** could this be due to administrator settings? – user9578771 Apr 16 '18 at 15:41
  • Have you used root for executing this? – Marinos An Apr 16 '18 at 15:41
  • no.. i did not have root access. won't work otherwise right? – user9578771 Apr 16 '18 at 15:42
  • No, but since it is a local installation, it is not difficult to have root access on mysql. – Marinos An Apr 16 '18 at 15:43
  • only the administrator (in the team) has the root access .. :( so I don;t know the pw for the following login `mysql -uroot -p` . In this case I should contact the admin user right? thank you so much ;D – user9578771 Apr 16 '18 at 15:47
  • The admin made the change, but still the same message. However using the following shell command in server A, I was able to make the SQL connection to server B. `ssh -p PORTNUM -CNf -L3307:127.0.0.1:3306 MYID@SERVERB'sIP` then `mysql -u MYID -h 127.0.0.1 -P 3307 -e "use DBNAME; $query;` Wondering what's the difference with flask? – user9578771 Apr 17 '18 at 10:24
  • At flask use: `127.0.0.1` as host / `3307` as port, or ask from the admin to grant access to `MYID@your-ip`, to access directly the db server. The error pasted in the original post was misleading: `Access denied for user 'MYID'@'localhost'`, since you never mentioned anything about port-forwarding before. – Marinos An Apr 17 '18 at 10:36
  • the admin made the change as follows: `grant all privileges on DBNAME.* to 'MYID'@'%' identified by 'MYPW';` shouldn't this enable access from any ip? – user9578771 Apr 17 '18 at 11:58
  • any ip except from localhost! (which is what db server sees when `port-forwarding` is used). In mysql for localhost you need an additional grant. – Marinos An Apr 17 '18 at 12:28
0

try changing your

app.config['MYSQL_HOST'] = ...

to

app.config['MYSQL_DATABASE_HOST'] = ...

this applies to the rest as well:

MYSQL_DATABASE_HOST default is ‘localhost’
MYSQL_DATABASE_PORT default is 3306
MYSQL_DATABASE_USER default is None
MYSQL_DATABASE_PASSWORD default is None
MYSQL_DATABASE_DB   default is None
MYSQL_DATABASE_CHARSET  default is ‘utf-8’

I had the same problem but this fixed it for me. Hope this helps you as well. Look at the Flask MySQL reference here

Christopher
  • 37
  • 9