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:
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 ?
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.
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,
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.