How to implement ASP.NET identity: CREATE DATABASE permission denied in database 'master'

Question

First of all, I already checked here: ASP.Net Identity how to set target DB?

I'm now getting this error

CREATE DATABASE permission denied in 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) +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 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
System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) +916
System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +117
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
System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +169
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
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
System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() +21
System.Data.Entity.Infrastructure.DbQuery1.System.Linq.IQueryable.get_Provider() +59
System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable1 source, Expression1 predicate, CancellationToken cancellationToken) +208
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
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(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

I changed the DbContext initialization in AppModel.vb to my existing connection string conn1, which points to the SQL Server database I already converted to the new identity tables.

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:

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.

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 master table as I'd expect that I'm not trying to connect to that in any way by the manager.Find method.

Anyway, the user through which I'm currently testing is already assigned to the sysadmin role:

UPDATE 2

Ok, thanks to Jeremy's comment below I'm a step closer...I needed to add user IIS APPPOOL\.NET v4.5 to 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))

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'

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 user is Nothing....What can it be?

Answer 1

Try this, if your application pool is running under a NETWORK SERVICE identity. Then give SysAdmin Role for “NT AUTHORITY\NETWORK SERVICE”:

Or give SysAdmin to which ever account running the app pool.

If that doesn't work, check if any of these solutions help: 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.

And what is going wrong with the FindByName method?

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?

Answer 2

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.

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

Answer 3

Why instead to utilize an identity field don´t you utilize a HASH value in a NVARCHAR() field?

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.

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.

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.

Answer 4

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.

Change the default database for sa to myapp, here:

Answer 5

Your user should be added to the dbcreator role.

Otherwise you're not allowed to create new databases.

Sysadmin/public are not enough.