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

  • 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

  • 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/

  • 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

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

    Steven

  • 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.

  • 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.

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

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