How to connect to an MS SQL Database

There can be two primary reasons for you to make a connection to an MS SQL database with GenRocket:

  • Reason 1: To retrieve data from the database while generating data using GenRocket runtime using generators like ListQueryGen, QueryBeforeLoop, etc.

  • Reason 2: To populate tables in the database using receivers like GenericSQLInsertReceiver, MySQLInsertReceiver, etc.


To use these Generators and Receivers, GenRocket leverages JDBC.


In This Article


What is JDBC?

JDBC stands for Java™ database connectivity (JDBC) and is the JavaSoft specification of a standard application programming interface (API) that allows Java programs to access database management systems. GenRocket can connect to any database that supports JDBC. 


Connection Steps 

Step 1 - Create a JDBC Configuration Properties File

For GenRocket to connect to an MS SQL database via JDBC, you must create a config.properties file. Any application can connect to an MS SQL database via JDBC by reading a JDBC properties file that contains the following information:

  • driver - Defines the name of the specific database driver to use to access the given database.  The driver file must be placed in your local computer's $GEN_ROCKET_HOME/lib folder. For MS SQL, the value should be com.microsoft.sqlserver.jdbc.SQLServerDriver.
  • user - Defines the Username used to access the database.
  • password - Defines the password used to access the database.
  • url - Defines the specific URL to access the database. See more of this information below. 
  • batchCount - Defines the number of SQL statements that are batched together and sent to the database for execution.  This property is only mandatory for GenRocket Receivers.
driver=<JDBCDriverClassName>
user=<userToConnectToDatabase>
password=<passwordToConnectToDatabase>
url= jdbc:sqlserver://<serverName>:<portNumber>;<property=value>
batchCount=1000


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 - Enter a valid value for the resource.jdbc.config Organization Resource in GenRocket

The resource.jdbc.config Organization Resource specifies the location of the JDBC config.property file on the logged in user's local machine. For more information about Organization Resources, click here.

This resource value should contain the full directory path (e.g., /home/user/dev)


Linux/Mac OSX Example


Windows Example


Step 3 - Update your Profile in the .genrocket Folder

Each user has their own Profile that is stored in the .genrocket folder on their local machine. You will need to download and replace your profile (in ~/.genrocket folder) any time the following changes are made: 

  • Resource value(s) are updated
  • A new resource is added
  • A resource is removed


GenRocket will read this resource value from your profile on your system to make a connection with the database. 


To see the complete steps for each Operating System, please see the article below: 


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

For system requirements and detailed information about each of the drivers listed above, please go to https://docs.microsoft.com/en-us/sql/connect/jdbc/system-requirements-for-the-jdbc-driver.


Step 5 - 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.


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.