Use VS2005 with Sql Server 2005 and attach a .mdf (ASP 2.0)

[Guest]

I know this is a long post, please bear with me. I have been working on
this all weekend to no avail although I have done a good amount of research
(see most pertinent links that I've looked at below). Please help me get
through this!!!

I'm having some serious problems attaching a .mdf file and accessing it. I
have a WroxUntied.mdf that I want to attach and then run the website that
uses it. This is a .mdf file from wrox books. This file to works correctly
with sqlexpress, but I need to use Sql Server 2005.

I have 2 different computer setups that I've tried this on. The first is a
Windows Server 2003 R1 Domain Controller.

1) Running Win Server 2003, VS2005 and SQL Server 2005 and IIS 6.0.
2) Log into the server via a Domain account.
3) Sql Server Setup
a) I run aspnet_regsql.exe, which creates the aspnetdb database on my
computer.
b) SQL Server Configuration Manager - Allowed TCP/IP and Named Pipes
c) SQL Server Surface Area Configuration - Allowed TCP/IP and Named Pipes
e) There is a nt authority\network service login.
c) In my main login page there is NOT an aspnet user
f) I can not add the local machines NT AUTHORITY/NETWORK SERVICE (I
think this is becuase I am logged on to the domain and not the local
machine, maybe?)
4) Unzip the wrox files:
a) I unzip a zip file that has all needed files for the wrox help site.
b) I add Network Service as a user to folder. ON THIS COMPUTER I CAN
ONLY SELECT THE DOMAIN NETWORK SERVICE. THIS IS NOT THE LOCAL MACHINES
NETWORK SERVICE
5) Add a virtual dirctory:
a) I created an alias and set the directory in a virtual directory in
my default web sites.
b) I make sure to set the ASP Version to 2.0XXXX.
c) I have changed to connection string in the local WroxUnited
(application name) web.config file from:
LocalSqlServer "data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttacheDBFilename=|DataDirectory|aspnetdb.mdf;User
Instance=true
WroxUnited data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttacheDBFilename=|DataDirectory|WroxUnited.mdf;User
Instance=true
to:
LocalSqlServer "data source=localhost;Initial
Catalog=aspnetdb;Integrated Security=true;"
WroxUnited data source=localhost;Initial Catalog=WroxUnited;Integrated
Security=true;
6) WroxUnited Setup
a) Attach the WroxUnited.mdf file
b) In the security tab for the database both NT Authority/Network
Service and ASPNET are users.
7) I open up the Internet Explorer:
a) enter http://localhost/WroxUnited
b) I recived this error: A connection was successfully established with
the server, but then an error occurred during the login process. (provider:
Shared Memory Provider, error: 0 - No process is on the other end of the
pipe.)
c) I look at my sql server log and see this: Login failed for user 'NT
AUTHORITY\NETWORK SERVICE'. [CLIENT: <local machine>]

Some of my questions using the Server setup:
1) The first is obviously how can I attach this mdf and make it work?
What am I doing wrong? Why is this network service not working?
2) When I look at my Active direcotry there is not an ASPNET user.
Must I use my "SQLUser" (user I set up to use as a login for sql server)
account to develop on my domain controller?
3) If I wanted to use my laptop to access this sql server instance, how
do I set that up? That way I could use VS2005 on my laptop and remove it
from the server. I would think that this maybe a better development setup?

I think the error has something to do with local vs domain network service
accounts? But I'm not sure. I'm just trying to figure out how to attacheh
this mdf correctly to sql server and run the app.

So thinking this was the problem was with the Domain I have tried this on my
laptop. I have a very similar setup on my laptop:
1) Running windows xp pro with VS2005, SQL Sever 2005 and IIS 5.1.
2) Log in locally (not the domain).
3) Sql Server Setup
a) I run aspnet_regsql.exe, which creates the aspnetdb database on
my computer.
b) SQL Server Configuration Manager - Allowed TCP/IP and Named
Pipes, restared service.
c) SQL Server Surface Area Configuration - Allowed TCP/IP and Named
Pipes, restared
e) Make sure there is both a NT AUTHORITY/NETWORK SERVICE and ASPNET
login
f) Map both NT AUTHORITY/NETWORK SERVICE and ASPNET to the aspnetdb.

4) Add the wrox files
a) unzip the files to C:\local code\WroxUnited
b) add the LOCAL COMPUTERS NT AUTHORITY/NETWORK SERVICE account to the
folder containing the .mdf file
5) Open IIS Admin:
a) create a Virtual Directory, set directory, set up alias.
b) Make sure in the ASP.net setting ASP 2.0 is selected.
c) edit the connection string for in the applications web.config from
the default:
LocalSqlServer "data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttacheDBFilename=|DataDirectory|aspnetdb.mdf;User
Instance=true
WroxUnited data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttacheDBFilename=|DataDirectory|WroxUnited.mdf;User
Instance=true

to:

