Geeks With Blogs

News
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of my employer.

Creative Commons License


Check out my about.me profile!

INETA Community Speakers Program

Follow enriquelima on Twitter
Locations of visitors to this page

Intentional Thinking Enrique Lima

The road to learning is such an awesome thing!

Yesterday, while browsing the MSDN Forums, I came across some people having frustration with SQL Server Express, Visual Web Developer 2010 and the Web Installer.

The Issue

After installation, if you create a New Website with VWD 2010, follow the steps below:

  1. Access the Web Site Administration Tool (Solution Explorer Toolbar)
  2. Click on Provider Configuration (Once the Web Site Admin Tool opens)
  3. Click on Select a single provider for all site management data
  4. You will find AspNetSqlProvider, click Test.
  5. Ready?  Frustration, here it comes …

    image

 

The Analysis

I took it as a call to research what in the world was going on. Went ahead an ran the Web Platform Installer, installed SQL Server 2008 R2 Express.

Note: You do not install two versions of SQL Server, you are just installing the SQL Server 2008 R2 Express engine, and the SQL Server 2008 Objects (as in API)

image

 

Everything ran fine, got installed and voilà (say it this way for maximum effect, boy-lah, I know that is not how it’s pronounced).  Anyway back to the issue at hand.

I go on to repeat the steps above and baaam, there it was.  The issue had been found.  What makes it more frustrating is the fact it gives you a hint on what to do, but no true identifier as to what is really going on.

Since we see that connection attempt fail, what can we blame?  You thought about it, SQL Server!  Remember, frustration is in the house.

What to do?  Likely you ran aspnet_regsql, went through the process of creating the membership database, went back ran the Test connection again, and it showed no difference.

 

Resolution

Maybe not 100% resolution, but in my IT career I have discovered that if you hold attitude against a specific product, product will win and it will get back at you with a vengeance.  It does not matter how many degrees, certification and such you have, none of this is in question (until you bring it up), the question then becomes yet again … What to do?

What makes this situation even worse is the fact you can go to the Security area within the Web Site Admin Tool, and switch authentication from Windows to Forms.  You can create users, roles, then assign the users to the roles.  You can also create Access Rules.  And to make it even more frustrating?  The Web Application works!  It authenticates you when you run the app in the browser.  What gives?

When looking at web.config for the site (and so far it is the same for a File System or HTTP hosted site), this is what I see:

 <connectionStrings>
    <add name="ApplicationServices"
    connectionString="data source=.\SQLEXPRESS;
Integrated Security=SSPI;AttachDBFilename=|DataDirectory|
\aspnetdb.mdf;User Instance=true"
providerName="System.Data.SqlClient" /> </connectionStrings>

So, it looks ok, right?  Correct, everything is fine.  BUT??!!??  The following section:

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

It is using SQL Server Express User Instances!  So, big deal, right?  NOT!!

Click the links in the References portion to understand the limitations and constraints of this. Among some things mentioned in those articles:

  • You can't find your database in Management Studio.
  • You can't attach to your database in the location where Visual Studio creates it.
  • If you manage to attach the database in Management Studio, your application can not longer user it.

Now that the “issue” is identified we have two options:

  1. Move about the web site development, knowing that clicking to Test the provider will fail but the environment is connected to a membership db.  I have not found how to have it pass the test with this configuration.
  2. Run aspnet_regsql to create an aspnetdb on the SQLEXPRESS instance installed earlier.  Then, once the aspnetdb database exists, modify the connection string on the web.config file to look like this:

    <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;
    Initial catalog=aspnetdb;Integrated Security=SSPI" providerName="System.Data.SqlClient" /> </connectionStrings>

Reloaded the Web Site Admin Tool, and then when validating the provider it showed a Successful connection.  With that said, the magic or root of the issue resides in the connection string.  And of course knowing where to go and look for in the effect of weirdness, can somewhat save the day.

References

http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

http://msdn.microsoft.com/en-us/library/ms254504.aspx

Posted on Saturday, August 21, 2010 8:39 PM Misc , ASP.NET | Back to top


Comments on this post: Visual Web Developer 2010 and SQL Server Express User Instances “Issues”

comments powered by Disqus

Copyright © Enrique Lima | Powered by: GeeksWithBlogs.net