asp.net - 如何实现ASP.NET身份: CREATE DATABASE permission denied in database 'master'

Question

首先，我已经在这里检查过: ASP.Net Identity how to set target DB?

我现在收到此错误

CREATE DATABASE permission denied in database 'master'.

在这行代码上:

Dim user As User = manager.Find(Trim(Username.Text), Trim(Password.Text))

完整错误:

[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +3249852<br/>
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +345<br/>
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4927<br/>
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1287<br/>
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +367
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +386
System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) +965
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) +505
System.Data.Entity.SqlServer.<>c__DisplayClass1a.b__19(DbConnection conn) +136
System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action1 act) +347<br/>
System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) +916
System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +117<br/>
System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) +212
System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) +172
System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +175
System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +116
System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func3 createMigrator, ObjectContext objectContext) +121<br/>
System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +169<br/>
System.Data.Entity.CreateDatabaseIfNotExists1.InitializeDatabase(TContext context) +257
System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +72
System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +483
System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) +177<br/>
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1 action) +274
System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +37
System.Data.Entity.Internal.Linq.InternalSet1.Initialize() +76<br/>
System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() +21
System.Data.Entity.Infrastructure.DbQuery1.System.Linq.IQueryable.get_Provider() +59<br/>
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable1 source, Expression1 predicate, CancellationToken cancellationToken) +208<br/>
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable1 source, Expression`1 predicate) +172

Microsoft.AspNet.Identity.EntityFramework.d__6c.MoveNext() +502
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13855856
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
Microsoft.AspNet.Identity.CultureAwaiter1.GetResult() +48<br/>
Microsoft.AspNet.Identity.<FindAsync>d__12.MoveNext() +357<br/>
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13855856<br/>
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61<br/>
Microsoft.AspNet.Identity.AsyncHelper.RunSync(Func1 func) +348
MyApp.Login_identity.UserLogin_Click(Object sender, EventArgs e) in C:\MyApp\Login_identity.aspx.vb:168
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +11747645
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +150
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3360

我更改了DbContext AppModel.vb 中的初始化到我现有的连接字符串 conn1 ，它指向我已经转换为新标识表的 SQL Server 数据库。

我的连接字符串:

<add name="conn1" 
     connectionString="data source=(local)\sqlexpress;Initial Catalog=myapp;User Id=sa;Password=XXXX;" 
     providerName="System.Data.SqlClient" />

AppModel.vb :

Imports Microsoft.AspNet.Identity
Imports Microsoft.AspNet.Identity.EntityFramework
Imports System.Collections.Generic
Imports System.ComponentModel.DataAnnotations
Imports System.Data.Entity
Imports System.Linq
Imports System.Web

Namespace AspnetIdentitySample.Models
    Public Class ApplicationUser
        Inherits IdentityUser
        ' HomeTown will be stored in the same table as Users
        Public Property HomeTown() As String
            Get
                Return m_HomeTown
            End Get
            Set(value As String)
                m_HomeTown = Value
            End Set
        End Property
        Private m_HomeTown As String
        Public Overridable Property ToDoes() As ICollection(Of ToDo)
            Get
                Return m_ToDoes
            End Get
            Set(value As ICollection(Of ToDo))
                m_ToDoes = Value
            End Set
        End Property
        Private m_ToDoes As ICollection(Of ToDo) 

        ' FirstName & LastName will be stored in a different table called MyUserInfo
        Public Overridable Property MyUserInfo() As MyUserInfo
            Get
                Return m_MyUserInfo
            End Get
            Set(value As MyUserInfo)
                m_MyUserInfo = Value
            End Set
        End Property
        Private m_MyUserInfo As MyUserInfo 
    End Class

    Public Class MyUserInfo
        Public Property Id() As Integer
            Get
                Return m_Id
            End Get
            Set(value As Integer)
                m_Id = Value
            End Set
        End Property
        Private m_Id As Integer
        Public Property FirstName() As String
            Get
                Return m_FirstName
            End Get
            Set(value As String)
                m_FirstName = Value
            End Set
        End Property
        Private m_FirstName As String
        Public Property LastName() As String
            Get
                Return m_LastName
            End Get
            Set(value As String)
                m_LastName = Value
            End Set
        End Property
        Private m_LastName As String
    End Class

    Public Class ToDo
        Public Property Id() As Integer
            Get
                Return m_Id
            End Get
            Set(value As Integer)
                m_Id = Value
            End Set
        End Property
        Private m_Id As Integer
        Public Property Description() As String
            Get
                Return m_Description
            End Get
            Set(value As String)
                m_Description = Value
            End Set
        End Property
        Private m_Description As String
        Public Property IsDone() As Boolean
            Get
                Return m_IsDone
            End Get
            Set(value As Boolean)
                m_IsDone = Value
            End Set
        End Property
        Private m_IsDone As Boolean
        Public Overridable Property User() As ApplicationUser
            Get
                Return m_User
            End Get
            Set(value As ApplicationUser)
                m_User = Value
            End Set
        End Property
        Private m_User As ApplicationUser 
    End Class
    Public Class MyDbContext
        Inherits IdentityDbContext(Of ApplicationUser)
        Public Sub New()
            MyBase.New("conn1") 'DefaultConnection
        End Sub

        Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
            MyBase.OnModelCreating(modelBuilder)

            ' Change the name of the table to be Users instead of AspNetUsers
            modelBuilder.Entity(Of IdentityUser)().ToTable("Users")
            modelBuilder.Entity(Of ApplicationUser)().ToTable("Users")
        End Sub

        Public Property ToDoes() As DbSet(Of ToDo)
            Get
                Return m_ToDoes
            End Get
            Set(value As DbSet(Of ToDo))
                m_ToDoes = Value
            End Set
        End Property
        Private m_ToDoes As DbSet(Of ToDo)

        Public Property MyUserInfo() As DbSet(Of MyUserInfo)
            Get
                Return m_MyUserInfo
            End Get
            Set(value As DbSet(Of MyUserInfo))
                m_MyUserInfo = Value
            End Set
        End Property
        Private m_MyUserInfo As DbSet(Of MyUserInfo)
    End Class


End Namespace

更新

根据评论，我还检查了 here .

我真的不明白为什么这是一个角色问题，因为我正在重用一个连接字符串，该连接字符串已经可以很好地更新其他(非身份相关)表。另外我不明白为什么错误指的是 master正如我所期望的那样，我不会尝试通过 manager.Find 以任何方式连接到该表。方法。

无论如何，我当前测试的用户已经分配给 sysadmin角色:

更新2

好的，感谢 Jeremy 下面的评论，我又近了一步...我需要添加用户 IIS APPPOOL\.NET v4.5角色sysadmin ，因为这是 SQL Server Profiler 中所示的用户连接(尽管我不确定将此用户添加到此角色时是否存在安全风险)。无论如何，Find 方法不再抛出错误，并且使用 SQL Server Profiler，我看到执行此代码时会触发 SQL 语句 Dim user As User = manager.FindByName(Trim(Username.Text))

exec sp_executesql N'SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[ApplicationId] AS [ApplicationId], 
[Extent1].[MobileAlias] AS [MobileAlias], 
[Extent1].[IsAnonymous] AS [IsAnonymous], 
[Extent1].[LastActivityDate] AS [LastActivityDate], 
[Extent1].[MobilePIN] AS [MobilePIN], 
[Extent1].[LoweredEmail] AS [LoweredEmail], 
[Extent1].[LoweredUserName] AS [LoweredUserName], 
[Extent1].[PasswordQuestion] AS [PasswordQuestion], 
[Extent1].[PasswordAnswer] AS [PasswordAnswer], 
[Extent1].[IsApproved] AS [IsApproved], 
[Extent1].[IsLockedOut] AS [IsLockedOut], 
[Extent1].[CreateDate] AS [CreateDate], 
[Extent1].[LastLoginDate] AS [LastLoginDate], 
[Extent1].[LastPasswordChangedDate] AS [LastPasswordChangedDate], 
[Extent1].[LastLockoutDate] AS [LastLockoutDate], 
[Extent1].[FailedPasswordAttemptCount] AS [FailedPasswordAttemptCount], 
[Extent1].[FailedPasswordAttemptWindowStart] AS [FailedPasswordAttemptWindowStart], 
[Extent1].[FailedPasswordAnswerAttemptCount] AS [FailedPasswordAnswerAttemptCount], 
[Extent1].[FailedPasswordAnswerAttemptWindowStart] AS [FailedPasswordAnswerAttemptWindowStart], 
[Extent1].[Comment] AS [Comment], 
[Extent1].[Email] AS [Email], 
[Extent1].[EmailConfirmed] AS [EmailConfirmed], 
[Extent1].[PasswordHash] AS [PasswordHash], 
[Extent1].[SecurityStamp] AS [SecurityStamp], 
[Extent1].[PhoneNumber] AS [PhoneNumber], 
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
[Extent1].[LockoutEnabled] AS [LockoutEnabled], 
[Extent1].[AccessFailedCount] AS [AccessFailedCount], 
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE ((UPPER([Extent1].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'flo@outlook.com'

奇怪的是:当我直接在 SQL Server Management Studio 中执行它时，我得到一条返回记录，但在我的代码中变量 user是 Nothing ....它会是什么？

Answer 1

如果您的应用程序池在网络服务标识下运行，请尝试此操作。然后为“NT AUTHORITY\NETWORK SERVICE”指定系统管理员角色:

或者将系统管理员授予运行应用程序池的帐户。

如果这不起作用，请检查以下解决方案是否有帮助:CREATE DATABASE permission denied in database 'master' (EF code-first)

编辑:

将所有这些帐户授予 sysAdmin 并不是很安全。完成此工作后，将 SQL 安全性加强到仅 dbReader 和 dbWriter 角色。

And what is going wrong with the FindByName method?

现在没有异常，问题是没有返回数据我认为权限方面仍然存在一些问题。

由于您在连接字符串中使用 SQL 身份验证，您可以尝试使用 Windows Integrated 吗？否则，请尝试在连接字符串中使用 appPool IIS 帐户凭据，看看是否能提供有关问题所在的线索？