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

CodSharp · September 26th, 2011, 02:05 PM

Hey all,

I apologize if this has already been asked. I tried searching around the forums, and while I found a couple of threads that were close, none seemed to match my exact issue here.

I copied the downloaded code/data from Chapter 15 into my test website folder. I am working on Chapter 16 (authentication), and more specifically, trying to go through the first try it out exercise where you use a login control to attempt a connection to the database. When I enter a bogus username/password, I get the following error in the browser:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I have abbreviated the error message, but would be happy to post the entire thing if it would be helpful.

It seems as if the application can't connect to the database; however, I put a DetailsView control with an associated SQL Data Source control right below the login box, and I am able to view/page through records just fine on that, so obviously I am able to make a connection to the database. It seems that I'm only unable to make the connection when I attempt to use the login control.

Does anyone have any idea what the difference is?

Imar · September 26th, 2011, 03:01 PM

Hi there,

What's the name of your SQL Server instance? The security controls assume it's called .\SqlExpress.

Check out Appendix B to learn how to override the settings for membership and so on in web.config to match your SQL Server installation. Alternatively, and much easier for now and for the long term is to install another instance of SQL Server Express edition using the WPI as explained in Chapter 1.

Cheers.

Imar

CodSharp · September 26th, 2011, 03:27 PM

Hey Imar,

Thanks very much for the reply. I did have a differently named SQL instance, and reading through the rest of the chapter seemed to tell me that was the problem. I uninstalled SQL Server and reinstalled with SQLExpress left as the named instance (as you suggested), and the application is working now. That should let me continue on through the book.

Thanks very much for your help!

Imar · September 26th, 2011, 03:46 PM

You're welcome. Glad it's working.

Cheers,

Imar

daveharney · February 10th, 2012, 12:12 AM

I have the exact same problem as CodSharp. However, I really would like to get around this problem without reloading SQL Server.

You helped me before with similar problems in earlier chapters and I've had no further issues until I got to this login control - which I really want to use.

My connection string (which has been working fine) is:

<connectionStrings>
<add name="PlanetWroxConnectionString1" connectionString="Data Source=.;AttachDbFilename=|DataDirectory|\PlanetWr ox.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
<add name="PlanetWroxEntities" connectionString="metadata=res://*/App_Code.planetWrox.csdl|res://*/App_Code.planetWrox.ssdl|res://*/App_Code.planetWrox.msl;provider=System.Data.SqlCl ient;provider connection string="data source=.;attachdbfilename=|DataDirectory|\PlanetWr ox.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=En tityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>

Of course, the key here is - connectionString="Data Source=.;

This is due to installing newer SQL version and then deleting older version - I know this issue, but it would seem I should still be able to use my existing version.

I tried the "Administer Website" associated with the login control and eventually got to the "test" AspNetSqlProvider" and it returns -

"Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider. "

Is there some way to tell the login control to use this connection string?

Thanks for your help - great book (I have both paper and "E")

Imar · February 10th, 2012, 03:55 AM

Quote:

My connection string (which has been working fine) is:

Has been? Or still is? In other words, are you only having problems with the membership and not your own PlanetWrox tables?

Quote:

This is due to installing newer SQL version and then deleting older version - I know this issue, but it would seem I should still be able to use my existing version.

If you uninstall something, it's gone and doesn't work anymore of course. It seems you uninstalled a SQL Server instance called SqlExpress which ASP.NET assumes is present.

Checkout Appendix B, page 771 and further. The section "Configuring Application Services" shows how to modify the web.config file so Membership and Roles look at a SQL Server instance other than SqlExpress.

Cheers,

Imar

daveharney · February 10th, 2012, 06:29 PM

Hi Imar,

< Has been? Or still is? In other words, are you only having problems with the membership and not your own PlanetWrox tables? >

