Link to home
Start Free TrialLog in
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.
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

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
Avatar of The_Nibbler

ASKER

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)?
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'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
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial