Is the login control with sql server express a known bug

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

  1. Hello!

    Here is some background information:
    I use windows XP pro with a Sql Server express 2005 database with Sql Server
    Management Studio Express
    I use VisualStudio(VS) 2005

    I use a file system web site that is using the built in Development Web
    server.
    I have two aspx files one to use when logging in and the other to redirect
    to when I have been successully authenticated

    I have implemented forms authentication by using ASP.NET Web Site
    Administration Tool which
    create an ASPNETDB.MDF and a web.config file
    This Web.config file look like this after having been edited
    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
    <system.web>
    <authorization>
    <allow users="John" />
    <deny users="?" />
    </authorization>
    <authentication mode="Forms">
    <forms loginUrl="LoginForm.aspx" timeout="5" cookieless="AutoDetect"
    protection="All" />
    </authentication>
    </system.web>
    </configuration>


    So If I run the web application from VisualStudio 2005 everything works
    fine.

    If I now start Internet explorer and enter this url
    http://localhost/Northwind/customerdata.aspx
    the login form is displayed. I enter my crededentials(userName,password) and
    then this error occur
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.


    I have used Sql Server Management Studio Express to attach to the database
    located at F:\NORTHWIND\APP_DATA\ASPNETDB.MDF and
    found that everything seems to be right in the ASPNETDB database.
    For example user guest is defined so ASPNET can use this user.
    I know this because in another data named Northwind I don't have user ASPNET
    defined because user guest is used by ASPNET.

    Some information concerning login control and SQL Server express that I have
    found claim that there is a bug when
    having error like Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.

    Does anybody have some kind information that give a solution to my problem
    why I can't
    use IIS and forms authentication when having the database ASPNETDB.MDF ?

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

  2. Tony Johansson

    Patrice Guest

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


    For now it seems just that the ASPNET account (under which IIS 5 runs by
    default) is not allowed to access the ASPNETDB database.

    Have you already checked this ? (the problem is
    http://support.microsoft.com/kb/316989/en-us, method 3 is this article is
    basically : http://support.microsoft.com/kb/815154/en-us).

    It works in VS because the development web server runs under your own
    account...

    --
    Patrice
    Patrice, Jan 1, 2010
    #2
    1. Advertising

  3. This article http://support.microsoft.com/kb/815154/en-us).
    doesn't match SQL Server Express because for example Database Access tab
    doesn't exist in SQL Server express

    //Tony

    "Patrice" <http://scribe-en.blogspot.com/> skrev i meddelandet
    news:%...
    >> Cannot open user default database. Login failed.
    >> Login failed for user 'HEMPC\ASPNET'.

    >
    > For now it seems just that the ASPNET account (under which IIS 5 runs by
    > default) is not allowed to access the ASPNETDB database.
    >
    > Have you already checked this ? (the problem is
    > http://support.microsoft.com/kb/316989/en-us, method 3 is this article is
    > basically : http://support.microsoft.com/kb/815154/en-us).
    >
    > It works in VS because the development web server runs under your own
    > account...
    >
    > --
    > Patrice
    >
    Tony Johansson, Jan 1, 2010
    #3
  4. When I right click on HEMPC\ASPNET under Security->Login dialog
    Login Properies is shown with these categories
    General
    Server Roles
    User Mappning
    Securables
    Status

    Can somebody tell me what I must change in these five sections to solve
    problem
    Cannot open user default database. Login failed.
    Login failed for user 'HEMPC\ASPNET'.
    when using IIS which is using account ASPNET to access ASPNETDB.MDF

    //Tony





    "Patrice" <http://scribe-en.blogspot.com/> skrev i meddelandet
    news:%...
    >> Cannot open user default database. Login failed.
    >> Login failed for user 'HEMPC\ASPNET'.

    >
    > For now it seems just that the ASPNET account (under which IIS 5 runs by
    > default) is not allowed to access the ASPNETDB database.
    >
    > Have you already checked this ? (the problem is
    > http://support.microsoft.com/kb/316989/en-us, method 3 is this article is
    > basically : http://support.microsoft.com/kb/815154/en-us).
    >
    > It works in VS because the development web server runs under your own
    > account...
    >
    > --
    > Patrice
    >
    Tony Johansson, Jan 1, 2010
    #4
  5. I do have SQL Server management studio express 2005 but the description is
    not possible to use because
    it differ so much between the SQL Server express and the the SQL Server that
    is more advanced

    //Tony

    "Patrice" <http://www..chez.com/scribe/> skrev i meddelandet
    news:4b3e1d99$0$17487$...
    >> This article http://support.microsoft.com/kb/815154/en-us).
    >> doesn't match SQL Server Express because for example Database Access tab
    >> doesn't exist in SQL Server express

    >
    > Do you mean SQL Server Management Studio Express or the database tools
    > provided by VS ? If not already done, download
    > http://www.microsoft.com/downloads/...FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b
    > (or the 2005 version depending on which version you are using).
    >
    > It's perhaps not 100% what is described but the UI should be clear enough
    > to find out how to do the same steps (I don't have it handy right now).
    >
    > --
    > Patrice
    >
    >
    Tony Johansson, Jan 1, 2010
    #5
  6. Tony Johansson

    Norman Yuan Guest

    You need to know a bit more on ASP.NET security, such as how to configure
    the ASP.NET, IIS and which account is running the ASP.NET.

    In your case, when you run the app from VS, the VS built-in web server is
    used to serve the ASP.NET app, and your account (it is likely you are an
    Admin user of your computer), so, you do not have a problem.

    When you run the app with IIS, it is complicated issue: the account used to
    run your ASP.NET app could be different. I am not going to go through all
    the possibilities. For example, based on your settings as you described
    (also from your other posts), if the ASP.NET app is configured to use
    "Windows" authentication mode, when the IIS is allowed "Anonymous" access,
    "Machine\IUer_XXXX" account would be the account to run the ASP.NET app;
    when the "Anonymous" is not allowed, "Machine\ASPNET" account is used; while
    "Anonymous" is not allowed and "impersonation" is enabled, the client user's
    window account is used...

    So, firstly, do some study to make sure, as ASP.NET app developer, you need
    to make sure you know which account runs your application and you can choose
    a configuration to have right account to run it. Then it wuold be easy to
    give that account a proper access to resources, such as SQL Server.

    As I replied you in the other post, it unfortunately proved I was correct:
    your ASP.NET uses SQL Server Express' USER INSTANCE because you followed a
    book example without knowing what USER INSTANCE is. Most ASP.NET examples I
    have seen do a bad job by irresposibly using SQL Server Express' USER
    INSTANCE without emphasizing USER INSANCE is a complicated concept and
    newbie should always avoid it until having full understand it. Try to remove
    "USER INSTANCE=True" from the ConnectionString and manually attach the
    Northwind database to SQL Server Express instance permanetly, once for all.
    Then assign access to the database to proper account (your ASP.NET running
    account, you should know it as developer, be it ASPNET account or other
    account YOU configured).

    "Tony Johansson" <> wrote in message
    news:run%m.14177$...
    > Hello!
    >
    > Here is some background information:
    > I use windows XP pro with a Sql Server express 2005 database with Sql
    > Server Management Studio Express
    > I use VisualStudio(VS) 2005
    >
    > I use a file system web site that is using the built in Development Web
    > server.
    > I have two aspx files one to use when logging in and the other to redirect
    > to when I have been successully authenticated
    >
    > I have implemented forms authentication by using ASP.NET Web Site
    > Administration Tool which
    > create an ASPNETDB.MDF and a web.config file
    > This Web.config file look like this after having been edited
    > <?xml version="1.0" encoding="utf-8"?>
    > <configuration>
    > <system.web>
    > <authorization>
    > <allow users="John" />
    > <deny users="?" />
    > </authorization>
    > <authentication mode="Forms">
    > <forms loginUrl="LoginForm.aspx" timeout="5"
    > cookieless="AutoDetect" protection="All" />
    > </authentication>
    > </system.web>
    > </configuration>
    >
    >
    > So If I run the web application from VisualStudio 2005 everything works
    > fine.
    >
    > If I now start Internet explorer and enter this url
    > http://localhost/Northwind/customerdata.aspx
    > the login form is displayed. I enter my crededentials(userName,password)
    > and then this error occur
    > Cannot open user default database. Login failed.
    > Login failed for user 'HEMPC\ASPNET'.
    >
    >
    > I have used Sql Server Management Studio Express to attach to the database
    > located at F:\NORTHWIND\APP_DATA\ASPNETDB.MDF and
    > found that everything seems to be right in the ASPNETDB database.
    > For example user guest is defined so ASPNET can use this user.
    > I know this because in another data named Northwind I don't have user
    > ASPNET defined because user guest is used by ASPNET.
    >
    > Some information concerning login control and SQL Server express that I
    > have found claim that there is a bug when
    > having error like Cannot open user default database. Login failed.
    > Login failed for user 'HEMPC\ASPNET'.
    >
    > Does anybody have some kind information that give a solution to my problem
    > why I can't
    > use IIS and forms authentication when having the database ASPNETDB.MDF ?
    >
    > //Tony
    >
    >
    Norman Yuan, Jan 1, 2010
    #6
  7. I added ASPNET into the admin group but it didn't solved my problem.
    I have done some changes so I might be able to understand what is happening.
    I hope somebody can explain the question at the bottom after reading this
    text

    I have four items that is interest here.
    ASPNETDB.MDF
    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 I didn't had 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





    "Norman Yuan" <> skrev i meddelandet
    news:%...
    > You need to know a bit more on ASP.NET security, such as how to configure
    > the ASP.NET, IIS and which account is running the ASP.NET.
    >
    > In your case, when you run the app from VS, the VS built-in web server is
    > used to serve the ASP.NET app, and your account (it is likely you are an
    > Admin user of your computer), so, you do not have a problem.
    >
    > When you run the app with IIS, it is complicated issue: the account used
    > to run your ASP.NET app could be different. I am not going to go through
    > all the possibilities. For example, based on your settings as you
    > described (also from your other posts), if the ASP.NET app is configured
    > to use "Windows" authentication mode, when the IIS is allowed "Anonymous"
    > access, "Machine\IUer_XXXX" account would be the account to run the
    > ASP.NET app; when the "Anonymous" is not allowed, "Machine\ASPNET" account
    > is used; while "Anonymous" is not allowed and "impersonation" is enabled,
    > the client user's window account is used...
    >
    > So, firstly, do some study to make sure, as ASP.NET app developer, you
    > need to make sure you know which account runs your application and you can
    > choose a configuration to have right account to run it. Then it wuold be
    > easy to give that account a proper access to resources, such as SQL
    > Server.
    >
    > As I replied you in the other post, it unfortunately proved I was correct:
    > your ASP.NET uses SQL Server Express' USER INSTANCE because you followed a
    > book example without knowing what USER INSTANCE is. Most ASP.NET examples
    > I have seen do a bad job by irresposibly using SQL Server Express' USER
    > INSTANCE without emphasizing USER INSANCE is a complicated concept and
    > newbie should always avoid it until having full understand it. Try to
    > remove "USER INSTANCE=True" from the ConnectionString and manually attach
    > the Northwind database to SQL Server Express instance permanetly, once for
    > all. Then assign access to the database to proper account (your ASP.NET
    > running account, you should know it as developer, be it ASPNET account or
    > other account YOU configured).
    >
    > "Tony Johansson" <> wrote in message
    > news:run%m.14177$...
    >> Hello!
    >>
    >> Here is some background information:
    >> I use windows XP pro with a Sql Server express 2005 database with Sql
    >> Server Management Studio Express
    >> I use VisualStudio(VS) 2005
    >>
    >> I use a file system web site that is using the built in Development Web
    >> server.
    >> I have two aspx files one to use when logging in and the other to
    >> redirect to when I have been successully authenticated
    >>
    >> I have implemented forms authentication by using ASP.NET Web Site
    >> Administration Tool which
    >> create an ASPNETDB.MDF and a web.config file
    >> This Web.config file look like this after having been edited
    >> <?xml version="1.0" encoding="utf-8"?>
    >> <configuration>
    >> <system.web>
    >> <authorization>
    >> <allow users="John" />
    >> <deny users="?" />
    >> </authorization>
    >> <authentication mode="Forms">
    >> <forms loginUrl="LoginForm.aspx" timeout="5"
    >> cookieless="AutoDetect" protection="All" />
    >> </authentication>
    >> </system.web>
    >> </configuration>
    >>
    >>
    >> So If I run the web application from VisualStudio 2005 everything works
    >> fine.
    >>
    >> If I now start Internet explorer and enter this url
    >> http://localhost/Northwind/customerdata.aspx
    >> the login form is displayed. I enter my crededentials(userName,password)
    >> and then this error occur
    >> Cannot open user default database. Login failed.
    >> Login failed for user 'HEMPC\ASPNET'.
    >>
    >>
    >> I have used Sql Server Management Studio Express to attach to the
    >> database
    >> located at F:\NORTHWIND\APP_DATA\ASPNETDB.MDF and
    >> found that everything seems to be right in the ASPNETDB database.
    >> For example user guest is defined so ASPNET can use this user.
    >> I know this because in another data named Northwind I don't have user
    >> ASPNET defined because user guest is used by ASPNET.
    >>
    >> Some information concerning login control and SQL Server express that I
    >> have found claim that there is a bug when
    >> having error like Cannot open user default database. Login failed.
    >> Login failed for user 'HEMPC\ASPNET'.
    >>
    >> Does anybody have some kind information that give a solution to my
    >> problem why I can't
    >> use IIS and forms authentication when having the database ASPNETDB.MDF ?
    >>
    >> //Tony
    >>
    >>

    >
    Tony Johansson, Jan 2, 2010
    #7
  8. Tony Johansson

    Patrice Guest

    >I do have SQL Server management studio express 2005 but the description is
    >not possible to use because
    > it differ so much between the SQL Server express and the the SQL Server
    > that is more advanced


    So we always go back to my personal preference that is using scripts. Try :
    use master
    go
    create login [YourPC\ASPNET] from windows
    go

    use aspnetdb
    go
    create user [YourPC\ASPNET]
    go

    exec sp_addrolemember 'db_datareader','YourPC\ASPNET'
    exec sp_addrolemember 'db_datawriter','YourPC\ASPNET'
    GO

    The steps are :
    - create a login (allows to connect to SQL Server) that maps to the ASPNET
    local machine account (of course replace YourPC with your machine name)
    - in the aspnetdb database, create a user that maps to this login (allows
    access to this particular db)
    - add this user in the db_datawriter, db_datareader groups so that this user
    is allowed to read and write data to this db

    In the UI you have a Securirty node under which you can create logins, under
    the db node you have another Security Node under which you can create users,
    and by showing the user properties you have checkboxes to include the user
    in particular roles.

    Also you may want to show your connection string. Even with SQL Server
    Express (that I'm using at home not at work), my personal preference is to
    run an instance (not to attach the file as needed) especially for a web
    application....

    Now the ASPNET account should be allowed to access to SQL Server Express. Do
    you have the same error message, another one (in both case, restate the
    exact error message you get) or does it work ?

    Also you may want to post your connection string. My preference for a web
    application is to connect to the instance (rather than attaching the file as
    needed) using the exact same connection string than if it were a full
    featured SQL Server Edition...

    --
    Patrice
    Patrice, Jan 2, 2010
    #8
    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. Daves
    Replies:
    1
    Views:
    364
  2. farseer

    SQL Server 2005 + SQL Server Express

    farseer, Aug 7, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    336
    farseer
    Aug 8, 2006
  3. Wolf Stephan Kappesser

    Known positioning-bug in Web Express?

    Wolf Stephan Kappesser, Aug 21, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    379
  4. Jean
    Replies:
    0
    Views:
    352
  5. Jean
    Replies:
    0
    Views:
    148
Loading...

Share This Page