logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
CPBOURG  
#1 Posted : Monday, September 17, 2018 8:19:50 AM(UTC)
CPBOURG

Rank: Advanced Member

Groups: Registered
Joined: 9/13/2018(UTC)
Posts: 41
Belgium
Location: Ottignies

Was thanked: 2 time(s) in 2 post(s)
Hello,

I have been struggling with the LDAP authentication, but could find a way to make it work...

Can someone provide me with some example of how to configure it?

Thanks!
Thomas
epf  
#2 Posted : Monday, September 17, 2018 9:41:26 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
LDAP authentication is configured using:
Server Manager->Configuration->Configure Web Security...

Then select the Security Provider 'LDAP Authentication'

First edit the parameters to be relevant with your implementation (Port, Server Name, etc.)

If it still not work, edit the 'Custom Security Script' to understand what is going on (may depend on your server and infrastructure).

Good luck.
CPBOURG  
#3 Posted : Monday, September 17, 2018 11:15:06 AM(UTC)
CPBOURG

Rank: Advanced Member

Groups: Registered
Joined: 9/13/2018(UTC)
Posts: 41
Belgium
Location: Ottignies

Was thanked: 2 time(s) in 2 post(s)
Thanks, I tried that, but in our configuration, it didn't work.

When comparing to other tools communicating with LDAP, I have a few more parameters:

- LDAP version (LDAPv3 for us);
- Connection filter;
- BaseDN (to specify the Organisational Unit);
- DN account (which is a 'read' profile to send a request to the AD);
- Identification field in AD (to match with the login entered);

Is it possible to specify those ones in the connection script?

Thanks!
Thomas
epf  
#4 Posted : Monday, September 17, 2018 11:44:40 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Sorry we have no experience for advanced LDAP configuration,
You have to check MSDN for "LdapConnection", then change the code in the "Custom Security Script" after:
Code:
var ldapConnection = new LdapConnection(new LdapDirectoryIdentifier(user.Security.GetValue("ldap_server"), user.Security.GetNumericValue("ldap_port"), false, false));

CPBOURG  
#5 Posted : Wednesday, January 9, 2019 5:04:58 PM(UTC)
CPBOURG

Rank: Advanced Member

Groups: Registered
Joined: 9/13/2018(UTC)
Posts: 41
Belgium
Location: Ottignies

Was thanked: 2 time(s) in 2 post(s)
Hi there,

I finally managed to find some time to finalize the razor script to get the LDAP authentication working with Seal Report :

