Does anybody know what encrypting technique is JDeveloper/SQL Developer using to persist credentials?

Question

I'd be more than interesting for me to understand which technique is being used here to persist sensible data since I'm needing to implement a similar solution. Here's a sample connection configuration and the resulting exported snippet:

<?xml version = '1.0' encoding = 'UTF-8'?>
    <References xmlns="http://xmlns.oracle.com/adf/jndi">
        <Reference name="My Connection" className="oracle.jdeveloper.db.adapter.DatabaseProvider" xmlns="">
        <Factory className="oracle.jdeveloper.db.adapter.DatabaseProviderFactory"/>
        <RefAddresses>
            <StringRefAddr addrType="user">
                <Contents>username</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="password">
                <Contents>054D4844D8549C0DB78EE1A98FE4E085B8A484D20A81F7DCF8</Contents>
            </StringRefAddr>
        <SKIPPED />
        </RefAddresses>
    </Reference>
</References>

Any advice would be really appreciated.

Jandel

Answer 1

For the curious, what you're actually seeing is the secret key concatenated with the encrypted password. For example, I tried encrypting the password "SAILBOAT" using:

DatabaseProviderHelper.goingOut("SAILBOAT")

In this particular instance, the result was:

0527C290B40C41D71139B5E7A4446E94D7678359087249A463

The first byte is constant:

05

The next 8 bytes represent the randomly generated secret key (for the DES cipher):

27C290B40C41D711

The remaining bytes are the encrypted password:

39B5E7A4446E94D7678359087249A463

Therefore, to decrypt the password, you simply use this:

public static byte[] decryptPassword(byte[] result) throws GeneralSecurityException {
    byte constant = result[0];
    if (constant != 5) {
        throw new IllegalArgumentException();
    }

    byte[] secretKey = new byte[8];
    System.arraycopy(result, 1, secretKey, 0, 8);

    byte[] encryptedPassword = new byte[result.length - 9];
    System.arraycopy(result, 9, encryptedPassword, 0, encryptedPassword.length);

    byte[] iv = new byte[8];
    for (int i = 0; i < iv.length; i++) {
        iv[i] = 0;
    }

    Cipher cipher = Cipher.getInstance("DES/CBC/PKCS5Padding");
    cipher.init(Cipher.DECRYPT_MODE, new SecretKeySpec(secretKey, "DES"), new IvParameterSpec(iv));
    return cipher.doFinal(encryptedPassword);
}

Baumgartner

@Jason: You're welcome! I'm glad to help. I found this investigation particularly interesting. :) – Baumgartner

Someone should rewrite this in Javascript so we can decrypt passwords online (without needing to upload it of course!) – Lough

Answer 2

Note that Tim's password hash above is not for "apps_ro" - presumably he cut and pasted from the wrong place... I won't post the real password in case it's something he doesn't want shared!

I had a similar problem, trying to store my db credentials centrally (for non-secure databases!) and then exporting sql developer xml files. I have no idea what the algorithm is - however, you don't really need to know the algorithm, as you can just call the Oracle java API yourself. If you have SQLDeveloper, just grab the right Jar files:

cp /Applications/SQLDeveloper.App/Contents/Resources/sqldeveloper/BC4J/lib/db-ca.jar .
cp /Applications/SQLDeveloper.App/Contents/Resources/sqldeveloper/jlib/ojmisc.jar .

Then either load them in your Java app, or use something like JRuby as I do:

$jirb
> require 'java'
> require 'ojmisc.jar'
> require 'db-ca.jar'
> Java::oracle.jdevimpl.db.adapter.DatabaseProviderHelper.goingOut("password")    
 => "059D45F5EB78C99875F6F6E3C3F66F71352B0EB4668D7DEBF8" 
> Java::oracle.jdevimpl.db.adapter.DatabaseProviderHelper.goingOut("password")
 => "055CBB58B69B477714239157A1F95FDDD6E5B453BEB69E5D49" 
> Java::oracle.jdevimpl.db.adapter.DatabaseProviderHelper.comingIn("059D45F5EB78C99875F6F6E3C3F66F71352B0EB4668D7DEBF8")
 => "password" 
