Create connection to SQL server DB from Oracle data modeler

I was able o figure it out.

Here's what I did:

JDBC URL: jdbc:sqlserver://testServer\SQLEXPRESS/Databases/testDB:1433;user=sa;password=pass;Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

Also I added sqljdbc4.jar in Prefrences. (under Tools option).

Make sure that 1433 port is set in SQL Server. to do so, perform below steps:

1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network Configuration.
2. Click Protocols for InstanceName, and then make sure TCP/IP is enabled in the right panel and double-click TCP/IP.
3. On the Protocol tab, notice the value of the Listen All item.
4. Click the IP Addresses tab:If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll.If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.
5. Make sure the TCP Port is 1433.
6. Click OK.

You can also use ODBC driver. I am not sure if ODBC driver is installed with SQL Server or native to Windows or you have to install it separately.

If using Windows XP:

--ODBC--

Start Button -> Setting -> Control Panel -> Admin Tools -> Data Sources (ODBC)

After opening the ODBC Data Source Administrator if not on then go to -User DSN- tab

Click Add -> Choose SQL Server Native Client 10.0 ->

Name: ex: AdventureWorks2008 Server: "SQL Server Name" from drop down box example: (local) or SQL Server name initially specified during setup->

Next ->

How Should SQL Server verify authenticity?Choose how you want to connect to db Windows integrated or SQL server (if SQL Server then add user parameters eg user= sa pass='*')

Next ->

Change default database to AdvetureWorks2008 -> Next 'dont touch anything here unless...-> and click Finish -> click on Test Data Source -> OK

--Data Modeler--

Now Goto Oracle SQL Data Modeler (mine is Ver 4.00)

First if you already have something in your Relational Model then create a new Relational Model in the Data Modeler browser -> RightClick -> New Relational Model - this will hold your imported data dictionary from the SQL Server

File -> Import -> Data Dictionary (you are now on Data Dictionary Import Wizard)

Click Add-> Connection Name: Ex: AdventureWorks2008 -'you can give it any name, you'll be able to connect to any db within SQL Server'

Click on JDBC tab and bring up JDBC-ODBC bridge (option button - click if not clicked on by default) -> ODBC Alias: AdventureWorks2008 (exactly as you specified in the ODBC setup)

Database Type: SQL Server

Click on -Test- to make sure connecting successfully.

Click Save

Click Connect

You're still on the Data Dictionary Connection Wizard

Highlight the name of the connection "AdventureWorks2008"

Click Next -> Choose db you want ex: AdventureWorks2008 -> Next -> Choose tables you want ->next -> Finish

This is wild but I found that when I added the SYBASE JDBC driver to Tools| Preferences | Database | Third Party JDBC drivers then my SQL Server connections started to work. I had a Microsoft JDBC driver added but until I added the Sybase JDBC driver, the connections would fail and not even reach the SQL Server instance. This may be because both Sybase and MS SQL use TDS data streams. The Sybase file I added was jtds-1.3.1.jar. Note: I wasn't using the JDBC tab, I was using the SQL Server tab in Connections.