15 Replies Latest reply: Mar 26, 2015 2:56 AM by Igor Burobin RSS

    QV Server, WebTickets. Group binding does not work

    Igor Burobin

      Hi! I try to setup QV Server work with DMS + Configurable ODBC + WebTickets combination.

      QV Server  configured and everything fine but access with user groups not working...

       

      Here is my "user table" specification:

      CREATE TABLE [dbo].[tbl_A_qvUser](
          [entityid] [int] IDENTITY(1,1) NOT NULL,
          [name] [nvarchar](max) NOT NULL,
          [email] [nvarchar](max) NULL,
          [descr] [nvarchar](max) NULL,
          [password] [nvarchar](max) NOT NULL,
      CONSTRAINT [PK_tbl_A_qvUser] PRIMARY KEY CLUSTERED
      (
          [entityid] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
      

       

      Here is my "group table" specification:

      CREATE TABLE [dbo].[tbl_A_qvGroupUser](
          [groupid] [int] NOT NULL,
          [memberid] [int] NOT NULL
      ) ON [PRIMARY]
      

       

      The names of the groups wound up as users in the table tbl_A_qvUser. Table tbl_A_qvGroupUser contains only links between users and groups.

       

      ODBC Configuration

       

      I checked the User Management page. Users appear from an ODBC connection.

       

      Then I set up access to the document for the groups from tbl_A_qvUser.

      Then when i go to AccessPoint (Using a user from a group Аналитики) i don't find document moves database.

        • Re: QV Server, WebTickets. Group binding does not work
          Bill Britt

          Hi,

           

          To using web ticketing you will have to write your own authentication login page. You will have request the ticket from QVS for the user and then past that ticket back to QVS in an URL. Qlik does not do any authentication that is outside of Qlik.

           

          Bill

            • Re: QV Server, WebTickets. Group binding does not work
              Igor Burobin

              Thank You Bill!

              I did alternate the sign-in page AccessPoint and users are authenticated through it (With this there is no problem). Here is the source code.

              <%@ Page Language="C#" AutoEventWireup="true" %>
              <%@ Import Namespace="System.Net" %>
              <%@ Import Namespace="System.IO" %>
              <%@ Import Namespace="System.Data.SqlClient" %>
              
              
              <html>
                  <head>
                      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
                      <title>QlikView - Портал</title>
                      <link href='http://fonts.googleapis.com/css?family=Ubuntu&subset=cyrillic' rel='stylesheet' type='text/css' />
                      <link rel="shortcut icon" type="image/x-icon" href="favicon.png">
                      <link rel="stylesheet" href="css/global.css" type="text/css" media="screen">
                      <link rel="stylesheet" href="css/custom.css" type="text/css" media="screen">
                  </head>
                  <body>
                      <div id="frame">
                          <div id="nav_utility">
                          </div>
                          <div id="header">
                              <!-- <a href=""><img src="css/img/logo_hor.png" alt="QlikView" id="logo_main"></a> -->
                              <span class="docNum"></span>
                              <span class="lastUpdated"></span>
                          </div>
                          <div id="loginBoxOuter">
                          <div id="loginBox">
                              <form runat="Server" id="MainForm" style="font-family: Ubuntu">
                                  <span class="formTitle top">Имя пользователя:</span>
                                  <span class="formField"><asp:TextBox runat="server" id="txtUser"></asp:TextBox></span>
                                  <span class="formTitle">Пароль:</span>                   
                                  <span class="formField"><asp:TextBox runat="server" TextMode="Password" id="txtPassword"></asp:TextBox></span>
                                  <asp:Button ID="GO" runat="server" class="loginSubmitButton" onclick="GO_Button_Click" />
                              </form>
                          </div>
                          </div>
                      </div>
                      <div id="footer">
                      </div>
                
              </body>
              
              </html>
              
              <script language="c#" runat="server">
                     
                      //Firsly Set up a few properties
                      static string QlikViewServerURL = @"http://localhost/QVAJAXZFC/getwebticket.aspx";  // Адрес сервера Qlikview для запроса тикета
                      static string QlikViewServer = @"http://myserver.com/";                        // Адрес сервера Qlikview для перехода 
                      static string ticketinguser = "mylogin";                                           // Имя пользователя компьютера с правами администратора QlikView
                      static string ticketingpassword = "mypassword";                                      // Пароль пользователя
                      static string document = "";                                                        // Если значение пустое, то входим в AccessPoint. Иначе открываем документ
              
                      protected void Alert(string message)
                      {
                          Response.Write("<div class=\"error\">"+ message + "</div>");
                      }
                      protected void GO_Button_Click(object sender, EventArgs e)
                      {
              
                          string username = txtUser.Text;
                          string password = txtPassword.Text;
                          string groups = "";
              
                          // Проверка пользователя и пароля
                          bool loginOK = ValidateUser(username, password);
              
                          // Если проверка пользователя и пароля успешна, то пытаемся получить тикет
                          string ticket = "";
                          if (loginOK)
                          {
                              // Получаем тикет
                              ticket = getTicket(username, groups, ticketinguser, ticketingpassword); // add groups into the empty string if required
              
                              // Строим строку для переадресации используя тикет
                              string RedirectLink = "";
              
                              if (document.Length > 0)
                              {
                                  // Открытие отдельного документа
                                  RedirectLink = QlikViewServer + "/qvajaxzfc/authenticate.aspx?type=html&try=/qvajaxzfc/opendoc.htm?document=" + document + "&back=/LoginPage.htm&webticket=" + ticket;
                              }
                              else
                              {
                                  // Перенаправление в AccessPoint
                                  RedirectLink = QlikViewServer + "/qvajaxzfc/authenticate.aspx?type=html&try=/qlikview&back=/LoginPage.htm&webticket=" + ticket;
                              }
              
                              Response.Redirect(RedirectLink);
              
                          }
                          else           
                          {
                              Alert("Неправильный логин или пароль! Повторите ввод.");
                          }
                      }
                         
                      // Функция проверки пользователя по логину и паролю
                      // Можно в теле написать все что угодно, в данном случае функция проверяет логин и пароль по таблице в MS SQL
                      private bool ValidateUser(string User, string Pass)
                      {
                          using (SqlConnection qvUserGetConnection = new SqlConnection("user id=loginToSQL;" +
                                                     "password=passToSQL;server=localhost;" +
                                                     "Trusted_Connection=false;" +
                                                     "database=FKAC; " +
                                                     "connection timeout=30"))
                          {
                              try
                              {
                                  SqlDataReader myReader = null;
                                  qvUserGetConnection.Open();
              
                                  SqlCommand myCommand = new SqlCommand("SELECT TOP 1 entityid, name, email, descr, password FROM FKAC.dbo.tbl_A_qvUser WHERE (name = '" + User + "')" + "AND(password = '" + Pass + "')", qvUserGetConnection);
              
                                  myReader = myCommand.ExecuteReader();
                                  bool resultExists = false;
                                  while (myReader.Read())
                                  {
                                      resultExists = true;
                                  }
              
                                  return resultExists;
                              }
                              catch(Exception ex)
                              {
                                  Alert("Ошибка соединения с сервером!");
                                  return false;
                              }
                          }
                      }
                      
                      // Функция получения тикета     
                      private string getTicket(string user,string usergroups,string ticketinguser, string ticketingpassword)
                      {
              
                          StringBuilder groups = new StringBuilder();
                          if (!string.IsNullOrWhiteSpace(usergroups))
                          {
                              groups.Append("<GroupList>");
                              foreach (string group in usergroups.Split(new char[] { ',' }))
                              {
                                  groups.Append("<string>");
                                  groups.Append(group);
                                  groups.Append("</string>");
                              }
                              groups.Append("</GroupList>");
                              groups.Append("<GroupsIsNames>");
                              groups.Append("true");
                              groups.Append("</GroupsIsNames>");
                          }
                          string webTicketXml = string.Format("<Global method=\"GetWebTicket\"><UserId>{0}</UserId></Global>", user);
              
                          HttpWebRequest client = (HttpWebRequest)WebRequest.Create(new Uri(QlikViewServerURL));
                          client.PreAuthenticate = true;
                          client.Method = "POST";
                          client.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;
              
                          // Используем указанный логин и пароль от СЕРВЕРА
                          client.Credentials = new NetworkCredential(ticketinguser,ticketingpassword);
              
                          using (StreamWriter sw = new StreamWriter(client.GetRequestStream()))
                              sw.WriteLine(webTicketXml);
                          StreamReader sr = new StreamReader(client.GetResponse().GetResponseStream());
                          string result = sr.ReadToEnd();
              
                          XDocument doc = XDocument.Parse(result);
                          return doc.Root.Element("_retval_").Value;
                      }
                     
              
                     
              </script>
              
              
            • Re: QV Server, WebTickets. Group binding does not work
              Bill Britt

              Hi Lgor,

              I don't see your reply here, but I am not sure what you are authenticating against. Our web pages uses Windows authentication and that is what is passed to QVS. If you are not going to use Windows you have to provide QVS with either a Web Ticket or a Header. The connecter you applied above is only used to assign authorization to the users to see the document. QVS will check that once it receives a Web ticket or the header information.

               

               

              Bill