I started the day with the optimistic goal of creating a new website for the Twin Cities Silverlight User Group. I decided to build the new site in Silverlight 3, selecting the new Silverlight Business Application Template. This template creates a project with navigation and authentication setup and ready to go, sort of.
The first thing I needed to do was to create a database. To create a membership database I used the C:\WINDOWS\Microsoft.Net\Framework\v2.0.50727\aspnet_regsql.exe command to create the tables in my database.
aspnet_regsql.exe -E -S localhost -A mrp –d TCSLUG
You can find an explanation of these command line switches here.
This command only creates the raw tables in the TCSLUG database. There is still a bit more effort to close the gaps. First things first. In the web.config, I added this connection string to the database for my application to use.
<connectionStrings>
<add name="TCSLUGDB" connectionString="Data Source=localhost;Initial Catalog=TCSLUG;User ID=tcslug;Password=xxxxxx"
providerName="System.Data.SqlClient" />
connectionStrings>
I then added this membership provider in the web.config.
<membership defaultProvider="AspNetSqlMembershipProvider">
<providers>
<clear />
<add
name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="TCSLUGDB"
applicationName="TCSLUG"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
requiresUniqueEmail="true"
passwordFormat="Hashed"
minRequiredPasswordLength="7"
/>
<membership/>
At this point I thought I was all setup. The last time I setup a membership database for a project was about two years ago for an ASP.Net project and I had simply gone into the database and started manually adding the application ID, users, and roles. But then I remembered there is a wizard to do that. In VS2008, under the project menu there is an ASP.Net Configuration option.
Clicking this menu launches a web page with tabs at the top, Home, Security, Application and Providers.
In looking around I noticed if you click the Providers tab, then click the link “Select a different feature for each feature (advanced)”. This now takes you to a display that lists the different providers that are already configured.
On this screen I see the provider I just configured and a link to test it. I hit the test link for my membership provider and “Success,” the provider is configured properly. What a wonderful little tool to make sure all my plumbing is connected end to end.
Now I go back to the security tab and am ready to create a user, so I click “Create User”, but I see this.
An error was encountered. Please return to the previous page and try again.
The following message may help in diagnosing the problem: Unable to connect to SQL Server database. at System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean isMembership, String methodName, Object[] parameters, Type[] paramTypes) at ASP.security_users_adduser_aspx.PopulateCheckboxes() in c:\WINDOWS\Microsoft.Net\Framework\v2.0.50727\ASP.NETWebAdminFiles\Security\Users\addUser.aspx:line 28 at ASP.security_users_adduser_aspx.Page_Load() in .......
What is going on? You just told me everything was configured correctly when I "tested" the provider! You see, the tool doesn't actually use the stuff you configured, that is apparently only for your .Net code. There is an “implied” SQL connection string, set to SQL Express, that is used for the ASP.Net membership features (or at least the tooling was written to use this name) rather than using the connection string specified in the membership definition. Remember? Look back at the profile provider I configured and notice that the TCSLUGDB connection string is specified as the connection string to use for membership.
You might ask, “Where is this magic LocalSQLServer connection string defined?” It is in the machine.config file. C:\WINDOWS\Microsoft.Net\Framework\v2.0.50727\CONFIG\machine.config
I don't like to mess with machine.config, because I don't move that to my production server as part of deployment, so I added this to the web.config file, as the error indicated I should.
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=TCSLUG;User ID=tcslug;Password=tcslug123"
providerName="System.Data.SqlClient" />
Did it work? Nope. I forgot one thing, as I have every time I’ve done this. I didn’t add <clear /> to the connection string section of the web.config file before I added the replacement for the LocalSQLServer connection string. At least this time the administration tool was nice enough to tell me it had been added twice, rather than showing me a stack trace.
After adding the clear, I could now add a user with my new connection string called LocalSQLServer. After adding my first user, I was pleasantly surprised to see that the tool also created an entry in the aspnet_Applications table, establishing the GUID that all other entries in the other tables will reference. I half expected that to be the next failure.
I did a quick Bing search to see if there was away to get the web tool to use the connection I defined, rather than one called LocalSQLServer, but didn’t find any results in my top search results. At this moment I’m going to assume it is a hard coded requirement that you must have a connection string by this name, if you want to use the tool.
This experience underlined the pain I felt in setting up the aspnet_membership stuff over a year ago. Clearly the experience didn’t stick with me because I nearly repeated all the same steps, relying on Internet searches to help me though my issues. It is quite amazing to me that the ASPNet WAT (Website Administration Tool) hasn’t improved much since VS2005 when it was introduced. There is certainly room for someone in the community to build a better gizmo for setting up both the database tables and the configuration files so all the plumbing is truly connected end to end.
I created this blog post in the hopes that I would not repeat the issues the next time I need to do it, but hopefully someone else will find value here as well.
Watch for more blog posts as I continue down the path of creating a Silverlight 3 Business Application.