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

[Ken D'Agostino]

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

[Bob Swift]

You need non-blank dbUser and dbPassword parameters since we are checking that. Presumably they will be ignored by your integrated security specification. Don't have experience there.  

[Dennis Roche]

How can I leverage AD authentication with this add-on then? Or do I have to setup a database user account in order to resolve this issue?

Thanks, 

[Dennis Roche]

So is there a way to use a user's active directory account to execute the query? This would be the logged in user's account credentials. Ex. I login to our stand alone instance of confluence as DOMAIN\jdoe and the user account DOMAIN\jdoe has access to execute the SQL query.

Is this possible or do I need to setup a database user account that has access to the query?

[Bob Swift]

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.

[Dennis Roche]

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]

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