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 -

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;

        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)
                            return true;
            if (token != IntPtr.Zero)
            if (tokenDuplicate != IntPtr.Zero)
            return false;

        public void undoImpersonation()

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;


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

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

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

                catch (Exception err)
                    // string strErr = err.Message;

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

Add comment