LocalSqlServer "data source=localhost;Initial Catalog=aspnetdb;Integrated
Security=true;"
WroxUnited data source=localhost;Initial Catalog=WroxUnited;Integrated
Security=true;
6) WroxUnited.mdf setup.
a) Attach the WroxUnited.mdf file under the name WroxUnited.
b) In the security tab for the database both Network Service and
ASPNET are users.

7) Run the program:
a) open http://localhost/WroxUnited in IE
b) get the error: An error has occurred while establishing a
connection to the server. When connecting to Sql Server 2005, this failure
maye be cuased by the fact that under the dfault setting sql server doesn't
allow remote connections. (provider: Named Pipes Provider, error: 40 -
Could not open a connection to SQL Server)
c) Check the Sql log and see nothing.


Now this post has taken me over two hours to write and retest everything...
LOL. any ideas on what I'm doing wrong? The main differnce between the two
computers is 1)( logged on the local machine,2) running different OS which
have different versions of IIS.

web sites I've used to get information on this:
http://p2p.wrox.com/topic.asp?TOPIC_ID=37298&SearchTerms=37465
http://p2p.wrox.com/topic.asp?TOPIC_ID=37465
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/ConfigASPNET_SQL.asp
a couple more I can not locate now, one in particular that pointed out the
tcp/ip connections.

 

[tdavisjr]

I'm so so sorry this took you a long time to write. However, you may be
disappointed but SQL Server cannot attach to a db file at runtime.
This is a SQL Express Feature only. This is why Microsoft had to
produce a seperate tool to Web Hosters to give their users the ability
to upload .mdf files and have sql server attach to it. I guess they
saw this as a security issue. Imagine how may people would be getting
away with uploading an unlimited number of databases have have SQL
Server use it. Hosters would loose money and no one would have to pay
for adding a extra database. Sorry, but you are out of luck on this one.

 

[Guest]

I am not trying to attach this mdf at run time, I am attaching it through sql
server managment studio by choosing database>attach. I think you may have
mis understood that?

Thanks for the try though...

Greg

 

[Steven Cheng[MSFT]]

Hi Greg,

I've also seen your other threads in other newsgroup and since this is a
pure ASP.NET issue I think it'll be proper to discuss it here. I think the
problem should somewhat related to the serve environment, as you mentioned,
you meet some problem dealing with the network service account. Based on my
experience, ASP.NET application is not recommended to run on DC machine
since there is no local account on DC which may make the ASP.NET process
account management and our application's security consideration confusing.
Therefore I suggest you test on a non-DC server. For the SQL Express
database, there is no problem for it to be attached to SQL Server 2005
enterprise version, I've just download the wrox sample application and
change the WroxUnit database from SQLEXPRESS to enterprise edition without
any problem(I just test through FileSystem project). For the ASP.NET
process identity, if you get confused with network service account, you can
first configure it (the application pool idenitity for IIS6) to use
another specific domain account or local admin accout and test it. Also,
if possible you can test it through filesystem project since the test
server will use the impersonated client user as the running process
idenitity.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Steven,

Thanks for getting back to me. Yes, it took me a while to find the correct
user group as this is the first time I've posted a asp.net question.

Could you explain to me how the steps you used to get the sql express
database to work on sql server? Also check to see if the default.aspx page
works. I'm able to get to the basic file structure but not to default.aspx.

Thanks,
Greg

 

[Guest]

Here is a link to the file if anyone is interested

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764588508,descCd-download_code.html

It is the finalapplication.zip

Thanks
Greg

 

[Guest]

Steve,

Sorry for so many replies but I did see some one else have issues hosting
SQL Server 2005 on a server this is acting as a DNS. If we could just focus
on the laptop then, I will have to set up another server I guess for when I
go into production. For now I would like to use SQL Server on my laptop for
my development. That should take some of the complexity out of this issue.

I do have a quick hardware question, if you had two servers and wanted to
install: DNS, DC, Active Directory, Exchange Server, and Application Server,
host Sql Server 2005 how would you split up those pieces of your network and
what type of hardware is needed on the two servers? I recently built my one
and only server. It has a gig of ram and an amd 3200 chip in it. It is
configured to do everything right now, from what I understand you are saying
I should split this up. What do I need to buy now? And what do I take off
of this one and put on a new one?

This is probably a better question for a different group...

Thanks,
Greg

 

[Steven Cheng[MSFT]]

Thanks for your response Greg,

Actually, to make it simple, my test just changed the WroxUnited database
from SQLExpress to Sql Server 2005(I think changing the aspnetdb should be
the same). I just move the WroxUnited.mdf and ldf file to another
location(and rename them) to avoid confusion. Then, I attach them in SQL
2005 server engine and renamed them to my customized names. After that,
what I've changed is just the connection string in the web.config for the
"WroxUnited" db:

<connectionStrings>
<add name="WroxUnited"
connectionString="my new connection string..." ....../>
.......


Anyway, you turn on profiler to check what's the security identity ASP.NET
applicaiton used to connect to the SQLServer. This is still likely a
security setting problem. BTW, for those AD, DNS deploying question, I
think it would be better to post them in some window platform specific
newsgroup instead of development newsgroup

Thanks & regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)