0

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?

1

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.

  • I would like to mark your answer as accepted but, honestly, I don't understand the core of it (I am not that knowledgeable of Access, SQL Server and Microsoft in general, plus I am no database administrator as per my profession). OU? – Feillen Jan 27 '17 at 13:50
  • I apologize, I'm familiar with your position as I was thrown into a DBA and sysadmin role last year with very little knowledge about Access/SQL and WS environment. OU- Organizational Unit. Think of it as a group that users can be added to for different things. SQLADMIN and SQLUSERS could be two types of OU's. Users who need nothing more then the ability to perform select/insert/update/delete could be assigned to SQLUSERS. In SSMS you can create a new login for the SQLUSERS group and assign the necessary permissions. OU's are usually created on whichever server houses your active directory. – Nic Jan 27 '17 at 16:28
  • Ok, and now just to make sure I got it right: when you say Add all the appropiate users to the OU (...) add the OU to the appropiate logins You mean to add to the SQLServer users with the same username&pass as their Windows accounts, right? Then manage their privileges according to their target tasks and be done, huh – Feillen Jan 30 '17 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.