Bob Swift Error rendering macro 'sql-query' Incomplete configuration for data source profile

Question

I am trying to configure the Bob Swift SQL query (v8.1.0) add on in Confluence (v5.9.11) and after i configure the Data Source Profile the Add-on displays the following message. Looking for help as the confluence.log file does not present

image2016-6-9 9:38:27.png

I have tried the following configurations for the Data Source Profiles all resulting in the same issue. I am wanting to use AD authentication so I am not using dbUser and dbPassword fields but the IntegratedSecurity=true value

dbDriver=net.sourceforge.jtds.jdbc.Driver| dbUrl=jdbc:jtds:sqlserver://SQLSERVERNAME:1433/TC| integratedSecurity=True| dbJar=c:\Program Files\Atlassian\Confluence\lib\jtds-1.2.2.jar

dbDriver=net.sourceforge.jtds.jdbc.Driver| dbUrl=jdbc:jtds:sqlserver://SQLSERVERNAME:1433/TC| integratedSecurity=True| dbJar="c:\Program Files\Atlassian\Confluence\lib\jtds-1.2.2.jar"

dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver | dbUrl=jdbc:sqlserver://SQLSERVERNAME:2433/SysAdminData|integratedSecurity=true|dbJar=c:\Program Files\Atlassian\Confluence\lib\sqljdbc4.jar

image2016-6-9 9:40:47.png

image2016-6-9 9:35:14.png

Here is the entry i get from the atlassian-confluence.log file

2016-06-09 01:22:19,897 WARN [http-nio-8090-exec-1] [xhtml.view.macro.ViewMacroMarshaller] marshalInternal Exception executing macro: sql-query, with message: Incomplete configuration for data source profile: DENNIS_TEMPLATE_SQL. Notify your administrator.
 -- referer: http://CONFLUENCESERVER:8090/pages/editpage.action?pageId=13041671 | url: /display/IT/Test+SQL | userName: USERNAME | action: viewpage | page: 13041671

image2016-6-9 9:40:47.png

image2016-6-9 9:35:14.png

image2016-6-9 9:38:27.png

Bob Swift · Answer

No, that is no possible. We have no access to the user's password. The only thing we have support for is running SQL setup actions and this has been used to do something similar - see How to implement role based security for SQL queries

Dennis Roche · Answer

So I was able to get the AD Authentication working with the following string. The caveat is I need to specify the user name and password int he string (yuck). I would ultimately like to leverage the "logged in user" as the "authenticated user". Is this possible? I tried specifying the dbUser/dbPassword as blank, astricks, not having those in there, all of which broke the ability to query. is this a possibility?

dbDriver=net.sourceforge.jtds.jdbc.Driver | dbUser={AD_USERID}|dbPassword={AD_PASSWORD}|dbUrl=jdbc:jtds:sqlserver://{SERVERNAME}:1433/{DATABASE};domain={DOMAIN};useNTLMv2=true | dbJar=C:\Program Files\Atlassian\Confluence\lib\jtds-1.3.1.jar

Bob Swift · Answer

SQL users a shared access configuration (profile or datasource). Normally, it is intended to have more capability than the view user may have. I am not well versed on integratedSecurity and how that works in general but you can certainly try and see what happens - you just need to provide non-blank dummy user and password to get through to that point. It is not clear to me whether that would use the server login or the magically get associated with the logged in user. Certainly we don't have any specific support if something is required on our part.

In terms of securing SQL access with a shared profile, this is normally done via Macro Security for Confluence.