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

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

mooriginalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

St3veMaxCommented:
Just to double-check, what if you set the login's default DB to Master in the DSN. Does that work then ?
0
mooriginalAuthor Commented:
Ive just double checked that
and it still fails

its so strange
0
Jim P.Commented:
It appears you are using SQL Authentication.

When you create a userid (SmithJ) on Server 1 he is assigned an unique Security IDentifier (SID) that is stored in the sys.syslogins in the master DB -- and eventually the sys.sysusers in other DB. So then when you go to Server 2, and create SmithJ again he has a total different SID. So when the Server1 SmithJ SID 1x1x0x3... is sent to Server 2, Server 2 is like "I have no clue who 1x1x0x3... is."

There are a few ways to get around this:

1)  When you link the servers put a translation in the top part of the page.

2) On Server 1 do a
----------------------------------------------------------
select *
from sys.syslogins
----------------------------------------------------------
then on Server 2 manually do the Create login
----------------------------------------------------------
CREATE LOGIN [SmithJ] WITH SID = '1x1x0x3...'
----------------------------------------------------------
Full syntax at  
CREATE LOGIN (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189751.aspx       

3) Use the sp_help_revlogin at the link below to sync the users.

How to transfer the logins and the passwords between instances of SQL Server 2005
http://support.microsoft.com/kb/918992/
Link-Srvr-2.jpg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mooriginalAuthor Commented:
thanks
that was a good answer ...

0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.