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

14 Jan 2013

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:

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

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.

Posted in Oracle and tagged #SQLDev, #SQLDevModeler, #techtip, @KentGraziano, @thatjeffsmith, data profiling, Free, jdbc, Oracle, software, SQL Developer, SQL Server, sql server database, technology

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

Henry Miller on January 15, 2013 at 7:40 am said:

Thanks much Kent. Perfect timing.

Reply ↓
- Kent Graziano on January 15, 2013 at 7:49 am said:
  
  You are welcome!
  
  Reply ↓
George Laframboise on January 16, 2013 at 10:19 pm 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

Reply ↓
- Kent Graziano on January 16, 2013 at 11:32 pm said:
  
  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.
  
  Reply ↓
Alex on January 17, 2013 at 5:15 pm said:

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

Reply ↓
- Kent Graziano on January 17, 2013 at 10:54 pm said:
  
  Thanks for the tip Alex!
  
  Reply ↓
Allan Ford on February 5, 2017 at 10:35 pm said:

This works for me and I have used it .. A question arises when the host name has multiple instances … eg. ADERAD01D\SQL14 and how to connect via SQL*Developer with this \ notation …

Reply ↓
Allan Ford on February 6, 2017 at 12:12 am said:

Regarding multiple instance on one sql*server host.
See:
http://dba.stackexchange.com/questions/68771/connecting-to-sql-server-using-oracle-sql-developer

I got this working in Oracle SQL* Developer now ..

i.e. you have to put the instance in the port number section as per this example ! (so we might be able to get this going in Boomi also ..)
1433:instance=SQL14

Reply ↓
- Kent Graziano on February 6, 2017 at 9:05 am said:
  
  Great! Thanks for sharing the solution.
  
  Reply ↓
Pingback: Migrate from Microsoft SQL Server to Oracle 11g | Duh! Microsoft did it again
James Prior on May 27, 2017 at 7:32 pm said:

Thanks for the great tips.
Has anyone had any success importing from Azure SQL Database?
I can connect fine when importing the Data Dictionary but the ‘Select Schema/Database’ page only shows me Master. I added the database I want as ‘initial catalog’ in the ‘port’ field in the connection, this connects successfully but the database selection still reverts to master when running through the import wizard.
A workaround is importing a bacpac file to a local traditional SQL instance, but would be handy to be able to connect directly to the cloud.

Reply ↓
- Kent Graziano on May 27, 2017 at 9:32 pm said:
  
  Good question. I have not heard of anyone trying. I was able to do it against Snowflake but that was using our JDBC connector. I
  
  Reply ↓
Admin on June 7, 2017 at 4:09 am said:

what would be database User name and Password for for it?
Thanks

Reply ↓
- Kent Graziano on June 7, 2017 at 8:07 am said:
  
  Whatever your DBA set up for you. There is no default, that would be a major security violation.
  
  Reply ↓
harry on September 1, 2017 at 12:19 pm said:

This is driving me crazy. I can connect to a remote SQL Server 2012 database using SQL Developer. I can connect to SQLEXPRESS on my laptop using TOAD for SQL Server. But SQL Developer won’t connect to SQLEXPRESS on my laptop. Just keeps telling me “cannot connect to Microsoft SQL Server on localhost”. I’ve tried about every combination I can think of/google up.

Reply ↓
- Kent Graziano on September 1, 2017 at 2:22 pm said:
  
  This is probably a good one to post on the OTN discussion forum. There has to be an answer for this.
  
  Reply ↓
  - harry on September 1, 2017 at 12:45 pm said:
    
    done! I should have thought of that. Thanks!
Peter Woolf on October 6, 2017 at 9:50 am said:

I have tried several different methods and connectors and still not able to connect, I have a MSSQL 2014 and 2016, I created a temporary virtual server using MSSQL 2008 and I was successful, but not with newer servers, Microsoft just released MSSQL 2017 and I couldn’t connect either.

I haven’t tried 64 bit JDK, but when used MSSQL 2008 I was succesful with 32 bit.

Do you have any ideas or solutions?

Reply ↓
- Kent Graziano on October 6, 2017 at 12:10 pm said:
  
  What version of SQL Developer are you using? Have you looked to see if there is a newer JTDS driver?
  
  Reply ↓
Mary Szczerbinski Sciuto on April 18, 2018 at 12:32 pm said:

I’m running SQL Developer 3, connecting to SQL Server 2014, SP2. I am able to connect to the SQL Server instance/database, but when I try to drop down any of the databases, I get error
“Invalid column name ‘suid’.
Vendor code 207.
Are there any solutions for this?

Reply ↓
- Kent Graziano on April 18, 2018 at 3:34 pm said:
  
  I do not know myself. Have you checked the Sql Developer forum on OTN.
  
  Reply ↓
Michael Coats on April 26, 2018 at 10:52 am said:

