CODEWITHSUNDEEP
asp.netsql-server
ChrisM
ChrisM

Reputation: 11

SQL Server resets aspnetdb file permissions from time to time

My aspnetdb database's (the database that get's created when you use asp.net's membership provider) windows file permissions keeps getting reset after a while. This happened on SQL Server 2005 Express and SQL Server 2008 Express.

The database is accessed via an asp.net website. When this happens, the website says login invalid and attempting to reset the password produces an error saying unsuccessful. When I examine the the windows file permissions for the aspnetdb.mdf, all the user and group permissions are gone. The only way to get things working again is by restoring the aspnetdb file from backup.

I've tried setting autoclose and autoshrink on the database to false and passing the -t1802 option to SQL Server and this problem still comes up from time to time. The website that accesses the database is running on IIS 7 and asp.net 3.5.

EDIT: My connections strings are:

<add name="LocalSqlServer" connectionString="data source=.\sqlexpress;Integrated Security=SSPI;initial catalog=aspnetdb"/>

<add name="ConnectionString" connectionString="Data Source=(local)\sqlexpress;Initial Catalog=NetDevices;Integrated Security=True" ProviderName="System.Data.SqlClient"/>

I am not dynamically attaching the database, I attach the aspnetdb.mdf file explicity in sql management express.

Upvotes: 1

Views: 239

Answers (1)

marc_s
marc_s

Reputation: 754220

In my opinion, the whole attach a .mdf database file approach to using a SQL Server database is severely flawed.

There are a multitude of potential problems that lots of folks are seeing - the permissions being reset is a new, additional one.

In my opinion, the real solution would be to

  1. install SQL Server Express (and you've already done that anyway if you're using this approach now)

  2. install SQL Server Management Studio Express

  3. create your database in SSMS Express, give it a logical name (e.g. ASPNETDB)

  4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

    Data Source=.\\SQLEXPRESS;Database=ASPNETDB;Integrated Security=True

    and everything else is exactly the same as before...

Upvotes: 2

Related Questions