It is a pretty cool feature to use Oracle’s SQLDeveper 1.1 to access SQLServer.
The steps are:
- Download jTDS (open-source SQLServer JDBC driver) from here. Unzip and extract the jtds-1.2.jar or whatever the latest version.
- Start Oracle’s SQLDeveloper, Tools->Preferences->Database->Third Party JDBC Drivers. Click “Add Entry” and point to the jtds-1.2.jar
- Create a new connection, choose SQLServer tab, type in hostname, port, username and password. It appears that the initial connection name has to be the same as the database and you can click the “Retrieve database” button. Once you found the database, you can rename the connection.
Try it out.
Of course, certain things don’t work. Like explain plan and auto trace.
Per comments below, please make sure jtds 1.2 is used. Apparently, 1.3 does not work.
powered by performancing firefox
DJ's blog 
January 9, 2008 at 8:54 pm |
hi there, I am having trouble with this connection. I found the driver and included it, but when creating the connection I get these error:
Satus: Failure -Network error IOException: Connection refused: connect
I have tried as many variations as I can think of, including:
Connection Name: MyDataBaseName
Username: user
Password: password
Hostname: localhost
Port:1433
Connection Name: MyDataBaseName
Username: user
Password: password
Hostname: localhost/SQLEXPRESS
Port:1433
Connection Name: MyDataBaseName
Username: user
Password: password
Hostname: localhost
Port:
Obviously I am doing something wrong, I just don´t know what! Any suggestions?
Thanks in advance
January 9, 2008 at 9:00 pm |
I don’t claim to be a sqlserver expert, but did you try the default password? sa/master?
Also I think the JDBC driver will connect by TCP/IP. Did you configure TCP/IP protocol for SQLServer?
That’s all I can think of. You can try some SQLServer forum.
February 1, 2008 at 5:28 pm |
Turning on TCP/IP did the trick… Thanks!
September 21, 2011 at 2:07 pm |
how do you turn on the TCP/IP??
March 14, 2008 at 8:57 pm |
Very helpful, Thanks a lot
February 16, 2009 at 3:48 pm |
Hi all,
I have tried all the above possibilities too. BUt could not connect, I am also getting the same exception. I have the tcp/ip enabled and disabled the firewall. May I know whether there is any other possiblity to solve this.
Thanks in Advance.
Kally.
February 16, 2009 at 3:58 pm |
Kally,
I am afraid you’ll have to try it the hard way.
I am not sure how comfortable you are with Java and JDBC. It’ll be fairly straightforward to write a small Java program, using jTDS library to ping your SQLServer db with TCP/IP protocol. If you’re able to, then you eliminate the issue from jTDS side. Then the issue is probably Oracle SQL Developer not recognizing jTDS library, particularly, both have newer versions out since I wrote about it.
It’s been a while since I last played with Oracle SQL Developer access to SQLServer. I am sorry I cannot of more help.
DJ
February 25, 2009 at 10:40 pm |
downlaod
March 18, 2009 at 2:29 pm |
Find out which port your SQL Server is listening and may be in your case it is not the default 1433.
May 2, 2009 at 7:27 am |
Use this connection setting
Connection Name: MyDataBaseName
Username: user
Password: password
Hostname: localhost
Port:1433/databaseName;instance=SQLEXPRESS
April 23, 2012 at 4:39 am |
Thanks a lot .. very great help
-Fajr
October 15, 2012 at 11:37 am |
Thank you!
August 9, 2013 at 12:30 am |
Thanks! It worked!
August 31, 2009 at 4:13 am |
Woww, thanks xptiger, it works
But my schema is not recognized 😦
October 23, 2009 at 1:34 pm |
Thank you guy for publising it out. It was really helpful
February 19, 2010 at 6:32 pm |
Thanks for putting this out there. Speed up figuring that out.
March 26, 2010 at 2:03 pm |
Thanks xptiger works.
November 5, 2010 at 9:53 am |
Thanks DJ, this really helped me!
April 15, 2011 at 3:48 pm |
I’m using Oracle SQL Developer and having the problem at the top of this post but I’m having trouble figuring out how to turn on the TCP/IP connection. Any ideas?
April 18, 2011 at 7:06 pm |
Thanks, works fine!!! I love Linux but at my work use MS SQL Server, and now I can use my Ubuntu!
April 21, 2011 at 12:41 pm |
Hi I am not getting this sql server tab in connection window. i am getting only oracle and access tabs.
so pls tell me how to get sql server tab.
April 28, 2011 at 1:22 pm |
http://www.oracle.com/technetwork/database/migration/omwb-getstarted-093461.html
May 3, 2011 at 11:02 am |
If using SQLEXPRESS put this in the box for port [1433/dbnameyouwantoconnectto;instance=SQLEXPRESS]
May 11, 2011 at 5:28 pm |
I had the same problem as Maria (first post). I finally figured it out from information on the jTDS driver site and thought I would share. If you are using instances (multiple sql servers on the same box) like Maria stated above this is normally referenced by /. However, the jTDS driver was built long before instances and it is a big deal to re-write the parser to add this so instead you have to use a property at the end. To do this you will include a [ ;= ] in the last field of the jdbc string. So, for sql developer in the port field you will put something like [ 1433;instance=SQLExpress ]. This will allow you to populate the database drop down and pick your database.
Hope this helps someone else out a bit.
May 11, 2011 at 5:55 pm |
sstacha, many thanks for sharing.
June 29, 2011 at 8:17 pm |
Thanks from me too. That was the solution
August 13, 2011 at 2:54 am |
Brilliant. That did the trick
November 20, 2011 at 9:52 am |
it’s true..it worked..thanks for the help sstacha…
July 11, 2011 at 9:48 am |
[…] – http://helpdeskgeek.com/databases/an-introduction-to-oracle-sql-developer-part-v/ – – https://djiang.wordpress.com/2007/02/27/using-oracle-sqldeveloper-to-access-sqlserver/ […]
August 29, 2011 at 1:56 am |
Thanks man – worked a treat
September 7, 2011 at 11:40 pm |
Anyone have any success running a MS-SQL stored proc via SQL Developer? I get the following error
Error starting at line 1 in command:
exec sp_who
Error report:
Incorrect syntax near the keyword ‘BEGIN’.
October 29, 2011 at 4:28 am |
hi sstacha,
my server name contains ‘\’ symbol, and tried with putting port as “1433;instance=SQLExpress”, but still i am not able to connect SQL server.
I am getting error like “Status: Failure – Unable to get information from SQL Server: [MyServerName]”
Can anyone help on this please?
November 14, 2011 at 5:17 pm |
anyone trying to use set identity_insert when access sql server this way? it thinks it’s a sql plus command and throws an error. how can i get sql developer to just send the t-sql command without question?
December 5, 2011 at 4:00 pm |
[…] e configure-o para acessar o Banco de Dados SQL Server, seguindo os passos descritos no link: https://djiang.wordpress.com/2007/02/27/using-oracle-sqldeveloper-to-access-sqlserver/. Em seguida, crie uma conexão ao BD SQL Server, como no exemplo da Figura 1 (ver […]
December 6, 2011 at 10:09 am |
Thanks for your post on this. really helped me straight away!
December 13, 2011 at 10:56 pm |
reciprocating pump curve…
[…]Using Oracle SQLDeveloper to access SQLServer « DJ’s blog[…]…
January 19, 2012 at 3:33 pm |
[…] 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 […]
January 25, 2012 at 7:04 pm |
I was using Windows Authentication and had trouble connecting to SQL server, but I read somewhere that if I add domain=thedomainname will connect. I combined it with what I found here in Port: 1433;domain=thedomainname
That allows Windows Authentication, but I could only view data and no updates. Using SQLDeveloper 3.0.04 and jtds 1.2.5. Anyone can help?
February 26, 2012 at 8:49 pm |
hi,
Just
1. copy the file ‘jtds-1.2.2-dist\x86\SSO\ntlmauth.dll’ from the unzipped JTDS into \\SQL Developer\jdk\jre\bin.
2. restart the SQL Developer and that’s it.
March 22, 2012 at 3:00 pm |
@Fred …. THAT WORKED!!!
Only that, in my case, the Path was %INSTALATION_PATH%\sqldeveloper\bin
and, of course, since i’m usining Win64, I used x64 (duh!)
May 23, 2012 at 4:26 am |
Reblogged this on Nathan.
May 29, 2012 at 7:03 am |
List the details of the employee earning more than the highest paid Manager but not same department.?
June 15, 2012 at 3:48 pm |
Awesome this is exactly what I needed to connect to our new SQL Server 2008R2 database, we are primarily an Oracle shop so never had to connect to SQL Database and this was the ticket after a lot of looking.
September 14, 2012 at 11:40 am |
Hi naveed just place the jarfile in sql developer tool
( tools >> preferences )–click on thrid part gar and place the jtds-1.2.5jar…
hopefully i will work i thick…
September 14, 2012 at 2:48 pm |
I did the same copying the file dll and know I can retrieve the sqlservers databases but I got the error:
Invalid ojbect name ‘PRODUCT_COMPONENT_VERSION’
Any suggestion…please..
Regards!
November 12, 2012 at 9:37 am |
Hi All,,
Can any one say how to connect the SQL server 2008 from the oracle Sql developer…
I have tried with the above options but i am still not getting connections…
Pls help me out…..
December 18, 2012 at 6:47 am |
Greetings, I think your website might be having web browser compatibility problems. Whenever I look at your site in Safari, it looks fine but when opening in Internet Explorer, it’s got some overlapping issues. I simply wanted to provide you with a quick heads up! Aside from that, great website!
December 27, 2012 at 5:33 am |
Hi… after connecting to sql server db using sql developer… i could not see delete row icon under data tab after opening a table.. so please let us know what could be the issue
January 14, 2013 at 2:29 pm |
Hi! I was trying to connect to a SQL Server with SQL Developer using this 1433;DATABASENAME;instance=SQLEXPRESS syntax but still could not working. I was getting the “Unable to get information from SQL Server” message. So I found on the jTDS faq that I need to enable the SQL Browser service and also enable the TCP/IP connection for my SQL Server. I followed these steps and now it’s OK for me: http://msdn.microsoft.com/en-us/library/ms177440.aspx
Hope it can help somebody! 🙂
Thanks for all!
January 19, 2013 at 6:44 am |
I’ve downloaded “jtds-1.3.0” file from your provided link but after adding the file I am unable to create a new connection because it doesn’t show the dialogue to create a new connection. And when I remove the “jtds-1.3.0” file it allows me to create a connection but “SQL SERVER” tab is not available there.
January 23, 2013 at 1:38 am |
Hello .. I am facing the same issue as Uzair Awan is experiencing. I tried referencing the library both on XP and Vista and none works. Please help. Thanks.
January 25, 2013 at 2:17 pm |
I am having the same issue as Uzair and oz. Anyone else able to correct this issue?
January 25, 2013 at 2:23 pm |
Update: The link at the top of the page takes you to jtds-1.3.0.jar. Need to actually download jtds-1.2.jar from http://www.findjar.com/jar/net.sourceforge.jtds/jars/jtds-1.2.jar.html and that seems to work properly.
February 14, 2013 at 12:57 am |
Can you update your post to point to the 1.2.2 download? The latest version 1.3 doesn’t work and your page comes up in Google. i was going around in circles trying to figure out what I did wrong till I saw the comment about the 1.2 jar being needed.
February 18, 2013 at 3:54 am |
URL for all JTDS files
http://sourceforge.net/projects/jtds/files/jtds/
February 19, 2013 at 9:41 am |
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.
March 5, 2013 at 4:27 pm |
Blog post has lasted the test of time.. Works perfectly, once I’d added the connection string parameters after port number 🙂
Thanks
Mike
April 4, 2013 at 7:00 pm |
Having problems connecting to sql database. The server has changed and the hostname is very different. It looks like ABCDEFG09\PA_CMPORT05. Unknown server host name is the message I get when I use this as the hostname and port 1433. Any ideas how to configure with this type of host. I assume the PA_CMPORT05 is an instance name.
April 19, 2013 at 10:26 am |
Man, so many questions here that one wonders if this is actually not that straightforward! When you think about it, a simple 4 field table has 16 permutations, each of which could have something slightly wrong with them. So no wonder everyone’s pulling their hair out here… :((
April 19, 2013 at 8:59 pm |
How is this comment relevant?
August 1, 2013 at 11:29 pm |
João Daniel , your solution of enabling TCP/IP in Sql Server 2008 r2’S configuration utility helped me. Thanks fella
September 25, 2013 at 3:51 am |
Good tip! Your post was easy and practice!
This other post helped me a configure the SQL Developer in Mac http://stackoverflow.com/questions/3452/sql-client-for-mac-os-x-that-works-with-ms-sql-server
September 26, 2013 at 10:20 am |
Hi im Moriah Oracle consultant. I was just browsing blogs there I found your blog is interesting.. thanks for posting… keep on posting oracleconnections
September 6, 2014 at 9:34 pm |
[…] Without SQL Developer this whole process would have been unnecessarily complicated. SQL Developer has the very nifty ability to connect to other non-Oracle databases and data sources via it’s support of Third Party JDBC Drivers. When the necessary JDBC Driver is present, SQL Developer will present the corresponding tab on the connection dialog. Jeff Smith explains the feature best in his Connecting to Access, DB2, MySQL, SQL Server, Sybase, & Teradata with SQL Developer blog post. We connected directly to the SQL Server database using the jTDS SQL Server JDBC driver that we found thanks to this other post. […]