Entrar
 
HomeBibliotecaWikiAprenderGaleriaDownloadsSuporte ComunidadeFóruns
Recursos para Profissionais de TI >
> 
> 
SQL Server Authentication at Management Studio is failing

Respondido SQL Server Authentication at Management Studio is failing

  • segunda-feira, 28 de maio de 2012 05:09
     
     
    Entrar para Votar
    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

     

Todas as Respostas

  • segunda-feira, 28 de maio de 2012 05:16
    Moderador
     
     
    Entrar para Votar
    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


     
  • segunda-feira, 28 de maio de 2012 05:29
     
     
    Entrar para Votar
    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

     
  • segunda-feira, 28 de maio de 2012 12:57
    Moderador
     
     
    Entrar para Votar
    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

     
  • segunda-feira, 28 de maio de 2012 13:09
     
     
    Entrar para Votar
    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/

     
  • segunda-feira, 28 de maio de 2012 14:42
     
     
    Entrar para Votar
    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

     
  • segunda-feira, 28 de maio de 2012 14:52
     
      Contém Código
    Entrar para Votar
    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

     
  • segunda-feira, 28 de maio de 2012 15:07
    Moderador
     
     
    Entrar para Votar
    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

     
  • segunda-feira, 28 de maio de 2012 15:10
    Moderador
     
     Respondido
    Entrar para Votar
    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

     
  • segunda-feira, 28 de maio de 2012 15:46
     
     
    Entrar para Votar
    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/