help--- SQL login issue in sql server 2000!!!

Question

help--- SQL login issue in sql server 2000!!!

spaliwal

SSChasing Mays

Points: 604
More actions
September 12, 2003 at 12:38 am

#358250

Hello,
Please read through below. This is an issue we are facing at our client site.
SQL server is installed with mixed mode authentication (SQL server and windows). Here I created the "test" login and assigned it to "MyDB" as the default database. When we try to connect using SQL query analyzer and type in localhost or the IP of the machine to connect to the Database with the "test" login it fails to connect. It gives error message "18456 Level 16" cannot connect.
If we use the sa login with localhost and IP it works.
If we use the "test" login and give the servername it allows login.
Which means the logins that are getting created are for some reason not allowing the user to connect if we specify the server as localhost or it's own IP. I am not sure why this is happening. Please let me know A.S.A.P if anyone has experienced this.
TIA,
Sp

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

So it's a connectivity/resolving issue. If it doesn't connect based on IP, my first thought is that you're only running named pipes maybe, or that you've got a problem with DNS/WINS.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

mccork SSC-Insane Points: 22288 More actions · Answer 2

My thoughts at first too Andy, but then: a) can connect as sa, and b) it issues "18456 Level 16" which means it's making contact and then being rejected.

Been trying unsuccessfully to replicate the problem. Appears to be a combination of things to do with network protocol, and possibly revoked/denied permissions in SQL. It's a weird one alright.

What's the bet there's a really simple solution.

Cheers,

- Mark

Cheers,
- Mark

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

You could audit failed logins on that sql server, which would log failed logins.

Steven

spaliwal SSChasing Mays Points: 604 More actions · Answer 4

Thanks for the replies,

I checked permissions in Login properties , of test login ,i have already given MyDB in database access.Also i have taken named pines and TCP/IP in network connections while installation.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

See http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=16187&FORUM_ID=20&CAT_ID=2&Topic_Title=18456+Level+16&Forum_Title=Connecting

Are you both from same company or it is just a coincident?

spaliwal SSChasing Mays Points: 604 More actions · Answer 6

Yeah both are same probem but another one is posted from US and i have posted from India.Thanks gurus problem is solved It was the MSDE installed on the same m/c and sa was there for both with blank password. So it was connecting to MSDE, Instead of sql server.