vb.net 如何实现 ASP.NET 身份：在数据库“master”中拒绝 CREATE DATABASE 权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31868943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to implement ASP.NET identity: CREATE DATABASE permission denied in database 'master'
提问by Flo
First of all, I already checked here: ASP.Net Identity how to set target DB?
首先，我已经在这里检查过： ASP.Net Identity how to set target DB?
I'm now getting this error
我现在收到这个错误
CREATE DATABASE permission denied in database 'master'.
CREATE DATABASE 权限在数据库“master”中被拒绝。
On this line of code:
在这行代码中：
Dim user As User = manager.Find(Trim(Username.Text), Trim(Password.Text))
Full error:
完整错误：
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +32498521 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +367
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +345
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4927
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1287
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
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) +3471 act) +916
System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action
System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +1171 commandTimeout, StoreItemCollection storeItemCollection) +212
System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable
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) +1211.InitializeDatabase(TContext context) +257
System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +169
System.Data.Entity.CreateDatabaseIfNotExists
System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +72
System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +483
System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) +1771 action) +274
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action
System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +37
System.Data.Entity.Internal.Linq.InternalSet1.Initialize() +761.get_InternalContext() +21
System.Data.Entity.Internal.Linq.InternalSet
System.Data.Entity.Infrastructure.DbQuery1.System.Linq.IQueryable.get_Provider() +591 source, Expression
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable1 predicate, CancellationToken cancellationToken) +2081 source, Expression`1 predicate) +172
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryableMicrosoft.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() +481 func) +348
Microsoft.AspNet.Identity.<FindAsync>d__12.MoveNext() +357
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13855856
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
Microsoft.AspNet.Identity.AsyncHelper.RunSync(Func
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
[SqlException (0x80131904): CREATE DATABASE 权限在数据库 'master' 中被拒绝。]
System.Data.SqlClient.SqlConnection.OnError(SqlException 异常, Boolean breakConnection, Action 1 完成, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) + 367 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +386 System.Data.Entity.Infrastructure.Interception.InternalDispatcher 3 操作，TInterceptionContext 拦截上下文，Action 3 执行) +965 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery （DbCommand 命令，DbCommandInterceptionContext 拦截 上下文）+505 System.Data.Entity.SqlServer.<>c__DisplayClass1a.b__19(DbConnection conn) +1361 wrapCloseInAction) +3249852
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +345
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4927
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1287
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1.Dispatch(TTarget target, Func3 executing, Action
System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action 1 act) +916 System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable 1 commandTimeout, StoreItemCollection storeItemCollection) +212 System.Data.Entity.Migrations. Utilities.DatabaseCreator.Create(DbConnection 连接) +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, Func 1.InitializeDatabase(TContext context) +2571 act) +347
System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +117
System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable3 createMigrator, ObjectContext objectContext) +121
System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +169
System.Data.Entity.CreateDatabaseIfNotExists
System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +72
System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +483
System.Data.Entity.Internal.RetryAction 1 action) +274 System.Data. Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +37 System.Data.Entity.Internal.Linq.InternalSet 1.get_InternalContext() +21 System.Data.Entity.Infrastructure.DbQuery 1源，表达式1源，表达式` 1谓词）+1721.PerformAction(TInput input) +177
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1.Initialize() +76
System.Data.Entity.Internal.Linq.InternalSet1.System.Linq.IQueryable.get_Provider() +59
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable1 predicate, CancellationToken cancellationToken) +208
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryableMicrosoft.AspNet.Identity.EntityFramework.d__6c.MoveNext() +502
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13855856
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
Microsoft.IdentityAsp .CultureAwaiter 1 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) +33601.GetResult() +48
Microsoft.AspNet.Identity.<FindAsync>d__12.MoveNext() +357
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13855856
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
Microsoft.AspNet.Identity.AsyncHelper.RunSync(Func
I changed the DbContextinitialization in AppModel.vbto my existing connection string conn1, which points to the SQL Server database I already converted to the new identity tables.
我将DbContext初始化更改AppModel.vb为我现有的连接字符串conn1，它指向我已经转换为新标识表的 SQL Server 数据库。
My connection string:
我的连接字符串：
<add name="conn1"
connectionString="data source=(local)\sqlexpress;Initial Catalog=myapp;User Id=sa;Password=XXXX;"
providerName="System.Data.SqlClient" />
AppModel.vb:
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
UPDATE
更新
Based on a comment I also checked here.
根据评论，我也检查了here。
I don't really get why this is a role issue, since I'm reusing a connection string that is already working fine with updating other (non-identity related) tables. Also I don't get why the error refers to the mastertable as I'd expect that I'm not trying to connect to that in any way by the manager.Findmethod.
我真的不明白为什么这是一个角色问题，因为我正在重用一个连接字符串，该字符串在更新其他（非身份相关）表时已经可以正常工作。此外，我不明白为什么错误会引用该master表，因为我希望我不会尝试通过该manager.Find方法以任何方式连接到该表。
Anyway, the user through which I'm currently testing is already assigned to the sysadminrole:
无论如何，我目前正在测试的用户已经被分配到这个sysadmin角色：
UPDATE 2
更新 2
Ok, thanks to Jeremy's comment below I'm a step closer...I needed to add user IIS APPPOOL\.NET v4.5to role sysadmin, because that was the user connecting as shown in SQL Server profiler (although I'm unsure about the security risks when adding this user to this role).
Anyway, the Find method no longer throws an error and using SQL Server profiler I see the SQL statement is fired when I execute this code Dim user As User = manager.FindByName(Trim(Username.Text))
好的，感谢下面 Jeremy 的评论，我更接近了......我需要将 user 添加IIS APPPOOL\.NET v4.5到 role sysadmin，因为那是 SQL Server 探查器中显示的用户连接（尽管我不确定添加此用户时的安全风险到这个角色）。无论如何，Find 方法不再抛出错误，并且使用 SQL Server 探查器我看到执行此代码时触发了 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'[email protected]'
The strange thing is: when I execute it directly in SQL Server Management Studio I get a record returned, but in my code the variable useris Nothing....What can it be?
奇怪的是：当我直接在 SQL Server Management Studio 中执行它时，我得到了一条记录返回，但在我的代码中，变量user是Nothing......它是什么？
回答by Bill
You connection string contains "User Id=sa;Password=XXXX", thus that connection is attempting to use SQL Server authentication. Your SSMS login property's title bar appears to be identifying a Windows account, which implies there is a belief that Windows Authentication should be used. And yet the connection is attempting to use SQL Server authentication (not Windows authentication). If not already configured, SQL Server's authentication mode can be changed to support both SQL Authentication and Windows Authentication ("Mixed Mode" authentication) see https://msdn.microsoft.com/en-us/library/ms188670.aspx.
您的连接字符串包含“User Id=sa;Password=XXXX”，因此该连接正在尝试使用 SQL Server 身份验证。您的 SSMS 登录属性的标题栏似乎标识了一个 Windows 帐户，这意味着应该使用 Windows 身份验证。然而该连接正在尝试使用 SQL Server 身份验证（而不是 Windows 身份验证）。如果尚未配置，可以更改 SQL Server 的身份验证模式以支持 SQL 身份验证和 Windows 身份验证（“混合模式”身份验证），请参阅https://msdn.microsoft.com/en-us/library/ms188670.aspx。
Alternatively, the connection string can be changed to use Windows authentication/integrated security, per https://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx#integratedsecurity
或者，可以根据https://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx#integratedsecurity更改连接字符串以使用 Windows 身份验证/集成安全
回答by Jeremy Thompson
Try this, if your application pool is running under a NETWORK SERVICE identity. Then give SysAdmin Role for “NT AUTHORITY\NETWORK SERVICE”:
如果您的应用程序池在 NETWORK SERVICE 身份下运行，请尝试此操作。然后为“NT AUTHORITY\NETWORK SERVICE”赋予 SysAdmin 角色：
Or give SysAdmin to which ever account running the app pool.
或者将 SysAdmin 分配给运行应用程序池的任何帐户。
If that doesn't work, check if any of these solutions help: CREATE DATABASE permission denied in database 'master' (EF code-first)
如果这不起作用，请检查这些解决方案中的任何一个是否有帮助：CREATE DATABASE permission denied in database 'master' (EF code-first)
Edit:
编辑：
Its not very secure to give all these accounts sysAdmin. After you get this working tighten up the SQL security to just dbReader and dbWriter roles.
将所有这些帐户都授予 sysAdmin 并不是很安全。完成这项工作后，将 SQL 安全性加强到 dbReader 和 dbWriter 角色。
And what is going wrong with the FindByName method?
FindByName 方法出了什么问题？
Now there is no exception and the problem is that no data is returned I think there is still something going on with permissions.
现在没有例外，问题是没有返回任何数据我认为权限仍然存在。
Since you're using SQL Authentication in the Connection String, can you try it with Windows Integrated? Otherwise try using the appPool IIS accounts credentials in your connection string and see if that yields a clue as to whats wrong?
由于您在连接字符串中使用 SQL 身份验证，您可以尝试使用 Windows Integrated 吗？否则，请尝试在您的连接字符串中使用 appPool IIS 帐户凭据，看看这是否会产生问题的线索？
回答by David BS
Why instead to utilize an identity field don′t you utilize a HASH value in a NVARCHAR() field?
为什么要使用身份字段而不使用 NVARCHAR() 字段中的 HASH 值？
For instance: you may get an individual data from user (or a join of his fields), calculate a HASH from .NET Cryptography library and set that NVARCHAR() with the data.
例如：您可以从用户那里获取单个数据（或他的字段的连接），从 .NET Cryptography 库计算 HASH 并使用数据设置 NVARCHAR()。
This way (and supposing you′re utilizing SHA-512 HASH) you will be able to have a Base64 string of an individual data, like an Identity itself.
这样（并且假设您正在使用 SHA-512 HASH），您将能够拥有单个数据的 Base64 字符串，例如身份本身。
But, if your goal is have an identity to the momentum, you may get the user data, plus the current datetime, calculate its HASH and save this data as the record′s identity.
但是，如果你的目标是对动量有一个标识，你可能会得到用户数据，加上当前的日期时间，计算它的 HASH 并将这些数据保存为记录的标识。
回答by smoore4
You should probably use a different SQL account than "sa" but the default database for sa is master. The Find method is likely using the default database for the user even though myapp is defined in the connection string.
您可能应该使用与“sa”不同的 SQL 帐户，但 sa 的默认数据库是 master。即使在连接字符串中定义了 myapp，Find 方法也可能使用用户的默认数据库。
Change the default database for sa to myapp, here:
将 sa 的默认数据库更改为 myapp，在这里：
回答by Ismail Hassani
Your user should be added to the dbcreator role.
您的用户应该被添加到 dbcreator 角色。
Otherwise you're not allowed to create new databases.
否则你不能创建新的数据库。
Sysadmin/public are not enough.
系统管理员/公共是不够的。
