Solved

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

Posted on 2009-07-07
6
500 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

0
Comment
Question by:mooriginal
  • 2
  • 2
6 Comments
 
LVL 13

Expert Comment

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

Author Comment

by:mooriginal
ID: 24793601
Ive just double checked that
and it still fails

its so strange
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 250 total points
ID: 24849430
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
 

Author Comment

by:mooriginal
ID: 24867493
thanks
that was a good answer ...

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24868811
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now