I have yet to find a driver that works for connecting SQL Developer to SQL Server 2014. The jtds sourceforge and Microsoft drivers all work fine for 2008/2008R2/2012 and 2016, but not for 2014. This includes the most recent 6.2 and 6.0.8112 drivers i could get from Microsoft’s site. I believe most SQL dbas know about this and we are either stuck building the driver or going without. If anyone figures it out, I would appreciate the info.

Reply ↓
- harry on April 27, 2018 at 11:45 am said:
  
  I’m connecting SQL Developer to SQL Server 2014 using jTDS 1.2. Maybe it’s just dumb luck on my part.
  
  Reply ↓
  - harry on April 27, 2018 at 11:46 am said:
    
    SQL Developer version 4.2.0.17.089
Abdi Hassan on October 15, 2018 at 1:32 pm said:

I was able to move SQL server Database to oracle, how can I create a bridge connection to validate both databases in Oracle?

Reply ↓
- Kent Graziano on November 25, 2018 at 8:56 pm said:
  
  I am sorry but I do not know how to do that. You may be able to do a compare databases using SQL Developer.
  
  Reply ↓
Pingback: Importing Your Data Dictionary to a new Oracle SQL Developer Data Modeler Design – ThatJeffSmith
kapil gupta on September 23, 2019 at 2:07 am said:

Hi All – I got the error while setting up SQL Developer to connect SQL Server.

Error –> “Status: Failur-I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.”

I have applied all the below solutions but no luck, getting the same error again and again.

1 – Download and open the JTDS folder.
2 – The ntlmauth.dll you will find inside the x86/SSO folder
3 – The JtdsXA.dll you will find inside the x86/XA folder
4 – Put the ntlmauth.ddl and JtdsXA.dll inside de Sql Developer/sqldeveloper/bin
5 – Copy the jtds-1.3.1.jar to Sql Developer/sqldeveloper/lib
6 – Reinstall Java as well
7 – Restarted the system and SQL Developer number of times.

I am using SQL Developer 18.4 version.

Looking for immediate solution or advise to fix this issue at the earliest.

my email – kapilg007@gmail.com

Thanks,
Kapil

Reply ↓
- Kent Graziano on September 23, 2019 at 7:37 am said:
  
  I do no have access to SQL Server to test. I would recommend you post this on the SQL Developer forum. There is already a Q&A on this topic there: https://community.oracle.com/thread/4289838
  
  Reply ↓
Rosibelda Mondragón Becerra on February 26, 2020 at 12:43 pm said:

Hi,
I was able to connect from Oracle SQL Developer to SQL Server 2008 r2, but I cannot connect to SQL Server 2016. I have the jtds-1.3.1 driver. I get the error “provider code 18456”.
The same happens to me, when I want to connect from Oracle SQL Server Data Modeler to SQL Server 2016. I get the error “Status: Failure: Test failed: Login failed for user ‘Est_Museos.update’. ClientConnectionId: 73821b60-ab82-4a9e- 9cb6-139243515da7 “. However, I do connect to SQL Server 2008 R2. Here I use the mssql-jdbc-6.4.0 driver.

Thanks,
Rosy

Reply ↓
- Kent Graziano on February 26, 2020 at 6:49 pm said:
  
  Likely need and updated driver. Unfortunately I no longer have access to SQL Server to test. You might better post the question on the SQL Dev discussion forum on the Oracle site.
  
  Reply ↓
  - Rosibelda Mondragón Becerra on February 27, 2020 at 9:31 am said:
    
    Thank you very much for your answer. I will try another driver 🙂
Pingback: database sql-server – Conexión a SQL Server mediante Oracle SQL Developer - CodeBug
anit (@keyounique) on May 27, 2022 at 2:45 am said:

Hi, I have successfully connected to Sybase database using this method. But, when I try to retrieve database / open SQL worksheet, there is an error “Status: Failure -SQL Anywhere Error -110: Item ‘jdts000001′ already exists’ Perhaps you can help me with that?

Thank you in advance,

Anitaanitasari2311@gmail.com

Reply ↓
- Kent Graziano on May 27, 2022 at 12:20 pm said:
  
  Last I knew, this really only works on databases that Oracle had built migration utilities for. But the jtds error is a bit suspicious. Your best bet is to post the question on the Sql Developer forum that Oracle hosts for the developer community.
  
  Reply ↓
Alex Chen on November 7, 2022 at 10:38 am said:

I was successfully connecting to SQL Server 2019 (15.0.4236.7) using jTDS-1.3.1.jar, but it stalls since the server OS applied TSL encryption. It seems jTDS-1.3.1.jar support ssl (https://jtds.sourceforge.net/faq.html). but when I add ssl=required to the existing connect string, the log shows error:
“Encryption is required to connect to this server, but the client lib doesn’t support encryption; the connect has been closed. Please upgrate your client library.”

Reply ↓