I’ve started to work with Visual Studio 2005 Beta 1. I installed it into a Virtual PC 2004 VM running Windows XP SP1a. In the process of trying new things out, I discovered the new ASP.NET Web Site Administration Tool. This is a web app that allows for easier configuration of Security, Profile, Application, and Provider settings. It was with the provider settings that I encountered a problem.
ASP.NET 2.0 is packaged with two providers for ASP.NET settings, AspNetSqlProvider and AspNetAccessProvider. The corresponding database for AspNetSqlProvider is created with the aspnet_regsql.exe utility. This utility only creates the database, tables, views, and stored procedures. This utility does nothing about granting a SQL Server login permissions to any of those objects. This became apparent when I was 100% unsucessful in testing the AspNetSqlProvider from the admin interface.
After spending an enormous amount of time working blind (SQL Server 2005 Express is only packaged with osql.exe, and no GUI tools such as Query Analyzer, SQL Profiler, or Enterprise Manager), I was able to crack the code. I ran the aspnet_regsql.exe as such:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.40607>aspnet_regsql.exe -E -S (local) -A all -sqlexportonly aspnet_regsql.sql
This just created a script (and did not execute it against the server) for the local instance of SQL Server with support for all features. I looked at the roles that are created and realized that I needed to add at least myself (a local admin) to the most privileged user role. The reason why I knew this was because the admin website is running as a descrete app (not in IIS, but rather WebDev.WebServer.EXE), and it was running as me, according to the Task Manager. So, armed with this pearl of wisdom, I then executed the following against the ‘aspnetdb’ database in osql.exe.
1> use aspnetdb
2> sp_addrolemember N'aspnet_Membership_FullAccess', 'BUILTIN\Administrators'
As I expected, when I tested the SQL Server Provider again, it worked. One big thing I learned here was how to drive SQL Server completely from a command line interface. So, at least I learned something.