November 18, 2013 at 11:10 pm
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
November 18, 2013 at 11:59 pm
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
November 19, 2013 at 12:29 am
No, I checked this by creating a new login with same password and that worked.
November 19, 2013 at 1:10 am
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
November 19, 2013 at 2:11 am
DNS alias?
November 19, 2013 at 5:25 am
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
November 21, 2013 at 1:32 am
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
------------------------------
November 21, 2013 at 1:47 am
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
November 21, 2013 at 2:07 am
I have mixed authentication enabled
November 21, 2013 at 2:21 am
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()
November 21, 2013 at 12:18 pm
According to this overview state 1 means your login (i.e. sa) is disabled.
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
November 21, 2013 at 2:20 pm
What is in the SQL server error logs for these failed login attempts?
November 21, 2013 at 11:47 pm
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
November 22, 2013 at 3:27 am
These timezones really suck. Any one awake that have suggestions?
November 22, 2013 at 8:56 am
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