Connecting to JIRA database with SQL Developer

[Andrew Thorne]

I would like to use Oracle's SQL Developer to connect to our JIRA database to create and run queries directly against the database. However my connection attempts all end in no connection being made. The IT people in charge of the servers are less than helpful (one of them said to my boss that I did not have the right "profile" for such activities despite the fact I was designing relational databases back in the 80s whilst he was in pre-school).

So my question is: does anybody know how I should configure SQL Developer to connect to the JIRA database?

BTW: We are running JIRA 7.3.6 with Oracle 12.

I am also quite willing to use other programs, as long as I can develop and run scripts to get results from the database. I already use SQL Server Management Studio for our MS SQL Server databases.

Thanks for any help you can give.

[Nic Brough -Adaptavist-]

This is a bad idea.  JIRA's database is designed as a data store, not as a reporting database, and it's not particularly realational either.  As a rough example, to get data from one issue, you may have to make joins to 40-50 tables, many of them 3-5 layers deep and often many times.

There is enormous potential for you to write even simple queries that will load a database, and even more potential for you to get your queries basically wrong.

And *never* write.

Warning out of the way, the database is just a simple database.  There is nothing complex or tricky about it.  Whatever you do with SQL developer to connect to a nice new empty database you've defined, you do the same with the JIRA one.

You'll need to check what you're doing to cause "no connection made".  What's the error and what does the database log tell you is wrong?

[Andrew Thorne]

Hi, Nic, the reason I need to do this is that some of our plugins require SQL code (instead of JQL) to extract information from the database. Whilst I can mess about for hours trying to get me SQL to work correctly, I would rather use a tool like SQL Developer to run requests against the database to debug my SQL.

BTW, I woud never update records via SQL code. It just isn't worth the risk.

Back to my connection problem. The message I am receiving is "Peer not authenticated". No error number, Nada.

Whilst there are loads of pages on the intrnet about this error, I cannot find one that relates to JIRA or to my particular problem.

[Nic Brough -Adaptavist-]

>some of our plugins require SQL code

You should get rid of these plugins, or rewrite them properly, as they're fundamentally broken.

"peer not authenticated" means you either don't have permission to log into the database with that user, or you've got the credentials wrong.