C# - System.Data.SqlClient.SqlException: 'Failed to generate SSPI context.'

Question

When trying to run my .NET Core 2.0 app from IIS Express, I get the following error:

System.Data.SqlClient.SqlException: 'Failed to generate SSPI context.'

This occurs when trying to access the DB on the SQL 2012 DB server using the following connection string (username and password redacted):

"Server=10.10.127.170;Database=NGProd;user 
id=*;password=*;Trusted_Connection=True;MultipleActiveResultSets=true;"

I'm using a SQL Server Login and password created specifically for this app. I can log in to SSMS with it and have all access to the DB. I can test the connection on other computers and it works fine. I also checked the SQL Error Logs and no errors are being reported when I try to log in through the app or through SSMS.

I've gone through several blogs, sites, and forums looking at "Cannot generate SSPI context" errors, but none of those resolutions have had any effect.

Anyone have any ideas? I'm stumped!

Answer 1

Set the parameter Trusted_Connection=False in your connection string. Or, if Integrated Security is present, set that to False instead.

Answer 2

I've had same problem recently. I removed the Trusted_Connection part from my connection string:

"Server=10.10.127.170;Database=NGProd;user id=*;pwd=*;"

and it worked for me.

Answer 3

As it has been said, it can be caused by a range of issues. One of them is the AppPool Account in IIS. Confirm that it has permissions or you can update it from the default built-in account to the account you want to access the server with. Under the App Pool tied to your app, got to Advanced Settings > Process Model > Identity. Then choose Custom Account and enter your server account. See the screenshot below.

Answer 4

Trusted_Connection should be set to false:

Trusted_Connection=False

Answer 5

You're mixing credential type. You should either use Integrated Security (aka Windows Authentication) or username and password, not both. Either use Trusted_Connection=true and give your personal user authorization on the remote server (which I'm guessing isn't the case right now), or set Trusted_Connection=false and use the username and password of a user that is authorized.

Also, as a side note, this was already pointed out in another answer, but, while IIS Express runs under your user account, this is not necessarily the case once you deploy to a remote IIS server. If you're using Integrated Security between an IIS Server and a remote SQL Server instance, you'll need to grant login permissions to the IIS Server itself to make this work (or have it run under something other than an App Pool user, which is just a virtual account and doesn't have an actual user in Active Directory).

If you're using a username and password, the previous paragraph is a non-issue, but obviously you'll need to store the connection string securely.

Answer 6

After much banging my head and hours of trying every blog post and suggestion I could find, I had a wild idea. I simply linked the remote SQL server to my local SQL instance using the SQL Server credentials I created. BAM! Works like a charm now, even if I do have to write a bit extra into my queries.