Fetching members of groups in AD

To continue from my last post. Now that I could query the customer AD I wanted to fetch all users belonging to a certain group. I found a lot of "solutions" of how to construct my LDAP query through google though non worked.

In the end, with a lot of trial and error, I managed to get it working. This is the way you query for members in a group using LDAP:

First find our the DN of your group (replace group1 with the name of your group):

SELECT sAMAccountName, distinguishedName
FROM   OPENQUERY(ADSI2,'<LDAP://server.customer.local/DC=customer,DC=local>;(&(objectClass=group)(cn=group1));sAMAccountName,distinguishedName;subtree')

Now copy the distinguishedName from the results and replace <distinguishedName> in the following query:

SELECT sAMAccountName, userPrincipalName, Name, department, Manager, title, mobile, ipPhone, mail, distinguishedName, streetAddress, l, postalCode, co, company
FROM   OPENQUERY(ADSI3,'<LDAP://server.customer.local/DC=customer,DC=local>;(&(objectcategory=person)(objectClass=user)(memberOf=<distinguishedName>));sAMAccountName,Name,Manager,userPrincipalName,title,ipPhone,mail,mobile,department,distinguishedName,streetAddress,l,postalCode,co,company;subtree')                      
There you are, all users in group "group1"

Query Trusted AD using Linked Server

I'm involved in a project where we query AD for user information from an SQL server and use this information in our product. As a new feature our customer wants to let their customers have some limited access to the system. To facilitate this a new customer AD (New Forrest) have been created where all customers will live. From our side we then needed to fetch the AD information from this AD as well into the SQL server. Since I never worked with LDAP or Linked Servers before it took some time to figure out how to do this.

The before situation:

Our client have 2 AD's, client.local and customer.local in different forests. There is a one way trust. A SQL server is located in client.local and is using a Linked Server to query information from client.local. The Linked Server used to query client.local uses a remote login for all users.

The new things wanted:

The customer.local AD should also be queried from the SQL server in client.local.

We already had a Linked Server up and running since we where already querying client.local. I tried to use that linked server running the following query:

SELECT sAMAccountName, userPrincipalName, Name, department, Manager, title, mobile, ipPhone, mail, distinguishedName, streetAddress, l, postalCode, co, company
FROM OPENQUERY(ADSI,'<LDAP://DC=customer,DC=local>;(objectClass=user);sAMAccountName,Name,Manager,userPrincipalName,title,ipPhone,mail,mobile,department,distinguishedName,streetAddress,l,postalCode,co,company;subtree')                     

This gave me the following error:
           
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "ADsDSOObject" for linked server "ADSI2" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "<LDAP://DC=customer,DC=local>;(objectClass=user);sAMAccountName,Name,Manager,userPrincipalName,title,ipPhone,mail,mobile,department,distinguishedName,streetAddress,l,postalCode,co,company;subtree" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

Ahh I thought, I need to specify the server. So I ran the following query:

SELECT sAMAccountName, userPrincipalName, Name, department, Manager, title, mobile, ipPhone, mail, distinguishedName, streetAddress, l, postalCode, co, company
FROM OPENQUERY(ADSI2,'<LDAP://server.custmer.local/DC=customer,DC=local>;(objectClass=user);sAMAccountName,Name,Manager,userPrincipalName,title,ipPhone,mail,mobile,department,distinguishedName,streetAddress,l,postalCode,co,company;subtree')

That didn't help either. Still the same error.

Then I thought, since I'm contacting a server in a different AD I should use a login from that AD when running the query. To do that I needed to setup a new Linked Server. To create one run the following stored procedure:

EXEC sp_addlinkedserver @server = N'ADSI2', @srvproduct=N 'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

To set the login run the following stored procedure, replacing the login information below with your own. The user you specify needs read rights and rights to traverse the AD tree.

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI2',@useself=N'False',@locallogin=NULL,@rmtuser=N'CUSTOMER\UserAccount',@rmtpassword=N'AccountPassword'

Now I expected everything to work so I ran the updated Query below:

SELECT sAMAccountName, userPrincipalName, Name, department, Manager, title, mobile, ipPhone, mail, distinguishedName, streetAddress, l, postalCode, co, company
FROM OPENQUERY(ADSI2,'<LDAP://server.custmer.local/DC=customer,DC=local>;(objectClass=user);sAMAccountName,Name,Manager,userPrincipalName,title,ipPhone,mail,mobile,department,distinguishedName,streetAddress,l,postalCode,co,company;subtree')

and succeeded.

Webpart error

I'm currently moving our product from a SharePoint 2007 WSS to a SharePoint 2010 Foundation. Most things have gone quite well. Migration of the content DB when without any problems at all. However one thing did happen that caused me some time to solve.A few of the webparts didn't work. I got an error in the webpart with an ID to the SharePoint Logs. Looking into the logs I found the following error message:

Error while executing web part: System.Xml.XmlException: Name cannot begin with the ' ' character, hexadecimal value 0x20. Line 1, position 19.

