single signon for a datasource

[dougp]

Cognos Analytics 11.1.7FP5 (or 11.2.3)
Windows Server 2012 (or 2016)
Windows 10
SQL Server 2016
Active Directory

There is an interest to stop using SQL Server logins (username and password) for signons in our Cognos environment.  Having the user log in to the database directly using their Windows credentials is preferred.  I already have single signon (SSO) configured so that users who have already authenticated with Windows have a seamless path into Cognos.  Now I want that same functionality when connecting to data.

How can I configure Cognos so that the users' credentials (or a token, or...?) are used when the user runs a report and requests data from the database?

I thought I had investigated this before.  I'm not finding questions about this on this site.  And I think it was before the IBM Support site changed, so I can't get to any old case that I may have had with them.  I vaguely recall that SSO for Cognos and SSO for data sources were not compatible.  In other words, SSO for data sources didn't work if SSO was configured on the gateway.  It didn't make any sense, but it seems I couldn't get around it.  But maybe I'm remembering wrong.

I did find https://www.ibm.com/docs/en/cognos-analytics/11.0.0?topic=co-configure-jdbc-data-source-connections-single-sign-using-kerberos, but I don't know all of the secrets (KDC name, etc.) to put into the krb5.conf file.  I'm hoping to get one of my server folks helping with this soon.  Is this the correct document?  What else will I need?

[cognostechie]

I don't understand why would you want to do this? Signing on to Cognos is one this which requires every user to have valid credentials and SSO for Cognos means the user will get thru without typing in his/her credentials. Sign-on for the DB is completely another issue which does not require SSO at all. It's the sign-on embedded in the Data Source which is used to connect to SQL Server regardless of which user is signed-on to Cognos. That way only one account is required for SQL Server. By trying to do what you want to do , every user will need to have his/her account configured in SQL Server.

[dougp]

Correct.  They are totally separate issues.  I was just stating that I remember (possibly incorrectly) that my previous attempt uncovered an incompatibility between the two.  It seems I could have SSO for data sources or SSO for the Cognos gateway, but not both.  That was also likely Cognos 10.2, so things may have improved.

every user will need to have his/her account configured in SQL Server

Correct.  My managers desire to configure data security on the database server and not rely on the security configuration in Cognos.  In other words, they want the security defined per person, not per application.

We use Active Directory.  Data owners already manage AD security group membership for groups that control what reports they can see and groups that control what data they can access.  So this effort shouldn't require much work.  We'll just need to add a handful of groups to the database server and grant them appropriate permissions on the database server and databases.  Most of this is already done because some users use Excel or other tools to access data.  All that would change is the group membership will likely grow.

Currently a group has specific permissions or capabilities in Cognos.  So anyone with access to something in Cognos can get data using the SQL login that Cognos uses for the data source.  This change would enable tighter security, while reducing restrictions regarding which tools are used to access data, and reducing workload on IT because business areas manage their own groups will handle more of the security configuration workload.  Also, with no more SQL logins, we will no longer need to maintain a list of non-expiring username/password combinations and share them among team members (bringing us more in line with our current security standards).  This change would enable us to loosen security around Cognos a bit because the real data security would be on the database.  A user may have access to a report, but not be able to run it because they don't have permission to see the data.  (I haven't decided if that's a pro or a con, but if it's properly managed -- no problem.)  It also eliminates the need to direct a user to a specific tool for data access.  With permissions set on the database server and database, users can use Cognos, Tableau, Power BI, Excel, or whatever to access the data.  Data curation, a semantic layer, etc. are still important and I will still be promoting Cognos, but it will not be a requirement if a user has needs that don't fit that tool.

As I think through this more, I'm not certain all of the data owners would agree with this new approach.  I think some of them appreciate that users are forced to use Cognos.  But while I'm considering and discussing that, any help regarding additional information I'll need to get users to access data as themselves (all the way to the data) through Cognos without bothering them with additional login prompts is appreciated.

[cognostechie]

So anyone with access to something in Cognos can get data using the SQL login that Cognos uses for the data source. 

Only an admin is supposed to have the password for the SQL login used in the Data Source so how would a user be able to use that sign-on?

For the methodology, does SQL Server implement row level security or is that not a requirement in your environment? We have Flash reports which are accessed by 250+ users and one report works for all of them because it has row level security using Cognos macros. If access to tables are secured in the DB using individual accounts, how would the report work for all of them?

Something to think of. If there is no such requirement and the requirements are simple then that methodology might work.

[dougp]

Only an admin is supposed to have the password for the SQL login

I understand, but to ensure system problems can be handled when the primary admin is unavailable, those passwords (and other information) must be shared somehow among 3 people.  Plus, if I want to keep the SQL login config, I'd really need to have those passwords expire on a schedule.  But I don't want to spend half of my time updating passwords on the data source signons in Cognos.  So, to meet security requirements, it's better to not have Cognos use passwords at all.
I try not to question the orders of my cybersecurity folks.  Much of it seems to be applied broadly when specific circumstances should weigh more heavily into the decision.  Or maybe I don't see enough breaches or understand the true nature of the attack vectors that are or can be used.
But on the whole, this one isn't nearly the most confusing from a policy/logic perspective.

I have no requirement for row level security.

Sounds like overall I'm on the correct technical path to implement this (if it's actually needed and preferred).  I'll stumble through it and take good notes regarding what worked and what didn't so my predecessor has some hope of maintaining it.