Yet another interesting situation where we had to display an Report on Excel and put it on SharePoint Power pivot gallery. We selected the Cube database and dragged in a few dimensions and Measures along with a few slicers. The query that was auto generated was not very optimized.
The report was to show data only for last one week. One of the slicer "Week Starting Date" was to have dates for the last one month only.
We had to use some hidden filters to ensure that we show only part of the data. i.e. only last 4 weeks data. There are few ways (Slicer Settings) where we can visually indicate that Items that do not have data should be disabled (will be shown) and also there is a way to move those items to the end of the slicer. There is no way of removing that altogether.
In such situations we can write a custom query to only fetch data with Week Starting Dates that is within the last one month range. This also improves the performance because of the optimized query against the Cubes.
Excel do not provide a direct way to update the query. There are a few Adins/products that aid to this, like Vivid http://www.varigence.com/products/vivid/features/navigation that will come into handy.
Another approach could be to use an ODC File where you can embed the MDX query. I am attaching a sample ODC file. Pay close attention to the highlighted section.
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Cube>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content=CubeDB>
<meta name=Table content=CDW>
<title>sqlserver2008r2 CubeDB CDW</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>sqlserver2008r2 CubeDB CDW</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSOLAP.4;Password=password;Persist Security Info=True;Data Source=Server\sqlserver2008r2;Initial Catalog=CubeDB</odc:ConnectionString>
<odc:CommandType>MDX</odc:CommandType>
<odc:CommandText>SELECT
{ [Measures].[Sales Amount],
[Measures].[Tax Amount] } ON 0,
{ [Date].[Fiscal].[Fiscal Year].&[2002],
[Date].[Fiscal].[Fiscal Year].&[2003] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )
</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
</head>
</html>
Thursday, February 17, 2011
Monday, February 14, 2011
Securing your LDAP Membership Provider
There are situations where you want to have Forms based Authentication in SharePoint. That is when you will choose the Claims based Authentication on the Web Application.
We had a requirement where we need to classify two kinds of users Employees and Clients. For a certain reason we had to chose Active Directory as the user store for both kinds of users. We could not reuse the Active Directory of the Employees for the Client users. Hence, we ended creating another domain just for the client users.
When you are using a LdapProvider if the Application Pool account do not have the rights to perform an LDAP request on the Active Directory then you will need to specify two attributes connectionUsername & connectionPassword in the Ldap membership provider of the web.config. This is where you would not want to keep the connectionPassword in plain text. Below I have given a simple implementation to Encrypt and store the password and how you could use a method to retrieve the password at run time.
The web.config of the Web Application, Central Administration & Security Token Service will look something like this with a connectionPassword having the encrypted string.
<membership defaultProvider="CustomLdapProvider">
<providers>
<add name="ClientsADMembershipProvider" type="Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="dc.clientsdomain.com" port="389" useSSL="false" userDNAttribute="distinguishedName" userNameAttribute="sAMAccountName" userContainer="CN=Users,DC=domain,DC=com" userObjectClass="person" userFilter="(|(ObjectCategory=group)(ObjectClass=person))" scope="Subtree" otherRequiredUserAttributes="sn,givenname,cn" connectionUsername="clientsdomain\administrator" connectionPassword="SDJFSew98234DFJ889==" />
<add name="EmployeesADMembershipProvider" type="Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="dc.employeesdomain.com" port="389" useSSL="false" userDNAttribute="distinguishedName" userNameAttribute="sAMAccountName" userContainer="CN=Users,DC=domain,DC=com" userObjectClass="person" userFilter="(|(ObjectCategory=group)(ObjectClass=person))" scope="Subtree" otherRequiredUserAttributes="sn,givenname,cn" connectionUsername="employeesdomain\administrator" connectionPassword="SL43Sew982342KLSDF==" />
<add name="CustomLdapProvider" type="Project.CustomLdapProvider, Project, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8027a17523a78ae328" />
</providers>
</membership>
The partial implementation of CustomLdapProvider will be something like below:
public class CustomLdapProvider : MembershipProvider
{
private static LdapMembershipProvider _employeesProvider = null;
private static LdapMembershipProvider _clientsProvider = null;
private LdapMembershipProvider GetMembershipProvider(string providerName)
{
LdapMembershipProvider provider = new LdapMembershipProvider();
// In SharePoint when your login page is coming from Layouts folder, HttpContext.Current is returning null. Hence the next line.
//Configuration config = WebConfigurationManager.OpenWebConfiguration(HttpContext.Current.Request.ApplicationPath);
Configuration config = WebConfigurationManager.OpenWebConfiguration(@"~/web.config");
MembershipSection section = (MembershipSection)config.GetSection("system.web/membership");
ProviderSettings providerSettings = section.Providers[providerName];
NameValueCollection param = providerSettings.Parameters;
param["connectionPassword"] = Utility.Decryption(param["connectionPassword"], true);
provider.Initialize(providerName, param);
return provider;
}
private MembershipProvider EmployeesProvider
{
get
{
if (_employeeProvider == null)
{
_employeeProvider = GetMembershipProvider("EmployeesADMembershipProvider");
}
return (MembershipProvider)_employeesProvider;
}
}
private MembershipProvider ClientsProvider
{
get
{
if (_clientsProvider== null)
{
_clientsProvider = GetMembershipProvider("ClientsADMembershipProvider");
}
return (MembershipProvider)_clientsProvider;
}
}
// Override all the membership provider and use the appropriate Membership provider to call the overloads.
public override bool ValidateUser(string name, string password)
{
// name = loginid@clientsdomain.com / loginid@employeesdomain.com
// Extract the domain name and based on the domain connect to the appropriate Membership Provider (EmployeesProvider, ClientsProvider) and call the ValidateUser.
// Ex: ClientsProvider.ValidateUser(name, password)
}
}
I am sure this will be quite useful in implementing Forms Based Authentication in SharePoint when you have to work against an LDAP Provider and do not want to compromise on the connection string.
We had a requirement where we need to classify two kinds of users Employees and Clients. For a certain reason we had to chose Active Directory as the user store for both kinds of users. We could not reuse the Active Directory of the Employees for the Client users. Hence, we ended creating another domain just for the client users.
When you are using a LdapProvider if the Application Pool account do not have the rights to perform an LDAP request on the Active Directory then you will need to specify two attributes connectionUsername & connectionPassword in the Ldap membership provider of the web.config. This is where you would not want to keep the connectionPassword in plain text. Below I have given a simple implementation to Encrypt and store the password and how you could use a method to retrieve the password at run time.
The web.config of the Web Application, Central Administration & Security Token Service will look something like this with a connectionPassword having the encrypted string.
<membership defaultProvider="CustomLdapProvider">
<providers>
<add name="ClientsADMembershipProvider" type="Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="dc.clientsdomain.com" port="389" useSSL="false" userDNAttribute="distinguishedName" userNameAttribute="sAMAccountName" userContainer="CN=Users,DC=domain,DC=com" userObjectClass="person" userFilter="(|(ObjectCategory=group)(ObjectClass=person))" scope="Subtree" otherRequiredUserAttributes="sn,givenname,cn" connectionUsername="clientsdomain\administrator" connectionPassword="SDJFSew98234DFJ889==" />
<add name="EmployeesADMembershipProvider" type="Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="dc.employeesdomain.com" port="389" useSSL="false" userDNAttribute="distinguishedName" userNameAttribute="sAMAccountName" userContainer="CN=Users,DC=domain,DC=com" userObjectClass="person" userFilter="(|(ObjectCategory=group)(ObjectClass=person))" scope="Subtree" otherRequiredUserAttributes="sn,givenname,cn" connectionUsername="employeesdomain\administrator" connectionPassword="SL43Sew982342KLSDF==" />
<add name="CustomLdapProvider" type="Project.CustomLdapProvider, Project, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8027a17523a78ae328" />
</providers>
</membership>
The partial implementation of CustomLdapProvider will be something like below:
public class CustomLdapProvider : MembershipProvider
{
private static LdapMembershipProvider _employeesProvider = null;
private static LdapMembershipProvider _clientsProvider = null;
private LdapMembershipProvider GetMembershipProvider(string providerName)
{
LdapMembershipProvider provider = new LdapMembershipProvider();
// In SharePoint when your login page is coming from Layouts folder, HttpContext.Current is returning null. Hence the next line.
//Configuration config = WebConfigurationManager.OpenWebConfiguration(HttpContext.Current.Request.ApplicationPath);
Configuration config = WebConfigurationManager.OpenWebConfiguration(@"~/web.config");
MembershipSection section = (MembershipSection)config.GetSection("system.web/membership");
ProviderSettings providerSettings = section.Providers[providerName];
NameValueCollection param = providerSettings.Parameters;
param["connectionPassword"] = Utility.Decryption(param["connectionPassword"], true);
provider.Initialize(providerName, param);
return provider;
}
private MembershipProvider EmployeesProvider
{
get
{
if (_employeeProvider == null)
{
_employeeProvider = GetMembershipProvider("EmployeesADMembershipProvider");
}
return (MembershipProvider)_employeesProvider;
}
}
private MembershipProvider ClientsProvider
{
get
{
if (_clientsProvider== null)
{
_clientsProvider = GetMembershipProvider("ClientsADMembershipProvider");
}
return (MembershipProvider)_clientsProvider;
}
}
// Override all the membership provider and use the appropriate Membership provider to call the overloads.
public override bool ValidateUser(string name, string password)
{
// name = loginid@clientsdomain.com / loginid@employeesdomain.com
// Extract the domain name and based on the domain connect to the appropriate Membership Provider (EmployeesProvider, ClientsProvider) and call the ValidateUser.
// Ex: ClientsProvider.ValidateUser(name, password)
}
}
I am sure this will be quite useful in implementing Forms Based Authentication in SharePoint when you have to work against an LDAP Provider and do not want to compromise on the connection string.
Subscribe to:
Posts (Atom)