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

Question

I have gone through this ASP.Net MVC Core tutorial at: https://learn.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?

Answer 1

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

• 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)

Answer 2

I was never going to have priveledges since who ever built this laptop and installed SQL Server (2012) did it under their account. So I installed SQL Server Express Advances 2014 and SSMS 2014. Now I can create DBs directly in SSMS 2014.

In appsettings.json I created this second connection string and updated it in Startup.cs to reflect the "SQLEXPRESS2014" conn string.

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

}

Then I ran

>dotnet ef database update

Everything is good for me now.