Unable to connect to SQL database in AppData when deployed to web server

Question

I have a ASP.Net program written in C# that uses a database stored in the AppData directory, the program works perfectly on my development (Win7\SQL 2008R2) when I deploy to a Win2008R2\SQl 2008 R2 server the program is unable to connect to the database. I'm using the connection string below. Several sites suggest I set the Application Pool to use 'Network Service' as this has Modify permission to the AppData directory, I have done this but it still won't work, another site suggested I Application Pool to "Load User Profile = True" but still no joy.

I'm pulling my hair out with what else to try, can anyone help?

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\DB.mdf;User Instance=true

I get "Object reference not set to an instance of an object." in my app and "An attempt to attach an auto-named database for file C:\inetpub\wwwroot\ADPhonebook\App_Data\DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." in the Application Event Log

I really appreciate any advice, thanks. Steven

Duplicate of Can't access SQL Server file in the server machine. See my answer and recommendation (forget the AttachDbFileName=/UserInstance= hack - attach MDF to the SQL Server instance instead) — marc_s
– marc_s, Commented Mar 20, 2011 at 17:14
When using "server=Venus\SQLExpress;Database=DB.mdb;Integrated Security=SSPI" I get "Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]". Any ideas marc? Thanks — StevenL
– StevenL, Commented Mar 20, 2011 at 17:28
@StevenL: You have to specify the name of your db without the .mdb extention if you specify the connection string like this (it has to be the exact name of the db you attached). — fretje
– fretje, Commented Mar 20, 2011 at 17:32
@stevenL: the name of a database is just a name (that you choose when you attach the file to the SQL Server) - no extension (like with files, or MS-Access "databases") — marc_s
– marc_s, Commented Mar 20, 2011 at 18:09

fretje · Accepted Answer · 2011-03-20 17:12:51Z

1

As Martin points out in the comments, it would be interesting if you posted the exact error message (and maybe the full exception detail) you are getting.

But problems like this have mostly to do with rights at the OS level.

So make sure that the user (identity) of the Application Pool has full access (ntfs) rights to the .mdf file, and try again.

Edit (after you posted the exact message):
Ok, I guess this means that on the server you're deploying to, there is already a database called DB on that Sql Server instance. So you will have to remove or rename that one in order to be able to attach a database with that name.
I think the user rights problem is also still possible though.

edited Mar 20, 2011 at 17:12

answered Mar 20, 2011 at 17:03

fretje

8,3722 gold badges52 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StevenL Over a year ago

I've double checked that Network Service has Modify on the .mdb file. I've updated my post to show the errors. Thanks

fretje Over a year ago

@StevenL: Are you sure that the application pools identity is "Network Service"? Also, I'm not sure that "modify" is enough for this. Have you tried "full access"?

Collectives™ on Stack Overflow

Unable to connect to SQL database in AppData when deployed to web server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related