Does the user authenticating with SQL Server have sufficient permissions to read and execute the stored procedure?
I would think so. The user was setup automatically by the application that manages the database. Given that, I think the user has full priveledges over the db. It is not integrated authentication, it is a user/pass combo.
Hi, I am experiencing a similar problem. I am using a shared hosting provider with a SQL Server 2005 instance and 1
database. I have a simple test application with a Login control and CreateUserWizard control and another aspx page
with a grid control. The grid control works without a problem. When I use the login control, I get the error (see
trace at bottom).
I can successfully use the login control in Web Developer Express 2005 on my local machine using a SQL Server 2000
db using the following connection string in my weg.config:
<connectionStrings>
<add name="MySQLServer" connectionString="Initial Catalog=myDatabaseName;data source=localhost;Integrated
Security=SSPI;"/>
</connectionStrings>
I used C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql to create all the appropriate tables, views and
stored procedures and imported them to my host site. I can see that aspnet_CheckSchemaVersion is there.
On the hosted site I changed the connectionStrings to the following:
<connectionStrings>
<add name="MyApplicationData" connectionString="Data Source=mssql05.HostingCompany.com;Initial
Catalog=MyDatabaseName;UID=MyUser_ID;PWD=myPassword"
providerName="System.Data.SqlClient"/>
<add name="MyMembershipConnectionString" connectionString="Data Source=mssql05.HostingCompany.com;Initial
Catalog=MyDatabaseName;UID=MyUser_ID;PWD=MyPassword"
providerName="System.Data.SqlClient"/>
</connectionStrings>
As you can see I am using the same database for application data and membership data.
I use MyApplicationData as the data source to populate a GridControl. This works successfully.
I use MyMembershipConnectionString with the Login control. I set the MembershipProvider property in the Login
control to MyMembershipConnectionString.
Here are other relevant parts of my web.config file.
<authentication mode="Forms">
<forms loginUrl="Login.aspx" protection="Validation" timeout="300" />
</authentication>
<authorization>
<allow users="*"/>
</authorization>
<membership defaultProvider="MySqlMembershipProvider">
<providers>
<clear/>
<add name="MySqlMembershipProvider" connectionStringName="MyMembershipConnectionString" applicationName="/"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"/>
</providers>
</membership>
Not sure what I am missing? Hopefully you can help me out. Thanks, Jeff
Stack Trace:
[SqlException (0x80131904): Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857306
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734918
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String
resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior,
Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean
returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean
sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features,
String version, Int32& schemaVersionCheck) +367
System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85
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) +1121
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) +83
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
System.Web.UI.WebControls.Login.AttemptLogin() +105
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean
includeStagesAfterAsyncPoint) +5102
The error message is pretty clear, the caller cannot find the stored procedure.
So I would create a little debug code to list out the stored procedures and database name from the aspx page, perhaps you are connecting to an unexpected database that does not have the sprocs installed.
Hi, aspnet_regsql creates files with dbo as the owner. The shared host site uses dboXXXXXXXXX as the owner.
As an example, the following was created by aspnet_regsql:
CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
@.Feature nvarchar(128),
@.CompatibleSchemaVersion nvarchar(128)
AS
BEGIN
IF (EXISTS( SELECT *
FROM dbo.aspnet_SchemaVersions
WHERE Feature = LOWER( @.Feature ) AND
CompatibleSchemaVersion = @.CompatibleSchemaVersion ))
RETURN 0
RETURN 1
END
GO
I assume that if the references to dbo were changed to dboxxxxxxxxx, it would work?
Is there a way that I can avoid hand editing all the files? A setting I can use when running aspnet_regsql? Other solution?
Thanks....Jeff
dbo is a special account. All it means is a member of the sysadmin group created the object.
When sql looks for an object, it first looks for objects owned by userX, then objects owned by dbo.
So everyone in the database should be able to access objects owned by dbo, there is no need to create the objects under another user account.
I suspect you just need to run aspnet_regsql against the target database and you should be set. If this does not work let me know what version of SQL you are running against and what version of .NET you are using.
Thanks Matt...I will see if my hosting site (www.1and1.com) supports the aspnet_regsql.exe and go from there. Not exactly sure how I would run it...the only SQL tool I have found at the host site is QueryAnalyzer. No equivalent of a command line prompt. Jeff
Jeff,
Did you ever solve this problem with 1and1?
I am having the exact same problem right now and it's driving me crazy!
Does anyone have a solution?
You might want to post some code. If you're not using an alias for the sp then you may not be fully qualifying the stored procedure name(http://msdn2.microsoft.com/en-us/library/ms187879.aspx). Otherwise it's probably occuring because the authenticated account does not have sufficient permissions to access the sp.
Hi, I did resolve my issue. There may be a more straightforward solution, but this worked for
me. If anyone has any success using a more direct approach, please let me know.
The error I was getting ('Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion') seems
to be caused by the fact that the stored procedures in the aspnetdb.mdf explicitly reference
'dbo' (as the database owner). My hosting company assigns a database owner such as
'dboxxxxxxxxx', where xxxxxxxxx = your user account. After uploading and importing the the
aspnetdb files, I ran the script below to change the database owner for all of the aspnetdb
objects. After that, my application worked.
Here are the steps I used:
1) On my local machine, I created the necessary tables, stored procedures and views using the
aspnet_regsql.exe utility (see C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 aspnet_regsql.exe).
2) Upload/import or create these files on the host site.
3) Using the SQLServer Query Analyzer provided by my hosting company (www.1and1.com), I ran the
script (see below) to change the database owner to 'dbo'.
sp_changeobjectowner aspnet_Applications,dbo
GO
sp_changeobjectowner aspnet_Membership,dbo
GO
sp_changeobjectowner aspnet_Paths,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAllUsers,dbo
GO
sp_changeobjectowner aspnet_PersonalizationPerUser,dbo
GO
sp_changeobjectowner aspnet_Profile,dbo
GO
sp_changeobjectowner aspnet_Roles,dbo
GO
sp_changeobjectowner aspnet_SchemaVersions,dbo
GO
sp_changeobjectowner aspnet_Users,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles,dbo
GO
sp_changeobjectowner aspnet_WebEvent_Events,dbo
GO
sp_changeobjectowner aspnet_AnyDataInTables,dbo
GO
sp_changeobjectowner aspnet_Applications_CreateApplication,dbo
GO
sp_changeobjectowner aspnet_CheckSchemaVersion,dbo
GO
sp_changeobjectowner aspnet_Membership_ChangePasswordQuestionAndAnswer,dbo
GO
sp_changeobjectowner aspnet_Membership_CreateUser,dbo
GO
sp_changeobjectowner aspnet_Membership_FindUsersByEmail,dbo
GO
sp_changeobjectowner aspnet_Membership_FindUsersByName,dbo
GO
sp_changeobjectowner aspnet_Membership_GetAllUsers,dbo
GO
sp_changeobjectowner aspnet_Membership_GetNumberOfUsersOnline,dbo
GO
sp_changeobjectowner aspnet_Membership_GetPassword,dbo
GO
sp_changeobjectowner aspnet_Membership_GetPasswordWithFormat,dbo
GO
sp_changeobjectowner aspnet_Membership_GetUserByEmail,dbo
GO
sp_changeobjectowner aspnet_Membership_GetUserByName,dbo
GO
sp_changeobjectowner aspnet_Membership_GetUserByUserId,dbo
GO
sp_changeobjectowner aspnet_Membership_ResetPassword,dbo
GO
sp_changeobjectowner aspnet_Membership_SetPassword,dbo
GO
sp_changeobjectowner aspnet_Membership_UnlockUser,dbo
GO
sp_changeobjectowner aspnet_Membership_UpdateUser,dbo
GO
sp_changeobjectowner aspnet_Membership_UpdateUserInfo,dbo
GO
sp_changeobjectowner aspnet_Paths_CreatePath,dbo
GO
sp_changeobjectowner aspnet_Personalization_GetApplicationId,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAdministration_DeleteAllState,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAdministration_FindState,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAdministration_GetCountOfState,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAdministration_ResetSharedState,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAdministration_ResetUserState,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAllUsers_GetPageSettings,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAllUsers_ResetPageSettings,dbo
GO
sp_changeobjectowner aspnet_PersonalizationAllUsers_SetPageSettings,dbo
GO
sp_changeobjectowner aspnet_PersonalizationPerUser_GetPageSettings,dbo
GO
sp_changeobjectowner aspnet_PersonalizationPerUser_ResetPageSettings,dbo
GO
sp_changeobjectowner aspnet_PersonalizationPerUser_SetPageSettings,dbo
GO
sp_changeobjectowner aspnet_Profile_DeleteInactiveProfiles,dbo
GO
sp_changeobjectowner aspnet_Profile_DeleteProfiles,dbo
GO
sp_changeobjectowner aspnet_Profile_GetNumberOfInactiveProfiles,dbo
GO
sp_changeobjectowner aspnet_Profile_GetProfiles,dbo
GO
sp_changeobjectowner aspnet_Profile_GetProperties,dbo
GO
sp_changeobjectowner aspnet_Profile_SetProperties,dbo
GO
sp_changeobjectowner aspnet_RegisterSchemaVersion,dbo
GO
sp_changeobjectowner aspnet_Roles_CreateRole,dbo
GO
sp_changeobjectowner aspnet_Roles_DeleteRole,dbo
GO
sp_changeobjectowner aspnet_Roles_GetAllRoles,dbo
GO
sp_changeobjectowner aspnet_Roles_RoleExists,dbo
GO
sp_changeobjectowner aspnet_Setup_RemoveAllRoleMembers,dbo
GO
sp_changeobjectowner aspnet_Setup_RestorePermissions,dbo
GO
sp_changeobjectowner aspnet_UnRegisterSchemaVersion,dbo
GO
sp_changeobjectowner aspnet_Users_CreateUser,dbo
GO
sp_changeobjectowner aspnet_Users_DeleteUser,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles_AddUsersToRoles,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles_FindUsersInRole,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles_GetRolesForUser,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles_GetUsersInRoles,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles_IsUserInRole,dbo
GO
sp_changeobjectowner aspnet_UsersInRoles_RemoveUsersFromRoles,dbo
GO
sp_changeobjectowner aspnet_WebEvent_LogEvent,dbo
GO
sp_changeobjectowner vw_aspnet_WebPartState_User,dbo
GO
sp_changeobjectowner vw_aspnet_Applications,dbo
GO
sp_changeobjectowner vw_aspnet_MembershipUsers,dbo
GO
sp_changeobjectowner vw_aspnet_Profiles,dbo
GO
sp_changeobjectowner vw_aspnet_Roles,dbo
GO
sp_changeobjectowner vw_aspnet_Users,dbo
GO
sp_changeobjectowner vw_aspnet_UsersInRoles,dbo
GO
sp_changeobjectowner vw_aspnet_WebPartState_Paths,dbo
GO
sp_changeobjectowner vw_aspnet_WebPartState_Shared,dbo
GO
JeffNBoston , love you so much.
You saved me like God!
Thank you so much.
Radishj, glad that worked for you.....J=-
Just wanted to say thank you very much also. I am also trying to setup a web site on 1and1.com as the host. I was experiencing the exact same problems. I have successfully ran your scripts. I have been struggling with this for about a week. I should have checked here first!
Again, thank you.
i also was having the same problem, but solved it a different way.
run aspnet_regsql from the command line and use the option to have it export the sql code needed to generate the necessary tables and store procedures. then you can just upload the resulting thesql.sql file to the web based tool on 1&1. you need to specify the database here, so after the -d flag put in dbxxxxxxxxxxx, (or whatever you were assigned from 1&1). the -A flag lets you specify which tables you want - you might as well create them all, but you could just choose membership..
i did
aspnet_regsql -A all -sqlexportonly thesql.sql -d dbxxxxxxxxxxx
No comments:
Post a Comment