Have you gotten an ORA-01017 error in your Oracle database? Learn what caused it and how you can resolve it in this article.
So you’re trying to connect to a database, or run a query across a database link, and you get this message:
ORA-01017: invalid username/password; logon denied
There are a few reasons that you can get this error:
- Your username or password is actually incorrect
- The database configuration is not correct (tnanames.ora, $ORACLE_SID parameter)
Now, let’s look at the solution to this error.
Get My SQL Cheat Sheets
SQL Cheat Sheets for Oracle, SQL Server, MySQL, and PostgreSQL. Save time writing SQL with these PDF reference files.
There are a few ways to resolve the ORA-01017 error:
- Check the username and password are correct
- Oracle 11g passwords are case sensitive, so ensure that your connection string caters for this
- Check the database link setup if you’re using a database link
Ensure that the Username and Password are Correct
It seems like an obvious step, but make sure that the username and password you’re entering is correct.
If you have them stored somewhere else, make sure you’re looking up the right password.
Also, try entering the username and password manually instead of copying and pasting (or vice versa) to make sure there are no issues with carriage returns or strange characters.
Case Sensitive Passwords in Oracle 11g
If you’re connecting to an Oracle 11g database, the passwords are case sensitive.
This was a new feature introduced in Oracle 11g. It’s a database setting that can be turned on and off, but if it’s on, you’ll need to treat your connection string differently.
To fix this, you can specify your password inside double quotes when connecting to the database.
For example, if your password is “DatabaseStar”, and you’re connecting to a database like this, you’ll get an error:
To resolve this error, specify the password in double quotes:
Check the Database Link Setup
If you’re getting this error while running a query, but you’ve already logged in to the database, it might be because your query uses a database link, and the connection details for the database link are incorrect.
To check the database link, you can run a simple query against it to see if it works.
You can then check the connection string to make sure that the user is correct, and that the password is case sensitive (if you’re using an Oracle 11g database).
If you’re connecting to an Oracle 11g database but running the query from a 10g or earlier version, the case sensitivity still applies. It’s related to the database you’re connecting to, not from.
Using the earlier example, if you specify your password in the connection string as DatabaseStar, you’ll get an error.
This is because the password of DatabaseStar is converted to uppercase, which will be DATABASESTAR. This is then checked against the stored value of DatabaseStar, and is found to be different.
To resolve it, put your password inside double quotes.
CONNECT TO order_database IDENTIFIED BY bob USING "DatabaseStar";
So, check these things for your database connection string.
Check Your TNSNAMES.ORA File
Your TNSNAMES.ORA file contains a list of service ID information for your databases.
You could be getting this ORA-01017 error if the information in that file is incorrect.
Open the file and check that this information is correct, such as the service name and ID.
So, that’s how you can check for and resolve any ORA-01017 invalid username/password issues.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!