It works as a two step authentication:
1. The windows credentials authentication to the LDAP server (works with username, or username@domain, not case sensitive);
2. Get the group to which the user belongs from an excel sheet (couldn't find if a user could be member of many groups).

The excel sheet is called sealreport_groups.xls, and is stored in c:\SealReport folder. It contains two columns (on the first line) : Username and Group.

Possible Improvements:
- Handle many groups for one user;
- Add LDAPS connection;
- Store the groups in the Active Directory instead of the Excel sheet;
- ...

Enjoy !
Thomas



@using Seal.Model;
@using System.Net;
@using System.Data
@using System.Data.OleDb
@using Seal.Helpers
@using System.DirectoryServices;
@{
SecurityUser user = Model;
user.Name = user.WebUserName; //Display name for the log file
bool blConnected = false;


// first validate the access of the user in the LDAP
try

{
// Get the connection
DirectoryEntry Ldap = new DirectoryEntry("LDAP://yourserver", user.WebUserName, user.WebPassword);

object nativeObject = Ldap.NativeObject;
blConnected = true;

}

catch(DirectoryServicesCOMException Ex)
{

//Console.WriteLine(Ex.Message);
user.Error = Ex.Message;
}

if (blConnected == true) {

// If the connection is successfull, get the user group in the excel sheet
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\SealReport\\sealreport_groups.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();

OleDbCommand command = new OleDbCommand(string.Format("select Group from [Sheet1$] where Username={0}", Helper.QuoteSingle(user.WebUserName)), connection);

object group = command.ExecuteScalar();
if (group != null && group != DBNull.Value)
{
user.AddSecurityGroup((string)group);
}
else
{
user.Error = "No reporting group assigned to this user";
}
}
else {
user.Error = "Please check your user / password";
}
}

Edited by user Wednesday, January 9, 2019 5:13:28 PM(UTC)  | Reason: Not specified

thanks 1 user thanked CPBOURG for this useful post.
epf on 1/10/2019(UTC)
epf  
#6 Posted : Thursday, January 10, 2019 7:37:36 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Thank you for sharing your experience.
What we do often is to load the Excel sheet into a table in your database using a Task (there are sample to load a table from an Excel Sheet), then you query the database (with potential other information) directly.

Otherwise it is better also to use the groups defined in the AD when possible....
dhd  
#7 Posted : Friday, June 21, 2019 8:00:02 PM(UTC)
dhd

Rank: Newbie

Groups: Registered
Joined: 6/17/2019(UTC)
Posts: 1
Brazil
Location: brasilia

Thanks: 3 times
Was thanked: 2 time(s) in 2 post(s)
Just sharing my solution for this problem.

I've created a SQL Server Database with 1 Table. This table contais User Name (AD Name) and the Security Group (Same name used in security Group Configure Web Security/Security Groups).

Created script to validade the user in AD.
if OK, then, Check on Database the correct group for user. (SQL Script for the procedure on the end of the post)


Code:
@using Seal.Model;
@using System.Net;
@using System.Data
@using System.Data.OleDb
@using Seal.Helpers
@using System.DirectoryServices;

@{
    SecurityUser user = Model;
    user.Name = user.WebUserName; //Display name for the log file
    bool blConnected = false;

    // first validate the access of the user in the LDAP
    try
    {
        // Get the connection
        DirectoryEntry Ldap = new DirectoryEntry("LDAP://"+user.Security.GetValue("ldap_server"), user.WebUserName, user.WebPassword);
        object nativeObject = Ldap.NativeObject;
        blConnected = true;
    }

    catch(DirectoryServicesCOMException Ex)
    {
        Console.WriteLine(Ex.Message);
        user.Error = Ex.Message;
    }	

    try
    {
        if (blConnected == true) {
            // If the connection is successfull, get the user group in the excel sheet
            //string connectionString = "Server=;Database=dbSegurancaBI;User Id=sa;Password=mengo74*;";
            
            string connectionString = "Provider=SQLOLEDB.1;Initial Catalog=dbSegurancaBI;Data Source=SERVERNAME;User Id=sa;Password=sapassword;";
                
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();

            string strSql = "EXECUTE spu_BuscaUsuario " + Helper.QuoteSingle(user.WebUserName) + "";

            OleDbCommand command = new OleDbCommand(string.Format(strSql), connection);

            object group = command.ExecuteScalar();

            if (group != null && group != DBNull.Value && (string)group != "SEM")
            {
                user.AddSecurityGroup((string)group);	
            }
            else 
            {
                user.Error = "No reporting group assigned to this user";
                throw new Exception("usuário autenticado com Sucesso, porém sem nenhum vínculo com os objetos de segurança do ATP BI.");
            }	
                
        }
        else {
            user.Error = "Please check your user / password";
        }
    }
    catch(Exception e)
    {
        throw e;
    }
}



The Procedure:
Code:
CREATE PROCEDURE [dbo].[spu_BuscaUsuario]
(
	@NomeAD	VARCHAR(255)
)
AS

SET @NomeAD = REPLACE(@NomeAD,'/','\')

IF CHARINDEX('@',@NomeAD) > 0 BEGIN
	SET @NomeAD	=	'ATP-BSB\' + SUBSTRING(@NomeAD,0,CHARINDEX('@',@NomeAD))
END ELSE BEGIN
	IF CHARINDEX('\',@NomeAD) = 0 BEGIN
		SET @NomeAD	=	'ATP-BSB\' + @NomeAD
	END
END

IF NOT EXISTS (
			SELECT 
					1
			FROM 
					Usuario
			WHERE
					Usuario.NomeAD = @NomeAD
		)
BEGIN
	INSERT INTO Usuario (NomeAD) VALUES(LOWER(@NomeAD))
	SELECT GrupoSeal  as Grupo FROM Usuario
END;

SELECT 
		GrupoSeal as Grupo 
FROM 
		Usuario 
WHERE 
		Usuario.NomeAD = @NomeAD
GO
thanks 1 user thanked dhd for this useful post.
epf on 6/24/2019(UTC)
ptsepeli  
#8 Posted : Friday, October 11, 2019 3:10:39 PM(UTC)
ptsepeli

Rank: Newbie

Groups: Registered
Joined: 2/14/2019(UTC)
Posts: 5
United Kingdom
Location: Harpenden

Was thanked: 1 time(s) in 1 post(s)
Hi,
I am trying to make LDAP to work in the following way:

  1. Search for the distinguishedName of the user using an account that has search privileges in LDAP.
  2. Use the distinguishedName instead of the user.WebUserName to authenticate because some LDAP systems don't accept simple usernames and we have to be sure the user exists in a specific OU.
  3. Query our SealUsers database table to get the Group if the user exists.
  4. Insert the username in our SealUsers database table with a default Group when it doesn't exit.

Steps 1 and 2 are working and I think I can do 3 and 4 thanks to dhd 's example.
I would like to know if it is possible to use the same Data Source Seal uses for the reports instead of a new OleDbConnection with passwords exposed in the script.
Any examples if possible are appreciated.


Finally, I second CPBOURG 's request to add the extra fields in the LDAP Configuration.
I was missing the following:
  • LDAPVersion
  • LDAPSearchUsername;
  • LDAPSearchPassword;
  • LDAPBaseDN
  • LDAPSearchFilter


Thanks for the great product.
ptsepeli

Edited by user Friday, October 11, 2019 3:11:10 PM(UTC)  | Reason: Not specified

epf  
#9 Posted : Saturday, October 12, 2019 7:45:49 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
You can easily use the Connections defined in the repository Data Sources, there is a Repository instance you can use everywhere:

Code:
var sources = Repository.Instance.Sources;
var connectionString = sources[0].Connection.FullConnectionString; //Main connection
var connections = sources[0].Connections;



check https://sealreport.org/H...e3-0b97-bf4137786d7d.htm and https://sealreport.org/H...c8-f509-54ce22228f92.htm

good luck for your specific LDAP login, your feedbacks will be welcome...
Users browsing this topic
Similar Topics
LDAP authentication assembly matches (General Discussions/Other)
by Maxiz 6/16/2021 3:44:21 PM(UTC)
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.