We help IT Professionals succeed at work.

ODBC login failed from DSN connections but when testing it works fine.

531 Views
Last Modified: 2012-05-07
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
Server: Server2
Use Integrated Security: No
Database: (Default)
Language: (Default)
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

Open in new window

Comment
Watch Question

Commented:
Just to double-check, what if you set the login's default DB to Master in the DSN. Does that work then ?

Author

Commented:
Ive just double checked that
and it still fails

its so strange
Most Valuable Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks
that was a good answer ...

Most Valuable Expert 2014

Commented:
Glad to be of assistance. May all your days get brighter and brighter.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.