Aidan Garnish

Collaboration Not Competition

MOSS 2007 double hop woes - and solution!

I have recently been working on a web part that accesses a SQL Server that is installed on a different server to the MOSS web front end. This web part worked great running under my own credentials which also had access to the SQL Server....until I tried running the page from a web browser on my own machine. At that point I get a login failed error as my credentials did not get as far as the SQL Server.

This was due to the double hop issue in Windows networks that only allows an application to pass the credentials it is running across one server hop. This meant that whilst running the web part from the MOSS Server I could hop once to the SQL Server and get the data. When running the web part from my PC I hopped once to the MOSS Server and from there was unable to pass my credentials across the next hop to the SQL Server.

After doing some investigation I realised that I could get around this using Kerberos and configuring AD correctly but as I don't have access to AD this is something I wanted to avoid.

Instead I came to the realisation that I didn't really need to pass the logged in user's credentials to the SQL Server as the information being pulled out is available to all users. At first I thought the solution was to use impersonation by adding a service account to the Web.Config of my SharePoint web application which is then used to access SQL.

This line needs to be added to Web.Config to do this:

<identity impersonate="true" userName="domain\serviceAccountUser" password="*******"/>

This user is then given permissions on SQL.......but that didn't solve the problem entirely!

Although my web part could now access SQL the whole of the SharePoint web application was running using the service account. The downside of this is that the user is no longer logged in as themselves so all their security permissions to document libraries, lists etc. are no longer applied.

Instead I tried using impersonation just around the piece of code that accesses the database using the following article - http://support.microsoft.com/kb/306158

I modified this code slightly to create a SecurityHelper class which I then used in my web part to impersonate the service account that had access to the database.

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Security.Principal;
namespace ProductSearch
{
    class SecurityHelpers
    {
        public SecurityHelpers() { }

        public const int LOGON32_LOGON_INTERACTIVE = 2;
        public const int LOGON32_PROVIDER_DEFAULT = 0;

        WindowsImpersonationContext impersonationContext;

        [DllImport("advapi32.dll")]
        public static extern int LogonUserA(String lpszUserName,
            String lpszDomain,
            String lpszPassword,
            int dwLogonType,
            int dwLogonProvider,
            ref IntPtr phToken);
        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern int DuplicateToken(IntPtr hToken,
            int impersonationLevel,
            ref IntPtr hNewToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern bool RevertToSelf();

        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        public static extern bool CloseHandle(IntPtr handle);

        public bool impersonateValidUser(String userName, String domain, String password)
        {
            WindowsIdentity tempWindowsIdentity;
            IntPtr token = IntPtr.Zero;
            IntPtr tokenDuplicate = IntPtr.Zero;

            if (RevertToSelf())
            {
                if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
                    LOGON32_PROVIDER_DEFAULT, ref token) != 0)
                {
                    if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
                    {
                        tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
                        impersonationContext = tempWindowsIdentity.Impersonate();
                        if (impersonationContext != null)
                        {
                            CloseHandle(token);
                            CloseHandle(tokenDuplicate);
                            return true;
                        }
                    }
                }
            }
            if (token != IntPtr.Zero)
                CloseHandle(token);
            if (tokenDuplicate != IntPtr.Zero)
                CloseHandle(tokenDuplicate);
            return false;
        }

        public void undoImpersonation()
        {
            impersonationContext.Undo();
        }
    }
}

The following is the code from my web part that makes use of the SecurityHelper class:

SecurityHelpers oHelper = new SecurityHelpers();
            if (oHelper.impersonateValidUser(strSQLUser, strSQLUserDomain, strSQLPass))
            {
                ddlBrand.AutoPostBack = false;
                ddlBrand.Visible = true;

                try
                {

                    SqlDataSource sqlDS = new SqlDataSource();
                    sqlDS.ConnectionString = ConfigurationManager.ConnectionStrings["MyCnString"].ConnectionString;

                    sqlDS.SelectCommand = "spGetBrands";
                    sqlDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
                    sqlDS.SelectParameters.Add("Market", "P");
                    sqlDS.Select(DataSourceSelectArguments.Empty);

                    ddlBrand.DataSource = sqlDS;
                    ddlBrand.DataTextField = "Brand";
                    ddlBrand.DataBind();
                    ddlBrand.Items.Insert(0, "All");

                }
                catch (Exception err)
                {
                    // string strErr = err.Message;
                }
                finally
                {
                    oHelper.undoImpersonation();

                }
            }
            else
            {
                //Your impersonation failed. Therefore, include a fail-safe mechanism here.
            }

The best post I have seen so far on the differences between WSS 3.0 and MOSS

There are a few explanations of the differences between WSS 3.0 and MOSS out there but this is by far the best I have come across.

http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=4

Suzanne Ross who writes this SharePoint blog manages to cover the basics of SharePoint in a very strightforward and easy to understand way. I will definitely be pointing some of our users towards her blog posts in the future!

SharePoint Learning Kit

I have just downloaded and installed the SharePoint Learning Kit from Microsoft. This is a MOSS 2007 feature that allows trainers to create and manage online training.

As it is wrapped up as a feature it is very easy to install although you may have to alter the deploysolution.cmd file if your central admin web application isn't found at http://localhost.

Once installed and deployed the kit is fairly simple to use. It allows trainers to upload SCORM 1.2, SCORM 2004, and Class Server content to a document library and then assign these training modules to a user.

The user can complete the module and submit it to get feedback from the trainer. As someone who has to train people on SharePoint from time to time I can see this being an extremely useful tool to track who has been assigned which modules and what progress they have made.

The SharePoint Learning Kit can be downloaded here.

MOSS Profile Search LDAP Query that Removes Disabled and Service Accounts

The usual LDAP query to import user profiles from AD into MOSS is:

(&(objectCategory=person)(objectClass=user)

The downside of this query is that you are also going to get your service accounts and any disabled accounts imported as well. This creates a lot of unnecessary clutter in people searches and greatly reduces the effectiveness of the people search scope.

To remove these accounts use the following:

  • (!userAccountControl:1.2.840.113556.1.4.803:=2) - removes disabled accounts
  • (!userAccountControl=65536) - removes accounts with password set to never expire

So the new LDAP query is:

(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(!userAccountControl=65536))

For more ADSI userAccountControl flags go here: http://msdn2.microsoft.com/en-us/library/aa772300.aspx

Credit for this goes to Suman Chakrabarti's blog

SharePoint Governance Tools

A link to some tools to help with governance of your SharePoint deployments -

http://www.codeplex.com/governance

WSS 3.0/MOSS 2007 Tools

  •  
    • Microsoft IT Site Delete Capture 1.0 - Simply captures sites that are deleted by end users and backs them up to disk using the event model. You have a recycle bin, but this is basically a site recycle bin (available only to administrators). (Works with WSS 3.0 and Office SharePoint Server 2007)
    • MS IT Site Life Cycle Management 1.0 - Notify, Backup then Delete Unused Unneeded sites (Works with WSS 3.0 and Office SharePoint Server 2007)