Can't connect to DB for authentication purposes (ch. 16)

Imar · February 13th, 2012, 05:34 AM

Hi there,

What you see is by design. ASP.NET uses a connection string called LocalSqlServer by default for Membership. That connection string then has a setup to create a new database in the application's App_Data folder.

See Appendix B, page 771 and further to learn how to point Membership and Roles to your custom database. You can store the database in C:\Data\SqlServer, attach it to SQL Server and then configure your application to look at that database.

Not sure why these database files don't have an extension. Maybe Windows is configured to hide extensions for known file types?

Quote:

If I click on the one in my app_data folder, it tells me I need to install SQL Server - I fear this is going to be another DB issue.

Probably a configuration issue. What do you have under Tools | Options | Database tools | Data connections? Make sure the instance name aligns with the SQL Server instance you're using.

Cheers,

Imar

daveharney · February 13th, 2012, 12:02 PM

Quote:

Originally Posted by Imar

What you see is by design. ASP.NET uses a connection string called LocalSqlServer by default for Membership.

You can store the database in C:\Data\SqlServer, attach it to SQL Server and then configure your application to look at that database.

Not sure why these database files don't have an extension. Maybe Windows is configured to hide extensions for known file types?

Probably a configuration issue. What do you have under Tools | Options | Database tools | Data connections? Make sure the instance name aligns with the SQL Server instance you're using.

Hi Imar,

This should really not be this hard!!

I deleted the aspnetdb from my app_data folder but VS put it back in again when I ran my website even though I have both "School" and "aspnetdb" in my C:\data\sqlserver folder and both attached via the SQL mgt tool. I also put a connection string in my web.config file (see below). In SQL mgt tool the names are shown as:

ASPNETDB.MDF and School

In the VS Server Explorer they are shown as:

mypc\sqlexpress.ASPNETDB.MDF.dbo
mypc\sqlexpress.School.dbo

In my File Explorer in C:\data\sqlserver explorer they are shown as :

ASPNETDB.MDF and School.mdf

In my Tools | Options | Database tools | Data connections I have:

mypc\sqlexpress - and obviously this is working for the School DB

And, no I my windows does not hide known file types

Here is how my config file looks now - per page 771:

<connectionStrings>
<clear />
<add name="SchoolConnectionString1" connectionString="Data Source=mypc\sqlexpress;Initial Catalog=School;Integrated Security=True" providerName="System.Data.SqlClient" />
<add name="SchoolEntities" connectionString="metadata=res://*/App_Code.SchoolModel.csdl|res://*/App_Code.SchoolModel.ssdl|res://*/App_Code.SchoolModel.msl;provider=System.Data.SqlC lient;provider connection string="data source=mypc\sqlexpress;initial catalog=School;integrated security=True;multipleactiveresultsets=True;App=En tityFramework"" providerName="System.Data.EntityClient" />
<add name="LocalSqlServer" connectionString="Data Source=mypc\sqlexpress;Initial Catalog=aspnetdb.mfd.dbo;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

I've tried every variation of aspnetdb sufix and the one currently in my config file is just one variation. No matter what, I now get this error:

Cannot open database "aspnetdb.mfd.dbo" requested by the login. The login failed.
Login failed for user 'mypc\myUserName'. Description: An unhandled exception occurred during the execution of the current web request....
Exception Details: System.Data.SqlClient.SqlException: Cannot open database "aspnetdb.mfd.dbo" requested by the login. The login failed. Login failed for user 'mypc\myUserName'.

Any help will be much appreciated.

Imar · February 13th, 2012, 12:19 PM

No, it shouldn't be so hard....

Quote:

aspnetdb.mfd

That doesn't look right. The database name should be aspnetdb in your connection string and SQL Server Management studio, and aspnetdb.mdf on disk. ALso, it's MDF not MFD.

With regards to LocalSqlServer: did you follow the steps in the book? Is this connection string now also used by Membership and Roles? Can you post your full web.config?

When you log in to SQL Server using Management Studio, what do you see? What are the names of the databases? And which user accounts have access to these databases?

Imar

daveharney · February 13th, 2012, 01:03 PM

Hi Imar,

