none
EF ObjectContext Overload RRS feed

  • Question

  • Hi!,

       I have a vault wcf service that provides me the generic accounts password which i am using in DAL (different wcf service) to connect to database. I have the db entities object defined globally for the DAL wcf implementation so its initialized in the constructor. With this approach i don't have to go to vault service to get password for each DAL query.

       Here is the problem i am running into. Our company has password expiration policy set to 180 days and i can change the password in vault so that the DAL wcf service can get it. But the DAL WCF is in running state and in that case if i change the password it will fail on the next db call in DAL WCF service. What i would like to do is if the db call fails and the error is "Login failed for user XYZ" then i want to get the new password from the vault and reinitialize my db entities object(i.e. by passing connection string with new password).

    Is there anyway i can derive a class from my auto generated entities class and write some logic like this or what's the best way to do it?

     Following is example to make my question simple

    let's say i have code like following

    public class MyDALService : IMyDALService, IDisposable
    {
     MyEntities db = null;
     
     InitializeMyEntities()
     {
      // get password from vault
      db = new MyEntities(/*connection string using password from vault*/);
     }
     
     public MyDALService()
     {
      InitializeMyEntities
     }

     public List<Employee> GetEmployees()
     {
      List<Employee> employees = db.Employees.ToList();
      return employees;
     }

    }

    Here the GetEmployees will work until the password is not changed but it will fail if the password in the vault is changed. One way to resolve this will be

    public class MyDALService : IMyDALService, IDisposable
    {
     MyEntities db = null;
     
     InitializeMyEntities()
     {
      // get password from vault
      db = new MyEntities(/*connection string using password from vault*/);
     }
     
     public MyDALService()
     {
      InitializeMyEntities
     }

     public List<Employee> GetEmployees()
     {
      List<Employee> employees = null;
      
      for (int attempt = 0; attempt < 2; attempt++)
      {
       try
       {
        employees = db.Employees.ToList();
        break;
       }
       catch (Exception ex)
       {
        if (attempt == 0 && null != ex.InnerException && ex.InnerException.Message.StartsWith("Login failed for user"))
        {
         InitializeMyEntities();
        }
        else
        {
         throw new FaultException("Error");
        }
       }

      }
      
      return employees;
     }
    }

    I don’t
    like this approach as i have to write this for loop in each db interaction
    method. I don’t think it’s good for reading the code and long term maintenance.
    What I am looking for is some way to write another base class that i will derive
    from MyEntities and will do this password validation check there and the DAL
    Service will use the new base class. And the base class should be smart enough
    to do the password check and call the vault for new password on any kind of db
    operation (i.e. select, insert, delete or update).<o:p></o:p>

    Is there anyone who has gone thru this and have a better solution? Any help is appreciated.<o:p></o:p>

    Andy

    Thursday, May 23, 2013 2:42 PM

All replies

  • To your question "Is there anyway i can derive a class from my auto generated entities class and write some logic"

    Yes. Your auto generated classes are partial classes. Which means you can create a new class file and declare the entity you want to work with as partial and do your additional work.

    Can you clarify your question on the services and the architecture? For example,

    Client, Service1, Service2, Database and tell us where you have your config file that defines the connection string? Are you using Entity Framework in this solution (I am asking because you say it is auto generated entities.)

    Thursday, May 23, 2013 7:38 PM
  • My company has this vault site where I need go and setup the password for my sql account which I use to connect to my application database.

    In WCFService1 I am using EF and my connection string looks something like this, connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=ServerName;Initial Catalog=DBName;User Id=SQLUserId;Password=;MultipleActiveResultSets=True;Application Name=EntityFramework&quot;"

    If you see here I don’t have password.

    So now when my client apps make call to any operation in my WCFService1, the service need to connect to database and to do so it needs to get the password from the Vault WCFService2 which is provided by my company. To take care of this I make call to the vault service in InitializeMyEntities which I call in my constructor as you see below.

    This all works well. Let’s say my WCFService1 is being used there every day and after 180 days my company expires the password for my SQLUserId account so to prevent that I go to the vault site and change the password. Now if my WCFService1 connects to database it will get login failed error as I changed the password for my account and its changes on db servers but my WCFService1 doesn’t know about it. If I recycle IIS then it will get new password as I do that in my constructor. What I am trying to find out is how can I have my WCFService1 running all the time and have it get the latest password if it gets the “logon failure” error. If you see in my proposed code I am writing code in a loop of 2 attempts and if in the first attempt I get “logon failure” exception then I know the password is changes and I call InitializeMyEntities which will get password from the vault WCFService2 which is not part of my program, its provided by my company.

    I can see this code working as is, but the problem is I will have to write this for loop in pretty much each db operation method inside my WCFService1. That’s where I am thinking is there any way I can create a base class that derives from the class that is generated by EntityFramework and the somehow write a override which will do this looping logic so the developers don’t even know whats happening with the password level attempt.

    I hope it makes sense this time, Yes i am using Entity Framework

    public class MyDALService : IMyDALService, IDisposable
    {
    MyEntities db = null;

    InitializeMyEntities()
    {
    // get password from vault
    db = new MyEntities(/*connection string using password from vault*/);
    }

    public MyDALService()
    {
    InitializeMyEntities
    }

    public List<Employee> GetEmployees()
    {
    List<Employee> employees = null;

    for (int attempt = 0; attempt < 2; attempt++)
    {
    try
    {
    employees = db.Employees.ToList();
    break;
    }
    catch (Exception ex)
    {
    if (attempt == 0 && null != ex.InnerException && ex.InnerException.Message.StartsWith("Login failed for user"))
    {
    InitializeMyEntities();
    }
    else
    {
    throw new FaultException("Error");
    }
    }

    }

    return employees;
    }
    }

    Thursday, May 23, 2013 9:17 PM