Solved

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

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

19 Experts available now in Live!

Get 1:1 Help Now