Open .msg files in SharePoint 2010

I recently upgraded a SharePoint 2007 server to SharePoint 2010. As part of our solution we store files, for instance email (.msg) files on the SharePoint server. Once the migration was doen I tried to open a .docx, a .txt and a .png file from the SharePoint. All of those worked fine. However when I tried to open a .msg file the file wouldn't open. I was precented with the option to save the file or to cancel. Saving the file localy and then opening the file resulted in the mail opening.

Apparently there is a new security feature in SharePoint 2010. To enable opening of .msg files, and I asume other file types, you need to do the following:

1) Open Central Admin
2) Go to Manage Web Applications
3) Pick your Web Application
4) Choose General Settings
5) Find "Change Browser File Handling"
6) Change it from "strict" to "permissive"

Done.

SharePoint Compatibility range mismatch

We have a project where I'm going to migrate a SharePoint 2007 server application to a SharePoint 2010. We had done the content db migration 3 times for test purposes and we have had compleete success all those 3 times.

The other day we did the real migration. From and to the same servers we had done during our tests. No settings where changed between the test migrations and the production migration.

The migration gave no error messages, it reported success. All content was migrated. However, we could not browse to the application. The error message we got from the event log was:

"There is a compatibility range mismatch between the Web server and database "WSS_Content", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range."

A search in google gave us a few possible solutions:

1) We checked that the correct patches (SharePoint 2007 SP2) was installed.
2) We where not running a beta version of SharePoint 2010.
3) We hade the correct rights to the WSS_Content db for the administrative user we where using

None of these solved our problem. We tride the migration 2 more times with the same result. We created a new site collection and tried the migration again with not much more luck. We got to the point where we where prompted for authentication three times then the page would fail to load.

After 3 hours of trying to solve the issue we browsed to the site not using Internet Explorer but instead using Chrome. using Chrome worked. We could browse our application. We quickly figured that the difference between IE 9 and Chrome must be Kerberos. Shutting down Kerberos in SharePoint alowed us to browse with IE 9 as well. So for some reason something had happend to the Kerberos settings during this migration.

We did not find anything amiss with our Kerberos settings in Sharepoint. Looking att the Kerberos settings outside of SharePoint we found out that Windows 2008 R2 uses an implementation called: SPNEGO2 this requires a SPN for the web application. This was however not the problem. Another article on the net told us that enabling kernel-mode authentication shouldn't be done since there are compatability problems with Internet Explorer 8. On a whim however we did enable kernel-mode authentication. All of a sudden everything worked.

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()