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:
- Download the jTDS 3rd party JDBC driver, version 1.2.8 from here (I ve not personally tested with other versions).
- 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\ ).
- Start SQL Developer go to Tools Preferences Database Third Party JDBC Drivers.
- Click “Add Entry”, point to the jtds-1.2.8.jar file location, then select it and click OK.
- Create a new connection, and choose the SQLServer tab.
- Enter the following details: Connection Name (required for database retrieval), Username, Password, Hostname, and Port.
- 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