August 25, 2009 at 1:50 pm
I have an application originally written around a SQL/2000 database, which read from/wrote to through an ODBC connection to a separate application which used an Access/2003 database. The ODBC connection relied on SQL/Server (mixed mode) authentication.
The application utilizing the Access Database has been upgraded to utilize SQL/Server 2005. When this ODBC Connection is set to use SQL/Server Authentication the other application returns an error “Login failed for user “. The user is not associated with a trusted SQL Server connection.” The ODBC connection tests correctly in Windows Data Sources using SA and the correct password, but something in the (no longer supported) application itself is reacting with the ODBC connection differently than it used to. I can change the ODBC connection to use Windows authentication and the application appears to function without errors, but that opens up a whole new set of issues.
Does anyone know why the ODBC connection works properly when tested through Windows, but returns this error when used with this application?
August 25, 2009 at 1:52 pm
Login mapping is the first thing that comes to my mind.
Do you have a "Use this login when ..." set up in the security for the linked server?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 25, 2009 at 2:15 pm
yes login mappings seems to be the culprit .... you could run the below script to fix any orphaned SQL logins that you might have on the SQL 2005 box.
USE
GO
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid 0x0) and suser_sname(sid) is null
and name in ( select name from master..syslogins)
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
/* fix the orphaned users in this db */
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 25, 2009 at 2:16 pm
first line of the script should be
USE [ your database name ]
HTML tags messed it up :pinch:
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 25, 2009 at 2:41 pm
Thank you ... the script seems to work for the database written for SQL/2000, but for the database converted from Access to SQL/2005 it returns "The login already has an account under a different user name." And same error launching the application.
August 25, 2009 at 3:45 pm
dmiller (8/25/2009)
for the database converted from Access to SQL/2005 it returns "The login already has an account under a different user name." And same error launching the application.
sounds like users at database level and logins at server level are messed up .... is it possible for you to create a New SQL login on SQL 2005 box and grant it the appropriate rights on the databases and try that one in your connection string .....
if it works, then its definitely the mappings of the old ID... at that point I would drop the original login on SQL 2005 and recreate it.
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply