SDM: Grloader not connecting to SQL Server database

Document ID : KB000116041
Last Modified Date : 27/09/2018
Show Technical Document Details
Issue:
Users may be attempting to use grloader to connect to a backend SQL Server database, using a command such as this

grloader -u user1 -p XXXX -s http://SERVER1:8080 -E -lttwa -cfg sqlServer.cfg -dbpswd XXXX -dbstmt "<sql statement>" -e "errorjbdcl.xml" 

An error may present as follows:

01/01 08:00:00.000 INFO GRLoader 494 GRLoader Started - load from jdbc database(jdbc:sqlserver://SQLSERVER1:1433;databaseName=DATABASE1; ) - to TWA 08/28 08:03:20.368 INFO GRLoader 510 =============================================================================== 08/28 08:03:21.790 INFO grDataMgr 200 Multi-tenancy is not enabled 08/28 08:03:22.119 INFO GRLoader 304 grJDBCrdr1500 Unable to access database url(jdbc:sqlserver://SQLSERVER1:1433;databaseName=DATABASE1;) userid(TESTUSER) reason(Login failed for user 'TESTUSER'. ClientConnectionId:4c634cc0-09a1-45ae-bb68-1f7fc9b77405) 08/28 08:03:22.119 INFO GRLoader 304 08/28 08:03:22.119 INFO GRLoader 304 Results: 08/28 08:03:22.119 INFO GRLoader 304 Read Skipped Inserts Updates Errors Warnings 08/28 08:03:22.119 INFO GRLoader 304 TWA CI 0 0 0 0 0 0 08/28 08:03:22.119 INFO GRLoader 304 TWA Rel 0 0 0 0 0 0 08/28 08:03:22.119 INFO GRLoader 304 08/28 08:03:22.119 INFO GRLoader 857 =============================================================================== 08/28 08:03:22.119 INFO GRLoader 304 GRLoader completed, no data processed. 08/28 08:03:22.119 INFO GRLoader 859 =============================================================================== 


GRLoader 304 grJDBCrdr1500 Unable to access database url(jdbc:sqlserver://SQLSERVER1:1433;databaseName=DATABASE1;) userid(TESTUSER) reason(Login failed for user 'TESTUSER'. ClientConnectionId:4c634cc0-09a1-45ae-bb68-1f7fc9b77405) 
Environment:
Service Desk 14.1, 17.1, SQL Server 2012 and higher
Cause:
Trying to connect to the SQL database using SQL Server authentication, but NT Authority was what should be used.
Resolution:
The URL in the grloader config file (sqlServer.cfg in the above example) should include this line:

grloader.jdbc.url=jdbc:sqlserver://SQLSERVER1;databaseName=DATABASE1;integratedSecurity=true; 

One then needs to add the SQL Server JDBC driver to the SDM Server and also include the sqljdbc_auth.dll in the bin folder of the Service Desk Manager install location. 

This should correct any problems connecting to the given SQL Server via JDBC with a user over NT Authority.

 
Additional Information:
For more information on the GRLoader config file:
https://docops.ca.com/ca-service-management/17-1/en/reference/ca-service-desk-manager-reference-commands/cmdb-technical-reference/general-resource-loader-grloader/grloader-configuration-file

For additional information on connecting GRloader to a source database via JDBC:
https://docops.ca.com/ca-service-management/17-1/en/reference/ca-service-desk-manager-reference-commands/cmdb-technical-reference/general-resource-loader-grloader/input-options#InputOptions-JDBCDatabaseInputOptions