SQL Developer is built by Oracle, for Oracle. It’s the database IDE. That’s my current elevator pitch, at least. But, SQL Developer is also a fully featured data modeling solution (have you been enjoying my data modeling posts?) AND a 3rd party database migration platform.
The migration piece is the Migration Workbench. Since we support migrations from these other database platforms, wouldn’t it be neat if SQL Developer could connect and query those 3rd party databases too? Surprise, surprise – that’s exactly what we DO do today.
What ‘Support’ means
You can connect to, browse, and query (SELECTs only) SQL Server, DB2, Access, MySQL, and Teradata.
January 2020 Update: As of version 18.1, we also now support Amazon Redshift migrations, as well as Azure SQL databases.
What ‘Support’ does NOT mean
- providing debuggers
- delivering database administration features
- write access to the database
Sound a little harsh or spartan? Going much further in any of these areas would just open a big ole can of worms, and we’re committed to providing an optimal experience for the Oracle database. But, since we already support these limited features, there’s no real reason to hide it.
So what’s it good for outside of migration projects?
You own one of those fancy-dancy MacBook Pros? Fall in love with Ubuntu? Need browse some SQL Server data? You sir/madam, are what we call a rebel! Instead of installing a VirtualBox Windows VM just so you can run SQL Server Management Studio (SSMS) or some other Windows-based database IDE, you can run Oracle SQL Developer natively on your machine and connect to that server.
This is pretty old news – here’s a nice blog post from 2007 talking about how to use SQL Developer to connect to SQL Server. But since the product name is ‘Oracle SQL Developer’, it’s probably a good idea to remind folks every now and then that SQL Developer is more than just a tool for Oracle developers 🙂
Download and Go
You will need the JDBC drivers for the database you want to connect to. These are conveniently indexed here. Couple of notes –
- You need to be a registered IBM customer to access their downloads
- The Teradata download manager page seems to be ‘missing’ – go grab the driver here
Once you have extracted the packages, you need to point SQL Developer to the .jar file
Be sure to point to the actual file, not just the directory containing the jar.
Once you’ve done this, you can open the connection dialog and navigate to the appropriate database panel.
re: Postgres and Sqldeveloper I was not able to access Postgres via 4.0.3, but Sqldeveloper 17.4 talks to Postgres with no problems at all.
We support it, quietly. There’s not as much Migration support there, but it is there.
I think that is one of the such a lot vital information for me.
And i am glad reading your article. But wanna observation on some common issues, The
website style is perfect, the articles is actually excellent
: D. Just right activity, cheers
Any plans to support installation of arbitrary jdbc drivers?
Would like to connect to a Vertica database using SQLDeveloper
I’m using SQL Developer 4.1.1 and I have downloaded jtds-1.2.jar. I can now see the SQL server tab connection. I set the Hostname value as “localhost” and the Port value as “1433/db1;instance=SQLEXPRESS” but when I tried to test the connection I get “Status : Failure -Test failed: Server localhost has no instance named SQLEXPRESS.” I’ve tried a number of things including different Port values, enabling SQL Server TCP/IP, and restarting the SQL Server Browser with no luck.
No…like, do you know that you have SQL Server running on your machine with an instance called ‘SQLEXPRESS’?
Also, we recommend the jtds-1.3.1.jar
Yep there’s a SQLEXPRESS instance running. I upgraded to jtds-1.3.1.jar and restarted the SQLEXPRESS service. I’m now getting the error “Status : Failure -Test failed: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.” Online forums say that I need to copy ntlmauth.dll to /sqldeveloper/bin, but I’m still getting the error. FYI I’m running Windows 7 64- bit. Do you have any other suggestions?
make sure you copied a 64 bit copy of the DLL if you’re running a 64 bit JDK
i have added the postgressql-9.3.1100-jdbc41.jar for connecting the postgressql in sql developer. but in connection page i couldnt see the postgres tab. am using Oracle sql developer 18.104.22.168 version.
kindly suggest any ideas
I downloaded Postgres driver ….42.2.1 (the latest), under Sqldeveloper 22.214.171.124. Added the driver as per Tools > Preferences > Database > 3rd Party…. > Add Entry. But when I click ‘New Connection’, nothing happens – no connection screen appears at all. I have to remove the driver to get my Connection screen back.
I found info at stackoverflow.com/questions/7592519/oracle-sql-developer-and-postgresql which verifies that Postgres can indeed be accessed under Sqldeveloper 126.96.36.199.
So I don’t understand the odd behaviour of my later version of Sqldeveloper.
For the best support, upgrade to 17.4 of SQL Developer.
I am trying to connect SQL developer with HP Vertical Database but did not succeed.
email: [email protected]
We don’t support that.
I’m glad you are still replying to questions in this post even though it’s archived. I have a little problem browsing MSSQL instances in SQL Developer. I can connect without any issues (using the 1.3.1 jtds driver) and even query from any database successfully inside an instance, once connected. But when I try to expand the database inside an instance in the connection panel, if the database name contains a hyphen (like Something-DB) then Oracle SQL Developer throws an error:
Incorrect syntax near ‘-‘.
Vendor code 102
I’m guessing it’s not escaping that character somehow?
Is it possible to fix that? Would be great if I could use this one tool for browsing several different databases (Oracle,MySQL, MSSQL)
Thanks in advance,
I am looking for driver files for db2 -a/s 400 (jt open) to connect from Oracle SQL Developer tool. I have tried finding it from google but no luck.
Any help is appreciated!! Thank you very much!!!!
You’ll have to get the drivers directly from IBM
I tried it already and it’s giving me below error. I have noticed that DB2 driver which is provided by IBM works only for DB2 but not for -a/s 400 (jt open). Not sure whats the difference.
I have SQL Developer 4.1.1, and followed the instruction to download jtds-1.3.jar. After that I can see SQL server tab connection. When I tried to test connection with username and password to myserver\sqlexpress I get status:Failure -Test failed:Cannot connect to Microsoft server on myserver\sqlexpress. In the same time I can connect from this PC with SQL Server through Microsoft Visual Studio.
Can you help me?
ok, but what do your connection properties look like in Visual Studio vs SQL Developer? Remember you’re comparing ODBC to JDBC…
Thanks for reply, Jeff!
I tried all the options the connection string in accordance with the recommendations here https://msdn.microsoft.com/en-us/library/ms378428(v=sql.100).aspx. Nothing helped. I tried to configure a connection string directly through connections.xml file – also to no avail. I give up.
If anyone else gets the following when trying to connect to Teradata:
“could not initialize class com.teradata.tdgss.jtdgss.tdgssmanager”
The issue is that you also need to point SQL Developer to the “tdgssconfig.jar” that comes in the Teradata JDBC archive (alongside “teradatajdbc4.jar”).
I have SQL Developer 4.1.1, and followed the instruction to download jtds-1.2.jar. However I can only see Oracle tab for connection, no SQL server or others. See the link below
I even tried updated which recommended in the link above. The SQL deveoper tool crashed. I have to re-install it.
Can you please verify it for me this works on v4.1.1?
for 4.1, you need jtds 1.3
Is it possible to connect to a DB2 database running on OS/390? I’ve got the driver (db2jcc.jar) and the license file (db2jcc_license_cisuz.jar) installed, and the license is in my CLASSPATH, but I keep getting an error saying it can’t find the license and throwing EERRORCODE=-4472, SQLSTATE=42968.
If you need more info, let me know and I’ll collect it and we can talk at KScope15 next week.
are those jars in the same directory? sounds like a DB2 issue, but i’ll ask our migrations guy – this is for a DB2 to Oracle migration, right?
Am trying to connect to DB2 LUW from Oracle SQL Developer 1.5.5. I got the driver jars (db2jcc.jar) and the license file (db2jcc_license_cisuz.jar) from the DB2 client installed path on my system. I have imported these jars as Third party JDBC drivers in my oracle SQL developer tool, but still am NOT able to see the DB2 tab :(. (I have restarted SQL developer after the import also!). On the oracle page I read, we need to configure the JDBC driver from the Help tab “Using Check For Updates to configure JDBC Drivers”. There are two options there; 1. Search Update Center and 2. Install from local file. Can you please tell me what file I need to have to go for the 2nd option. Since I work in a client location, I cannot connect outside with the proxy settings(option 1 ruled out for me)
Would really appreciate your help with this as I am waiting for this for a long time!
same question it will really help if you could provide a sollution for this.
First things first, tell me you’re NOT running v1.5.5 and you’re running version 4 or 4.1.
Can we connect to DB2/400 (DB2 installed on i-series) using SQL Developer?
I have installed the JDBC drivers but I am getting below error while accessing. Can you please let me know the solution for this.
Status : Failure -Test failed: [ibm][db2][jcc][t4] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
A DRDA Data Stream Syntax Error was detected. Reason: 0x3.
Doc details on adding 3rd party jdbc drivers could be better.
Not sure why ANY std jdbc driver ( at jdbc 4.0 etc ) can’t be loaded in sql developer.
Added Derby 10.11 jar ( derbyclient.jar, derbytools.jar ) to the 3rd party drivers list.
The connections wizard does not display a jdbc tab at all
Any work arounds possible?
Because we’re not a generic jdbc open database query tool. We’re an IDE for Oracle Database (and TimesTen.) The 3rd party JDBC support is there to help folks migrate their databases to Oracle. We don’t have migration support for Derby built into our migration project, so we don’t accept the JDBC driver.
Thanks for the feedback. Is there a simple URL that has a list of what 3rd party jdbc drivers are supported for a given sql developer version? that would help a lot. we were trying to migrate from derby to mysql but it looks like that can’t be done with sql developer.
‘conveniently indexed here’ 🙂
You’d use the MySQL tooling to migrate to MySQL. You can find that here
Is there any way to get the official sybase connector jconn4.jar file to work? The open source jtds doesn’t support sql anywhere files.
No, we don’t support Sybase Anywhere migrations, just ASE.
I am trying to use SQL Developer to connect to HIVE2. I downloaded the jars and added them through “Third Party JDBC Driver” screen. It is not one but several of them. It is recognizing and showing the tab for HIVE2 but, it does not connect. I wrote a java program and connected from the same system, it is connecting fine. Typically we specify driver name in Java program but here it is figuring out the driver name looks like. Do you think something is happening while figuring out the name? On the server, I am seeing the error message in the log file. So connection information is correct. Any help is greatly appreciated.
This is how it SHOULD look and work if setup correctly.
Is it possible to connect sybase database using “Oracle SQL devloper”. if yes can you please mention the steps .
Yes. Grab the jTDS jdbc driver, v1.3 for SQL Developer v4. Then follow the instructions above for adding it to SQL Dev.
Then create your Sybase ASE connection.
Hey, Jeff, hope this finds you well.
I am trying to use SQL Devr to connect to an Access DB. I have tried connecting to the older .mdb files that I actually need and to a newer .accdb that I created solely for testing.
In all cases I get “Status : Failure -Test failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”.
What little documentation I see on connecting to Access seems to be suggesting that it is so easy as to be obvious, but it’s not to me. A possible problem that comes to mind is 32/64 bit issues.
Do you have any thoughts?
I’m always well, but thanks for asking!
Biggest thing to know – what version of the JDK are you using? Newer version of Java don’t have the ODBC-JDBC bridge that’s required to connect to Access.
So if you have Java 6, you should be good. Like, I might recommend just downloading the SQL Dev 3.2 with embedded JDK just to do your Access work in…
That sounds like a reasonable solution except that finding it on Oracle’s website is quite the challenge. All the download links I find point to the latest version only. I can get to release notes and documentation for older versions, but even from those older versions’ screens, “download” takes me to 188.8.131.52.84. Do I need more coffee?
We actually don’t want it to be THAT easy to get the older stuff, but yeah…anyways, here’s the link.
I have SQl Developer 3.2.2. Coonections page doesn’t even have a db2 tab. It only has Oracle. Is this an installation issue? I already add the db2 .jar files to 3rd party connections area. Restrarted SQL Developer. The db2 tab still is not there.
We don’t ship any of the 3rd party JDBC drivers. For DB2 you have to download the JDBC driver from IBM’s site. I think that requires a license of some sort…
If I wanted to connect to a AS400 DBMS, what connector driver is it I’m supposed to use? . . . Sybase? ODBC? or IBM DB2?
We don’t support that platform, but you could try the latest IDM DB2 driver and see if it will work…
We’re doing a migration from DB2 to Oracle but at this point we only need to query the DB2 tables. It would be helpful to have a guide on the exact JDBC jar files to use with SQL Developer 4. I tried some recommended on various web sites but have also not been able to get the DB2 tab to show up.
You need the latest db2 jdbc drivers directly from IBM. Don’t go to ‘various websites’ – go to IBM.com.
Thanks. I’m not getting the drivers from various web sites, just the instructions. Was wondering if anyone has done this and could provide an authoritative guide.
The authoritative guide would be the official Oracle Docs. If you need help, you can also contact My Oracle Support. We have a dedicated OTN Forum for Migrations as well.
I tried installing the DB2 drivers using your post. For some reason, I don’t get a DB2 tab in the New Connection window, and I don’t see a DB2 tab in your screenshot either.
Were you able to get the DB2 drivers to work with SQL Developer?
I cannot do any stored procedure sp_help, sp_tables etc. when using oracle sql developer on SYBASE from LINUX any ideas why?
Yes, SQL Developer currently doesn’t support proprietary commands of non Oracle databases like SQL Server, Sybase … . But a feature called Worksheet Hints allows these commands to be executed.
In the next version of SQL Developer, we’ll let ANYTHING past the parser and count on the connected database to return a resultset or text…so it should work w/o the hints going forward.
Hi Jeff thanks. I tried that and saw that info from another blog but still not showing any results although it seem to be working with “Task completed in 0.92 seconds.” no output though. I might be missing something.
I work in a SQL Developer environment. I have just been tasked with pulling some data from a Sybase database. Is SQL Developer the best tool to use to work in a Sybase environment?
I want to migrate/Transfer/Copy/Shift Oracle 10g database (Tables, Indexes, Stored Procedures) to MySql. How can I do it with SQL Developer.
I have tried one third party tool “KEEP TOOLS – HORA” , it allows me to generate create table scripts for all tables and insert scripts for each table, but the problem is some of my tables has more than 800,000 records, and when I copy the insert sql script into MYsql it does not accept so many rows, I tried to reduce the rows to 100,000 but still it does not accept, MysqlYog accept it but it take hours and hours to just transfere a few thousand records.
Is there any solution in SQL Developer, I am trying to use it but I want to check with you first.
You should use MySQL Workbench to migrate from Oracle to MySQL – Oracle SQL Developer is for helping people move TO Oracle Database, not away from it.
Received your suggestions, Thanks, actually I tried many other third party products, They were very slow to transfer huge record tables. In the end I got Navicate software which gave me the best result and it transfered all the data in minutes. It is very simple and easy too.
Now my problem is solved and I thank you for your cooperation.
I just came across your site and see that this is an older post but it would be great if the new version 4.0 could support JDBC-ODBC Bridge. At one job I work I switch between Oracle, SQL Server, and AS/400 a lot. For Oracle I use SQL Developer and have used it with SQL Server too. But for AS/400 the DB2 drivers did not work and IBM software for it is not efficient so I use a program I wrote to query it via ODBC.
Today I just learned about SQL Developer 4.0 Early Adapter 1 so next week I will download it and try it out when I get to work! It looks awesome, keep up the good work!
We are getting ready to add a new RDBMS platform, but it’s not AS/400. If the demand for migrations from that platform dictates, we’ll add support for it.
Thanks for the quick reply Jeff! I see on Oracle SQL Developer Exchange that PostgreSQL is highly requested. I would never expect AS/400 to be natively supported but if there was a way to connect to any ODBC source for Windows Users that could open up almost any database. Just a thought as it’s nice to be able to access many databases from one program rather than using several so my preference is to use Oracle SQL Developer.
i am dev more from india .i most interesting oracle.
I’m not sure I’m qualified to give out career advice, especially for the Indian economy and marketplace, but I’d suggest building out your profile on LinkedIn.