Microsoft Access.The login is from an untrusted domain and cannot be used with Win Auth.

We have migrated our SQL server 2008 db on SBS 2011 to a new Windows 2012 server.The migration was successfull but now we are having issues with our workgroup pc's connecting via Microsoft Access Frontend to SQL srv 2008.

We previously had issues a year ago when we got our SBS 2011 srv,connecting Workgroup pc's(XP Home, Vista Home) to network.We eventually resolved the Workgroup issue and Access connected to SQL server.I just can't remember how and what we did.

Most pc's on domain connect via Windows auth. on ODBC to SQL srv 2008.
We created a user on SQL srv with SQL mixed mode auth and it worked BUT every time i close Access and re-open it have to re-enter the password into the SQL server login box.
I understand that that's the way Access will work when setting up a SQL mixed mode user.

I ideally would want to return to the previous state when Access worked before migrating db server.

Is there not User settings on Workgroup pc 's i can set  to work around this problem?

We have 6 of these machines on domain,so we can't just replace them simultaneously.
The_NibblerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
If you've moved the database to another server, the users will need to be reset. You need to remove and add the users from windows in the sql server security node (not the database security node). You must then match up the users SQL Server IDs.

THe SQL needs to run for each user replacing the TestUser part with each username.

EXEC sp_change_users_login 'Auto_Fix', 'TestUser'

Kelvin
0
The_NibblerAuthor Commented:
Can you please advise as to how i can "....n match up the users SQL Server IDs"

Is this the principal id or sid(This is just hexdecimal code when executing select query)?
0
Kelvin SparksCommented:
The EX code I gave matches the database SID to the login SID, but you have to tell the database server who these logins are. I assume that you restored a backup, so the database server does not know of the logins or believes them to be from a domain that it doesn't know. I always drop and recreate them, then align using the EXEC sp_change_users_login
0
The_NibblerAuthor Commented:
I've done this now and it still doesn't work.
I think you misunderstood me.All the users connect fine over a domain pc (ex.Win 7 Pro,Win 8 Pro, Win XP Pro) with Access to SQL backend but as soon as any of the users mentioned above connect with Windows Home(Workgroup) pc's we get "The login is from an untrusted domain and cannot be used with Win Auth".

Any other recommendations?
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Win auth can be used if kerberos isn't required.
What you do is create a domain user with the same loginne as on the workstation. And with the same pwd.
When the workstation sends it's auth the pwd hash matches the pwd hash on the domain server. And the access is granted.
You need these accounts on the domain, or locally on the sql server. Then grant these accounts rights in sql server, now the workstations should be able to work as normal.
Regards Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.