Travel

Sep 30 2017

Using SQL Developer to connect to SQL Server databases #sql #server,sql #developer,jtds,sybase


Using SQL Developer to connect to SQL Server databases

Apparently, being able to use Oracle s SQL Developer to connect to SQL Server databases has been around for a while (via third party drivers), but I only started using it today for the first time, with SQL Developer 4 EA2 after my colleague mentioned it to me this afternoon

First of all, thanks to DJ s blog here as a base for the instructions that follow. Here s how you can set this up:

  1. Download the jTDS 3rd party JDBC driver, version 1.2.8 from here (I ve not personally tested with other versions).
  2. Save and extract the contents of the jtds-1.2.8-dist.zip file somewhere safe (I extracted mine under C:\oracle\sqldeveloper\add-ons\jtds-1.2.8-dist\ ).
  3. Start SQL Developer go to Tools Preferences Database Third Party JDBC Drivers.
  4. Click “Add Entry”, point to the jtds-1.2.8.jar file location, then select it and click OK.
  5. Create a new connection, and choose the SQLServer tab.
  6. Enter the following details: Connection Name (required for database retrieval), Username, Password, Hostname, and Port.
  7. Click Retrieve database and select the appropriate database, then Save/Test/Connect as you would do normally.

You ll notice from the screen shot above that you can also use this to connect to Sybase databases, although I ve not tried this out myself.

UPDATE: If you at any point decide to use Windows authentication to connect (like I just did), then you might hit this error:

Status. Failure -I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

…I quickly found a work around, which involved copying the ..\jtds-1.2\x64\SSO\ntlmauth.dll file from the jTDS directory to the %JAVA_HOME%\jre\bin folder. Then I restarted SQL Developer and it all worked fine

This works, but unfortunately there are a number of shortcomings. Some key words used in T-SQL and SQL Server scripts like GO and EXEC don t play nicely with Oracle SQL Developer. Also issues with Stored procs that return multiple result sets etc. It really depends on your environment, but if you re dealing with a lot of large scripts that were written in T-SQL you ll find issues.

Thanks for the heads up I never really spent too much time using SQL Developer for managing SQL Server (defaulted to using SSMS), so that s good to know. Hopefully the development team will pick up on such issues and fix in later releases though



Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *