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

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

1 answer

This widget could not be displayed.

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.  

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, 

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?

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.

 

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

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

468 views 6 6
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you