The webparts that didn't work where all of the same type. A Data web part where we use a web service call to fetch data from a DB. One of the parameters to the web service call is a "where" statement.
The web service ran fine. There was no problem with the where statement. It ran fine both through the web service and through a query window directly to the DB.

After some time working with this problem I found out that SharePoint 2007 and 2010 differs in one small area. In SharePoint 2007 I could write my where statement like this:

State &amp;lt; 70

State is a column in a table. The above would generate a SQL query looking like this:

SELECT * FROM table WHERE State < 70

However in SharePoint 2010 using &amp;lt; or (<) in a WHERE statement like that generates the above mentioned error message.

Knowing this it is obvious that in SharePoint 2010 the XML parser parsing the web service believes that a new tag is starting and finds nothing following the start tag (<) sign and hence responds with the error message.

My solution was to change the where statement to something like this:

State BETWEEN 0 AND 69

After that everything ran fine.

SharePoint 2010 - Multiple Domains - People picker

The other day I had to configure a SharePoint 2010 server to support 2 AD using a one way trust. The AD the server it self resides in and a customer AD. I found out that you need to tell SharePoint to look search in other Domains too. Fortunately this is quite simple.

You need to run 2 stsadm commands.

stsadm -o setapppassword -password <yourkey>

Replace <yourkey> with your own key. This key is used to encrypt things(I'll explain what later on). This command should be run on each server  in the farm with the exact same Key.

Next you register all the domains you want searched (except the on your server is in). You'll need to do this for each web application, not for each server.

stsadm -o setproperty -pn peoplepicker-searchadforests -pv domain:domainA.local,domainA\account,password -url http://webapplication

Running this command overwrites the previous entry. Fortunately the commands support adding multiple domains. You do that like this:

stsadm -o setproperty -pn peoplepicker-searchadforests -pv domain:domainB.local,domainB\account,password;domain:domainA.local,domainA\account,password -url http://webapplication

<domain:domainA.local> -  this is how you specify what domains to tell SharePoint to search in. You separate different domains with a ";"

<domainA\account,password>  - is used to give SharePoint an account to traverse a domain to look for accounts. This is needed to find people by the people picker. The user account needs read rights and rights to traverse the AD tree. Now here the Key set in the first stsadm command comes into play. The key is used as an encryption key to encrypt the password during communication between the SharePoint server and the AD.

It is also possible to add a Forest instead of a domain. You do that like this:

stsadm -o setproperty -pn peoplepicker-searchadforests -pv
forest:domainA.local,domainA\account,password -url http://webapplication

You could combine forests and domains like this:

stsadm -o setproperty -pn peoplepicker-searchadforests -pv
forest:domainA.local,domainA\account,password;domain:domainB.local,domainB\account,password -url http://webapplication

If the domains or forests are trusted, it is not necessary to pass in the loginname or password (if you don't mind not finding people from the trusted domain in the people picker). You could then skip that part and your command would look something like this:

stsadm -o setproperty -pn peoplepicker-searchadforests -pv
forest:domainA.local;domain:domainB.local -url http://webapplication

That's about it I think.

Merging Documents

Merging documents can be a bit tricky when you add watermarks into the equation. I had to implement a document merge function that merges a preface page and a main document. This package could then be multiplied several times with different data in the preface page and a watermark on all but the first main document.

oDoc is the active document.

Dim aCCDocuments AsArrayList

aCCDocuments = NewArrayList()
UnlinkAndCreateSectionBreak(oDoc)
AddWatermark("Copy", i)
s = GetTempPath() & "Watermarked_"& i & "_" & m_sDocumentName
oDoc.SaveAs(CType(s, Object))
DeleteWatermark(i)
m_oDoc.SaveAs(CType(m_sFullDocumentName,Object))

If Not GenerateRecipientLetter(m_sDocumentName, aCCDocuments) = False Then

    'Merge Documents and print
    MergeDocuments(aCCDocuments, s)
End If

ClearTempDocuments(s, aCCDocuments)

The first thing I do is to insert a new SectionBreakNextPage and then unlink the headers and footers in the main document. This is needed to allow us to have different headers and footers in the merged document later. The following code explains this process.

Private Sub UnlinkAndCreateSectionBreak(ByRef oDoc As Word.Document)

    Dim oParagraph As Microsoft.Office.Interop.Word.Paragraph
    Dim oRangeEnd AsMicrosoft.Office.Interop.Word.Range
    Dim oHeader As Word.HeaderFooter
    Dim oFooter As Word.HeaderFooter
    Dim oSection As Microsoft.Office.Interop.Word.Section

    oRangeEnd = oDoc.Range

    'Insert the SectionBreakNextPage
    With oRangeEnd
        .Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        oParagraph = oDoc.Paragraphs.Add(CType(oRangeEnd, Object))
        oParagraph.Range.InsertParagraphAfter()
        oParagraph.Range.InsertBreak(Word.WdBreakType.wdSectionBreakNextPage)
    End With

    'Move to newly created section
    oSection = oDoc.Sections.Last
    With oSection
        'Unlink all headers from previous.
        For Each oHeader In .Headers
            oHeader.LinkToPrevious = False
        Next

        'Restart page count if there is one in the header.
        .Headers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).PageNumbers.RestartNumberingAtSection = True
        .Headers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).PageNumbers.StartingNumber = 1

        'Unlink all footers from previous.
        For Each oFooter In .Footers
            oFooter.LinkToPrevious = False
        Next

        'Restart page count if there is one in the footer.
        .Footers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).PageNumbers.RestartNumberingAtSection = True
        .Footers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).PageNumbers.StartingNumber = 1
    End With
