Why is dotnet ef not able to create a database against SQL Server Express?

asp.net-core asp.net-mvc entity-framework-core sql-server-express

Question

I have gone through this ASP.Net MVC Core tutorial at: https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/working-with-sql. And I am able to use dotnet migrations to create the database against the localDb with this connection string in the appsettings.json file:

{
  "ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-MvcMovie-3005e0f3-42f4-4163-94cc-f794a05e8b91;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

Now I want to point it to SQL Server Express to see if I can work from there and SSMS.

So now I use this in the appsettings.json file:

{
  "ConnectionStrings": {
   "SqlExpressConnection": "Server=.\\SQLEXPRESS;Database=MvcMovieDb1;Integrated Security=True;MultipleActiveResultSets=true"
  }

And in StartUp.ConfigureServices(), change:

services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

to

services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("SqlExpressConnection")));

Then in the command prompt run:

dotnet ef database update

But now I get: "CREATE DATABASE permission denied in database 'master'.

How can I give ef permission to create the database? Is there a login/password parameter that the dotnet ef can use if I create a login with CREATE Permissions?


Well it seems I don't have DB permissions like I thought.

I just did a test to add a db from within SSMS and I don't seem to have CREATE privilege "CREATE DATABASE Permission denied in database 'master'.

I am in SSMS under my computer admin login account. And I startup SSMS using windows authentication. So the top connection node looks like: FullComputerName\SQLExpress(SQL Server 11.0.6020 - CompanyDomainName\Samuel).

The Windows Authentication login is in the form: CompanyDomainName\Samuel. I don't see my CompanyDomainName\Samuel account under Computer Manager - Users. Not sure how I am able to login to the computer with this account. How can I add this account as a login under Security Logins?

1
0
12/26/2016 6:51:39 PM

Popular Answer

Your local Windows account CompanyDomainName\Samuel is the SQLExpress administrator. It means you are admin of SQLExpress when you are logged on Windows.

The Integrated security=true is used to connect to SQL Server with the Windows account, but it doesn't work because IIS (or Kestrel or any web server) will use a different Windows user account.

You have two options :

  • add the Windows user account of you web server in Security/Logins, and check Windows authentication enter image description here

  • or create a new login with SQL Server authentication, and update you connection string to use it instead
    "Server=.\\SQLEXPRESS;Database=MvcMovieDb1;User Id=dbUserName;Password=thePassword"

(note : if you choose this last solution, you should be aware of potential security issues if you store password in your source code. Read this page for more information)

1
12/26/2016 9:01:15 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow