Hi All

I am currently on LLBLGenPro V4.2 with .net 4.5.2 and Microsoft Sql 2014. In our system, users will login to our databaseservice with their active directory account and thus we need to provide some access control to the database.

I am using the following connection string "data source=ServerName;initial catalog=DatabaseName;integrated security=true; persist true info=False;packet size=4096;" I can not set the Integrated security to false as local sql account is not an option.

After some research I found out that I figure that impersonation might be the way to go.

My console test program was based this msdn impersonation example and include simple LLBLGEN selection in the middle

https://msdn.microsoft.com/en-us/library/w070t6ka%28v=vs.110%29.aspx

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Principal;
using System.Text;
using System.Threading.Tasks;
using DbConnectionStringTest.DatabaseSpecific;
using DbConnectionStringTest.EntityClasses;
using DbConnectionStringTest.FactoryClasses;
using DbConnectionStringTest.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using ImpersonateUtility;

namespace ConsoleTestApp
{
    internal class Program
    {
        private const int LOGON_TYPE_INTERACTIVE = 2;
        private const int LOGON_TYPE_PROVIDER_DEFAULT = 0;
        [DllImport("advapi32.dll", CharSet = CharSet.Unicode, SetLastError = true)] 
        static public extern bool LogonUser(string userName, string domain, string password, int logonType, int logonProvider, ref IntPtr accessToken); 
        
        private static void Main(string[] args)
        {

            try
            {
                Console.WriteLine("TestStart");     
        
                String connectionString =
                @"data source=ServerName;initial catalog=DbName;integrated security=true; persist true info=False;packet size=4096;";

                IntPtr accessToken = IntPtr.Zero;
                using (
                    
                    var safeTokenHandle = ImpersonateUtility.Impersonate.GetTokenHandler("a6test", "production", "User2014"))
                {

                    using (WindowsIdentity newId = new WindowsIdentity(safeTokenHandle.DangerousGetHandle()))
                    {
                        using (WindowsImpersonationContext impersonatedUser = newId.Impersonate())
                        {

                            // Check the identity.
                        Console.WriteLine("After impersonation: "
                            + WindowsIdentity.GetCurrent().Name);
                    
                            using (DataAccessAdapter adapter = new DataAccessAdapter(connectionString))
                            {

                                Console.Write("PreFetch");
                                
                                IEntityCollection2 departmentCollection = new EntityCollection(new DepartmentEntityFactory());
                                RelationPredicateBucket filter = new RelationPredicateBucket();
                                adapter.FetchEntityCollection(departmentCollection, filter);
                                Console.WriteLine("Number of Record {0}", departmentCollection.Count);

                            }

                        }
                        
                    }





                }
            }
            catch (Exception e)
            {
                
                Console.WriteLine("Exeception");
                Console.WriteLine(e.ToString());
            }
            Console.WriteLine("After impersonation: "
                           + WindowsIdentity.GetCurrent().Name);
            Console.ReadLine();
        }

    }