End Sub

Next we use the AddWatermark method I described in the previous blog post to add a watermark to the document and then save the document in the users %TEMP% folder. GetTempFolder fetches %TEMP% and looks like this:

Private Function GetTempPath() AsString

    Dim sPath As String

    sPath = System.Environment.GetEnvironmentVariable("TEMP")

    If sPath = "" Then

        sPath = System.Environment.GetFolderPath(Environment.SpecialFolder.InternetCache)
    End If

    If NotsPath.EndsWith("/") Or NotsPath.EndsWith("\") Then

        sPath = sPath & "\"
    End If

    GetTempPath = sPath

End Function

Once the document is saved we Delete the watermark again and resave the original document. The next step involves creating the preface documents. I will not include any code for this since this part highly depends on your specific needs. The result however is that I create each preface document saves it in the user %TEMP% folder and also adds an entry to an ArrayList (aCCDocuments). This leads upp to the actual merge part.

Private Sub MergeDocuments(ByValaCCDocuments As ArrayList,ByVal sWaterMarked AsString)

    Dim i As Integer

    i = 0

    For Eachfile In aCCDocuments

        ' Insert the files to our template
        oSection = oDoc.Sections.Last
        oSection.Range.InsertFile(file)
        oSection = m_oDoc.Sections.Last
        oSection.Range.InsertFile(sWaterMarked)
        i += 1
    Next

    ' Save the merged document.
    oDoc.Save()
End Sub

This part is quite simple. We iterate over the preface documents and insert, after the original main document, the preface document and then a watermarked copy of the original document, then the next preface document and then again a watermarked copy of the original and so on until there are no more preface document. The final thing we do is to save the merged document.

Now all that remains is to clean up a bit. We did create a watermarked copy of the original document. We also created a number of preface documents. These need to be deleted and that is what I do in ClearTempDocuments()

Watermark in a word document

I resently had to implement watermarking of a document in word. One important thing to remember is that a watermark is actually placed in your header or footer section. It might be displayed across the document but it is actually in either the header or footer.

Here is what I did.

I added an AddWatermark function that looks like this:

Private Function AddWatermark(ByVal WatermarkText As String, ByVal iSeed As Integer) As Boolean

    AddWatermark = True

    Try

        Dim wmShape As Word.Shape
        Dim oSection As Word.Section
 
        For Each oSection In m_oDoc.Sections
            For Each CurrentHeader As Word.HeaderFooter In oSection.Headers
                'Select the current header.
                CurrentHeader.Range.Select()

                'Create the watermark shape

                wmShape = CurrentHeader.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, WatermarkText, "Times New Roman", 1, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoFalse, 0, 0, CurrentHeader.Range)

                'Set all of the attributes of the watermark

                With wmShape
                    .Select()
                    .Name = "ExformaticsWaterMarkObject_" & iSeed.ToString()
                    .TextEffect.NormalizedHeight = Microsoft.Office.Core.MsoTriState.msoFalse
                    .Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
                    .Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                    .Fill.Solid()
                    .Fill.ForeColor.RGB = Word.WdColor.wdColorGray25
                    .Fill.Transparency = 0.4 'Make the watermark transparent
                    .Rotation = 315 'Tilt of the watermark
                    .LockAspectRatio = Microsoft.Office.Core.MsoTriState.msoTrue
                    .Height = m_oUtilites.WordApp.InchesToPoints(2.4)
                    .Width = m_oUtilites.WordApp.InchesToPoints(4.8)
                    .WrapFormat.AllowOverlap = CType(True, Integer)
                    .WrapFormat.Side = Word.WdWrapSideType.wdWrapBoth
                    .WrapFormat.Type = Word.WdWrapType.wdWrapNone
                    .RelativeHorizontalPosition = Word.WdRelativeHorizontalPosition.wdRelativeHorizontalPositionMargin
                    .RelativeVerticalPosition = Word.WdRelativeVerticalPosition.wdRelativeVerticalPositionMargin
                    .Left = Word.WdShapePosition.wdShapeCenter
                    .Top = Word.WdShapePosition.wdShapeCenter
                End With
 
                iSeed = iSeed + 100
            Next
        Next
    Catch ex As Exception
        AddWatermark = False
    End Try
End Function

To remove the watermark I added the folowing method:

Private Sub DeleteWatermark(ByVal iSeed As Integer)

    Dim oSection As Word.Section

    For Each oSection In m_oDoc.Sections

        For Each CurrentHeader As Word.HeaderFooter In oSection.Headers
            CurrentHeader.Shapes("ExformaticsWaterMarkObject_" & iSeed.ToString()).Delete()
            iSeed = iSeed + 100
        Next
    Next
End Sub