Search notes:

Connect to SQL Server with Oracle's SQL Developer

SQL Developer can be used to connect to MS SQL Server if jTDS (TDS = Tabular Data Stream) is installed. Such an installation requires the following steps:
Then, in SQL Developer, this jar file needs to be pointed at in menu Tools -> Preferences -> Database -> Third Party JDBC Driver.
Probably, SQL Developer should be restarted now.
Note: the dialog displays the .jar file as a folder rather than a file.
Update 2020-11-11: I have written a PowerShell script that is supposed to automate these steps: install-jTDS.ps1.
The connection dialog box now comes with the possibility to choose SQL Server as database to connect to:
In order to connect to my local SQL Server 2017 (developer edition), I had to change the default port of 1433 to 1434. I determined the port with
use master;
go

xp_readerrorlog 0, 1, N'Server is listening on';
go

Specifying an instance and database name

The name of the instance needs to be stated in the port field(!): 1433;instance=INSTNAME.
In the same vein, the database name to connect to can (also in the port field) be specified with …;databaseName=DBNAME.
See also this dba.stackechange.com answer.

Using Windows Authentication (SSO Failed: Native SSPI library not loaded)

In order to be able to use Windows authentication, I also had to copy the file x64\SSO\ntlmauth.dll (which implements the Security SupportProvider Interface (SSPI) and is also found in the jtds zip file) to the directory jre\bin under the root directory of the JDK used.
(Without that DLL, the error message Status: Failure -Test failed: I/O Error: SSO Failed: Native SSPI library not loaded was thrown.)
When connecting, the username-password requires a username. With Windows authentication, such a username is technically not necessary. I was able to proceed with the connection by just entering a space into the username field.

Circumenventing the entering of a blank username

Dennis Laliberte notified me that is is possible to circumvent the entering of a space by following these steps in the SQL Developer connection properties:
  • Uncheck Use Windows Authentication
  • Check Use Default Password
  • Check Use Windows Authentication

begin transaction / rollback

When trying to enter begin transaction, SQL Developer seems to recognize begin as a PL/SQL keyword and the error Incorrect syntax near the keyword 'begin' is thrown.
I was able to start a transaction by prepending the begin and rollback with a semicolon like so:
; begin transaction;

update tab …

; rollback;

PowerShell script to install jTDS

This PowerShell script tries to download jTDS.jar and install it so that it can be used in SQL Developer.
Note: I didn't thoroughly test the script, so use at your own risk!
$jtds_version     = '1.3.3'
$sqldev_dir       = "$home/bin/sqldeveloper/"
$dest_dir         = "$sqldev_dir/jdbc/lib"
$prod_pref_dir    = "$env:appdata/SQL Developer/system19.2.1.247.2212/o.sqldeveloper"
$prod_pref_name   = "$prod_pref_dir/product-preferences.xml"

invoke-webRequest "https://github.com/milesibastos/jTDS/releases/download/v1.3.3/jtds-$jtds_version-dist.zip"    -o jtds.zip

expand-archive jtds.zip  jtds

$jtds_jar_name = "jtds-$jtds_version.jar"
copy-item "jtds/$jtds_jar_name" $dest_dir

pushd $prod_pref_dir
$jtds_jar_rel_path =  get-item "$dest_dir/$jtds_jar_name" | resolve-path -relative
popd

# ----- Write config.xml

[xml] $prod_pref_xml = get-content $prod_pref_name

$list_TPDRIVER = $prod_pref_xml.CreateNode('element', 'list', '') # 3rd parameter is namespace
$url           = $prod_pref_xml.CreateNode('element', 'url' , '')

$list_TPDRIVER.SetAttribute('n', 'TPDRIVER')
$url.SetAttribute('path'     , $jtds_jar_rel_path)
$url.SetAttribute('jar-entry',''                 )

$list_TPDRIVER.AppendChild($url)

$db_config = $prod_pref_xml.SelectSingleNode('//*[@n="DBConfig"]')
$db_config.AppendChild($list_TPDRIVER)

$prod_pref_xml.Save($prod_pref_name)

# ----- Copy x64\SSO\ntlmauth.dll

copy-item jtds/x64/SSO/ntlmauth.dll  "$env:JAVA_HOME/jre/bin"
Github repository about-SQL-Developer, path: /SQL-Server/install-jTDS.ps1

Index