> Java::oracle.jdevimpl.db.adapter.DatabaseProviderHelper.comingIn("055CBB58B69B477714239157A1F95FDDD6E5B453BEB69E5D49")
 => "password" 

Note that the algorithm, whatever it is, has a random factor so the same password used twice can produce two different hex strings.

Roaster

Thanks much for the contribution kornelissietsma! – Jandel

This is a great solution, but note the jar locations have changed slightly in sqldeveloper 3. I use this jython code (Oracle ships jython with almost everything...):import sys; sys.path.append(r'C:\sqldeveloper\sqldeveloper\extensions\oracle.datamodeler\lib\ojmisc.jar'); sys.path.append(r'C:\sqldeveloper\modules\oracle.adf.model_11.1.1\db-ca.jar'); from oracle.jdevimpl.db.adapter.DatabaseProviderHelper import goingOut as encrypt; from oracle.jdevimpl.db.adapter.DatabaseProviderHelper import comingIn as decrypt – Secor

Answer 3

This solution works great for me... Copied from: http://www.mischiefblog.com/?p=912

import javax.crypto.*;
import javax.crypto.spec.*;
import java.security.*;

/**
 * Decrypt passwords stored in Oracle SQL Developer. This is intended for
 * password recovery.
 * 
 * Passwords are stored in
 * ~/.sqldeveloper/system2.1.1.64.39/o.jdeveloper.db.connection
 * .11.1.1.2.36.55.30/connections.xml
 */
public class Decrypt {
    public static byte[] decryptPassword(byte[] result)
            throws GeneralSecurityException {
        byte constant = result[0];
        if (constant != (byte) 5) {
            throw new IllegalArgumentException();
        }

        byte[] secretKey = new byte[8];
        System.arraycopy(result, 1, secretKey, 0, 8);

        byte[] encryptedPassword = new byte[result.length - 9];
        System.arraycopy(result, 9, encryptedPassword, 0,
                encryptedPassword.length);

        byte[] iv = new byte[8];
        for (int i = 0; i < iv.length; i++) {
            iv[i] = 0;
        }

        Cipher cipher = Cipher.getInstance("DES/CBC/PKCS5Padding");
        cipher.init(Cipher.DECRYPT_MODE, new SecretKeySpec(secretKey, "DES"),
                new IvParameterSpec(iv));
        return cipher.doFinal(encryptedPassword);
    }

    public static void main(String[] args) {
        if (args.length != 1) {
            System.err.println("Usage:  java Decrypt <password>");
            System.exit(1);
        }

        if (args[0].length() % 2 != 0) {
            System.err
                    .println("Password must consist of hex pairs.  Length is odd (not even).");
            System.exit(2);
        }

        byte[] secret = new byte[args[0].length() / 2];
        for (int i = 0; i < args[0].length(); i += 2) {
            String pair = args[0].substring(i, i + 2);
            secret[i / 2] = (byte) (Integer.parseInt(pair, 16));
        }

        try {
            System.out.println(new String(decryptPassword(secret)));
        } catch (GeneralSecurityException e) {
            e.printStackTrace();
            System.exit(3);
        }
    }
}

Newmann

Isn't that my answer with a main method? The article gives no credit. :( – Baumgartner

ops! Sorry Adam, I didn't see that. So, thanks for your answer! – Newmann

You don't have to be sorry at all. I am just a little saddened that my code was used without credit. – Baumgartner

export xpath="//References/Reference/RefAddresses/StringRefAddr[@addrType='user' or @addrType='password']/Contents/text()" – Tomchay

Unfortunately this doesn’t work on SQL Developer 4.x anymore. – Comet

Answer 4

Given solution is too old and only works with version 2.x but not now. because Oracle SQL Developer, changed the encryption algorithm in version 3.x and 4.x.

Version 3

Passwords are stored encrypted in the connections.xml file in those locations:

Windows: C:\Users\<USER>\AppData\Roaming\SQL Developer\system<VERSION>\o.jdeveloper.db.connection.<VERSION>\connections.xml
Linux: ~/.sqldeveloper/system<VERSION>/o.jdeveloper.db.connection.<VERSION>/connections.xml

Version 4

Passwords are stored encrypted in the aforementioned connections.xml file but the encryption key uses a machine-unique value db.system.id in the product-preferences.xml file accessible here:

