The following screen illustrates a typical SQL Server JDBC connection:

Fields

Description

Connection name

Enter a name for the connection.

Connection type

Select JDBC from the drop-down list.

Database type

Select MS SQL Server from the drop-down list.

Host

Enter the host name or IP address.

Port

The default SQL Server port is 1433, however this can be changed by the Database Administrator. To determine the port, check the SQL Server Configuration Manager or use path 'C:\WINDOWS\system32\drivers\etc\services' or the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp'. The port can also be inspected through the command 'netstat -an' or or 'netstat -b'. 'netstat -b' requires running the Command Prompt as an Administrator.

Further details for finding the Port are explained below.


Database

Type the MS SQL Server database name.

Max simultaneous

Set the maximum number of simultaneous connections 3D will open to the system.

Manual tree expand

Select the checkbox to manually expand and load the database and host connections tree, instead of automatically drilling down the tree.

JAR libraries

Select the SQL server jar file from the JDBC-DRIVERS directory.

User

Enter the username for connecting to the specific database.

Password

Enter the password for connecting to the specific database.

Store password in

Select one of the following options if you choose to save or not to save your password:

  • Repository
  • Locally
  • Do not store

Test connection

Click the button to ensure the entered parameters are correct.

To use Windows Authentication and JDBC to connect to a SQL Server database, a windows dynamic link library (DLL) called ntlmauth.dll must be present on your machine. In this case, leave the User and Password blank.
WhereScape 3D includes both a 32-bit and a 64-bit version of ntlmauth.dll inside x86 and x64 directories respectively. If your Windows version is a 32-bit version, 3D will install the 32-bit dll which will show in the program files directory.
If your Windows version is a 64-bit version, 3D will install the 64-bit dll which will show in the program files directory.

To connect to MS SQL Server, first determine whether you can log in using SQL Server Authentication. This can best be done using Microsoft SQL Server Management Studio, selecting SQL Server Authentication.

If the connection could not be established then confirm the following settings:

  1. Login to SQL Server Management Studio using Windows Authentication from the database server. Right-click on localhost and choose Properties.
  2. Ensure that the Server authentication allows both SQL Server and Windows Authentication.
  3. Select the check-box allow remote connections to this server.
  4. We need to check that the database user has the right permissions. Right-click on the database user and choose Properties.
  5. Confirm that the Server roles are appropriate.

    The minimum required server roles are: public.
  6. Confirm that the User Mapping settings are appropriate

    The minimum required server roles are: db_datareader and public.

  7. Enable permissions to connect on the Status tab and login.
  8. Open SQL Server Configuration Manager and confirm that for MSSQLSERVER, TCP/IP is enabled.
  9. Confirm that for Client Protocols, that TCP/IP is enabled.
  10. This can be verified by the 'netstat -an' command in Command Prompt. Here we see that port 1433 is enabled.
  11. Finally, stop and start the SQL Server service on the localhost drop-down list or alternatively using the services control panel.