9 Replies Latest reply: Oct 27, 2016 5:32 PM by Gary Strader RSS

    SQL Integrated Security in Sense Server based on Local User

    Steve Dark

      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

        • Re: SQL Integrated Security in Sense Server based on Local User
          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.

            • Re: SQL Integrated Security in Sense Server based on Local User
              Steve Dark

              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

                • Re: SQL Integrated Security in Sense Server based on Local User
                  Gary Strader

                  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.

                    • Re: SQL Integrated Security in Sense Server based on Local User
                      Steve Dark

                      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