I have this problem and needed some ideas for getting to bottom of it
I have 2 servers.
Server 1 - DR Application server.
Server 2 - Live DB server 2005.
Server one is a VM and has been setup to mirror the Live Application server that points to Server2.
Server 1 has like for like ODBC connections setup to Server 2.
1 System DSN - SQL Native Client
2 File DSN.
To test things I have created 3 different users to match the 3 different dsn's
These I connect to Server 2 from Server1 - and I test the connections from the DSN's and everthing is sucessful and login complete.
However on the server 2 [db server] when i run security profiler trace and in the event log - the users I have created from server 1 are showing as failed login.
Even though i test within the DSN and it connects fine.
"Login failed for user" to database ID 1
So I check Master on server 2 and ive given the user access to Master already so shouldnt be a problem.
I can login using the test user on Server 2 via management studio
But still its failing in the security audit when its coming from Server 1.
For purposes of being clear - all the DSN's are failing but lets concentrate on the 2 file DSN's
ive included the DSN test section from the odbc administrator below.
Any ideas welcome
Microsoft SQL Native Client Version 09.00.1399
Data Source Name: son_db.dsn
Data Source Description: Elite son_db File DSN
Use Integrated Security: No
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: Yes
Use ANSI Quoted Identifiers: No
Use ANSI Null, Paddings and Warnings: No