In This Article


How to Connect to MS SQL Database

Step 1 - Create a JDBC Config File (config.properties)

For GenRocket to connect to an MS SQL database via JDBC, you must create a JDBC Config File (config.properties). Any application can connect to an MS SQL database via JDBC by reading the information included in this file. A template and example are shown below. Click here to learn more.


Template

driver=<JDBCDriverClassName>
user=<userToConnectToDatabase>
password=<passwordToConnectToDatabase>
url= jdbc:sqlserver://<serverName>:<portNumber>;<property=value>
batchCount=1000

Example

As an example, to connect to a SQL Server database INVENTORY  that is in a server named Host1 and is configured to listen in the port 2455 with the user User1 and password P@ssword1, the configuration file would be:

driver= com.microsoft.sqlserver.jdbc.SQLServerDriver
user=User1
password= P@ssword1
url= jdbc:sqlserver://Host1:2455;databaseName=INVENTORY
batchCount=1000


JDBC URL Explanation 

The general form of the connection URL is jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

  • jdbc:sqlserver:// is known as the sub-protocol and is constant.
  • serverName is the address of the server to connect to. This could be a DNS or IP address.
  • instanceName is the database instance to connect to. If not specified, a connection to the default instance is made.
  • portNumber is the port number of the database instance. The default is 1433.
  • Property is one or more option connection properties.


Note: You can refer here for further details.


Step 2 - (Optional) Encrypt the JDBC Config file

You can encrypt this file for additional security. Click here to learn more.

Step 3 - Update Your JDBC Organization Resource Value

The resource.jdbc.config Organization Resource specifies the location of the JDBC Config file on the logged in user's local machine. This resource value should contain the full directory path (e.g., /home/user/dev). Click here to see step-by-step instructions.


Step 4 - Update your Profile in the .genrocket Folder

Each user has a profile stored in the .genrocket folder on their local machine. You must download and replace your profile (in ~/.genrocket folder). GenRocket will read this resource value from your profile on your system to make a connection with the database. Click here for more information.


Step 5 - Download and place the JDBC connector Jar file in the $GENROCKET_HOME/lib/ folder


Step 6 - Test the JDBC Connection

You can test the JDBC connection before referencing the resource in GenRocket. Click here to learn more.

Step 7 - Reference the JDBC Resource in your Generators or Receivers

When you create a Scenario containing a Generator or Receiver that references your jdbc.config resource, it will connect directly to your database. GenRocket feature that uses a database connection will also need to reference this file. 


Connecting via Microsoft Authentication

To connect to an MS SQL database using Microsoft Authentication, users must update the connection URL in their config.properties file and add integratedSecurity=true.  


URL= jdbc:sqlserver://;servername=server_name;integratedSecurity=true;


Note: For more information, please see the following Microsoft article: https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15


If the following DLL error occurs, please see this page for more information: 

https://stackoverflow.com/questions/17277001/dll-missing-in-jdbc

WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.


MS SQL JDBC References

The following list contains useful links that will help you with Java based connection to an MS SQL database:


MS SQL JDBC Troubleshooting Connection Issues

The most common issues when trying to connect to an MS SQL server instance are:

  • User does not exist.
  • Wrong Password.
  • Wrong port number in the connection string.
  • Wrong instance name used in the connection string.
  • User does not have the appropriate privileges.
  • User does not have privileges to the default database.
  • SQL Server Services are down.
  • Orphaned users (this is when you restore a database from one database instance where a user and login exist but not in the instance where the database is being restored)
  • TCP/IP Protocol is disabled.


Before going through the troubleshooting steps, ensure the login exists and has appropriate privileges in the database you want to connect. If the user does not exist, create it and assign a valid password. To troubleshoot the issues mentioned above, follow the next steps.


