SQL Server – Access Cannot Generate SSPI Context

kerberosms accessodbcsql server

I actually understand why this error messages pops up, but I do not know how to implement my solution.

There is a SQL Server network instance I am providing some MS Access forms to interact with, and I myself have no connectivity issues. This is because both in the server and in the database I am actually registered with the same username and password as in my own pc, so the kerberos authentication works brilliantly… until I share the Access file with someone else, then they get this error (because their local users are not registered in the server &/or database)

The thing is, I don't want the process to use client-local user and password to authenticate the connections (or the kerberos service all together), but rather database-local user and passowrd. I originally linked the remote tables to Access using ODBC calls, providing database-local user and password (different from my credentials) and I would like it to be kept that way (or similar enough) so that someone else can open the forms and interact with them using database-local credentials.

I just don't know how to get around this. Help please :'(

Last-minute updated before posting: after this error pops up the user is prompted with the connection dialog where they can edit the username and password provided to make the connection. So the problem is not actually a blocking wall, but rather an uncomfortable detail.

The question remains though: is there a way to make this the default behavior?

Best Answer

Why not have both?

That login screen you're seeing is SQL server rejecting the login parameters.

  1. Create a OU and label it something like SQLAccess. Add all the appropriate users to the OU.
  2. From there goto SSMS, add the OU to the appropriate logins.
  3. Set your permissions
  4. Create a DSN(Or use current). When it says use Network ID for authentication, make sure it's checked/bubbled. It uses the current user's credentials.

With the above steps done, when your enduser opens the access file, SQL will shake behind the scenes and thus allow your custom login screen to pop... Only showing 1 login screen. Also should a user ever leave the company, just remove them from the OU and you're golden(or deactivate the account)

In theory you could develop a DSN for every specific users and specify the login with a SQL login but that is tedious, unless of course you do a universal login. I don't recommend it for obvious reasons.

Related Question