My "MDF not MFD" typo was causing a problem. I changed it to "Initial Catalog=aspnetdb.mdf" and now I can see the DB in my C:\data\sqlserver folder being updated with a new login from the website. I can see new logins from the website being added to the aspnet_Users table in either the VS Server explorer or the SQL mgt tool.

I guess I wasn't clear that I was using a shorthand for the SQL Server Management Studio when I said "SQL Mgt tool". Also, when I said VS Server Explorer I was talking about the Visual Studio 2010 Server Explorer on the left side of the screen in the tool box area. So, to recap:

SQL Server Management Studio shows:

ASPNETDB.MDF
School

Visual Studio 2010 Server Explorer shows:

mypc\sqlexpress.ASPNETDB.MDF.dbo
mypc\sqlexpress.School.dbo

Windows File Explorer in C;\data\sqlserver shows:

ASPNETDB.MDF
School.mdf

And, what now is working in my LocalSqlServer connection string is:

aspnetdb.mdf

I'll now be moving on in ch 16 to page 587 for more on Logins and the other topics - hopefully, this DB access issue is behind me.

Thank you very much for your guidance and catching my typo (that was a big one!)

Once I get entirely through the book as a learning exercise, I intend to go back and implement my actual application by following each chapter for anything that applies to my actual website. I've been away from programming for a few years and your book is exactly what I needed to get me up and running again. The web site I intend to implement is a non-profit, public service project. No money to be earned - just an expense for me! So, I appreciate all the help I can get. Thanks again!

daveharney · February 24th, 2012, 09:08 PM

Hi Imar,

I've now made it to page 720 and all was going well until I tried to use "localhost" in my browser to bring up my default.aspx page in the C:\BegASPNET\Release folder. I got the error:

Cannot open database "xxxx" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.

I should note that something like "localhost/demos/calculatordemo.aspx works fine (does not use the DB).

Based upon a suggestion I read (somewhere?) I did this:

1. Opened addvanced settings from IIS\APP Pool V4.0

2. changed identity to localSystem

Now apparently using windows account and site works properly with DB data. Note that my DB (per your suggestion) was in C:\data\SqlServer Folder and I gave that folder the same procedure as you described for the app_data folder.

Can you tell me if my need to make this change actually indicates that I have some other configuration problems? Thanks.

Imar · February 25th, 2012, 03:01 PM

Using the system account works, but grants way too many permissions.

Take a look a Appendix B to see how to set up the account used by the web server (the IIS AppPool\SiteName account).

Cheers,

Imar

daveharney · February 25th, 2012, 10:43 PM

Hi Imar,

Thanks again for your help. I followed Appendix B and switched to Server Authentication and also switched IIS\APP Pool V4.0 back to ApplicationPoolIdentity and everything worked fine.

I understand that Windows Authentication is more secure, but eventually I will need to host my site on a commercial hosting service and they will require Server Authentication. So, I guess I'll just stick with that.

Question: I have the commercial version of VS 2010. If I use the package feature and everything is compiled, does this mean my web.config is not visible to anyone? I'm most concerned about email accounts and passwords.

Thanks again for all the great help you have given me.

Imar · February 28th, 2012, 03:08 PM

Yes and no.

Packaging does not affect the web.config file. However, by default, IIS / ASP.NET is setup to block access to the web.config (try accessing this URL: http://imar.spaanjaars.com/web.config to see what I mean).

Everyone with access to the server (remote desktop, FTP, what have you) will be able to see and edit the web.config file).

Hope this helps,

Imar

daveharney · February 28th, 2012, 04:46 PM

Thanks again Imar. You've just convinced me to use the email account offered by the hosting service. I believe that can be forwarded to my regular email without exposing my regular email password.

Your book has been great and now I'm building my own website and using your book as a very valuable reference. One thing I really appreciated about your book was the great attention to detail and accuracy. Also, the fact that you make very few assumptions about how much experience the reader has. In my case, I've been away from programming for several years and your level of detail was perfect for me.

I just got a copy of the Professional version but only intend to use it as an additional reference - in most cases, your book will be sufficient for my site. Thanks again.

Imar · February 28th, 2012, 05:06 PM

Quote:

You've just convinced me to use the email account offered by the hosting service.

Yes, that sounds like a smart thing to do.

Good to hear you like the book so much. Spread the word!! ;-)

Cheers,

Imar