Anaplan Connect: SQL Server JDBC Connection (No suitable driver found...)

Kyle204
Kyle204
in Integrations and extensibility

For the love of free internet points and karma, I am sharing my struggles with Anaplan Connect setting up a JDBC connection with SQL Server.  

 

Purpose: The below has been documented to assist with setting up a JDBC connection to a SQL Server database, the version of JDBC driver used will be dependant on the database version you are connecting to. The instruction does not go into the detail of using certificates for authentication.

This is not intended to be a how to use Anaplan connect but rather how-to setup the JDBC connection for SQL Server – not obvious for non-technical resources.

 

Pre Conditions:

  • Database: SQL Server 2016
  • Anaplan connect installed, managed, and scheduled on a Management Virtual Machine
  • Service account setup in Active Directory which allows for access to the network share folders and RDP to virtual machine where Anaplan Connect is installed. Service account also has read only access to the SQL Server database through Windows Authentication (Integrated Security).

 

Setup on virtual machine:

Kyle204_0-1608268611898.png

HOT TIP: if you do setup the above environmental variable you will likely encounter the error “No suitable driver found…”

Kyle204_1-1608268611902.png

 

Example Process:

 

  • ExampleCompany.bat

 

@Echooff

rem For Windows OS : This example explains the usage of basic Authentication with Anaplan Connect.

rem It also loads a source text file and runs an Anaplan import into a module.

rem For details of how to configure this script visit https://help.anaplan.com/anapedia/Content/Downloads/DL_Downloads.html

 

set AnaplanUser="<insertuserid>:"

set WorkspaceId="<insertworkspaceid>"

set ModelId="<insertmodelid>"

set ServiceUrl="https://api.anaplan.com"

set AuthUrl="https://auth.anaplan.com"

set FileName="ExampleCompany.csv"

rem set FilePath="C:\ExampleCompany.csv" -put %FilePath%

set ImportName="ExampleCompany"

set DumpName="C:\error\errorlog.txt"

set Chunksize=1

set JDBCProperties="C:\scripts\ExampleCompany.properties"

 

set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -chunksize %Chunksize% -file %FileName% -jdbcproperties %JDBCProperties%  -import %ImportName% -execute -output %DumpName%

 

rem *** End of settings - Do not edit below this line ***

 

setlocal enableextensions enabledelayedexpansion || exit /b 1

cd %~dp0

if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%

set Command=C:\anaplan-connect\AnaplanClient.bat %Credentials%  %Operation%

@Echo%Command%

cmd /c %Command%

pause

 

  • ExampleCompany.properties

 

# JDBC Connection string (Oracle, Mysql, H2, etc.)

 

jdbc.connect.url=jdbc:sqlserver://<insertdatabaseserver>;DatabaseName=<insertdatabaseschema>;integratedSecurity=true

 

# JDBC login username

#jdbc.username=xxxxx

 

#JDBC login password

#jdbc.password=xxxxx

 

# (Optional) JDBC Fetch size, might not be applicable for all JDBC driver flavors.

jdbc.fetch.size=10

 

# Boolean flag, needs to be true if provided {jdbc.query} is a

# stored-procedure/function call.

# Defaults to false if no value provided, or invalid.

jdbc.isStoredProcedure=false

 

# SQL query could be Select or a Stored-Procedure/Function call. In case of latter,

# require {jdbc.isStoredProcedure} above set to "true".

# NOTE: SQL query needs to be parametrized with ? placeholders and values for

# placeholders needs to be provided below in {jdbc.params}.

#jdbc.query=SELECT * FROM Anaplan where col1 = ? and col2 = ?

 

#Note the below SQL Query does not connect to any tables/views or stored procedures but just test that the JDBC connection is working and the database privileges are setup correctly. Query can be reused on any database.

 

jdbc.query=select '1' as code, ‘TESTJDBCCOMPANY' as name

 

# JDBC parameters for parametrized SQL query in {jdbc.query} above.

# NOTE: Number of parameters must equal to the number of parameters provided in {jdbc.query} above.

#jdbc.params=1234,ABC

Tagged:

Answers

  • whitby
    whitby

    Thanks for this @Kyle204 - this post was very helpful on my JDBC 'journey'...

     

    Did a similar thing, but connecting Anaplan to Snowflake.

     

    A couple of things that caused issues for me (for anyone else battling through the same error):

    • Putting quotation marks around the JDBC connection url (as they do in the Anaplan connect technical documentation) gave me the same failure (no Driver found): 
       

      Doesn't work: jdbc.connect.url= "jdbc:mysql://localhost:3306/apcustomer”

      Works: jdbc.connect.url= jdbc:mysql://localhost:3306/apcustomer
    • Also - be careful not to have a / on the end of the URL. The Snowflake JDBC documentation has:
      jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>

      Ignoring the connection parameters means removing "/?<connection_params>" (initially I left in the / which caused the "No Driver" error as well)

Categories