I probably wasn't too clear about this. Originally I had VS2003 and SQL Express 2005 on my Win 7 machine. I installed VS 2010 and SQL Express 2008 R2 and then deleted the old VS & SQL. On one hand, this has led to endless problems, but it has also forced me to learn more about configuration issues. Up until chapter 16 (I've done all the previous exercises) I've been able to use the my 2008 R2 DB by leaving the "SQL Server Instance Name" BLANK when using VS 2010 Options->Database Tools->Data Connections. And then, in the web.config file using "Data Source=.;" With this arrangement, I see the tables in my Server Explorer in VS and all the data from the exercises is just as it should be. The VS properties windows for PlanetWrox.mdf shows the DB is located in C:\BEGASPNET\SITE\APP_DATA\PLANETWROX.MDF. If I close VS I can also open the same DB in the SQL management tool and see the tables with up-to-date data in them.

The problem only started with Chapter 16. As it stands now, I can bring up the site/default.aspx page in my browser and do things like see the reviews with no problem. If I go to the Login page I can click on the " Sign Up for Your New Account " and get the Sign Up page. After filling in the boxes, I click on "Create User" and this is where the problem starts. Immediately I get Server Error in '/' Application. Cannot open database "aspnetdb" requested by the login. The login failed. Login failed user for 'MyPC\MyUserID'.

I've spent many hours in Appendix B trying different approaches and so far I can't get past this problem. My current web.config connection string is this:

<clear />

<add name="PlanetWroxConnectionString1" connectionString="Data Source=.;AttachDbFilename=|DataDirectory|\PlanetWr ox.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />

<add name="PlanetWroxEntities" connectionString="metadata=res://*/App_Code.planetWrox.csdl|res://*/App_Code.planetWrox.ssdl|res://*/App_Code.planetWrox.msl;provider=System.Data.SqlCl ient;provider connection string="data source=.;attachdbfilename=|DataDirectory|\PlanetWr ox.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=En tityFramework"" providerName="System.Data.EntityClient" />

<add name="LocalSqlServer" connectionString="Data Source=.; Initial Catalog=aspnetdb; Trusted_Connection=True" providerName="System.Data.SqlClient" />

I added this LocalSqlServer part as an attempt to force getting at the aspnetdb. Before this, I got much the same error but it took several minutes to occur - this yields the error immediately.

The ASPNETDB.mdf shows in both my VS Server Explorer and the SQL Mgt Tool. The properties window shows it is also in C:\BEGASPNET\SITE\APP_DATA\ along with PlanetWrox.mdf

I did the exercise starting on Page 771 and both the PlanetWrox and AspNetDB show a list of "aspnet_" tables - not sure why these new tables show up in both databases?

I spent a couple of hours trying the whole "Using SQL Server Admin" approach - no luck - always the same, can't find the DB. I tried the "attaching DB to SQL" on page 761 - no luck.

At this point, I don't know what to look for or try. It seems the most pressing issue is to figure out why ASPNETDB can't be found by the application. Thanks.

daveharney · February 11th, 2012, 12:51 AM

I uninstalled and re-installed SQL Express 2008 R2 and now I can't get the Wrox or any DB into any web application in VS. Keeps telling me to install SQL.

The SQL Mgt Tool seems to work fine for creating and attaching DBs. Just can't get anything to work in VS.

The instance name is now .\SQLEXPRESS (or myPCname\SQLEXPRESS). I've used this name in the VS database connection option and the web.config file. I followed Ch 19 carefully for ASP.Net V4 setup and permissions on the BegASPnet folders. This is not fun!

I suppose re-installing VS is an option but that seems like a pretty drastic step.

Imar · February 11th, 2012, 04:51 AM

Hi there,

When using SQL Server during deployment, there are a few things you need to take into account:

1. When you attach a database to SQL Server (Express or not) you should move the MDF file out of the App_Data folder and into something like C:\Data\SQLServer.

You can't use the database in Visual Studio through App_Data when it's attached to SQL Server. Both VS and SQL Server will try to get an exclusive lock on the file, which causes problems. When the database is attached, simply update your connection strings to refer to the attached version. E.g.:

