On Redirect - Failed to generate a user instance of SQL Server...

Question

Hello (this is a long post sorry),

I am writing a application in ASP.NET MVC 2 and I have reached a point where I am receiving this error when I connect remotely to my Server.

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

I thought I had worked around this problem locally, as I was getting this error in debug when site was redirected to a baseUrl if a subdomain was invalid using this code:

    protected override void Initialize(RequestContext requestContext)
    {
        string[] host = requestContext.HttpContext.Request.Headers["Host"].Split(':');

        _siteProvider.Initialise(host, LiveMeet.Properties.Settings.Default["baseUrl"].ToString());

        base.Initialize(requestContext);
    }

    protected override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        if (Site == null)
        {
            string[] host = filterContext.HttpContext.Request.Headers["Host"].Split(':');

            string newUrl;

            if (host.Length == 2)
                newUrl = "http://sample.local:" + host[1];
            else
                newUrl = "http://sample.local";

            Response.Redirect(newUrl, true);

        }

        ViewData["Site"] = Site;
        base.OnActionExecuting(filterContext);
    }
    public Site Site
    {
        get
        {
            return _siteProvider.GetCurrentSite();
        }
    }

The Site object is returned from a Provider named siteProvider, this does two checks, once against a database containing a list of all available subdomains, then if that fails to find a valid subdomain, or valid domain name, searches a memory cache of reserved domains, if that doesn't hit then returns a baseUrl where all invalid domains are redirected.

locally this worked when I added the true to Response.Redirect, assuming a halting of the current execution and restarting the execution on the browser redirect.

What I have found in the stack trace is that the error is thrown on the second attempt to access the database.

    #region ISiteProvider Members

    public void Initialise(string[] host, string basehost)
    {
        if (host[0].Contains(basehost))
            host = host[0].Split('.');

        Site getSite = GetSites().WithDomain(host[0]);

        if (getSite == null)
        {
            sites.TryGetValue(host[0], out getSite);
        }

        _site = getSite;
    }

    public Site GetCurrentSite()
    {
        return _site;
    }

    public IQueryable<Site> GetSites()
    {
        return from p in _repository.groupDomains
               select new Site
               {
                   Host = p.domainName,
                   GroupGuid = (Guid)p.groupGuid,
                   IsSubDomain = p.isSubdomain
               };
    }

    #endregion

The Linq query ^^^ is hit first, with a filter of WithDomain, the error isn't thrown till the WithDomain filter is attempted.

In summary: The error is hit after the page is redirected, so the first iteration is executing as expected (so permissions on the database are correct, user profiles etc) shortly after the redirect when it filters the database query for the possible domain/subdomain of current redirected page, it errors out.

Answer 1

Make sure App Pool is set to NetworkService if under windows 7 and IIS 7.5

I searched long and hard for this one, it was my error all along. It seem that at sometime someone decided to change my applciationpool settings and changed the applicationpool for my application. But for some strange reason it didn't impact me until I tried to use the redirection code.

Before adding the code all databases connected without any issues and data was pulled without error, but afterwords it didn't, so after looking at my appool, I noticed it was not set to networkservice any longer, switched, fixed.

So maybe this question should be, why would database connections work before the new code, but not after while not under networdservice user?