Tuesday, September 07, 2010

Using Oracle SQL Developer with MS SQL

Having chosen Oracle SQL Developer as your preferred Oracle database tool, do you have to install and learn a new technology for supporting your MS SQL databases? Nope! It's easy to connect SQL Developer to MS SQL databases, and I'll show you how.

Background

For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases they'd only give me command-line SQLPlus access. Consequently I never really chose a preferred Oracle client, nor became particularly proficient with anything other than command-line SQLPlus.

Eventually I found myself working directly with a client, and could finally choose whichever tool I liked best, and like many of you I chose Raptor - now known as Oracle SQL Developer. Explaining my choice is outside the scope of this article, but suffice it to say that it's a powerful time-saver at best, and a big step up on command-line SQLPLus at worst.

Connecting to MS SQL

Not all the database applications we support are going to be Oracle-based, and the most popular of the alternatives is Sybase's curious nephew MS-SQL. Fortunately there's no need to find another application for your MS-SQL instances, you can still use SQL Developer, and here's how.

Step 1. Get the JDBC driver for MS SQL

If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember where.

Step 2. Load the JDBC driver

As explained in Oracle's tutorial, under Preferences, scroll down to Database and Third Party JDBC Drivers. Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection (You get Oracle and Access by default).

Step 3. Set up your new database connection

Now you can create a new MS SQL database connection the same you would an Oracle connection. The only difference is that you need to select the SQLServer tab instead of Oracle. If you don't see that tab, then something went wrong - either you got the wrong jar file or it didn't load correctly.
If you're brand new to Oracle SQL Developer, Anders Andreasen has an example with a few extra screenshots.

Use the Test button to make sure you can connect, and if you can't the error message ought to be descriptive enough to give you a starting point to troubleshoot.

Troubleshooting

Needless to say, technologies as complex as these will occasionally present you with unexpected difficulties.

For instance, you may find that you need to copy the ntlmauth.dll file from the SSO directory to SQL Developer's jdk/jre/bin directory.

Or, you may need to check the Configuration Manager on your SQL Server database to make sure you have TCP/IP communication enabled, and over default port 1433.

If you used the driver I recommended, they have a FAQ that can help you track down some types of errors.

Recap

If you support multiple databases, there may not be a need to install multiple tools. Oracle SQL Developer can be used to connect to different flavours of databases, including MS SQL. Just download and load in the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.

Comments:
This comment has been removed by the author.
 
Hi,

there's one big trick to provide a value for the porperty "instance": use for "Port" "1433;instance=instance_name"!

Regards Ralf
 
It's ironic that you can use SQL Developer to look at an MS SQL Server database, but NOT to look at an Oracle 8i database!
 
Some of the stuff up there is very relevant and useful... keep it up..http://sqlserver-guide.blogspot.com
 
Ralf,

Thanks for adding your comment, I've been looking for hours on why I couldn't connect, and this solved it for me.
 
Thanks, Ralf.

Once connected, I had an issue using what was apparently the non-default database, so I ended up using the following for Port:

1433/database_name;instance=instance_name

The jTDS driver FAQ (http://jtds.sourceforge.net/faq.html) is a great resource if you're having issues.
 
I've been using JDeveloper (similar to SQL Developer) for Oracle, never thought about using it for Sql Server. I'm going to start using that instead the outdated enterprise manager.
 
I have been facing so many issues when trying to connect the SQL Developer with MS SQL but all the points are cleared after reading your post. I figured out what I was doing wrong that created the whole problem. Thanks for explaining each and every step for the connectivity.
 
I am looking for the information about SQL Developer with MS SQL but all the points are cleared now

Oracle Fussion Middleware
 
Hi, just added the JDBC driver to access an SS2008 database, but we can't figure out what to use for connection credentials. Maybe it's a bit obscured/harder because of the fact that I'm trying to connect from a Linux-VM on my machine to a MSW2K8server-VM running SS2K8 on his system.
The user he told me to use is something like informatica\Administrator, with some password.
But when I try to test the connection I always get ""Failure - Test failed: Login failed for user ". I've also tried it for just informatica, and just administrator, all to no avail.
I've also tried to tick the "Use Default Password" box, no luck there. Haven't tried the "Windows Authentication" box.
For hosthame I'm using the IP-address, and port is standard 1433. Trying the "retrieve database" button yields same error message.

Any help is welcome!
Thanks!!!
 
Nice topic! Good job!
 
Windows Authentication does not seem to work on a Mac. Anyone got this to work? I can connect fine using a SQL Server login but not with an AD login.
 
If you think about JDBC Driver Download you could here at http://www.datadirect.com/products/jdbc/index.html.
 
Thanks! Your article helped me a lot. It was really quick and easy to configure the Oracle SQL developer to collaborate with MSSQL DB. Great.
 
Unfortunately you can't run MSSQL Stored Procedures using Oracle SQL Developer. I find this extremely disappointing, and is a show stopper for my scenario.
 
I add the jar file to the third party jdbc list however it results in the connection manager not coming up when you either click on add new or try to edit an existing connection.
 
this works only with jar version 1.2 ..1.3 fails to connect to sql.
Any way thankyou for the content....gr8 job.
 
Hi.. everything works fine with sqldeveloper for MSSQLSERVER DB.. but i am unable to see the delete row icon under content tab.. could please let usknow what could be the reason??.. if that icon to appear is anything need to be configured??
 
if you can not open New connect dialog window using jtds-1.3.0.jar just use jtds-1.2.7.jar as described here:
http://www.gokhanatil.com/2012/11/oracle-sql-developer-and-jtds-incompatibility.html
 
Hi,
I am able to connect to the SQL Server, and also i can view all my elements of this SQL Server instance. But when i drag and drop in the cart, when i click 'Deploy' it gives an error 'The objects are not Oracle Objects'.
Is there are way to convert SQL Server objects into Oracle Objects.
 
Nice post very helpful

dbakings
 
Hi, I follow all your instruction on how to connect to sql database, i'm using sql2008r2, with instance name SQL2008R2,

Here is my configuration

Connection Name : SQL2008R2
Username: username
Password: password

Hostname: localhost
Port: 1433

I getting this error..

Unknown packet type 0x48, please help me..

Thanks in advance..
 
This is such a nice post, actually I'am new in Oracle industry and I don't know much about so that's why I'am looking on the web for tutorials and Oracle Consulting and Oracle Services to gain my knowledge about Oracle. Thank you so much for sharing such a nice post with us.
 
Very Nice Thoughts. Thanks For Sharing with us. I got More information about Java from Besant Technologies. If anyone wants to get Oracle Training in Chennai visit Besant Technologies.
 
I was not able to connect to SQL Server 2008 until a User ID was created on the SQL Server database. I could not connect using my own User ID or Windows Authentication.
 
This post is really good. It can help me perfect but while I am facing some problem with SQL, it cannot work well than I can download sql database recovery tool. It was work wonderful on my system and solve all the problems.
 
Thanks for sharing the information

For more info : Oca Certification
 
I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/
 
Thanks for sharing fabulous information.It' s my pleasure to read it.I have also bookmarked you for checking out new posts.
Digital Marketing Training in Hyderabad



 
Thanks for sharing such a useful information
 
Now I can connect to MS SQL using oracle sql developer, that's interesting.
 
This is important information,
Thanks for sharing this
Certified Ethical Hacker CEH Training VA
|Scrum Master Agile CSM Training VA


 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?