Windows: C:\Users\<USER>\AppData\Roaming\SQL Developer\system<VERSION>\o.sqldeveloper.<VERSION>\product-preferences.xml
Linux: ~/.sqldeveloper/system<VERSION>/o.sqldeveloper.<VERSION>/product-preferences.xml

To decrypt latest encrypted file you can use Show me password extension for SQL Developer. Or decrypt file with SQL Developer password decryptor

Abnaki

Answer 5

The same code as kornelissietsma has given, but written on java:

import oracle.jdevimpl.db.adapter.DatabaseProviderHelper;

class Decode {
    String pass = ""; 

    public Decode() {
        pass = DatabaseProviderHelper.comingIn("HASH");
        System.out.println(pass);
    }   

    public static void main(String[] args){
        new Decode();
    }   
}

Can be executed as following:

# javac -classpath .:/full/path/to/sqldeveloper/BC4J/lib/db-ca.jar:/full/path/to/sqldeveloper/jlib/ojmisc.jar sqldeveloper_hash_decode.java
# java -classpath .:/full/path/to/sqldeveloper/BC4J/lib/db-ca.jar:/full/path/to/sqldeveloper/jlib/ojmisc.jar Decode

Rodrich

Answer 6

Methods described in other answers unfortunately doesn’t work in SQL Developer 4.x. There’s extension that works on both 3.x and 4.x versions and it’s very easy to use:

https://github.com/tomecode/show-me-password-sqldev-jdev

Comet

Answer 7

I'm not sure about this but I always thought hashes can't be decrypted, only compared to another hash. MD5 generates a hash. The saved password in SQL Developer needs to be decrypted and send to the server. So the DES3Encrypt and DES3Decrypt procedures in dbms_obfuscation_toolkit package are a better bet. But the decrypt should be called before connecting to a database, so it's probably a Java crypto package with DES methods.

Cabman

Answer 8

Here's a python snippet if anyone is intersted. It's a translation of Adam Paynter's example above. It uses pyDes

import os
import pyDes

import binascii

if __name__ == '__main__':
    # Encrypt example
    zero = '\0\0\0\0\0\0\0\0'
    key = os.urandom(8)
    plainText = 'open sesame'
    cipher = pyDes.des(key, mode=pyDes.CBC, IV=zero, padmode=pyDes.PAD_PKCS5)

    cipherText = '\5%s%s' % (key, cipher.encrypt(plainText))
    cipherHex = binascii.hexlify(cipherText)

    # This is what SQLDeveloper stores in XML
    print cipherHex

    # Decrypt above
    cipherText = binascii.unhexlify(cipherHex)
    assert cipherHex[0:2] == '05'
    key = cipherText[1:1+8]
    cipher = pyDes.des(key, mode=pyDes.CBC, IV=zero, padmode=pyDes.PAD_PKCS5)
    print cipher.decrypt(cipherText[1+8:])

Kalsomine

Answer 9

I don't know, but I wouldn't be surprised if it was DBMS_OBFUSCATION_TOOLKIT being used something like this:

l_hash := dbms_obfuscation_toolkit.md5(input_string=>:username||:password);

Discrepancy

Interesting Tony! Thanks much for the contribution! – Jandel

I doubt that. To use the DBMS_OBFUSCATION_TOOLKIT, you must already be connected to the database. So how could the client log on to the database when it needs the database to retrieve the password?!? – Hoarding

Answer 10

The length of the hash is 50 hex characters, which is 200 bits, so it may be the the hash of the password with a salt, prepended with the salt, like:

salt | hash(salt | password)

where | means concatenation.

Just speculation though. My guess would be a 40-bit salt and a SHA-1 hash, since SHA-1 produces 160-bit hashes.

Would be helpful to provide some input/output test data to check against!

Stiltner

Thanks for the comment Peter! The authentication data I've used for the example is simply "username" and "password". – Jandel

Answer 11

FYI the password 'apps_ro' encrypts as:

     <StringRefAddr addrType="password">
        <Contents>051DC8A88C574538CC4AEE32D326E9480659C06CEC271EA6D7</Contents>
     </StringRefAddr>

Ginglymus

This does not answer the question. – Crossways