로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
SQL Server Authentication at Management Studio is failing

Answered SQL Server Authentication at Management Studio is failing

  • 2012년 5월 28일 월요일 오전 5:09
     
     
    로그인하여 투표
    0

    Hi All,

    I've 2 instances installed on my laptop. One is SQLExpress and other from Enterprise edition. I'm able to connect to both the instances using my Windows Credentials, but not by using the SA credentials.

    I've even changed the SA password after logging onto the the instance using Management Studio. Can anyone give me a clue about what went wrong and how can I allow my instances to get authenticated by SA user?

    Thanks for reading

    Every thing is perfect as long as you share

     

모든 응답

  • 2012년 5월 28일 월요일 오전 5:16
    중재자
     
     
    로그인하여 투표
    0

    Hello,

    Please try changing the authentication mode to “SQL Server and Windows Authentication mode”.

    http://msdn.microsoft.com/en-us/library/ms188670.aspx#SSMSProcedure

    Try to enable the sa account also:

    ALTER LOGIN sa ENABLE;   

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


     
  • 2012년 5월 28일 월요일 오전 5:29
     
     
    로그인하여 투표
    0

    Hi Alberto,

    Thanks for posting your comments. The link is really great.

    As a matter of fact, both intallations are with mixed mode authentication. Thus, technically, it should allow the SA account. And I've executed the mentioned command as well, but in vain.

    Apart of, I really appreciate your community support. Please let me know if you could imagine anyother scenario.

    Regards

    Every thing is perfect as long as you share

     
  • 2012년 5월 28일 월요일 오후 12:57
    중재자
     
     
    로그인하여 투표
    0

    Hello,

    If you have changed the authentication mode a restart to SQL Server Service may be required.

    Right click the account using SQL Server Management Studio, select Properties, select the Status page, and make sure the account is not locked and has been granted permissions to the database engine.
      
    Thank you for your kindness. Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

     
  • 2012년 5월 28일 월요일 오후 1:09
     
     
    로그인하여 투표
    0

    In addition to Alberto's response, check the SQL Server error log.  The message will contain more detailed information as to why the sa login failed.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

     
  • 2012년 5월 28일 월요일 오후 2:42
     
     
    로그인하여 투표
    0

    Hi Alberto,

    Thanks for your second response. To my surprise, the check box of "Login is locked out" is disabled at the STATUS tab of the properties of SA user. The LOGIN & GRANT are allowed, yet, I'm not able to login. I'm not sure as what happened.

    Sorry for bugging you like this, but, I'm worried for this issue. Hope you would understand my situation

    Every thing is perfect as long as you share

     
  • 2012년 5월 28일 월요일 오후 2:52
     
      코드 있음
    로그인하여 투표
    0

    Hi Dan,

    Thanks for throwing another angle for the same problem to investigate. I've to agree that I'm not aware of how to read and understand the SQL Server Error Log. Please let me know how can i investigate the erro log.

    But I get an error code 18456 when the login is failed. Secondly, when I fired the below message

    select * from sys.messages where message_id = 18456

    I get the login failed text message in different languages.

    Every thing is perfect as long as you share

     
  • 2012년 5월 28일 월요일 오후 3:07
    중재자
     
     
    로그인하여 투표
    0

    Hello,

    Well, at least we found it was locked.

    Please check on the Properties dialog and on the Server Roles page, if the account has the sysadmin role.

    Are you the only DBA on the company? Some DBAs change the name of the sa account, disable it, or lock it for security reasons.

    As Dan mentioned, please check SQL Server error log to try to find an error number for the login failed and share it with us. It could us find a solution.
      
    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

     
  • 2012년 5월 28일 월요일 오후 3:10
    중재자
     
     답변됨
    로그인하여 투표
    0

    Hello,

    Ok. You found error 18456. What about the state of the error? Use the state and the following post to know the reason of the login failed:

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
      
    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

     
  • 2012년 5월 28일 월요일 오후 3:46
     
     
    로그인하여 투표
    0

    You can view the current SQL Server error log from the Management Studio Object Explorer.  Select Management-->SQL Server Logs.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/