5 Replies Latest reply on Aug 23, 2012 10:22 AM by Russell Christopher

    SQL authentication issues

    Eric Wilson

      I have a CRM server and an SQL server on the same AD domain. The SQL database's views restrict access to certain records depending on the AD user that is logged in.

       

      My Tableau Desktop is running on my workstation (NOT on any AD domain) and my Tableau Server is running on a 3rd server (also NOT on any AD domain).

       

      I need to authenticate Tableau Desktop and Tableau Server to SQL so I can start building reports. If I create a plain old SQL user and grant the DB access I need, unfortunately I still cannot access the data in the views, which are hard coded to restrict access based on which AD user is logged in.

       

      There is a workaround from the CRM provider to set the user context prior to querying SQL, however this is not possible on Tableau because Tableau only seems to allow "SELECT".

       

      I'm at an impass. I can't use Tableau unless I can get it to access these views. Are there any other solutions I haven't thought of?

       

      Thanks.

        • 1. Re: SQL authentication issues
          Russell Christopher

          Hey Eric -

           

          SQL Server doesn't allow you to map a standard SQL login to to an existing AD user in a SQL database via ALTER USER, so you're really fighting the way SQL Server is designed to work:

           

          (from SQL Server BOL, ALTER USER topic) :

           

          The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause. Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a SQL Server login.

           

          Since SQL Server explicitly disallows what you want to do, I'd think you're going to need to go back to the drawing board and do one of the following:

           

          • Get new standard SQL Server logins/users added to the database and clone the existing permission sets to them
          • Get Tableau into AD
          • Create some sort of staging database with cuts of the original data, and then re-secure that data using a Tableau User filters.

           

          Good luck!

          • 2. Re: SQL authentication issues
            Eric Wilson

            WITH LOGIN is not the clause I would need to use.  Here is a post that explains exactly what I'm trying to accomplish:

             

            http://crmland.blogspot.com/2011/02/accessing-dynamics-crm-filtered-views.html

             

            And here's a sample query (the first 3 lines would need to precede the SELECT statement):

             

            DECLARE  @uid uniqueidentifier

            SET @uid = convert(uniqueidentifier, 'user_guid_goes_here')

            SET CONTEXT_INFO @uid

             

            SELECT TOP 1000 *

              FROM [ORGDB_MSCRM].[dbo].[FilteredContact]

            • 3. Re: SQL authentication issues
              Russell Christopher

              You're not going to be able to change context information via Tableau using the TSQL before the SELECT. We only fire SELECT statements, really (that's a bit of an overstatement, but whatever).

               

              Perhaps you can encapsulate all this logic in a SQL Table Valued Function or in a pinch, a stored procedure? Then you might be able to get this to work.

              • 4. Re: SQL authentication issues
                Eric Wilson

                I'm not an SQL expert, so it would help if you could clarify a little bit...

                 

                Are you saying that I can have Tableau run a stored procedure, and that stored procedure can include these SQL clauses and SELECT the view?

                • 5. Re: SQL authentication issues
                  Russell Christopher

                  Sure thing.

                   

                  What we're talking about here is fairly advanced - so you're going to need your SQL Server Administrator to help you.

                   

                  In SQL Server, Stored Procedures and Table Valued Functions are ways that you can encapsulate multiple commands.

                   

                  For example, one might write a Stored Procedure that does something trivial, like execute a single SELECT statement. Or, that Stored Procedure might be hundreds of lines long, with very complex control-of-flow statements that run different SELECT statements based on the output of other commands.

                   

                  Since Tableau doesn't allow for firing "initialization SQL statements" (those first 3 of 4 lines of code), you could get someone to put all four lines of SQL into a SQL Stored Procedure.

                   

                  Then, YOU could call the stored procedure from Tableau using a "sneaky" technique. You'd use Tableau's ability to fire custom SQL to fire a statement which calls the stored procedure:

                   

                  OPENQUERY([LINKSERVER],'SET FMTONLY ON; exec database.user.my_stored_proc ''user_guid_goes_here')

                   

                  http://stackoverflow.com/questions/4159465/execute-stored-proc-with-openquery

                   

                  Why is this so complex? Because you're trying to make Tableau do something it's not designed to do

                   

                  Edit: Note --  I've never tried to do exactly what you're doing myself. But it should be possible.

                   

                  Message was edited by: Russell Christopher