Everything should be made as simple as possible, but not simpler.
– Albert Einstein
- On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the “Act as part of the operating system” privilege:
- Run “secpol.msc”
- Click Local Security Policy, then click Local policies, and then click User rights assignment.
- Add the service account.
- Restart Excel Services and reboot the Analysis Services server.
Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. Note: If the backend data source is on the same server as the Analysis Services instance, delegation is not required.
Sometimes source systems do not store Windows credentials as an attribute of a person, and when that happens security becomes an interesting challenge.
For this project, I needed to retrieve the EmployeeNumber attribute from Active Directory to be able to link the user back to the source system (very useful for Reporting, but same logic applies for any service from the SQL Server BI stack).
First we need to create a ‘Linked Server’ from a SQL Server instance with the following system stored prod:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' GO
After you just create a Data Source pointing to the instance where you added that Linked Server and start querying the attributes you need from the Active Directory domain you need:
SELECT WinUsername ,EmployeeNumber FROM OPENQUERY(ADSI, 'SELECT sAMAccountName ,employeeNumber FROM ''LDAP://ou=users,ou=YourDomain,dc=YourDomainControler'' WHERE objectCategory = ''Person'' AND objectClass= ''user'' AND sAMAccountName='''+ YourUsername + '''')
If you need to get groups:
SELECT User ,GroupName FROM OPENQUERY( ADSI, 'SELECT Name ,distinguishedname FROM ''LDAP://ou=YourDomain,dc=YourDomainControler'' WHERE objectCategory = ''GROUP'' AND CN = ''* *''')
If you need to know the people that belong to a specific Group:
SELECT samaccountname ,Name FROM OPENQUERY(ADSI, 'SELECT samaccountname ,Name FROM ''LDAP://ou=YourDomain,dc=YourDomainControler'' WHERE objectCategory = ''user'' AND memberof = ''CN=NameOfTheGroup,OU=Groups,OU=YourDomain,DC=YourDomainControler''')