Solved

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

Posted on 2009-07-07
6
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

695 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