Solved

one user cannot connect to sql server; can ping server

Posted on 2006-06-30
6
272 Views
Last Modified: 2008-03-10
One of my network logins has lost his ability to connect to the SQL Server.  Cannot create an ODBC connection.  I am using SQL Authentication so the network login shouldn't matter.  If someone else is logged on to this workstation, the connection works fine (still SQL Authentication).

The bad login can still Ping the SQL server.  

The ODBC connection is using port 1433.  Telnet to 1433 works fine.

Any ideas? Is MDAC user or workstation specific?

thanks

bonnie
0
Comment
Question by:bonniemckee
  • 3
  • 3
6 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17028203
Is that person a member of a group that has login rights to the SQL Server.

You should see the windows login (or a group that that person is a member of) under

Server / Security / Logins

in Enterprise Manager


If that all looks good, try running Profilfer with all the security audit events on and see what that says about the particular user.
0
 

Author Comment

by:bonniemckee
ID: 17030947
thanks for the reply.

Yes, the person (officer2) is logging in using a SQL login name (svcreq).  I'll try the Profiler

thanks

bonnie
0
 

Author Comment

by:bonniemckee
ID: 17033607
the profiler shows a "Login line" with a bunch of setting set on/off in the text data.  then a "Logout line" with 17 reads and the rest of the fields blank ok 0

How can just one specifid Windows login not be allowed to get to SQL Server with a totally different SQL Server login?? Other Windows logins can log in to SQL just fine with that same SQL login.

thanks

bonnie
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 30

Accepted Solution

by:
nmcdermaid earned 125 total points
ID: 17036367
So when the guy logs in, you actually see an event in the profiler?

You can use different templates in Profiler, I think there is a security one which may give you more info.


There must be something in that users windows profile which is causing the connection issue.


Curious that TELNET to 1433 works fine because that would be my next guess... that something in the windows profile is blocking this.


You could try altering the client network settings. For troubleshooting purposes, make sure that only TCP/IP (not named pipes) is enabled for each person that logs in and see if that gives you any more enlightening messages..... BTW what exact message do you get from ODBC? You say 'Cannot create an ODBC connection' does this mean when they test the connection it fails?


You could also try creating a UDL connection instead of a ODBC one:

1. Create a blank text file
2. Rename the extension to .UDL
3. Double click the file, set all the tabs accordingly and see if you can connect. First try an OLE-DB connection on the first tab then try an ODBC one from here and see if they work.


MDAC is workstation specific but theoretically a given user could be denied file righs to the MDAC folder and therefore have a problem. You could try using FileMon (from sysinternals.com) and see if there's a difference.

0
 

Author Comment

by:bonniemckee
ID: 17046509
turns out the problem was overzealous "web surf control"

thanks
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17048913
Its curious that the TELNET worked in that case but I'm glad you got it solved.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 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

12 Experts available now in Live!

Get 1:1 Help Now