SQL Integrated Security in Sense Server based on Local User

[stevedark]

Hi,

In Qlik Sense all database connections are made on the server, and therefore the account used for Integrated Security to SQL Server is that of the service account on the server.  This makes sense.

However, when you create a connection using that service account it is possible to read data from all databases that this service account has access to, by prefixing the table name with the database name.

What we would like to do is have Connection A set up with rights to Database A, and Connection B with rights to Database B (on the same server) and then use the Sense security model to allocate the right connections to the right users.  This would include giving them the right to build load scripts using that connection and reloading in their own browser.

I can see that this is simple if we use SQL Server logins, as we can create many users and each can have access to different databases.  Each Sense Connection would then use a different login and all is good.

However, we want to use Integrated Security as this is the more secure route.

The ideal would be that the connection is set up to use Integrated Security, and then the credentials are passed through when Load Data is clicked, so that the credentials of the end user are used to make the connection.

Does anyone have any thoughts on how we might be able to achieve what we are after, without using SQL Server logins?

Thanks in advance.

Steve

[Gysbert_Wassenaar]

I'm afraid you're going to need expert SQL Server help on this. I don't know of any easy way. Perhaps something involving a logon trigger and a context switch or the use of application roles. But I couldn't find an answer telling me how to put things together.

[stevedark]

Thanks Gysbert,

In a way, I'm pleased I wasn't missing something obvious (I would have felt a bit dim).  We have discussed some ways of avoiding the issue, as it felt like a solution may not present itself.

My preference is for using SQL Server logins, even though I usually try to avoid them, so that different connections are limited to different databases.

Steve

[garystrader · Accepted solution]

I don't know that this is a SQL Server issue. You can limit access to databases with network (service account) logins in exactly the same way you limit access to SQL logins.  There's really no difference in SQL Server.  Once a login is authenticated into SQL Server, that is the security context that it's in, it's not aware of anything else such as which user in Qlik Sense initiated the request.  It sounds like what you're asking for is for pass-through authentication to work in Qlik Sense connections.

[stevedark]

Hi Gary,

Indeed.  We know how to give different AD accounts different access in SQL Server.  What we are unable to do is get Sense to use different credentials in different places (i.e. Reload Task / Manual Load Data).

Pass through security would be ideal, as it would mean that even if a user was granted rights to a Connection in Sense they would still be limited by their own rights.  However, I can see that delegated security (so you don't have to grant to individual users) would be right in some circumstances too.

The ability to specify an AD account and password to masquerade with at the time of setting up the Connection would work, but I suppose if we were doing that then we just as well be doing SQL Server security.

All good fun!

Steve

[#6]

Hi Steve,

I have a question regarding SQL Security and Reload Task / Manual Load Data. You seem like you know a bit or two about it.

When nobody is logged on to the QMC or the Hub, SQL reload tasks fails. I figure the Local System doesn't have proper security enable on the MSSQL server. I tried lots of things, adding logins for the root service account, NT AUTHORIY/NETWORK SERIVCE, Domain/ServerName$ but nothing seems to work. I am using SSPI in the dataconnection.

Any ideas?

The setup is a EC2 instance on Amazon Web Services that is added to the AD of our local network where the SQL servers are. We use a EC2 instance with OpenVPN for a secure connections. The root service account is a AD administrator, same as I installed Qlik Sense Enterprice with, as recommended by our Qlik Sense consulting firm.

Regards

Karl

[stevedark]

I would change the services to work off a named user account rather than a system user account.  This account will then be passed on to the SQL Server and be used for authentication.

You will need to give the account log on as a service right, and a bunch of other permissions (I would make it a Local Administrator if you can).

As it could potentially impact on other workings in Sense I would ensure you test the change either when users are not on, or on a test server.

Steve

[#8]

Thanks for the suggestions, I'll try them out.

[#9]

That didn't work for me. I think it's because I use an EC2 instance on AWS.

I tried to use a autologin that waits for the network adapter to kick in and 2 additional minutes after that to login. But that didn't work either so my solutions is that the server that starts the AWS instance trigger a remote desktop session when the server answers to ping.

That seems to do the trick for me. More a EC2 issue than a Qlik Sense issue.

[garystrader]

I just noticed this feature for ODBC connections that appears to have been added in v3.0.  I haven't tested it yet, but I wonder if it's the "pass through" authentication you were talking about.

https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/DataSource/ODBC.htm

Single Sign-On

You can enable Single Sign-On (SSO) when connecting to SAP HANA or SQL Server data sources. See: Configuring SAP HANA for SAML Single Sign-On with Qlik Sense If this option is not selected, Engine service user credentials are used, unless you specify credentials inUsername and Password. If this option is selected, Engine service user or Username / Password credentials are used to do a Windowslogon, followed by a subsequent logon to SAML (SAP HANA) or Windows (SQL Server) using current user credentials.