22

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!

Mighty Ferengi
  • 746
  • 3
  • 8
  • 22
  • There is a huge range of problems that might be resulting with this exception. One of the reason might be- You are trying to connect to remote server using domain credentials from a computer that is not under active-directory. Are you sure that you are using sql credentials in a connection string? – Maris Feb 28 '18 at 14:06
  • Yes, it's a SQL Server login and not a domain login. I set it up on the SQL server myself and confirmed that it works. I've even confirmed it works in SSMS on computers that are not on the domain, it just doesn't work in my app. – Mighty Ferengi Feb 28 '18 at 16:28

8 Answers8

54

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

Andi Emma Davies
  • 298
  • 4
  • 19
Vlad K.7
  • 541
  • 4
  • 3
  • 3
    Integrated Security=False could helps too – Ali Yousefi Jun 26 '19 at 05:03
  • Or if you want to connect with Windows Authentication you can enable this in the `Credential Manager`. Use the database url (or ip?) and then add the port with a colon like `:1433`, and add your username and password. – Rubenisme May 21 '21 at 08:20
8

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.

Gerhard
  • 6,850
  • 8
  • 51
  • 81
Ondra Starenko
  • 428
  • 1
  • 6
  • 12
6

Trusted_Connection should be set to false:

Trusted_Connection=False
Elikill58
  • 4,050
  • 24
  • 23
  • 45
Abbas Hadavandi
  • 111
  • 1
  • 2
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.enter image description here

Felix Too
  • 11,614
  • 5
  • 24
  • 25
  • 2
    Great, except I'm running this from IIS Express at the moment, so this doesn't help. – Mighty Ferengi Feb 28 '18 at 20:41
  • 2
    I was running the app pool as myself, and my password expired. I didn't realize it until I read this. "Logging" back into the app pool with my new password fixed it. Thank you! – codeMonkey Dec 03 '19 at 16:35
2

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.

EJoshuaS - Stand with Ukraine
  • 11,977
  • 56
  • 49
  • 78
0

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.

Mighty Ferengi
  • 746
  • 3
  • 8
  • 22
0

What helped me (after installing exact same version of licensed SQL Server 2019 on a different machine, where I set IP address and hostname to same as on the previous machine) was just changing hostname to IP address in connection string, e.g.:

before: "Server=myserver;Database=MyDB;Integrated Security=True;Timeout=30;"

in query string try to write Encrypt=False;

Server=ip_or_your_servername,1433\SQLEXPRESS;database=Your_database_Name;user=your_username;password=1your_password;TrustServerCertificate=True;Encrypt=False;"

Hamid Tohid
  • 3
  • 3
myro
  • 1,158
  • 2
  • 25
  • 44
-6

fix "Cannot generate SSPI context" error and fix SqlException: Failed to generate SSPI context.

try to download Microsoft® Kerberos Configuration Manager download link

in query string try to write Encrypt=False;

query string Server=ip_or_your_servername,your_port_or_1433\SQLEXPRESS;database=Your_database_Name;user=your_username;password=your_password;TrustServerCertificate=True;Encrypt=False;"

for SQL Server® and install in your server. Microsoft® Kerberos Configuration Manager download link Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network. To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain. In addition, many customers also enable delegation for multi-tier applications using SQL Server. In such a setup, it may be difficult to troubleshoot the connectivity problems with SQL Server when Kerberos authentication fails.

The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions: Gather information on OS, Microsoft SQL Server instances and Always On Availability Group Listeners installed on a server. Report on all SPN and delegation configurations on the server. Identify potential problems in SPNs and delegations. Fix potential SPN problems.

Microsoft Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services.

enter image description here

Installer will install in this path. if you want to install to a different folder, use Browse. C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server\

then try to config SPN For example, a typical SPN for a server that is running an instance of SQL Server is as follows:

MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433

https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/connect/cannot-generate-sspi-context-error#see-also

Hamid Tohid
  • 3
  • 3