Avatar of The_Nibbler
The_NibblerFlag for South Africa asked on

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.
Microsoft AccessMicrosoft SQL ServerWindows Server 2012Windows NetworkingMicrosoft Server OS

Avatar of undefined
Last Comment
Marten Rune

8/22/2022 - Mon
Kelvin Sparks

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
ASKER
The_Nibbler

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)?
Kelvin Sparks

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
The_Nibbler

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?
ASKER CERTIFIED SOLUTION
Marten Rune

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question