Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

860 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