Connecting to Access, DB2, MySQL, SQL Server, Sybase, & Teradata with SQL Developer

thatjeffsmith SQL Developer 61 Comments

Tell Others About This Story:

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.

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

Registering 3rd party JDBC drivers in SQL Developer

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.

Windows Authentication is mostly going to be tough from OSX, mostly

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 61

  1. Any plans to support installation of arbitrary jdbc drivers?
    Would like to connect to a Vertica database using SQLDeveloper

    1. thatjeffsmith Post
      Author
  2. Hello Jeff,
    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.

    Any suggestions?
    Thanks

    1. thatjeffsmith Post
      Author
      1. 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?

        Thanks

        1. thatjeffsmith Post
          Author
  3. Hi Jeff,

    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 2.1.1.64 version.
    kindly suggest any ideas

  4. Dear All

    I am trying to connect SQL developer with HP Vertical Database but did not succeed.

    email: [email protected]
    Regards
    Hassan

    1. thatjeffsmith Post
      Author
  5. Hey Jeff,

    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,

    Daniel

  6. Hey Jeff,

    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!!!!

    1. thatjeffsmith Post
      Author
      1. 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.

  7. Hi Jeff,
    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?
    Thanks

    1. thatjeffsmith Post
      Author
  8. 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”).

    1. thatjeffsmith Post
      Author
  9. Hi Jeff,

    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.

    Thanks,
    Mark

    1. thatjeffsmith Post
      Author
      1. Hello Jeff,

        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!

        Thanks,
        Akash

          1. thatjeffsmith Post
            Author
  10. Hi Jeff,

    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][2034][11148] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
    A DRDA Data Stream Syntax Error was detected. Reason: 0x3.

    Thanks,
    Aashish

  11. 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?
    Thanks

    1. thatjeffsmith Post
      Author

      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.

      1. 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.
        Thanks

        1. thatjeffsmith Post
          Author
      2. 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.

        1. thatjeffsmith Post
          Author
  12. 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.

    1. thatjeffsmith Post
      Author
  13. Hi ,
    Is it possible to connect sybase database using “Oracle SQL devloper”. if yes can you please mention the steps .

    Thanks ,
    Sameer

    1. thatjeffsmith Post
      Author
  14. 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?

    TIA,
    Sarah

    1. thatjeffsmith Post
      Author

      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…

      1. 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 4.0.3.16.84. Do I need more coffee?

        sk

        1. thatjeffsmith Post
          Author
  15. 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.

    1. thatjeffsmith Post
      Author
  16. If I wanted to connect to a AS400 DBMS, what connector driver is it I’m supposed to use? . . . Sybase? ODBC? or IBM DB2?

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author
          1. 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.

          2. thatjeffsmith Post
            Author

            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.

  17. Hi,

    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?

    -KP

    1. thatjeffsmith Post
      Author

      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.

      Sybase Example:
      /sqldev:query*/sp_help;

      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.

      1. 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.

  18. Jeff,

    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?

  19. 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.

    Regards,

    Ahmad Jan

    1. thatjeffsmith Post
      Author
      1. 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.

  20. 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!

    1. thatjeffsmith Post
      Author
      1. 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.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *