Single database for ASPNETDB.mdf and database.mdf

grouchy · August 26th, 2011, 11:44 AM

I have deployed both db from the App Data file to a single database. The data from the database.mdf is accessible but the ASPNETDB is throwing errors. Specifically "26 Error locating server/instance" I have worked through the security, deployment and Appendix B (3.5) but no joy as yet and varied everything I can think of.

here is the webconfig. The provider uses SQL Server authentication.

Code:

<configuration>
  <connectionStrings>
    <clear/>
    <add name="ConnectionString" connectionString="Data Source=213.171.201.131; Initial Catalog=Firstdb; User ID=xxxx;       Password=xxxx;" providerName="System.Data.SqlClient"/>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="Data Source=213.171.201.131; Initial Catalog=Firstdb; User ID=xxxx;         Password=xxxx;" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <customErrors mode="Off"/>
    <roleManager enabled="true"/>
    <authentication mode="Forms"/>
  </system.web>
</configuration>

Imar · August 26th, 2011, 12:52 PM

Not sure why this doesn't work, as it looks good to me. Is this your complete config file? Have you tried this config file in a simple web site with nothing but, say, a Login control? Did you run aspnet_regsl against the target database? And where exactly (inlcuding the stack trace if you have it) do you get the error?

The only odd thing I see is a Remove statement after a Clear but that would throw a different error if it wasn't allowed, I'd say.

Cheers,

Imar

grouchy · August 26th, 2011, 03:24 PM

thanks Imar

I do have other config files generated in sub folders relating to permissions i.e. a admin folder. This is the asp_regsl output:

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 262 and the SqlException message is: CREATE DATABASE permission denied in database 'master'.
Creating the aspnetdb database...

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

Code:

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

Commands failed:

DECLARE @dbname nvarchar(128)
DECLARE @dboptions nvarchar(1024)

SET @dboptions = N'/**/'
SET @dbname = N'aspnetdb'

IF (NOT EXISTS (SELECT name
                FROM master.dbo.sysdatabases
                WHERE name = @dbname))
BEGIN
  PRINT 'Creating the ' + @dbname + ' database...'
  DECLARE @cmd nvarchar(500)
  SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
  EXEC(@cmd)
END


SQL Exception:
System.Data.SqlClient.SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
Creating the aspnetdb database...
   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()
   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)

Imar · August 26th, 2011, 03:29 PM

What command are you feeding to aspnet_regsql? The error message says the account you use doesn't have permissions to create the database, but I thought the database was already there?

Imar

grouchy · August 27th, 2011, 12:36 AM

As you suggested I amended the site to one login page. Again all works fine locally before deployment. The database is on the host and contains all the tables. I'm asking the provider for advice but nothing yet. It is likely to be my error as this is my first deployment to a database.

It does seem that some SQL management tools are blocked (greyed out) i.e. restart or do not function as expected - I assume this is a host issue - so have not been able to follow all your instructions to the letter.

Error received on submitting login details following my changes is:

The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Firstdb', schema 'dbo'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

Code:

[SqlException (0x80131904): The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Firstdb', schema 'dbo'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +378
   System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +89
   System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +815
   System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
   System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
   System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78
   System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +60
   System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +119
   System.Web.UI.WebControls.Login.AttemptLogin() +115
   System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Imar · August 27th, 2011, 03:36 AM

The account used to access the database also needs permissions to execute the stored procedures used by Membership and Roles (as well as reead/write access to the Tables and Views). The aspnet_regsql creates a number of roles that start with aspnet_ that your account needs to be in. Page 766 in Appendix B shows the configuration screen for the roles (it doesn't list the aspnet_ roles, but on your database you should see them).

If you can't seem to access these settings, your host needs to do that for you.

Cheers,

Imar

grouchy · August 27th, 2011, 11:41 AM

Getting there I think, but my book doesn't have a 766 but 700 looks roughly right.

So, although my db works fine for content, I must specifically create (re-create) the roles required on the server to support access for my roles via the login.

I do this by opening my database on the host server, and then security, roles, database roles. Right click lets me create a new role i.e. 'manager'. This then appears as a role.

Not quite sure how to allocate permissions - do I (for example) then open db_reader and add 'manager' as a role member?

thanks.

Imar · August 28th, 2011, 10:01 AM

When you run aspnet_regsql, the tool does all this for you. E.g. it creates the correct roles to which you can then assign the proper permissions.

If you decide to do this manually (although I wouldn't recommend it; I would try running aspnet_regsql against my target database again, or against a local database and then deploy the SQL scripts), take a look at files such as InstallMembership.sql in the folder C:\Windows\Microsoft.NET\Framework\v2.0.50727. You'll find examples of creating the roles and assigning the permissions to it.

Cheers,

Imar

grouchy · August 29th, 2011, 07:07 AM

Thanks for your help. It is now functioning - I invested in your ASP.NET 4 book and 766 really is right..... ended up on this forum as I am working in 4 but using your 3.5. Just needed to be reassured that I was looking in the right area.

In summary the Administration Tool is great for getting things set up but is unlikely to be supported by the host. I don't need to create new roles but need to check all aspnet_ under Role Members for the User Id account and that's it.

Imar · August 29th, 2011, 05:40 PM

Ah, didn't know that; would have given you the page number for the 3.5 version if I had.

Anyway, thanks for your support and glad to hear it's working now.

Cheers,

Imar