Solved

one user cannot connect to sql server; can ping server

Posted on 2006-06-30
6
273 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
string fuctions 4 26
SQL Server 2012 - Merge Replication Issue 1 22
divide by zero error 23 16
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

773 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