Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Force VWD2005 Web Site Administration Tool to save site configuration data in remote SQL Server?

By default, the Visual Web Developer 2005 Web Site Administration Tool creates a SQL-Server Express file (.MDF) and places it in the App_Data subdirectory.  Is there a way to force the Web Site Administration Tool to save site configuation data in SQL Server 2005 instead (either local or remote)?

Thanks!
0
penlandt
Asked:
penlandt
1 Solution
 
laotzi2000Commented:
Provider Tab
Use the Provider tab to test or assign providers for membership and role management for the Web site. Database providers are classes that are called to store application data for a particular feature. By default, the Web Site Administration Tool configures and uses a local Microsoft SQL Server Standard Edition database in the App_Data folder for the Web site. Instead, you can choose to use a different provider, such a remote SQL Server database, to store membership and role management.

-From MSDN: http://msdn2.microsoft.com/en-us/library/yy40ytx0.aspx
0
 
penlandtAuthor Commented:
Thanks for this reply.  I know about the Provider tab but I don't know how to change the provider.  On my tab there is only one provider (AspNetSqlProvider) and I don't see any way to add a new provider.  I read the instructions for adding a new provider in "How do I use this tool?" under "Configuring the SQL Server Provider".  I ran the aspnet_regsql.exe utility without command line options and entered my remote server name, username, and password (as well as my database name on the remote server) and received the error that appears below:

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 229 and the SqlException message is: EXECUTE permission denied on object 'aspnet_Setup_RestorePermissions', database 'dbname', schema 'dbo'.

----------------------------------------
Details of failure
----------------------------------------

SQL Server:
Database: [dbname]
SQL file loaded:
InstallCommon.sql

Commands failed:

-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_RegisterSchemaVersion'


SQL Exception:
System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'aspnet_Setup_RestorePermissions', database 'dbname', schema 'dbo'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
0
 
penlandtAuthor Commented:
I'm increasing the points on this question because it is apparently more difficult than I first thought.  The VWD help is clear that you can set up a remote SQL Server to be the repository of site configuration data but I can't seem to make it work.  I ran the aspnet_regsql.exe utility and received the error in my previous message.  Any ideas out there?  Do I need to switch hosts?  Anybody know of a host that fully supports SQL Server 2005 Express files rather than forcing you to go through this (seemingly endless excersise of trying to make VWD conform to this hosts restrictions?).
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TornadoVCommented:
I don't think you can force the Administration Tool to use a different server, but you can do it yourself.  Here is what I've done for my project,

After initial setup of users, roles, etc. using SQLEXPRESS and attached aspnetdb.mdf in App_Data folder, I created backup of aspnetdb.
Then restored it into a remote SQL 2005 server, after db was up and running I added a new user 'webuser', password 'webuser', granted rights to the database, then simply updated my web.config like this:
<connectionStrings>
        <remove name=”LocalSqlServer”/>
        <add name="LocalSqlServer" connectionString="Data Source="MyRemoteHost";Initial Catalog="MyRestoredFromASPNETDB_Database"; providerName="System.Data.SqlClient" Password=webuser;Persist Security Info=True;User ID=webuser;/>
</connectionStrings>

At this point I detached aspnetdb.mdf from my local SQL Express server and renamed the file to aspnetdb.mdf.old.  

Actually right now my 'connectionStrings' section has a different '<add name="MyRemoteServer"' value.  This was done for readibility purpose only, I did not want to have a setting name 'LocalSqlServer' pointing to a remote server.

Here is a great article that I've used as a reference:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

I hope this helps.

0
 
penlandtAuthor Commented:
This may take a few steps for me to grasp.  I've created my users and roles and I can see the ASPNETDB.MDF database in Solution Explorer.  I tried to attach this database in SQL Server Management Studio to make a backup but I can't attach it (can't even drill down to it using 'Browse').  Am I going about this the wrong way?
0
 
PAR6Commented:
right click detach from Solution Explorer from VS first (sorry, I may be assuming the obvious).  just trying to help.

0
 
penlandtAuthor Commented:
I actually figured out the first part of my problem.  I was able to create a local SQL Server 2005 database and, using the aspnet_regsql.exe utility was able to change the provider to that database.  Then I created my roles, user accounts, and access rules which were saved in the SQL Server database rather than an MDF file.  I was then able to make a backup but that's where I got stuck again.  On my hosted SQL Server, I don't have restore permissions and I am not allowed to change my permissions.  So I'm a little further than I was but still haven't solved the problem.

Anybody have any other ideas how to move beyond this point?
Thanks!
0
 
TornadoVCommented:
Do you at least have a valid login on a hosted server?  If not you should request it, otherwise it's 'no go'.  

After you have a functional db (with valid user and credentials) you should be able to use that db instead of your local aspnetdb.  

Here is a fragment of my web.config, as you can see I've replaced 'LocalSqlServer' with 'RemoteServer', and then added connectionStringName='RemoteServer' to every provider that I wanted to be stored on my remote server.

 <connectionStrings>
            <remove name="LocalSqlServer"/>
            <add name="RemoteServer"
      connectionString="Password=passwd;Persist Security Info=True;User ID=user;Initial Catalog=mydb;Data Source=remotesvr"
      providerName="System.Data.SqlClient"/>
      </connectionStrings>

<roleManager enabled="true"
        cacheRolesInCookie="true"
        defaultProvider="AspNetSqlRoleProvider"
        cookieName=".BNROLEMGR"
        cookiePath="/"
        cookieTimeout="30"
        cookieRequireSSL="false"
        cookieSlidingExpiration="true"
        createPersistentCookie="false"
        cookieProtection="All" >
      <providers>
      <remove name="AspNetSqlRoleProvider"/>
      <add connectionStringName="RemoteServer"
          applicationName="/"
          name="AspNetSqlRoleProvider"
          type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
                        <remove name="AspNetWindowsTokenRoleProvider"/>
                  </providers>
            </roleManager>


<membership>
                  <providers>
                        <remove name="AspNetSqlMembershipProvider"/>
                        <add connectionStringName="RemoteServer"
            enablePasswordRetrieval="false"
            enablePasswordReset="true"
            requiresQuestionAndAnswer="true"
            applicationName="/"
            requiresUniqueEmail="false"
            passwordFormat="Hashed"
            maxInvalidPasswordAttempts="5"
            minRequiredPasswordLength="7"
            minRequiredNonalphanumericCharacters="1"
            passwordAttemptWindow="10"
            passwordStrengthRegularExpression=""
            name="AspNetSqlMembershipProvider"
            type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"            
         />            
                  </providers>
            </membership>


<profile>
      <providers>
        <remove name="AspNetSqlProfileProvider" />
        <add connectionStringName="RemoteServer"
          applicationName="/"
          name="AspNetSqlProfileProvider"
          type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      </providers>
                  <properties>        
        <add name="FirstName" type="System.String"/>
                        <add name="LastName" type="System.String"/>
                        <add name="Audience" type="System.String"/>
        <add name="SideKickLogin" type="System.String"/>
        <add name="SideKickPWD" type="System.String"/>
      </properties>      
    </profile>
0
 
penlandtAuthor Commented:
Yep, I'm sure I have a good login.  I'm able to open and administer the remote SQL server using SQL Server Management Studio but when I go to the Web Site Administration Tool and try to configure security, I get the following error message:

----------------------------
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'penland', schema 'dbo'.
----------------------------

Here is my web.config file (with usernames and passwords removed)

----------------------------
<?xml version="1.0"?>
<!--
    Note: As an alternative to hand editing this file you can use the
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in
    machine.config.comments usually located in
    \Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
    <appSettings/>
  <connectionStrings>
    <clear/>
    <add name="localSqlServer" connectionString="data source=canal.safesecureweb.com;initial catalog=mydatabase;user id=myuserid;password=mypassword;"/>
  </connectionStrings>
    <system.web>
        <!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.
        -->
        <authorization>
            <deny users="?" />
        </authorization>
        <roleManager enabled="true" />
        <compilation debug="false" />
        <!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
        <authentication mode="Forms" />
        <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
      <customErrors mode="Off"/>
    </system.web>
    <system.net>
        <mailSettings>
            <smtp from="myemailaccount">
                <network host="mymailserver" password="mypassword" userName="myuserid" />
            </smtp>
        </mailSettings>
    </system.net>
</configuration>
---------------------------
0
 
TornadoVCommented:
I had the same problem and it went away when I re-run aspnet_regsql.exe.
0
 
penlandtAuthor Commented:
Tried that again...I think I'm on to something though.  I'm just a "user" on my remote database, not the dbo...so I think I have to grant my userID permissions on all the database objects in order to proceed.  Am I on the right track?  Here's the error I get when trying to run aspnet_regsql.exe against the remote server:

----------------------------------
Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 229 and the SqlException message is: EXECUTE permission denied on object 'aspnet_Setup_RestorePermissions', database 'penland', schema 'dbo'.

----------------------------------------
Details of failure
----------------------------------------

SQL Server:
Database: [penland]
SQL file loaded:
InstallCommon.sql

Commands failed:

-- Restore the permissions
EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_RegisterSchemaVersion'


SQL Exception:
System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'aspnet_Setup_RestorePermissions', database 'penland', schema 'dbo'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
------------------------------------------------
0
 
TornadoVCommented:
I gave my 'userID' dbo rights and never had any issues, I think you're correct, it's definitely permissions at this point.
0
 
penlandtAuthor Commented:
I just had the host company give me dbo rights and everything is working now.  Thanks to everyone who helped, but TornadoV gets the points (for endurance at the very least :-))  Here's what had to be done, in the end, to make this work:

1.  The host (or the user if permitted) must give the user dbo rights in the datbase
2.  The host (or the user if permitted) must run aspnet_regsql.exe against the remote database
3.  The user must put a connection string to the remote database in the connectionStrings section of web.config

After these three steps, everything worked perfectly.  Hopefully this will help someone else avoid many of the irrelevant detours I took on the way.

By the way...if I've missed anything, please feel free to correct me.

Thanks again!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now