How to connect to a MS SQL Database
There can be two primary reasons for you to make a connection with MS SQL database with GenRocket:
1. To retrieve data from the database while generating data using GenRocket runtime using generators like ListQueryGen, QueryBeforeLoop etc.
2. To populate tables in the database using receivers like GenericSQLInsertReceiver, MySQLInsertReceiver etc.
To use these Generators and Receivers, GenRocket leverages JDBC.
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.
Step 1: Create a JDBC Configuration Properties File
For GenRocket to connect to an MS SQL database via JDBC you will need to create a config.properties file. Any application can connect a 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 actual driver file must be placed in the $GEN_ROCKET_HOME/lib folder on your local computer. 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 in 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>\<instanceName>:<portNumber>;<property=value> batchCount=1000
As an example, to connect to a SQL Server instance named SQL2016 that is in a server named Host1 and is configure 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\\SQL2016:2455 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.
Step 2: Next step is to add a new resource named resource.jdbc.config in GenRocket Web
The resource.jdbc.config resource in this image points to the dev folder.
- Set the value of resource variable to the config file name with the full file path.
- For example, say you have added config.properties at location /home/user/Desktop/. Then the resource value will be /home/user/Desktop/config.properties.
- Make sure to update your Profile (in ~/.genrocket folder) on your system after adding the new resource with its value. GenRocket will read this resource value from your profile on your system to make a connection with the database.
Step 3: Download and place the JDBC connector Jar file to the $GENROCKET_HOME/lib/ folder
- Download and place an Microsoft JDBC Connector for SQL Server in your genrocket-3.5.12/lib folder.
- It is OK if your GenRocket version doesn't match the one above -- still just place the Microsoft Connector connector in the lib directory
- Microsoft provides the JDBC driver for free. You can download the one that works for your version of SQL Server from one of these links:
- Microsoft JDBC Driver 6.2 for SQL Server
- Microsoft JDBC Driver 6.0 for SQL Server
- Microsoft JDBC Driver 4.2 for SQL Server
- Microsoft JDBC Driver 4.1 for SQL Server
- Microsoft JDBC Driver 4.0 for SQL Server
For information about 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 4: Reference the JDBC Resource in your Generators or Receivers
When you create a Scenario that has a Generator or Receiver that references your jdbc.config resource it will connect directly to your database.
MS SQL JDBC Trouble Shooting Connection Issues
The most common issues when trying to connect to a 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 troubleshoot steps, ensure that the login exist and has appropriate privileges in the database you want to connect. If the user does not exist, create it and assign an appropriate password. To trouble shoot issues mentioned above follow the next steps.
Checking User Privileges
- Connect to the database instance with SQL Server Management Studio with a user with sysadmin or security admin role.
- In the right panel in management studio, go to Security, expand Logins, and check that the user exist.
- If the user is disabled, double click the user name in the object explorer panel. In the login properties window go to Status and from there enable the user (Do not close this windows yet).
- In the Login Properties window go to user mapping. Select the appropriate database and check the privileges that the user has in that database. For instance if you want that the user has 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 want to give the user write privileges.
- In the Login Properties window go to General and take note of the name of the default database assigned to that user.
- Ensure that the user has the appropriate privileges and grants to the default database that has been assigned to that user. 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).
- Now, after validating all this, click 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 not a 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 2 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 it is not mapped to that login.
To fix this issue, follow the steps below.
- In the left panel, expand security and right click in Logins then go to New and then Login.
- In the Login – New Window, in the Login Name, type the name of the user. This must be the same as the user name in the database (for this example it must be cvidal5). Then select SQL Server Authentication and type the password of the user. Ensure that User must change password at next login and enforce password expiration is not checked.
- Click Ok. This will create the login.
- 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 logins does exist just execute the step 4 from previous process, because in this situation, the login exist and you just need to link the user with the login.
Checking SQL Services and Protocols
Ensure that SQL Server services are started and that protocols are enable and ensure that you are connecting to the appropriate port and instance. If you do not know the database instance, use the IP address instead and the port number.
- Open SQL Server Configuration Manager.
Check the instance name to ensure you are connecting to the appropriate instance name in your connection string. 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 2 instances, one named SECONDARY and one named SQL2016. If you see an instance named MSSQLSERVER it means that that is the default SQL Server instance name so it is not mandatory to specify the instance name in the connection string. If you are using IP address in your connection string, you do not need to specify the instance name, only the port number.
Checking SQL Services and Protocols
- If you see that the service state of the database instance is not Running state it means that you need to start the service. Just right click the service name and then click start.
- Now go to SQL Server Network Configuration expand it and select the protocols for the instance you are trying to connect.
- Ensure that the TCP/IP protocol is enabled. If it is disabled, right click and select enable.
- Once the protocol is enabled, right click on it and select properties.
- In the TCP/IP
properties go to IP Addresses tab and ensure that the port number configure 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
- If telnet is configured in the host open a command line and perform a telnet to the op address and port of the database instance that you are trying to connect.
If connection failed it means that the port number is being blocked by the firewall. 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.
- Open Windows firewall. Open control panel and go to System and Security. Select Windows Firewall and in the left pane click on Advance settings.
- 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 next steps.
- In the New Inbound Rule Wizard window. Select Port and click next.
- In the next windows select TCP and in Specific local ports type the port number of SQL Server and click next.
- In the next window select Allow the connection and click next
- In the next window, for security reasons uncheck public.
- In the next screen give a name to the rule and click Finish