topic Re: Error connecting to MySQL from Visual Analytics in SAS Visual Analytics

Error connecting to MySQL from Visual Analytics

avinashaecwb — Mon, 19 Dec 2016 11:43:05 GMT

I have SAS/ACCESS ODBC licence through which I am trying to connect to MySQL database. name of the database is "repairsdb". I have below entry created in my obbc.ini file:

[SOLVUP]
Driver = OracleODBC-10g
Server = 10.1.8.138
DATABASE = repairsdb
Port = 3306
UID = repairsdbuser
Password = repairspw

When I try to create a access it using LIBNAME statement with dsn=SOLVUP User= repairsdbuser pwd =repairspw, it throws error "do not have access rights to repairsdbuser@localhost"(PASSWORD USE =YES).

If i remove the Pwd parameter, LIBNAME is successfully assigned but when I try to access any table in data set, I get an error "no database selected" .

Could you please suggest how to make a successful connection ?

Regards

Avinash

Re: Error connecting to MySQL from Visual Analytics

Kalind_Patel — Mon, 19 Dec 2016 12:25:26 GMT

first of all,

this line " Driver = OracleODBC-10g"

confusing me in odbc.ini file,

are you sure you have configured proper driver information in odbcinst.ini and odbc.ini files ?

because for MySQL driver, you have mentioned OracleODBC-10g.

Re: Error connecting to MySQL from Visual Analytics

avinashaecwb — Mon, 19 Dec 2016 18:01:55 GMT

@Kalind_Patel Hi.. that was when I was trying different solutions. I also tried keeping Driver = MySQL . it still gavce same errors.

Re: Error connecting to MySQL from Visual Analytics

Kalind_Patel — Tue, 20 Dec 2016 05:25:43 GMT

thanks for the clarification,

it looks like that you need some additional permission from MySQL database,

You can try to connect with mysql command in linux,

I'm sure it will give you the same error,

In MySQL,

try to tun this command :

SHOW GRANTS FOR 'repairsdbuser'@'localhost';

and check what permission you are missing,

Sometimes we have to additionaly grant privilages to users like repairsdbuser@localhost

Your MySQL DBA can help you on this issue.

Re: Error connecting to MySQL from Visual Analytics

avinashaecwb — Thu, 29 Dec 2016 09:02:26 GMT

This is fixed now. I modified the odbcisnt.ini and odbc.ini files at etc and /opt/sas/SASHome/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver . Also, added envionment variables in file sasenv_local .