Solved

one user cannot connect to sql server; can ping server

Posted on 2006-06-30
6
271 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

757 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

19 Experts available now in Live!

Get 1:1 Help Now