Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Tech Tip: Connect to SQL Server Using Oracle SQL Developer (updated)

I spend a lot of time reverse engineering client databases to see what kind of design they are working with or to simply create a data model diagram for them (so they know what they have).

Along the way I often need to actually look at the data as well to do some analysis and profiling.

Often this means looking at data and models in SQL Server as well as Oracle.

What’s an Oracle Data Warrior to do?

Hook up my FREE handy dandy Oracle SQL Developer to the SQL Server database.

How do you do that?

First you need to get the right driver. You can find it here: http://sourceforge.net/projects/jtds/files/jtds/1.2.5/jtds-1.2.5-dist.zip/download

NOTE: For SQL Developer 4.0EA3 and SQL Developer Data Modeler 4.0 (production) you now need jtds-1.3.1. Get it here: http://sourceforge.net/projects/jtds/files/

Then follow these steps:

  1. Download and unzip the file into the main SQL Developer directory (or the directory of your choice).
  2. In SQL Developer go to Tools -> Preferences -> Database -> Third party JDBC Drivers
  3. Click the “add entry” button
  4. Navigate to the jtds-1.2.5.jar file. (or the 1.3.1 file for 4.x installs)
  5. Save and exit preferences.
  6. Close and restart SQL Developer
  7. Open “Add Connection” – there should now be a SQL Server tab.
SQL Developer Preferences

SQL Developer Preferences

With this in place, you can now connect to SQL Server without having to load any other software.

Pretty useful.

Happy Querying!

Kent

P.S. You can connect to other non-Oracle dbs as well. Check out this post by Jeff Smith for even more details.

Additional Notes on SSO errors:

Lots of folks, including me, have had issues getting the native Windows SSO connection to SQL Server to work. Based on answers on the OTN Forum and this post (http://www.oracle-base.com/blog/2013/10/01/sql-developer-4-ea2-connecting-to-sql-server/) I finally got my new 4.x versions to work.

For SQL Developer 4.0EA3, I did as suggested in the article: http://www.oracle-base.com/blog/2013/10/01/sql-developer-4-ea2-connecting-to-sql-server/. I put the ntlmauth.dll where my JDK 1.7 was installed: C:\Program Files\Java\jdk1.7.0_40\jre\bin

For Data Modeler 4.0.13 (production), based on a suggestion from Jeff Smith, I put the dll file here: C:\SQLDeveloper\SQLDeveloper4.0.13\sqldeveloper\sqldeveloper\bin

If I was better at setting windows paths, I am sure there is a better way to do this.

About these ads

Single Post Navigation

6 thoughts on “Tech Tip: Connect to SQL Server Using Oracle SQL Developer (updated)

  1. Henry Miller on said:

    Thanks much Kent. Perfect timing.

  2. George Laframboise on said:

    I set my machine up this way quite some time ago, being an Oracle bigot I am embarrassed to say I like SSMS better than SQL Developer for connecting to SQL Server.

    George M. Laframboise LightWorx Technology Consulting 303-517-2454 Sent from iPhone

    • I like the fact that I can be connected to both an Oracle and a SQL Server db at the same time. Very helpful for my data warehouse type work. But I can imagine that using the native tool for SQL Server would be superior for some activities.

  3. Hi Kent,

    it’s also worth to mention how you can adjust connection string, because SQL Developer doesn’t allow to do it through GUI, one has to edit file %USERPORFILE%\AppData\Roaming\SQL Developer\system3.2.09.23\o.jdeveloper.db.connection.11.1.1.4.37.59.48\connections.xml

Follow

Get every new post delivered to your Inbox.

Join 940 other followers

%d bloggers like this: