HELP!! First, my confusion comes from my inexperience with SQL Server and how ASP.NET interacts with it. I LOVE these Wrox books and have bought several--specifically the ASP.NET 2.0 Beginner book. I am going over the part about login controls. It works perfectly with V Studio with Ctrl F5..but I am trying to import into IIS. I have SQL Server 2005 running/installed and have SQLSERVEREXPRESS running (free copies from school--just graduated with IT Degree from UMKC)

I am having all sorts of problems - all stemming from not knowing what to do. I tried opening up SQl Server and "attaching" the aspnetdb.mdf file--which confuses me as I would think all DB's would reside in a common folder---so when I attach..is it just storing a path to the app_data folder that it resides in or is it copying it to SQL Server--did I even need to do that?

Then--I get a localhost/ASPNET cannot access the DB (Took me MUCH research to even thing about attaching the DB--not sure if I should have done that or left the file alone in the app_data folder).

I tried adding a ASPNET login and granted TONS of access--again--not sure what I am doing..and then added that login as a user to the DB...still nothing.

Is there an easy way to do this? I'd be willing to pay someone to help---I would even buy another Wrox book on just understanding how SQL Server works (defining how things interact with it...etc).

Please help...what am I supposed to do when I save a web site with VS to get the authentication to work properly?

Thanks, in advance!!

Best Regards,
Rob Searing

Hi Rob,

You may want to take a look at these two articles:

http://weblogs.asp.net/scottgu/archi...25/423703.aspx
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395

The first explains how yo configure a SQL Server 2000 or 2005 database to work with the .NET provider features.

The other, by yours truly, explains how to configure SQL Server 2005 so it can be used from within ASP.NET 2.0.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar,

First--I sent you a pretty lengthy email through your web site--thank you for posting. I have bought three Wrox books and will consider yours. My problem is simply ignorance of knowing what I am doing and not even knowing where to start. For instance...

1) Stupid question--I don't even know where the connection string is--I don't see it in my web.config file that is stored in the web site folder...

2) I take it SQL Server doesn't keep all of it's databases centrally (in one folder)? Meaning..I "attach" the aspnetdb.mdf file to the DB..then I cannot move it?

2a) How was VStudio accessing the .mdf file? I couldn't test my app unless I had SQLServerExpress running...and it didn't have an aspnetdb.mdf file in there--so it had to use that file (was stored in file system). Yet when I use IIS---no good.

Stupid question--but do you tutor (for pay) by chance?

I am going to try to go through those links .. I printed them from work along with several other links from your document. I wish there was simply a book to get that would talk about SQLServer differences..basics...how it works (connection strings) and how it works with the security controls in ASP.NET.

Thank you VERY much for your time!

Kind Regards,
Rob Searing

Ok..followup questions after reading ScottGu's Blog

1) VERY embarrassed to ask, but is there meaning behind him "appservicesdb". I notice that in his drop down--should I have that in mine--or could I just name it that. (or should I have had that in my drop down).
2) What am I doing in this step--is this to replace the aspnetdb.mdf file?

I am sorry if I am coming across as beligerent...this is just SO new to me--thus the "beginner". You have NO idea how appreciative I am!

Kind Regards,
Rob

Hi Rob,

Thank you for your personal message. I'll reply here if you don't mind.

There is no meaning in the name appservicedb' Scott could have chosen anything. However, because he's giving the LocalSqlServer connection string name a new value, he's basically telling the ASP.NET run-time to use this new database, instead of the automatically generated one in the App_Data folder.
And there *is* meaning in that database: it has tables, views and stored procedures to support all the new "provider model" features, like Membership, Roles and Profiles (all discussed in Beginning ASP.NET 2.0 I assume).

Regarding your second question: with the default connection string called LocalSqlServer in the Machine.config, you'll see something like this:

AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

This ensures the database is attached at run-time under the user' credentials. For more details about user instances, take a look here:
http://msdn2.microsoft.com/en-US/library/ms254504.aspx

And re 2a: This isn't Visual Studio doing this; this is ASP.NET 2 doing this. This may sound like a trivial difference, but it's important to understand that the auto created database, the membership and role providers etc are run-time features supported by ASP.NET 2.0. These are not just some smart tool plugins, but true run-time features.

And yes, when you attached a database it gets named after the full path. Not sure why; guess this is meant to make unique names. However, you can easily rename the database if you want.

Does this answer all your questions here and in your e-mail?


Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar,

First--thank you, again, for your time---you have no idea how helpful this is. I feel, at this point, I have SO much information, I need to figure out how to sift through it all and make sense, so please forgive if some of these questions are retorical

1) Am I correct in stating that what I am doing via Scott's blog is creating a different database that ASP.NET will use for the roles/users etc that is used by the login controls in ASP.NET? If that is correct, then say I am creating a site in Visual Studio and I go to compile and test--will it use this DB instead of the auto aspnetdb.mdf?

2) Where is machine.conf? Are you saying that once I change the DB it uses, (say I call it robaspconf) it would state:
AttachDBFilename=|DataDirectory|robaspconf.mdf;Use r Instance=true ?
Again..the steps I am doing here is to create a DB myself for ASP.NET to use rather than the aspnetdb.mdf?

3) Ok--so I set up the DB...how do I know what user to grant it access? I think that there was an article on this and it's as easy as setting up an account for ASPNET (localhost/ASPNET) with windows authentication? The reason I ask this is because, as I was starting to piece some of this together...I actually did the following:

a) created a new asp.net website in visual studio with only two pages. Default had a loginstatus view and I had a login.aspx that had a logincontrol
b) I compiled this and saved in IIS wwwroot directory
c) in sqlserverexpress I attached the aspnetdb.mdf file
d) I created an ASPNET login
e) I added the ASPNET user and granted all sorts of memberships--as I wasn't sure on what to give.
f) I tried to run and it said localhost/ASPNET couldn't access.

Imar--I swear--I am so close to getting this. You have no idea the amount of articles I have printed and read through and it confuses me...I wish someone could reach into my head and see what I am meaning and the logical steps I am trying to take to get there.

Lastly--your book--I would buy in a heartbeat--as I am limiting myself to only Wrox books (as I said, own three right now) because of the way they teach----your book, is it for SQL Server beginners? What would I get out of it.

Honestly--to have someone such as yourself taking time to answer posts really shows me a lot. I am a father with three kids and know how precious time is. I really appreciate yours!

Kind Regards,
Rob Searing

Hi Rob,

Whew, lots of questions. Let's see if I can address all of them.

1. First of all; you should be aware of the differences between SQL Server Express Edition and the commercial versions. If you only have the Express Edition and want to learn how to develop ASP.NET applications, you may be better off sticking to the Express edition for a while. When you understand more about SQL Server and ASP.NET you can make the move to the commercial versions of SQL Server. At this stage, I think the Express Edition can do pretty much what the commercial versions can do.

Configuring SQL Server is not an easy undertaking. There's a very good reason that DBA's exists.... So, maybe you should do yourself a favor and stick with the autogenerated databases that work against SQL Server Express.

That said, let's look at the rest.

Like I said, at run-time a database is created automatically. This is because, by default *every* website inherits this LocalSqlServer connectionstring that says to attach an empty aspnetdb database to a local SQL Server Express instance. You'll find this connection string in the machine.config file in the folder: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONF IG (on a default Windows installation)

The machine.config is like the mother of all websites / config files. It provides *default* behaviors for all new websites. However, you can override most of the settings. Basically, that is what Scott's article is showing you: get rid of the default auto-generated Express database, and make the app point to your own database running on SQL Server 2000 or 2005. (Still with me??)

So to answer your first question: it depends. If you changed the web.config file for your application to point to your new database then yes: it will use that database. Like I said, these are run-time features, so at run-time ASP.NET will pick up the change in the web.config file and redirect the provider features (Roles, Membership, Profiles and so on) to this new database.

2) You better not know where the machine.config is... ;) (although I said where it was in the previous section). Basically, you don't want to change stuff in the machine.config file. Instead, override relevant elements in an application's web.config file. Scott's article explains how to do this for the SQL Server database, but there are many other settings you can override.

3) The article on my website (http://Imar.Spaanjaars.Com/QuickDocId.aspx?quickdoc=395) shows you how to do this. It talks about finding out what account is used, it talks about adding the proper permissions to SQL Server, it talks about configuring the app. Basically, it gives you a step by step explanation of all the things you need to do to make this work. (Notice, I am explicitly not talking about configuring the providers in my article. That's what Scott's blog post is all about).

Maybe you should take a step back, take a deep breath, let things sink in a bit and reread both articles. It might help in giving you a clearer picture; especially with the global information from this post.

But what's important to understand is the different options. Basically, there are three ways to interact with a SQL Server database (at least in the scenario's were dealing with here). Maybe you're confusing some of them...

1. Your *own* code accesses your *own* tables.
All you need is a connection string to your SQL Server. My article explains how this works.