Code:

 
Data Source=.; Initial Catalog=NameOfDB; Integrated Security=True

or

Code:

 
Data Source=.\SqlExpress; Initial Catalog=NameOfDB; Integrated Security=True

The first can be used for an unnamed instance, the second one targets SQLExpress

2. When you attach a database, it may get a strange name. E.g. rather than being called aspnetdb, it may have a name such as C:\BegASPNet\Site\App_Data\aspnetdb. This way, your code won't recognize the database. You should rename the database (right-click and choose Rename in SQL Server Management Studio) to aspnetdb (or PlanetWrox or whatever database you're working with).

3. When you deploy your site, you typically don't use AttachDbFilename anymore. Although it may work on your local machine, it won't typically work with hosting parties. Instead, attach the databases to SQL Server (see above in 2)) and then use the connection strings from 1)

4. Now that your instance name is SQLExpress again, you should change the instance name field in Visual Studio again under Tools | Options | Database tools. This way, VS knows where to find your default SQL Server instance.

5. When overriding the LocalSqlServer connection string, make sure your Membership and Role configuration still point to this connection string.

So in order to get this to work, here's what I would do:

1. Create a folder called C:\Data\SqlServer

2. Copy your ASPNETDB.mdf and PlanetWrox.mdf files into this folder.

3. Log in to SQL Server Management Studio. Note the instance name you're logging into (e.g. . or .\SqlExpress)

4. Attach the ASPNETDB file and make sure it's called ASPNETDB and not something else that looks like a path

5. Repeat for the PlanetWrox database

6. Update your connection strings to match the example I provided above.

7. Configure SQL Server security as explained in the book.

8. Try again.

This should do the trick. If it doesn't , can you provide the following:

1. Instance name of SQL Server you log in to using SSMS
2. The security account you configured in SQL Server to access the database
3. Your full web.config code.

Hope this helps.

Cheers,

Imar

daveharney · February 13th, 2012, 12:14 AM

Hi Imar,

I gave up on trying to get the PlanetWrox web site working past Chapter 15 - things were fine up to page 584 in ch 16 and then I hit a brick wall with DB problems. Everything I tried resulted in a failure to access the DB. And, I tried to follow your suggestions carefully.

So, I decided to take a step back and create my own DB in the SQL Mgr - a classic "school" DB with Students, Courses, and Enrollments (students enrolling in courses). I used your suggestions for how to set up the DB - your suggestions were really great and got me back on track again to understand the bigger picture. I'm not using app_data for the DB itself - it is in the C:\Data\SqlServer folder (folder has permissions per ch 19 & appendix B).

I cherry picked sections of your book to set up the tables, keys, foreign keys, EF model, gridviews, listviews, SqlDataSource, EntityDataSource, Fileupload, etc. The EF model was particulary good for managing FK relationships in the tables. All of these exercises worked great.

I'm now back to page 584 and the whole Login/SignUp.aspx pages are working fine - this is where I ran into that brick wall before. The rest of ch 16 will probably present some challenges, but I feel much more confident now.

Here is what my Connection Strings look like:

<connectionStrings>
<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" />
</connectionStrings>

My concern going forward is the fact that I put your copy of aspnetdb.mdf in my database folder - (C:\Data\SqlServer - where it is named ASPNETDB - with no .mdf suffix) (along with where the "school" DB is located) and attached it using the SQL mgr tool. However, I see now that VS has put an aspnetdb.mdf in my app_data folder. 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. What is your advice for getting it into my C:\Data\SqlServer along with my School DB? Should I move it there and delete it from my app_data folder and then attach it with the SQL mgr tool? And then put the "Data Source=myPC\SqlExpress; Initial Catalog=aspnetdb; Integrated Security=True" in my connection strings. Also, should it have the .mdf suffix - in fooling around with this it doesn't seem to matter if this is left off - if everything else is setup OK?

At this point, I'm a little gun-shy about moving this aspnetdb file without a little more guidance from you.

Thanks to your advice, I've clearly made progress and I really hope to get past this DB access issue.