Post reply

Sql Account login mystery, use TCP/IP vs namepipe

  • July 13, 2011 at 10:03 am

    #254408

    Sql 2005 Symptoms: For some unknown reasons, since yesterday we cannot connect to our develpment sql server using sql account by TCP/IP, while in Sql Configuration both namepipe and TCP/IP are enabled. Meanwhile, when we can connect to the server by window authentication and namepipe with sql account.

    TCP/IP ---work--> Window authetication

    TCP/IP --not work--> Sql Account

    Namepipe ---work--> Window authetication

    Namepipe --work--> Sql Account

    Please advise how we can solve the problem.

    Thanks,

    Ming

  • July 13, 2011 at 10:06 am

    #1352271

    First thing I would check is to make sure that the SQL Account does not have the password enforcement enabled.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 13, 2011 at 10:53 am

    #1352302

    We don't have password enforcement enabled. Thanks.

  • July 13, 2011 at 10:57 am

    #1352308

    Do you have sql authentication enabled on the server? Do you have the error report?

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 13, 2011 at 11:57 am

    #1352337

    yes

  • July 13, 2011 at 11:59 am

    #1352339

    So can you post the error report? That will help.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 13, 2011 at 12:34 pm

    #1352366

    The error is just too generic, not quite helpful I think.

    Cannot connet to Devsql52.

    Login Failed for user 'MingTest'. (Microsoft Sql server, Error: 18456)

  • July 13, 2011 at 12:38 pm

    #1352367

    what is the severity and state?

    here is the state discriptions:

    ERROR STATE

    ERROR DESCRIPTION

    2 and 5

    Invalid userid

    6

    Attempt to use a Windows login name with SQL Authentication

    7

    Login disabled and password mismatch

    8

    Password mismatch

    9

    Invalid password

    11 and 12

    Valid login but server access failure

    13

    SQL Server service paused

    18

    Change password required

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 14, 2011 at 7:30 am

    #1352820

    We took Microsoft's help to track the issue, and found that under server security -> Server Roles -> public, Permission to TCP endpoint was missing. Once we added TSQL Defaut TCP, issue got resolved.

    Thanks for your help. It's a good exercise.

Viewing 9 posts - 1 through 8 (of 8 total)

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