2. Default setup with default provider stuff
You don't need to do anything; everything is created at run-time

3. ASP.NET application with a *different* SQL Server (2000 or 2005) database for the roles. membership etc.
Follow Scott's blog for the setup, the look at mine to see how to tune the security rights.

Basically, that's all there's to it. Of course there's a lot more (or all DBA's would be out of business) but it's at least a general overview of the stuff you'll encounter.

Regarding my book: It's not for absolute beginners, and it's not targeted at SQL users only. Basically, it gives you 12 complete working applications and explains how they work. From Design (class views, UML diagrams, high level overview) to dirty details like the inner working of stored procedures, the new ASP.NET login controls, configuring your own SQL Server database so you don't have to rely on the auto-generated one (what a coincidence) and much more.

Of course I am biased, but I am sure you'll like it....

I think that's it for now. Good luck and have fun reading.....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: My Doorbell by The White Stripes (Track 3 from the album: Get Behind Me Satan) What's This?

Imar,

WOW---again, thank you. Live close to KS City?--I'll buy ya a beer!

The whole issue started first when I did everything default. I used Visual Studio and allowed it to create aspnetdb.mdf in the app_folder on the file system and it worked great. The problems started when I wanted to see it work in real system--so I simply copied the files to wwwroot in IIS...nothing worked. I played with quite a bit and wound up attaching it to EXPRESS. Then, I created the ASPNET login and user..and still didn't work..that's where I'm at now.

Im going to delete my projects and try this all again. Thank you so much for all your time!

Kindest Regards,
Rob

I THINK I GOT IT!!

Imar--I got it working---but not sure why...these are my last questions--promise.

a) I just deleted (detached) the db..deleted all my projects and started new. When I went to try running it..I got an error that it (aspnetdb.mdf) was already in use. I did some searching and made the following change (NOTE--I ask this below--I did this in the IIS properties under ASP.Net tab and "edit configuration)

From: data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspn etdb.mdf;User Instance=true

To: data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttachDBFilename=|DataDirectory|aspn etdb.mdf;User Insstance=true

NOTICE--the only difference was the integrated security. Why did changing from SSPI to true work?

b) Ok--this is killing me. I have the aspnetdb.mdf file in the app_data folder--but it is NOT in the databases in SQLSERVERExpress. I am using Server Management Studio and have refreshed a dozen times--it's not there. However, if I shut down SQLServerExpress--it doesn't work...why is it not listed in SQLSERVER under Databases?

c) Ok--I have read EVERYWHERE about (ok..shooting myself as I opened up my config file and it's different this time--I was about to say that connection string is nowhere in there...as the only place I used to be able to see it was in the "edit configuration" of the ASP.NET in IIS---not sure why my connection string is in there now....I guess that is not a question but a comment.

d) Ok---I notice in the connection string it has |DataDirectory|aspnetdb.mdf in there....I assume DataDirectory is a variable ... where can I change that? reason I ask is that, right now in IIS, i have the files stored in a directory so that I have to use http://localhost/logindemo I wasnt to put it in the main directory so that I can just type http://localhost..meaning, I have to move the aspnetdb.mdf file---I'm sure I have to change the path.

(It is driving me crazy that I cannot find the aspnetdb.mdf file in the database listing in SQLServerexpress)

Imar---thank you!!!!!!!!!!!!

Rob

Hi Rob,

I don't think the Netherlands qualify as close to KS City... ;)

a) I don't see a difference other than a spelling mistake in User Instance. What did you end up using? Take a look here for proper connection strings: http://www.connectionstrings.com/

b) Take another look at the MSDN article about User Instances I linked to. Look at the section "Lifetime of a User Instance Connection". Basically, databases are attached, opened, closed and detached automatically. That's why you don't see them in SQL Enterprise Manager. If you do want to see them, use my article to attach a database to a commercial version of SQL and then change the connectionstring to use that fixed database. Don't forget to remove the "user instance" part from the connection string.

c) When you edit the web.config file through the IIS admin interface, you actually edit a copy in your web.config file. At first, it shows the connectionstring from the machine.config file (inherited) but when you alter it, you end up with a copy in your website's config file.

d) |DataDirectory| is not really a variable; but a folder expansion expression. At run-time, this is expanded to the App_Data folder of your website. So, if you have a site at c:\inetpub\wwwroot\App1, then |DataDirectory| expands to c:\inetpub\wwwroot\App1\App_Data

This way, this placeholder always points to your application's App_Data folder, even when you move the app around...

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.