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


 
This information which you provided is very much useful for us.
It was very interesting and useful.
Visit us:
Oracle PL SQL Database Training VA
Big Data Hadoop Training VA
 
Managing the database and share with an active server is essential for their safe, easy and effective sharing. You just need to create an MS access folder for storing the data and later on simply upload it on the server for easy usage. To know more please click here
 
Excellent information
Salesforce Training in Hyderabad

 
I have installed 11g on window 7 using SQL Developer of 11g I am migrating the SQL server 2008 database to Oracle 11g Migration> Quick Migration selecting Source Connection >Target Connection> Repository AND verify it generate Errors:

Locating source plugin FAILED
Checking multi-schema roles FAILED Role 'RESOURCE' is not granted with admin option

I have granted the all required permission to the User But it still generating the same error.

Please Help
 
I am trying to create a database that will have all of my employees stats and task efficiency records. Is something like the SQL Client a program that I could use to get this idea accomplished? It would would be nice to have everyone I oversee, on the same page.
http://www.datasparc.com/dbhawk-features/
 
hi,this is excellent information..i would be like for that for your information..hadoop training in chennai
 
hi,this is the good information i have being for this sites information..
 
Uniqe informative article and of course True words, thanks for sharing. Today I see myself proud to be a hadoop professional with strong dedication and will power by blasting the obstacles. Thanks to hadoop training in chennai
 
SAP stands for Systems Applications and Products in Data Processing. SAP is one of the leading ERP software in the world. SAP has been widely used by large to very large organizations and also by SMEs to manage their business processes visit us,
Sap training in chennai | SAP Success factor training in chennai | Workday training in chennai | Success factor training in chennai

 
hi ,this is blogs information is very excellent if i had to learning about the lot information..hadoop training in chennai | hadoop training in chennai
 
hi ,in blogs in really good...We have to provide preparing many option for this sites....oracle training in chennai..Thanks a lot..oracle training in chennai
 
hi, learning more that lot info..i hope really like for this info..oracle dba training in chennai | oracle dba training in chennai
 
Oracle database course students can clear oracle global certification exams like OCA, OCP ,OCM.Trainingbangalore povides latest oracle version training like 10g,11g,11g R2.Our training center equiped with high level lab facilites.
Visit Us, Oracle Training in Bangalore
 
