Solved

one user cannot connect to sql server; can ping server

Posted on 2006-06-30
6
276 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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