JDBC WF activity to use integrated authentication instead of credentials?

[Vespertinus]

Hello SNow community,

we want to run a JDBC wf activity to run a SQL command on a internal MSSQL server (INSERT) over MID Server. For testing we added a JDBC data source and enabled "Use integrated authentication"-Flag, so no credentials are required. MID Server process is running in a system-users context that has sufficient rights on DB. So doing an import via JDBC data source worked fine and data was imported from db table.

Now we want to do same in a workflow with JDBC wf activity. Unfortunately wf activity requires credentials to be provided. How to use integrated authentication instead of credentials for JDBC WF activity?

Thx and best regards

Vesp

[Paul35]

Hi Vesp,

Stumbled across this when I was troubleshooting the same issue so forgive the very late response.

Under the assumption that you are able to successfully connect using "Integrated authentication" on your data source. You can make this work within workflow/orchestration activity by modifying the "Connection URL" in the "JDBC Connection" record to include the following: 

integratedSecurity=true

This tells the instance to use integrated authentication when making the JDBC connection.

There are 2 ways in which to modify the Connection URL to include this:

1) Change the "Format" to "-- None --" and adding the Connection URL 

ie. jdbc:sqlserver://<Server>;selectMethod=cursor;databaseName=<Database name>;integratedSecurity=true

I believe the drawback is that you won't able to use "Test Connection" in the activity designer and have to use "Test Inputs".

OR

2) I ran a background script to modify the "connection_url" field in the record to include "integratedSecurity=true".

The reason why I suggest this roundabout way is because it still allows the "Test Connection" to be used. The only drawback I've encountered so far was that if you modify the JDBC Connection record, it may reset the connection URL and you will have to modify the field again.

Hope that helps!

Paul

[arordeep]

Hi Paul, This is certainly helpful, however, how to use this approach with WF activity as in WF activity, credential field is a mandatory field and you can’t leave it blank. In connection record, we could leave it blank but when using that connection within WF activity, credential field is a mandatory field. Regards, Deepak Arora

[jonbebb]

I tried the above but it tries to logon to the SQL database as the default server account where the mid server resides any ideas

[Paul35]

Hi Jonbebb,

Correct, when attempting integrated security to log into your SQL server it will use the credentials specified in the MID server service.

I would look at setting up the connection as a data source first. Once created and verified it is working as a data source, you should be able to take a look at the JDBC connection URL and use the same thing in your workflow/orchestration activity.

Hope that helps,

Paul