how to load NT users from SQL Server DB to grant access to Qlikview files?

Question

is it possible to load 'user' and 'access' data directly from a SQL Server database to compare with the actual NT User logged and grant access to my QlikView file?

Thank you in advance.

Answer 1

Your question does not specify much detail in terms of your deployment. I am assuming you are using a Qlikview Server, Qlikview Publisher, and are accessing documents via an Access Point.

There are effectively two layers of security required in order for a user to be able to access a Qlikview application, and that you are running in the default NTFS mode (not DMS mode).

1. File Permissions
2. Section Access (Row Level Security)

The file permissions control who can access the document, and the Section Access controls what rows of data each users can see within the application. If section access has been implemented within a Qlikview Application, and a user has not been granted access to any rows of data, they will not be able to open the application.

Section Access is not mandatory, and will only be required if your security model requires row level filtering of data. Users can be identified in a number of ways (AD group or account SID, Windows Login, Windows AD Group Name, etc).

So in short, yes, you could load security data from a SQL Server database (or another data source) and use it to apply security within your Qlikview application. The Section Access functionality is described in detail in the QlikView Desktop online help. The only other consideration would be ensuring that all appropriate users had file level permissions to access the file in addition to being specified in the section access data.

--- Following Example Added after clarifcation of Question ---

I normally store the section access data to a QVD first as a part of an extract script:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YOURDB;Data Source=YOURDBSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False];

SectionAccessData:
LOAD
    Upper(Trim(NetworkUserID)) AS [NTNAME]
    //NTSID can be used in place of NTNAME if that's what you have stored in your data - but you should only use one or the other on a given row of data.  
    //,Upper(Trim(NTSID)) AS NTSID
    ,'USER' AS [ACCESS]
INLINE
    [
    NetworkUserID,SomethingToSecure
    DOMAIN\graeme.smith,SOMEDATATOFILTER
    ];


STORE SectionAccessData INTO SectionAccessData.qvd (qvd); 

You can then load this data into your Section Access table in the QVW containing the UI elements:

SECTION ACCESS;
LOAD 
    [ACCESS]
    ,[NTNAME] 
    //,NTSID    
    //This field will be joined to the data model to restrict the data that the user can see. 
    ,[SomethingToSecure] AS [SomethingToSecure]
FROM
    $(QvdDirectory)\SectionAccess.qvd (qvd)
WHERE 
    (1=1);

SECTION APPLICATION;

You will also need to amend the following document properties in the UI document:

Document Properties -> Opening Tab

1. Initial Data Reduction Based on Section Access Checked
2. Strict Exclusion Checked
3. Prohibit Binary Load Checked

If you need to load the data from SQL instead of an inline table:

Replace this:

INLINE
[
NetworkUserID,SomethingToSecure
DOMAIN\graeme.smith,SOMEDATATOFILTER
];

With this:

SQL
SELECT  
MyNetworkUserIDField as NetworkUserID,
MySomethingToSecure as SomethingToSecure
FROM MyDB.dbo.MyTable;