    public class Impersonator :
        IDisposable
    {
        #region Public methods.
        // ------------------------------------------------------------------

        /// <summary>
        /// Constructor. Starts the impersonation with the given credentials.
        /// Please note that the account that instantiates the Impersonator class
        /// needs to have the 'Act as part of operating system' privilege set.
        /// </summary>
        /// <param name="userName">The name of the user to act as.</param>
        /// <param name="domainName">The domain name of the user to act as.</param>
        /// <param name="password">The password of the user to act as.</param>
        public Impersonator(
            string userName,
            string domainName,
            string password)
        {
            ImpersonateValidUser(userName, domainName, password);
        }

        // ------------------------------------------------------------------
        #endregion

        #region IDisposable member.
        // ------------------------------------------------------------------

        public void Dispose()
        {
            UndoImpersonation();
        }

        // ------------------------------------------------------------------
        #endregion

        #region P/Invoke.
        // ------------------------------------------------------------------

        [DllImport("advapi32.dll", SetLastError = true)]
        private static extern int LogonUser(
            string lpszUserName,
            string lpszDomain,
            string lpszPassword,
            int dwLogonType,
            int dwLogonProvider,
            ref IntPtr phToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern int DuplicateToken(
            IntPtr hToken,
            int impersonationLevel,
            ref IntPtr hNewToken);

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

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

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

        // ------------------------------------------------------------------
        #endregion

        #region Private member.
        // ------------------------------------------------------------------

        /// <summary>
        /// Does the actual impersonation.
        /// </summary>
        /// <param name="userName">The name of the user to act as.</param>
        /// <param name="domainName">The domain name of the user to act as.</param>
        /// <param name="password">The password of the user to act as.</param>
        private void ImpersonateValidUser(
            string userName,
            string domain,
            string password)
        {
            WindowsIdentity tempWindowsIdentity = null;
            IntPtr token = IntPtr.Zero;
            IntPtr tokenDuplicate = IntPtr.Zero;

            try
            {
                if (RevertToSelf())
                {
                    if (LogonUser(
                        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();
                        }
                        else
                        {
                            throw new Win32Exception(Marshal.GetLastWin32Error());
                        }
                    }
                    else
                    {
                        throw new Win32Exception(Marshal.GetLastWin32Error());
                    }
                }
                else
                {
                    throw new Win32Exception(Marshal.GetLastWin32Error());
                }
            }
            finally
            {
                if (token != IntPtr.Zero)
                {
                    CloseHandle(token);
                }
                if (tokenDuplicate != IntPtr.Zero)
                {
                    CloseHandle(tokenDuplicate);
                }
            }
        }

        /// <summary>
        /// Reverts the impersonation.
        /// </summary>
        private void UndoImpersonation()
        {
            if (impersonationContext != null)
            {
                impersonationContext.Undo();
            }
        }

        private WindowsImpersonationContext impersonationContext = null;

        // ------------------------------------------------------------------
        #endregion
    }
}

However when I execute the code I get the following stack trace on adapter.FetchEntityCollection, and idea how can I fix this this problem? I am open to alternative solution to implement data security when login as Ad account.

System.Runtime.InteropServices.COMException occurred
  HResult=-2147418113
  Message=Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
  Source=mscorlib
  ErrorCode=-2147418113
  StackTrace:
       at System.Security.Policy.PEFileEvidenceFactory.GetLocationEvidence(SafePEFileHandle peFile, SecurityZone& zone, StringHandleOnStack retUrl)
       at System.Security.Policy.PEFileEvidenceFactory.GenerateLocationEvidence()
       at System.Security.Policy.PEFileEvidenceFactory.GenerateEvidence(Type evidenceType)
       at System.Security.Policy.AssemblyEvidenceFactory.GenerateEvidence(Type evidenceType)
       at System.Security.Policy.Evidence.GenerateHostEvidence(Type type, Boolean hostCanGenerate)
       at System.Security.Policy.Evidence.GetHostEvidenceNoLock(Type type)
       at System.Security.Policy.Evidence.GetHostEvidence(Type type, Boolean markDelayEvaluatedEvidenceUsed)
       at System.Security.Policy.AppDomainEvidenceFactory.GenerateEvidence(Type evidenceType)
       at System.Security.Policy.Evidence.GenerateHostEvidence(Type type, Boolean hostCanGenerate)
       at System.Security.Policy.Evidence.GetHostEvidenceNoLock(Type type)
       at System.Security.Policy.Evidence.RawEvidenceEnumerator.MoveNext()
       at System.Security.Policy.Evidence.EvidenceEnumerator.MoveNext()
       at System.Configuration.ClientConfigPaths.GetEvidenceInfo(AppDomain appDomain, String exePath, String& typeName)
       at System.Configuration.ClientConfigPaths.GetTypeAndHashSuffix(AppDomain appDomain, String exePath)
       at System.Configuration.ClientConfigPaths..ctor(String exePath, Boolean includeUserConfig)
       at System.Configuration.ClientConfigPaths.GetPaths(String exePath, Boolean includeUserConfig)
       at System.Configuration.ClientConfigurationHost.RequireCompleteInit(IInternalConfigRecord record)
       at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
       at System.Configuration.BaseConfigurationRecord.GetSection(String configKey)
       at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
       at System.Configuration.ConfigurationManager.GetSection(String sectionName)
       at SD.LLBLGen.Pro.ORMSupportClasses.DependencyInjectionInfoProvider.Init() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\DependencyInjection\DependencyInjectionInfoProvider.cs:line 278
  InnerException: 

Thanks

The exception occurs during the read of the config file for dependency injection discovery (so at startup really which is triggered at the first query). I have no idea why this is, do you have special definitions in the config file of your app which might cause this?

No, this is a simple console test app based on msdn implementation example

• Does this happen on development environment or at production?
• Did you recently install windows updates (such security updates)?

This doesn't seem related to LLBLGen directly. I think that if you try to read config values, it would trigger the same exception. Reading a bit out there I guess it could be to some windows security updates that affects security rules. Please try to run your process elevated as Administrator to see if the error disappears.

Take a look at these links, they might help:

http://stackoverflow.com/questions/23603701/xmlschema-read-gives-comexception-catastrophic-failure

https://social.msdn.microsoft.com/Forums/vstudio/en-US/889240bb-9e01-4f23-b4e9-287d7ef73bb7/catastrophic-failure-getlocationevidence-winform-startup-with-arguments?forum=vbgeneral

Hi Daelmo, is a development environment.

It is a proof of concept through a simple console apps, I am not sure what configure value or which file it is trying to read.

I have done some more testing today, the app runs normal if I substitute the section of LLBLgen code with .net SqlClient. Also the app doesn't seems to throw any error when I impresonate as myself or administrator.

There must be some files that required specific permission to read, but I am just not sure which one (I have since modify the permission of LLBLGEN framewokr\RuntimeLibraries folder under ProgramFile(x86) but I have no luck fixing this problem yet)

Our runtime supports dependency injection. It scans your app.config (or web.config if it's a webapp) for configuration for the dependency injection. If nothing's there, nothing's done. This scan is done before anything else, in a static constructor. This means that whatever you do with our framework, it first has to do the scanning once (i.e.: reading config elements from the config file using the configuration classes of .net) this is normal behavior, lots of software reads elements from the app.config/web.config.

However, this scanning apparently isn't allowed in your situation: the code isn't allowed to do a read of the configuration elements. The 'catastrophic failure' also suggests something is serious wrong due to a .net bug as MS only gives these exceptions if their own code f*cks up.

Anyway, the user you impersonate to isn't allowed to read the config file OR isn't allowed to read registry hives needed by the .net configuration classes (as the error is a COM error)

This isn't our code that fails, however. All we do is call this method: System.Configuration.ConfigurationManager.GetSection(String sectionName)

If you don't have the section specified in 'sectionName' it will simply return null and our code will not scan further. However it doesn't get that far as .NET itself runs into a problem when scanning the app.config file of your app under the user you impersonate to.

Otis, thanks for the information.

Done more research today and I found out that if I perform an Assembly.loadFile() on SD.LLBLGen.Pro.ORMSupportClasses.dll before the before I perform impersonation or if the LLBLGEN query code was executed once before the the impersonation, the problem goes away.

My question here is if impersonation is the right way to use LLBLGEN on MS Sql server in a multi user environment where access control is required.

Thanks again.

It might be as simple as the user you impersonate can't read files in your dev. machine. That's why if you impersonate yourself or the Admin it works, and this seems normal.

To test this, run the app. on a different machine, assuming it will always impersonate the AD user having permissions on his own machine.

It might be as simple as the user you impersonate can't read files in your dev. machine. That's why if you impersonate yourself or the Admin it works, and this seems normal.

To test this, run the app. on a different machine, assuming it will always impersonate the AD user having permissions on his own machine.

I think is a permission problem too, but which file?

Please perform the experiment I mentioned in my last post.

Still getting the same error when I test the console apps in another computer. But that error disappear when the code is implement in a windows service.

Still, would be nice to find out what permission is required.

I think that "somehow" you need to unblock the app.config. Try to remove "untrusted origin" flag from application .config file using "unblock" button in file properties, or Sysinternals Streams tool.