Sql-server – Old ODBC app throws “cannot generate sspi context” on Windows 10

kerberosodbcsql serverwindows 10

An old Access 2003 .adp project is currently running on Windows 7 machines, while SQL Server 2008 is on the backend.
Since the app is moved to a Windows 10 machine, the app will trigger "Cannot generate SSPI context" error.

There are a lot of messages over the Internet to explain possible reasons and solutions.

In our case, we may overcome the problem running Msaccess.exe from commandline, using: 

runas /netonly /user:userdomain\useralias "C:\Program Files\<path-to-office-folder>Msaccess.exe"

The downside is that the user is prompted for password each time the shortcut is being used.

EDIT:

The weird fact is that the context under which the program is running is apparently the same as the Windows 10 user has the right credentials to access domain resources (can access server side networks shares correctly), and also has credentials within SQL server configuration. As a proof, under Windows 7, the same credentials are being used without errors.

enter image description here

It looks like the app in Windows 10 is incorrectly passing computername\useralias credentials to SQL Server in place of userdomain\useralias…..

Is there a way to fix this so that the app (or Windows 10) are addressing the domain user name correctly, avoiding the error?

Best Answer

When you launch your app using "runas" command, you use some domain user, and that user has Windows login at your SQL server. Or that domain user is part of some AD group that has Windows login at your SQL Server

When you launch app without "runas" command, the app is running, but under different security context (under different windows user), right ? In this case you have to create Windows login for this domain user at your SQL Server and grant it some permissions, so that different windows user can connect to SQL Server. That would resolve "Cannot generate SSPI context" error

Edit 1: after re-reading your question - its also possible that your windows 7 machine has login at your SQL Server (example "yourdomain/win7machine$") so it can connect, and when you move your app to windows 10 machine, that machine's computer account ("yourdomain/win10machine$") does not have login at your SQL Server... you just have to create this login

Edit 2: to find out what Windows login app is using to connect to SQL Server, while app on Win 7 machine is connected to SQL Server, run below command at SQL Server:

select * from sys.dm_exec_sessions

Look at columns host_name (it will be your Win 7 machine) and login_name (it will be the actual login that application is using to connect to SQL Server)

Then try to launch the app on Windows 10 machine. Make it throw "cannot generate SSPI context" error. Go to SQL Server machine, launch Event Viewer, go to Application logs. Is there a new Logon event from MSSQLSERVER ? if so, does it say "login failed for user...." ? if yes, it will show you what login app on Win 10 machine is using to connect to SQL Server

Related Question