Cannot open user default database. Login failed. Login failed for user 'HEMPC\ASPNET'

Discussion in 'ASP .Net' started by Tony Johansson, Jan 2, 2010.

  1. Hello!

    I have made my problem clearer because I have added more information that
    might help
    to solve my silly problem that cause
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.

    I hope somebody can explain the question at the bottom after reading this
    text

    I have four items that is interest here.
    ASPNETDB.MDF(This database is generated automatically by the ASP.NET web
    Site Administation Tool)
    CustomerData
    web.config
    The well known Northwind database


    TEST NUMBER 1 using the Northwind database running from VisualStudio
    I used the code below and run from VisualStudio using the built-in
    development web server and the result back was HEMPC\Tony
    *********************************************************
    public partial class CustomerData : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    SqlConnection sqlConn = null;
    try
    {
    ConnectionStringSettings cs =
    ConfigurationManager.ConnectionStrings["MyConnectionString"];
    string connString = cs.ConnectionString;
    sqlConn = new SqlConnection(connString);
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandText = "select suser_sname(), current_user";
    sqlCmd.Connection = sqlConn;
    sqlConn.Open();
    string svar = (string)sqlCmd.ExecuteScalar();
    Response.Write(svar);
    }
    catch (Exception)
    {
    throw;
    }
    }
    }
    Here is the connection string that was located in web.config that was used
    in Test number 1
    <connectionStrings>
    <add name="MyConnectionString"
    connectionString="Data Source=.\SQLEXPRESS;
    Initial Catalog=Northwind;
    Integrated Security=True"
    providerName="System.Data.SqlClient" />
    </connectionStrings>


    TEST NUMBER 2 using the Northwind database running IIS
    Was the same as Test 1 except that I run from IIS so the result back was
    HEMPC\ASPNET
    If I in this Test 2 didn't have any HEMPC\ASPNET defined for Security->Users
    and had disable the Guest account for the Northwind database I get this
    error message
    Cannot open database "Northwind" requested by the login. The login failed.
    Login failed for user 'HEMPC\ASPNET'.


    TEST NUMBER 3 using the ASPNETDB.MDF running from VisualStudio
    Was the same as Test 1 but had a different connectionstring see below in
    web.config the result back was HEMPC\Tony
    Why do I get this error message ???
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\Tony'.
    if I removed this attribute User Instance=True from the connection string
    <connectionStrings>
    <add name="MyConnectionString"
    connectionString="Data Source=.\SQLEXPRESS;
    AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;
    Integrated Security=True;
    User Instance=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

    TEST NUMBER 4 using the ASPNETDB.MDF running from IIS
    Was the same as Test number 3 except that I run from IIS
    When I run this I got this error message.
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.


    So as a summary we have the same Security->Logins for both the Northwind
    database and the ASPNETDB.MDF
    My second summary I have exactly the same definitions for Security->users
    for ASPNET for both the Northwind database
    and the ASPNETDB.MDF

    My first question that I hope somebody can answer
    If I removed this attribute User Instance=True from the connection string in
    Test number 3 why do I get error message
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\Tony'.

    My second question why does it work so fine when I use the Northwind
    database for both when running
    from VisualStudio and from IIS but when I use the ASPNETDB.MDF it works only
    from VisualStudio.
    I mean it's just two databases with different names and a little different
    connectionstring so how can the result be so completely different.

    My third question and the one that is most important is why do I get this
    error message
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.
    in Test Number 4

    I hope that somebody might get some bright idea about the error message
    Cannot open user default database. Login failed
    because in Test number 3 I got this error message
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\Tony'.
    when I removed this attribute User Instance=True from the connection string

    Conclusion for the problem
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.
    I mean that if ASPNET didn't have access to the ASPNETDB.MDF I would
    instead receive error message something like
    Cannot open database "ASPNETDB" requested by the login. The login failed.
    Login failed for user 'HEMPC\ASPNET' as I got in Test number 2

    //Tony
     
    Tony Johansson, Jan 2, 2010
    #1
    1. Advertising

  2. Tony Johansson

    Patrice Guest

    Good point.

    Check the default database for the ASPNET login in SQL Server Management
    Studio (under security, select the ASPNET connection and use "properties",
    the default database should be at the bottom of the ASPNET connection
    properties).

    The default database must be valid even if you specify explicitely the db
    name in the connection string (likely because it connects first to the
    default database before switching to the one defined in the connection
    string).

    --
    Patrice

    > Conclusion for the problem
    > Cannot open user default database. Login failed.
    > Login failed for user 'HEMPC\ASPNET'.
    > I mean that if ASPNET didn't have access to the ASPNETDB.MDF I would
    > instead receive error message something like
    > Cannot open database "ASPNETDB" requested by the login. The login failed.
    > Login failed for user 'HEMPC\ASPNET' as I got in Test number 2
    >
    > //Tony
    >
    >
     
    Patrice, Jan 2, 2010
    #2
    1. Advertising

  3. If I rightclick on HEMPC\ASPNET under Security->Login and take properies I
    have just as you say a combo box field
    at the bottom. In this combobox field I have these values to choose from.
    F:\NORTWIND\APP_DATA\ASPNETDB.MDF
    master
    model
    msdb
    Nortwind
    tempdb

    I just wonder which one should I choose. As you can understand this account
    ASPNET is sometimes using
    for example database Nortwind and sometimes as in my case the
    F:\NORTWIND\APP_DATA\ASPNETDB.MDF
    everything depends on what I use in the connectionstring.

    //Tony



    "Patrice" <http://scribe-en.blogspot.com/> skrev i meddelandet
    news:...
    > Good point.
    >
    > Check the default database for the ASPNET login in SQL Server Management
    > Studio (under security, select the ASPNET connection and use "properties",
    > the default database should be at the bottom of the ASPNET connection
    > properties).
    >
    > The default database must be valid even if you specify explicitely the db
    > name in the connection string (likely because it connects first to the
    > default database before switching to the one defined in the connection
    > string).
    >
    > --
    > Patrice
    >
    >> Conclusion for the problem
    >> Cannot open user default database. Login failed.
    >> Login failed for user 'HEMPC\ASPNET'.
    >> I mean that if ASPNET didn't have access to the ASPNETDB.MDF I would
    >> instead receive error message something like
    >> Cannot open database "ASPNETDB" requested by the login. The login failed.
    >> Login failed for user 'HEMPC\ASPNET' as I got in Test number 2
    >>
    >> //Tony
    >>
    >>

    >
     
    Tony Johansson, Jan 2, 2010
    #3
  4. Tony Johansson

    Patrice Guest

    And the current value was ? Just use "master" which is the default. The
    connection will use what is defined in the connection string but the default
    one needs to be valid (my guess is that the connection to SQL Server is
    established using the default database then the database defined in the
    connection string is selected, so it fails at the previous step if the
    default database is not valid).

    I notice that you are using a user intance
    (http://msdn.microsoft.com/en-us/library/ms254504(VS.80).aspx) as the
    ASPNETDB is defined by its full path. I would use the default instance
    whenever I can (such as for a web application as you have full control over
    the server). Not sure buf if aspnetdb is the default database it could
    perhaps explain the problem (as this is a user istance, the db is not
    available at all time).

    --
    Patrice

    "Tony Johansson" <> a écrit dans le message de
    news:FvJ%m.14196$...
    > If I rightclick on HEMPC\ASPNET under Security->Login and take properies I
    > have just as you say a combo box field
    > at the bottom. In this combobox field I have these values to choose from.
    > F:\NORTWIND\APP_DATA\ASPNETDB.MDF
    > master
    > model
    > msdb
    > Nortwind
    > tempdb
    >
    > I just wonder which one should I choose. As you can understand this
    > account
    > ASPNET is sometimes using
    > for example database Nortwind and sometimes as in my case the
    > F:\NORTWIND\APP_DATA\ASPNETDB.MDF
    > everything depends on what I use in the connectionstring.
    >
    > //Tony
     
    Patrice, Jan 2, 2010
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?RGF2aWQ=?=

    Cannot open user default database login failed aspnet

    =?Utf-8?B?RGF2aWQ=?=, Dec 4, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,921
    =?Utf-8?B?RGF2aWQ=?=
    Dec 4, 2005
  2. micky
    Replies:
    0
    Views:
    4,674
    micky
    Dec 13, 2005
  3. Homer
    Replies:
    3
    Views:
    7,999
    Alexey Smirnov
    Sep 25, 2007
  4. johntigner
    Replies:
    0
    Views:
    1,100
    johntigner
    May 22, 2008
  5. Max
    Replies:
    0
    Views:
    407
Loading...

Share This Page