Using Oracle SQLDeveloper to access SQLServer

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

About these ads

63 Responses to “Using Oracle SQLDeveloper to access SQLServer”

  1. Maria Says:

    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

  2. Dong Says:

    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.

  3. treasaigh Says:

    Turning on TCP/IP did the trick… Thanks!

  4. Ramu Says:

    Very helpful, Thanks a lot

  5. kally Says:

    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.

  6. DJ Says:

    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

  7. khaled Says:

    downlaod

  8. Leo Says:

    Find out which port your SQL Server is listening and may be in your case it is not the default 1433.

  9. xptiger Says:

    Use this connection setting

    Connection Name: MyDataBaseName
    Username: user
    Password: password
    Hostname: localhost
    Port:1433/databaseName;instance=SQLEXPRESS

  10. b@Wah Says:

    Woww, thanks xptiger, it works

    But my schema is not recognized :(

  11. Avinash Says:

    Thank you guy for publising it out. It was really helpful

  12. Jim Shumaker Says:

    Thanks for putting this out there. Speed up figuring that out.

  13. Marcio Costa Says:

    Thanks xptiger works.

  14. Ton Segboer Says:

    Thanks DJ, this really helped me!

  15. Jonathan Says:

    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?

  16. arthur Says:

    Thanks, works fine!!! I love Linux but at my work use MS SQL Server, and now I can use my Ubuntu!

  17. naveen Says:

    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.

  18. Matt Corbett Says:

    http://www.oracle.com/technetwork/database/migration/omwb-getstarted-093461.html

  19. Matt Corbett Says:

    If using SQLEXPRESS put this in the box for port [1433/dbnameyouwantoconnectto;instance=SQLEXPRESS]

  20. sstacha Says:

    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.

  21. – ORACLE SQL DEVELOPER - « Richlab team's Blog Says:

    [...] – http://helpdeskgeek.com/databases/an-introduction-to-oracle-sql-developer-part-v/ – – http://djiang.wordpress.com/2007/02/27/using-oracle-sqldeveloper-to-access-sqlserver/ [...]

  22. Jojo Says:

    Thanks man – worked a treat

  23. Jojo Says:

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

  24. KrishnaBabu Says:

    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?

  25. john reynolds Says:

    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?

  26. Exportando dados do MS SQL Server para o Oracle | Profissionais TI - Pra quem respira informação Says:

    [...] e configure-o para acessar o Banco de Dados SQL Server, seguindo os passos descritos no link: http://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 [...]

  27. Ravikalaa Krishnan Says:

    Thanks for your post on this. really helped me straight away!

  28. reciprocating pump curve Says:

    reciprocating pump curve…

    [...]Using Oracle SQLDeveloper to access SQLServer « DJ’s blog[...]…

  29. SQL Developer Supports DB2, Sybase, MySQL, Access, and Teradata Says:

    [...] 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 [...]

  30. Jin Says:

    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?

  31. Fred Says:

    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.

    • artexmg Says:

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

  32. nathan Says:

    Reblogged this on Nathan.

  33. Vamsi Jamalla Says:

    List the details of the employee earning more than the highest paid Manager but not same department.?

  34. krcourser Says:

    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.

  35. kingsmaped Says:

    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…

  36. Ricardo Cortés Says:

    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!

  37. vithyashree Says:

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

  38. Alvin Heninger Says:

    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!

  39. sen Says:

    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

  40. João Daniel Says:

    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!

  41. Uzair Awan Says:

    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.

  42. oz Says:

    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.

  43. Andy Limbert Says:

    I am having the same issue as Uzair and oz. Anyone else able to correct this issue?

  44. Andy Limbert Says:

    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.

  45. Brian B Says:

    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.

  46. Ricky T Says:

    URL for all JTDS files

    http://sourceforge.net/projects/jtds/files/jtds/

  47. vishal0soni Says:

    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.

  48. Mike Hudson Says:

    Blog post has lasted the test of time.. Works perfectly, once I’d added the connection string parameters after port number :)

    Thanks
    Mike

  49. Berverly Says:

    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.

  50. Chris Says:

    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… :((

  51. Amit Says:

    João Daniel , your solution of enabling TCP/IP in Sql Server 2008 r2’S configuration utility helped me. Thanks fella

  52. Denis Santos Says:

    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

  53. Moriah Riley Says:

    Hi im Moriah Oracle consultant. I was just browsing blogs there I found your blog is interesting.. thanks for posting… keep on posting oracleconnections

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: