CODEWITHSUNDEEP
c#sql-servert-sqlvisual-studio-2012entity-framework-6
Janus
Janus

Reputation: 135

SQL Server and EF Migration from local to remote database

I'm working on an Entity Framework (EF) project that functions as a supplemental class library to another project within my Visual Studio 2013 solution. I initially had a mock local database on my client dev system and build out the schema and other utilities from there. The data adapter I'm using uses SQL Authentication.

Once I had a validated stable build that satisfied my requirements, I incorporated the EF project into the solution. Working off of the local database, everything worked fine. I then re-aimed the EF database to a remote SQL server by:

  1. Updating my Data adapter to the remote server
  2. Successfully testing my data adapter's connection
  3. updating the App.Config files in the start-up project and the EF project with the correct connection string
  4. Ran the T-SQL script generated by EF on the remote server
  5. Validated that the T-SQL script worked by observing the database view and seeing my new tables present

Once I finished, I re-ran the solution. EF threw an exception on the first call from the Framework:

An unhandled exception of type 'System.Data.Entity.Core.EntityException' occurred in EntityFramework.SqlServer.dll

Additional information: The underlying provider failed on Open.

When I checked the inner-exception, I found this:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

This exception puzzles me because if you'll recall at the beginning of my post, I'm using SQL authentication.

I'm currently not using anything ASP.NET related, my testbed is simply being executed from a console application which had no problems pre-migration.

I want to say this is an administration/environment issue, not code because locally I have no issues and my T-SQL script seems to run fine on the remote server, and I have connectivity with it. Unfortunately for me my SQL server skills are no where near my coding ability, I'm unable to "prove" it. So I find myself at a loss.

Any help would from the fine folks at stackoverflow would be appreciated!

Appended:

Per DrewJordan:

Context Constructor -

public DatabaseContext(): base("name=DatabaseContext") {}

Connection String -

connectionString="metadata=res:///Database.csdl|res:///Database.ssdl|res://*/Database.msl;provider=System.Data.SqlClient;provider connection string="data source=mycompany.com\SQLEXPRESS;initial catalog=sandbox2_db;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"

Upvotes: 1

Views: 2496

Answers (2)

Janus
Janus

Reputation: 135

I found the problem. As I said before, my project uses SQL authentication, but if you notice in my connection string there is no SQL user ID.

Entity Framework will use the local client user ID by default if no user ID has been provided.

The later is qualified within the remote domain, the local client is not, which is why I fail to connect.

Upvotes: 0

Raj Kamal
Raj Kamal

Reputation: 577

You have turned windows authentication on by this setting integrated security=True; change it to integrated security=False; or you can remove that field altogether.

Source: 2 hours of bug hunting on a past project. here is the msdn reference

Upvotes: 2

Related Questions