deploy asp.net application with sql server express database

Discussion in 'ASP .Net' started by Loane Sharp, Sep 24, 2005.

  1. Loane Sharp

    Loane Sharp Guest

    Hi there

    I had no luck in getting the following question answered in the SQL Server
    newsgroups. Please point me in the right direction.

    Background: I am using the "System.Data.SqlClient" namespace and a
    SqlConnection() object to connect to a local SQL Server 2005 Express
    database from within VB code contained in <SCRIPT /> tags in an ASP.NET
    page. I use Windows authentication (ie. "Integrated Security=SSPI;") to
    connect to the database, and Windows then uses the (local)\ASPNET account to
    make a connection.

    Problem: Every week there is a new database to attach. Making use of the
    XCopy functionality with AutoClose enabled, I simply delete the old .mdf
    file, copy the new .mdf file to that directory, and then let the
    "AttachDBFilename=..." part of the connection string attach the database.
    The problem is, the database file I want to attach is created on a different
    SQL Server, and as such does not contain login and permissions for the local
    machine's ASPNET user account. At present I am manually adding the login
    (exec sp_grantlogin '(local)\ASPNET') and user (exec sp_grantdbaccess
    '(local)\ASPNET') each time I attach a new database. Surely there must be an
    automatic way?! I can but I'd prefer not to give the ASPNET account login
    and database access from the SQL Server that I use to create the databases,
    since that would mean keeping track of the various user, machine and network
    names, which would be a nightmare.

    Please help!

    Best regards
    Loane
    Loane Sharp, Sep 24, 2005
    #1
    1. Advertising

  2. Loane Sharp

    Jéjé Guest

    if your aspnet account is a "system administrator" then this account should
    access all databases.


    "Loane Sharp" <> wrote in message
    news:...
    > Hi there
    >
    > I had no luck in getting the following question answered in the SQL Server
    > newsgroups. Please point me in the right direction.
    >
    > Background: I am using the "System.Data.SqlClient" namespace and a
    > SqlConnection() object to connect to a local SQL Server 2005 Express
    > database from within VB code contained in <SCRIPT /> tags in an ASP.NET
    > page. I use Windows authentication (ie. "Integrated Security=SSPI;") to
    > connect to the database, and Windows then uses the (local)\ASPNET account
    > to
    > make a connection.
    >
    > Problem: Every week there is a new database to attach. Making use of the
    > XCopy functionality with AutoClose enabled, I simply delete the old .mdf
    > file, copy the new .mdf file to that directory, and then let the
    > "AttachDBFilename=..." part of the connection string attach the database.
    > The problem is, the database file I want to attach is created on a
    > different
    > SQL Server, and as such does not contain login and permissions for the
    > local
    > machine's ASPNET user account. At present I am manually adding the login
    > (exec sp_grantlogin '(local)\ASPNET') and user (exec sp_grantdbaccess
    > '(local)\ASPNET') each time I attach a new database. Surely there must be
    > an
    > automatic way?! I can but I'd prefer not to give the ASPNET account login
    > and database access from the SQL Server that I use to create the
    > databases,
    > since that would mean keeping track of the various user, machine and
    > network
    > names, which would be a nightmare.
    >
    > Please help!
    >
    > Best regards
    > Loane
    >
    >
    >
    >
    Jéjé, Sep 24, 2005
    #2
    1. Advertising

  3. Loane Sharp

    Loane Sharp Guest

    isn't that a bit dangerous?


    > if your aspnet account is a "system administrator" then this account
    > should access all databases.
    Loane Sharp, Sep 24, 2005
    #3
  4. Loane Sharp

    Jéjé Guest

    yeap...
    the only other solution is to restore the access to the user like you do it


    "Loane Sharp" <> wrote in message
    news:%23$...
    > isn't that a bit dangerous?
    >
    >
    >> if your aspnet account is a "system administrator" then this account
    >> should access all databases.

    >
    >
    Jéjé, Sep 25, 2005
    #4
  5. Loane Sharp

    Chris Botha Guest

    How about impersonation? If you are not familiar with it, the declaration
    comes in the web.config file inside the <system.web> section and to
    impersonate the connecting user, use
    <identity impersonate="true"/>
    or to impersonate a specific user, use
    <identity impersonate="true" userName="domain\UserName"
    password="whatever"/>


    "Loane Sharp" <> wrote in message
    news:...
    > Hi there
    >
    > I had no luck in getting the following question answered in the SQL Server
    > newsgroups. Please point me in the right direction.
    >
    > Background: I am using the "System.Data.SqlClient" namespace and a
    > SqlConnection() object to connect to a local SQL Server 2005 Express
    > database from within VB code contained in <SCRIPT /> tags in an ASP.NET
    > page. I use Windows authentication (ie. "Integrated Security=SSPI;") to
    > connect to the database, and Windows then uses the (local)\ASPNET account
    > to
    > make a connection.
    >
    > Problem: Every week there is a new database to attach. Making use of the
    > XCopy functionality with AutoClose enabled, I simply delete the old .mdf
    > file, copy the new .mdf file to that directory, and then let the
    > "AttachDBFilename=..." part of the connection string attach the database.
    > The problem is, the database file I want to attach is created on a
    > different
    > SQL Server, and as such does not contain login and permissions for the
    > local
    > machine's ASPNET user account. At present I am manually adding the login
    > (exec sp_grantlogin '(local)\ASPNET') and user (exec sp_grantdbaccess
    > '(local)\ASPNET') each time I attach a new database. Surely there must be
    > an
    > automatic way?! I can but I'd prefer not to give the ASPNET account login
    > and database access from the SQL Server that I use to create the
    > databases,
    > since that would mean keeping track of the various user, machine and
    > network
    > names, which would be a nightmare.
    >
    > Please help!
    >
    > Best regards
    > Loane
    >
    >
    >
    >
    Chris Botha, Sep 25, 2005
    #5
  6. Loane Sharp

    Loane Sharp Guest

    thanks very much Chris, Jeje, all worked perfectly. Best regards Loane
    Loane Sharp, Sep 25, 2005
    #6
    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. Loane Sharp
    Replies:
    2
    Views:
    687
    Loane Sharp
    Oct 17, 2005
  2. Michael
    Replies:
    2
    Views:
    9,052
    magscy
    Jul 19, 2009
  3. Keith
    Replies:
    1
    Views:
    2,323
    Keith
    Jul 3, 2006
  4. Nick
    Replies:
    5
    Views:
    410
  5. AAaron123
    Replies:
    6
    Views:
    2,511
    AAaron123
    Oct 28, 2008
Loading...

Share This Page