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. |
|
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:
|
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. |
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:
- 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
- Ensure that the Server authentication allows both SQL Server and Windows Authentication.
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT6.png
- Select the check-box allow remote connections to this server.
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT7.png
- 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
- 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. |
- 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. |
- Enable permissions to connect on the Status tab and login.
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT11.png
- Open SQL Server Configuration Manager and confirm that for MSSQLSERVER, TCP/IP is enabled.
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT12.png
- Confirm that for Client Protocols, that TCP/IP is enabled.
REPLACE WITH THIS IMAGE: Images/connections/SQL_JDBC_CONNECT13.png
- 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
- 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