SA login question.

  • Good day,

    I found an issue where I am unable to log on the the local instance with my SA credentials (invalid username and password) However I can log on from another server with SSMS installed using the exact same credentials. Has anyone had this before? Could it be registry or windows policy related?

    SQL Server 2008 R2 running on Windows Server 2008 R2.

    Instance is set to mixed authentication mode.

    I can log on fine using my windows credentials.

    Regards

  • One server doesn't accidentally have another keyboard layout?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • No, I checked this by creating a new login with same password and that worked.

  • double check the firewall has the needed exceptions to allow connecting to SQLServer/port using the tool of your choice

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • DNS alias?

  • oh, yes, indeed, totally forgot about DNS aliasses, however this being a client level issue, chances are it is a client side defined SQLServer alias.

    SQL Server Configuration Manager / SQL Native Client configuration / Aliases

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Asked the networkd guys to check the dns as well, doesn't seem to be at fault.

    What is strange is that when I log on this server and try to connect with SA password it gives this error: - But trying again it gives me the login/password error

    TITLE: Connect to Server

    ------------------------------

    Cannot connect to primepims.

    ------------------------------

    ADDITIONAL INFORMATION:

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

  • I think this is SQL Authentication related. I just created a new login with sql authentication. I can't connect with this from the local server however I can remotely

  • I have mixed authentication enabled

  • Here is the detailed error: - Strange though as in the sql server error log it shows state 8

    ===================================

    Cannot connect to primepims.

    ===================================

    Login failed for user 'sa'. (.Net SqlClient Data Provider)

    ------------------------------

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------

    Server Name: primepims

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    ------------------------------

    Program Location:

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

    at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

    at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

    at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

    at System.Data.SqlClient.SqlConnection.Open()

    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

  • According to this overview state 1 means your login (i.e. sa) is disabled.

    Troubleshooting Error 18456

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    Error: 18470, Severity: 14, State: 1.

    Login failed for user '<x>'.

    Reason: The account is disabled.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • What is in the SQL server error logs for these failed login attempts?

  • Hi Johan,

    Yes, But, both the sa and my test account are enabled and I can connect to both from another server.

    Here is the SQL error log details:

    Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    Error: 18456, Severity: 14, State: 8.

    This is a very strange issue. At first I thought it could be privileges on my windows account, after confirming that I am an admin it was clear that wasn't the issue.

    I can't find anything on the net related to this specific issue.

    Any help would be appreciated

    Thanks

  • These timezones really suck. Any one awake that have suggestions?

  • How are you trying to connect from the local machine to the SQL server?

    SSMS or is it a application?

Viewing 15 posts - 1 through 15 (of 21 total)

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