MVC6 Connection Strings for SQL Server Express

Question

I was using MVC5 Entity Framework for a long time with connection string in web.config that look like this:

<connectionStrings>
    <add name="GuestContext" 
         connectionString="Data Source=DAVESQLSERVER\SQLEXPRESS;Initial Catalog=NexDec12;Integrated Security=False;User ID=sa;Password=changeME!;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

I am trying to figure out how to do this in the appsettings.json file. I am learning MVC6 using Rick Anderson's tutorial here.

Rather than use the built in SQL Server in VS or IIS10 I want to connect to my external SQL Server Express. I have tried this but it doesn't work, says the login is invalid for user sa (so it's trying to use the correct username?) I feel like it isn't using the correct password but I don't know how to construct a connnection string for the new MVC6 json config format.

I tried this:

"Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=DAVESQLSERVER\\SQLEXPRESS;Database=MovieTut;Trusted_Connection=True;MultipleActiveResultSets=true; Integrated Security=false;User ID=sa;Password=changeME!"
    }

The problem was I hadn't initiatred database migrations. Previously with MVC5 EF6 I never had to use migrations unless I was going to change my schema. It seems with MVC6 EF7 you DO have to initialize migrations. — dave317
– dave317, Commented Dec 17, 2015 at 3:01

Bardo · Accepted Answer · 2015-12-17 12:40:45Z

2

I would recommend a different structure to your config.json, like so (notice "ConnectionString element in both connection declarations)

EDITED to add format to the code

"Data": {
    "DefaultConnection": {
        "ConnectionString": "Server=.;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true"
    },
"EmployeeContext": {
        "ConnectionString": "Server=.;Database=Manish_Database;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
}

edited Dec 17, 2015 at 12:40

Bardo

2,5233 gold badges26 silver badges44 bronze badges

answered Dec 17, 2015 at 12:10

S.Rusdana

2691 gold badge2 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vzayko · Accepted Answer · 2015-12-17 12:01:16Z

1

Your two connection strings are not equal: the one that for MVC 6 contains "Trusted_Connection=True" but still wants to use "sa" as user. You have to choose between Windows and SQL authentications. See Choose an Authentication Mode and update your connection string accordingly.

answered Dec 17, 2015 at 12:01

vzayko

3352 silver badges11 bronze badges

Comments

David Tansey · Accepted Answer · 2015-12-17 01:36:23Z

0

I think the problem is the way you have chosen to 'name' your connection string in the json version.

Try changing the value "ConnectionString" to "GuestContext"

"Data": {
    "DefaultConnection": {
      "GuestContext": "Server=DAVESQLSERVER\\SQLEXPRESS;Database=MovieTut;Trusted_Connection=True;MultipleActiveResultSets=true; Integrated Security=false;User ID=sa;Password=changeME!"
    }

Also, I would expect for this to be defined in config.json rather than appsettings.json (but I think that may be configurable).

answered Dec 17, 2015 at 1:36

David Tansey

6,0104 gold badges39 silver badges54 bronze badges

2 Comments

dave317 Over a year ago

I don't think so, because my app is calling the Default Connection in the Startup.cs file, so I feel like if i input my connection string into default connection it should work...will see and try the alternate method you suggested.

David Tansey Over a year ago

Does your startup.cs have a step for configuring your DB context? If so, what is full 'key' it is using to retrieve the connection string. That portion might look something like this: configuration.Get("Data:DefaultConnection:ConnectionString") (to match the json from your post).

Collectives™ on Stack Overflow

MVC6 Connection Strings for SQL Server Express

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related