p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 4 General Discussion (http://p2p.wrox.com/forumdisplay.php?f=561)
-   -   Single database for ASPNETDB.mdf and database.mdf (http://p2p.wrox.com/showthread.php?t=84866)

grouchy August 26th, 2011 11:44 AM

Single database for ASPNETDB.mdf and database.mdf
 
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

aspnet_regsl against the target database
 
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

Single database for ASPNETDB.mdf and database.mdf
 
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


All times are GMT -4. The time now is 07:25 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.