Saturday, 22 Jun 2024

How to Fix ORA-01017 Invalid Username/Password; Logon Denied

Have you encountered an ORA-01017 error in your Oracle database? In this article, we’ll explore the possible causes and provide effective solutions to resolve this issue.

So, you’re attempting to connect to a database or execute a query across a database link, but instead, you receive the following error message:

ORA-01017: Invalid username/password; logon denied

There are several reasons why you might encounter this error:

  • The username or password you’ve entered is incorrect.
  • The database configuration, including tnanames.ora and the $ORACLE_SID parameter, is not accurate.

Now, let’s dive into the solutions for this error.

Resolving the ORA-01017 Error

To resolve the ORA-01017 error, consider the following steps:

  • Check the username and password: Ensure that the username and password you’ve entered are correct. Sometimes, it’s essential to manually enter them instead of copying and pasting to avoid any issues related to carriage returns or strange characters.

  • Be mindful of case-sensitive passwords: If you’re connecting to an Oracle 11g database, remember that passwords are case-sensitive. It’s a database setting that can be enabled or disabled. If it’s enabled, you’ll need to adjust your connection string accordingly. You can fix this by enclosing your password within double quotes when connecting to the database. For example, if your password is “DatabaseStar” and you’re connecting to the database as follows, you’ll encounter an error:

CONN bob/databasestar;

To resolve this, specify the password in double quotes:

CONN bob/"DatabaseStar";
Tham Khảo Thêm:  Data Breaches: A Review of Recent Incidents

While we’re on the subject, if you’re interested in an easy-to-use list of the main features in Oracle SQL, you can get my SQL Cheat Sheet here.

  • Verify the database link setup: If you’re encountering this error while running a query but have successfully logged into the database, it’s possible that the query utilizes a database link, and the connection details for that link are incorrect. To troubleshoot, execute a simple query against the database link to check if it functions properly. Additionally, review the connection string to ensure that the user and password are correct. Remember that the password is case-sensitive for Oracle 11g databases, regardless of the version from which you’re running the query.

Using the previous example, if you specify your password in the connection string as “DatabaseStar,” you’ll encounter an error. This is because the password “DatabaseStar” is converted to uppercase as “DATABASESTAR” before being compared to the stored value of “DatabaseStar,” resulting in a mismatch. To fix this issue, enclose your password within double quotes.

CONNECT TO order_database IDENTIFIED BY bob USING "DatabaseStar";

Therefore, carefully check these aspects when configuring your database connection string.

  • Review your TNSNAMES.ORA file: Your TNSNAMES.ORA file contains a list of service ID information for your databases. An incorrect entry in this file can lead to the ORA-01017 error. Open the file and ensure that the information, such as the service name and ID, is correct.

So, these are the steps you can take to identify and resolve ORA-01017 invalid username/password issues.

While you’re here, if you’d like a comprehensive list of the main features in Oracle SQL, feel free to get my SQL Cheat Sheet here.

Tham Khảo Thêm:  How to Tell If Your Security Camera Has Been Hacked