Checking User Privileges

  1. Connect to the database instance with SQL Server Management Studio with a user with a sysadmin or security admin role.

  2. In the right panel in the management studio, go to Security, expand Logins, and check that the user exists.



  3. If the user is disabled, double click the user name in the object explorer panel.

  4. In the login properties window, go to Status, and from there, enable the user (Do not close this window yet).
     

  5. In the Login Properties window, go to user mapping.

  6. Select the appropriate database and check the privileges that the user has in that database. For instance, if you want the user to have full privileges on that specific database, give db_owner privilege to the user. If you want only read access, give db_datareader or db_datawriter if you wish to provide the user with write privileges.



  7. In the Login Properties window, go to General and note the name of the default database assigned to that user.



  8. Ensure that the user has the appropriate privileges and grants to the default database that has been assigned to that user.

  9. To do that, go to User mapping and select the database name that you saw in the step above and ensure that the user has at least privileges to connect to that database (public privilege should be enough)
    .


  10. Now, after validating all this, click the OK button.


Troubleshooting Orphaned Users

Orphaned users occur as a result of restoring a database from an instance where a specific login exists, but it does not exist in the database instance where you are restoring the database, so the user of the database has no login associated to it. The following image shows an example of this situation:


As you can see, the database TestDB has a user named cvidal5, but there is not a login named cvidal5. There are just two users named cvidal and cvidal2. So, if you try to connect to this database using the user cvidal5 or any other orphaned user, you will get a “login failed” error. It can also happen that the login exists, but the user is not mapped to that login.


To fix this issue, follow the steps below.

  1. In the left panel, expand security, and right click in Logins, then go to New and then Login.



  2. In the Login – New Window, in the Login Name, type the user's name. This must be the same as the user name in the database (for this example, it must be cvidal5).

  3. Then select SQL Server Authentication and type the password of the user. Ensure that User must change the password at next login and enforce password expiration is not checked.



  4. Click OK. This will create the login.
     
  5. Then open a new query window and execute the next command:
    Use <database_name>
    Go
    ALTER USER <user_name> WITH Login = <login_name>;  
    Go


    For this example, the command would be:

    Use TestDB
    Go
    ALTER USER cvidal5 WITH Login = cvidal5;  
    Go




    To fix orphaned users when the login does exist, just execute step 4 from the previous process because, in this situation, the login exists, and you need to link the user with the login. 


Checking SQL Services and Protocols

Ensure that SQL Server services are started, that protocols are enabled, and that you are connecting to the appropriate port and instance. If you do not know the database instance, use the IP address and port number instead.

  1. Open SQL Server Configuration Manager.



  2. Check the instance name to ensure you are connecting to the appropriate instance name in your connection string.
     

  3. In the configuration manager, you can see the instance name, look at the service name, and between parentheses, you will be able to see the instance name.



    In this example, we have two instances, one named SECONDARY and one named SQL2016. If you see an instance named MSSQLSERVER, that is the default SQL Server instance name, so it is not mandatory to specify the instance name in the connection string. If you use an IP address in your connection string, you do not need to specify the instance name, only the port number.


Checking SQL Services and Protocols

  1. If you see that the service state of the database instance is not a Running state, you need to start the service. Right click the service name and then click start.



  2. Now go to SQL Server Network Configuration, expand it, and select the protocols for the instance you are trying to connect.



  3. Ensure that the TCP/IP protocol is enabled. If it is disabled, right click and select enable. 
  4. Once the protocol is enabled, right click on it and select properties. 
  5. In the TCP/IPproperties, go to the IP Addresses tab and ensure that the port number configured for the interface you are connecting to is the same that you are using in the connection string. If not, take note and use the port number you see in here.



Checking that Ports are Opened

  1. If telnet is configured in the host, open a command line and perform a telnet to the IP address and port of the database instance that you are trying to connect.


    If the connection failed, it means that the firewall is blocking the port number. Sometimes telnet will fail anyway for different reasons. So, another way to check this is to go to the windows firewall and check if the port number is in the inbound rules.

  2. Open Windows firewall. 
  3. Open the control panel and go to System and Security. 
  4. Select Windows Firewall, and in the left pane, click on Advance settings.



  5. Go to inbound rules and click New Rule…



    Check if the port number is listed here. If the port number is listed here, something else could be blocking incoming connections. Consider checking your antivirus software. If the port number is not listed here, continue with the next steps.

  6. In the New Inbound Rule Wizard window, select Port and click next.



  7. In the next window, select TCP, and in Specific local ports, type the port number of SQL Server and click next.



  8. In the next window, select Allow the connection and click Next.



  9. In the next window, for security reasons, uncheck Public.



  10. In the next screen, give a name to the rule and click Finish.