Sql Express DB in APP_DATA when using IIS Virtual Directory

views:

answers:

Sql Express DB in APP_DATA when using IIS Virtual Directory

I want to setup a website that uses an SQL Express .mdf file located in the APP_DATA folder. when I create the site as a file-system website, it connects to the database file without issue...

however when I create the same site in IIS and I get to the point where it wants to attach to the SQL Express database, it says it cannot connect.

I'm kind of lost as to how these are different, as I've only ever used the full SQL Server, that is creating a new database and setting connection strings. This sql express site seems to just "pick up" the local database. It appears to be doing this automatically in file-system mode...

can someone provide me with some insight as to how these are different and if a) I can do the same thing when using IIS instead of the file-system and b) how this would be done (I'm thinking permissions?)

thanks!

+1 A:

Check Server Configuration Manger on your SQL Express setting. Make sure remote connections are allowed and also check your firewall settings.

By default remote connections are not allowed in SQL Express.

Matt 2010-10-12 17:08:45

thanks, I will try that now, but are you saying that I shouldn't see any difference between file-system and IIS when it comes to sql express databases in the App_Data folder?

Josh 2010-10-12 17:09:49

No, you shouldn't. SQL Express functions mostly like SQL Server, but it uses the connection string to _tell_ SQL Server to attach the database file in your App_Data folder (instead of you doing that ahead of time). What error do you get when you try to run this site under IIS?

Coding Gorilla 2010-10-12 17:11:50

I can't see the exact error message, the site only reports "could not connect to server" I'll see if I can see the Event log or something... thanks again

Josh 2010-10-12 17:12:58

okay, it turns out that the Application Pool had the "Load User Profile" setting to "False" in IIS. This results in the error:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. [CLIENT: ]

I don't remember why this happens or what it means, or if changing it is a security risk, but I ALWAYS encounter this problem, and setting it to "True" completely fixed the problem!

can anyone tell me more about what this setting is for and why it keeps getting set to false?

Josh 2010-10-12 17:31:21

It's a security setting; loading the user profile is more secure in some situations. The most common is writing temp files, without loading the user profile the temp files would go to c:\windows\temp, and in theory could be read by another site. Loading the user profile causes those temp files to go to the user temp folder (instead of system). It also allows the loading of environment variables.

Coding Gorilla 2010-10-12 18:07:05

ansaurus

tags:

views:

answers:

Sql Express DB in APP_DATA when using IIS Virtual Directory

related questions