It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Ken D'Agostino Jun 08, 2016

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

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 Jun 14, 2016

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 Jun 14, 2016

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.

 

Dennis Roche Jun 15, 2016

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Confluence

6 Awesome Ways to Apply Trello, JIRA and Confluence to your Project

I attended  Atlassian Summit 2019  and learned a lot from the presenters, attendees and knowledgeable Atlassian product managers. The presentations I attended focused on applying Agile, pla...

2,109 views 11 27
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you