?
Solved

one user cannot connect to sql server; can ping server

Posted on 2006-06-30
6
Medium Priority
?
278 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
[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
  • 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

800 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