6 Replies Latest reply on Sep 22, 2016 1:33 AM by Abhilash Singh

    Scheduling Custom SQL extract refresh on Server

    Abhilash Singh

      Hi All,

       

      I am new to tableau server and was hoping someone can help me out here.

       

      I have a workbook (twbx) which has 3-4 extract connected to oracle and I have custom sql for all. When I connect to oracle database I use windows authentication, and this works.

       

      After I published this workbook on server, I wanted to schedule automatic refresh. I went with everyday 6 AM refresh. But in order to test, i thought I will run a full refresh right away to check everything.

       

      I got an error message saying 'sign in failed'. I then went to the data source tab of my workbook and tried to edit the connection. It already had the correct server name, I typed in my user name and password (windows password) but I got an error message saying invalid username/Password.

      I then tried the same user name and password on tableau desktop and got the same message. Windows authentication works but if I type my username and password, it doesn't.

       

      As my second try, I didn't enter my password while editing connection details on server. I choose the option 'prompt user for password if needed'. Now I get a different error, saying 'Oracle database error 12154: ORA-12154: TNS:could not resolve the connect identifier specified'. I googled this error but and tried a few solutions (setup environmental variables etc.), But no luck.

       

      Also I am not getting the impersonation options . So I think my credentials are not getting embedded when I publish (This is probably because of server setting, I am not the admin. But I will check this and update asap)

       

      What I want to ask is what are the exact steps that one should follow in order to publish and automate extract refresh of a oracle connected workbook?

       

      Thanks in Advance!

      Abhilash

        • 1. Re: Scheduling Custom SQL extract refresh on Server
          Dmitry Chirkov

          What authentication type did you choose when you published your workbook?

           

          This dialog:

          • 2. Re: Scheduling Custom SQL extract refresh on Server
            Abhilash Singh

            Hi Dmitry,

            Thanks for your response.

            I only get 'run server as account' option when I publish. (Is this because I connect tableau desktop to oracle using windows authentication? and since I haven't entered a username and password, I don't get an option to embed it)

             

            But even if I select run server as account, what I am trying to do is after the publish, open the workbook, go to data source and then try to do a manual full refresh. Which is when I enter my user name and password. but it says ORA-12154: TNS:could not resolve the connect identifier specified.

             

            One of my friend connected to oracle using his username and password and he got the option you mentioned and was able to schedule and successfully refresh the server extract. So I think my problem is I should not use windows authentication. I will have to contact my IT to resolve this.

             

            Let me know if you have some other reason as to why this is not working.

            • 3. Re: Scheduling Custom SQL extract refresh on Server
              Manideep Bhattacharyya

              Dear Abhilash - The problem is precisely "Windows Authentication". This works fine for your desktop but when it comes to refresh Tableau jobs through Server you will face issue. Now you have two solution for this .

               

              A. Don't use Windows Authentication to Login to your Database Server. Instead create a new connection and provide password. This will definitely work.

               

              PS: While publishing data source don't forget to choose the option "Embed Password".

               

              B. If you still want to use Windows Authentication then please login to Tableau Server Machine and from their connect to Tableau and Publish Data Source.

               

              Thanks,

              Manideep

              • 4. Re: Scheduling Custom SQL extract refresh on Server
                Abhilash Singh

                Thanks Manideep Bhattacharyya for the response.

                Yes, I have mailed my IT team here to enable the username and password login for me.

                 

                Just on point, In the solution B above, when you say login to server, connect to tableau and from their publish the data source, you meant after publishing the workbook, open the workbook, go to data source tab and from their edit the connection i.e. enter the username password so that tableau server can now connect right? or you meant something else?

                • 5. Re: Scheduling Custom SQL extract refresh on Server
                  Manideep Bhattacharyya

                  In Solution B - I was referring to login to the Tableau Server box (Obviously Windows Box). Now once you login to this windows box with your own ID, from this box you should have Oracle Client Installed to connect to your Oracle Data Base Server. You should also have to install Tableau Desktop software in the Server machine so that you can create new data sources and Publish from Server.

                   

                  Now You Open Tableau Desktop from your Windows Tableau Server Machine and connect to Oracle Database through Windows Authentication and then do an extract and then Publish and then you schedule your extract. This should work.

                   

                  Thanks,

                  Manideep

                  • 6. Re: Scheduling Custom SQL extract refresh on Server
                    Abhilash Singh

                    Hi all,

                    I think the problem was that tableau needs 'native service account' in order to connect to oracle. I cannot simply use my oracle username and password to authenticate connection for tableau server.

                    you can read more here: Service Accounts

                    Once you have a native service account username and password, server can connect to oracle and you can schedule automatic refreshes.