You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

The following screen illustrates a typical SQL Server JDBC connection:
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT1.png

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'.<br />. 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.

Tip

Further details for finding the Port are explained below.
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT2.png

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.


Note

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.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT3.png

  • 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.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT4.png
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.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT5.png

  1. Ensure that the Server authentication allows both SQL Server and Windows Authentication.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT6.png

  1. Select the check-box allow remote connections to this server.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT7.png

  1. We need to check that the database user has the right permissions. Right-click on the database user and choose Properties.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT8.png

  1. Confirm that the Server roles are appropriate.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT9.png

Note

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

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT10.png

Note

The minimum required server roles are: db_datareader and public.
  1. Enable permissions to connect on the Status tab and login.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT11.png

  1. Open SQL Server Configuration Manager and confirm that for MSSQLSERVER, TCP/IP is enabled.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT12.png

  1. Confirm that for Client Protocols, that TCP/IP is enabled.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT13.png

  1. This can be verified by the 'netstat -an' command in Command Prompt. Here we see that port 1433 is enabled.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT14.png

  1. Finally, stop and start the SQL Server service on the localhost drop-down list or alternatively using the services control panel.

REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT15.png
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT16.png

  • No labels