Oracle sql developer with ms-sql is new topic.Its really sup[erb.QTP Training in Chennai | Selenium Training in Chennai | Loadrunner Testing Training in Chennai | Software Testing Training in Chennai

 
Thanks for share the innovative message its very useful for us

cloud computing training in chennai | salesforce training in chennai | dot net training in chennai
 
well said nice article its useful for everyone

dot net training in chennai | salesforce training in chennai | dot net training in chennai | salesforce training in chennai
 
I observed some kind of stuff in your blog. That makes all to tend your blog. Keep it up.

Java training in Chennai | Android training in chennai | Java training in Chennai
 
Yes that is true. Such a blog. Anyone can do everything.

Java training in Chennai | Android training in chennai | IOS training in chennai
 
Awesome blog. I am waiting for your next blog.

Java training in Chennai | Android training in chennai | IOS training in chennai


 
Can found little difference in all your part. Such an awesome usability.

Java training in Chennai | Android training in chennai | Java training in Chennai
 
Thanks yar. Nice article.

Java training in Chennai | Android training in chennai | Java training in Chennai
 
This article is nice to read and updating the knowledge also. Thanks for sharing.
Sap training in chennai | SAP Success factor training in chennai | Workday training in chennai | Success factor training in chennai

 
This article is nice to read and updating the knowledge also. Thanks for sharing.
Sap training in chennai | SAP Success factor training in chennai | Workday training in chennai | Success factor training in chennai

 
Your blog is really useful for me, and I gathered some information from this blog. I did SAP Training in Chennai, at FITA Academy. It's really useful for me to make a bright career in IT industry.

 
Thanks for sharing this Information blog. Recently I have completed Php Training Courses in chennai at a leading Company. It's really useful for me to make a bright Future career. Visit Us,Php Training Institute Chennai. besant technologies reviews | besant technologies reviews | besant technologies reviews
 
Thanks for sharing this Information blog. Recently I have completed Php Training Courses in chennai at a leading Company. It's really useful for me to make a bright Future career. Visit Us,Php Training Institute Chennai. besant technologies reviews | besant technologies reviews | besant technologies reviews
 
Hi I am Victoria lives in Chennai. I am a technology freak. Recently I did Java Training in Chennai at a leading Java Training Institutes in Chennai. This is really helpful for me to make a bright career in IT industry.

 
Very informative feature indeed, i have come across a similar solution at an ERP Job Board
 
Hi, This is Emi from Chennai. I have read your blog and I got some knowledge information. Really useful blog. Keep update your blog.

Regards...
Java Training Institutes in Chennai

 
Nice blog. Please sharing again more information. Java Training Chennai
 
Hi, I am Vijay from Chennai. I am technology freak. I did Android mobile application development course at Fita academy, this is very useful for me to make a bright career in IT industry. So If you looking for best Android Training Institutes in Chennai please visit fita academy.



 
Hi friends, This is Jamuna from Chennai. Your technical information is really useful for me. Keep update your blog.
Regards..
Oracle Training in Chennai

 
Thanks for sharing your innovative ideas..Its really useful and interesting...

Regards...

Salesforce Administrator Training in Chennai
 
I get a lot of great information from this blog. Thank you for your sharing this informative blog. Recently I did PHP course at a leading academy. If you are looking for best PHP Training Institute in Chennai visit FITA IT training academy which offer real timePHP Training Chennai.

 
This blog is really very helpful.It helps for the oracle interiew thank you friends..


Java Training in Chennai
 
Cloud computing is storing and accessing the large data sets over the internet instead of your PC computer. So that you can manage the data and program anywhere through the internet.
Regards..
Cloud Computing Training in Chennai



 
Hi, Your blog is really very informative and useful for me. Thanks for sharing this valuable blog.
Regards..
Unix Training
 
Thanks for sharing; Salesforce crm cloud application provides special cloud computing tools for your client management problems. It’s a fresh technology in IT industries for the business management.
Regards,
Salesforce training in Chennai|Salesforce training |Salesforce training institute in Chennai
 
This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic.
Regards,
PHP Training in Chennai|php training in velachery|php training institute
 
I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging.
Web design institutes in Chennai|Web design training institutes in Chennai|Web designing course in chennai
 
Really awesome blog. Software testing is a method of executing the application or program with the intent of searching the software errors. Software Testing Training in Chennai offering this course at reasonable cost.






 
Thanks for sharing this niche useful informative post to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future so do your sap training in Chennai
Regards,
SAP institutes in chennai|SAP Training Chennai|sap course in Chennai|SAP MM Training In Chennai
 

Thanks for sharing this valuable post to my knowledge; SAS has great scope in IT industry. It’s an application suite that can change, manage & retrieve data from the variety of origin & perform statistical analytic on it.
Regards,
sas training center in Chennai|sas training in Velachery|sas course in Chennai
 
Thanks for sharing nice info.You can also find information on Oracle apps on ebiztechnics.
Oracle application training and consulting
 
Very informative post. If interested, one can take up AngularJS training in Chennai and stay up to date in technology.
 
Well post, Thanks for sharing this to our vision. In recent day’s customer relationship play vital role to get good platform in business industry, Sales force crm tool helps you to maintain your customer relationship enhancement.
Regards,
Salesforce training in Chennai|Salesforce institutes in Chennai|Salesforce training center in Chennai
 

The information you have given here is truly helpful to me. CCNA- It’s a certification program based on routing & switching for starting level network engineers that helps improve your investment in knowledge of networking & increase the value of employer’s network...
Regards,
ccna courses in Velachery|ccna training center in Chennai|ccna institutes in Chennai
 
Post a Comment

<< Home

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