July 13, 2010 at 12:24 pm
Hi guys,
I have a problem I can't solve, despite my efforts.
I've a w2k8 r2 server in w2k3 domain on which I've installed a SQL 2005 SP3 database. Everything was alrights till I've changed the IP address.
The problem now, even by trying to put the older address back, I can't reconnect to the database anymore with admin console. Worse, I've tried to reinstall first the database system, and after the complete server (so removeal from domain, new VM reinstallation and fresh new SQL installation); the same result !
But I need to keep the same machine name, otherwise, applications will not find it.
Error from admin console and in the log files :
Login failed for user "XXXX\User". (Microsoft SQL Server, Error 18456)
2010-07-12 13:44:12.61 Logon Error: 18456, Severity: 14, State: 11.
2010-07-12 13:44:12.61 Logon Login failed for user 'XXXX\User'. [CLIENT: <local machine>]
When I browse the server (browse for more), I can't find local, but network can be seen. TCP protocol and IP addresses 192.168.X.X, 127.0.0.1 are active in configuration manager.
There was nothing anymore in DNS servers concerning former IP address when I've reinstallled the VM but somewhere, probably in ACtive Directory, something is kept that avoid me to reinstall the server properly.
I'm stuck, does somebody have an idea ? Many thanks in advance for your help. 😉
Of course, if I need to provide more information, no problem
July 13, 2010 at 12:41 pm
Have you checked to make sure the SQL Server TCP port (default is 1433) is configured correctly in the Configuration Manager, and that there is no firewall blocking connectivity?
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2010 at 12:50 pm
Hi
The error means that the Login is correct but the server couldn't be accessed.
Create an Alias using SQL Server Configuration Manager by giving the SQL Server Name and its IP Address and try to connect.
Thank You,
Best Regards,
SQLBuddy.
July 13, 2010 at 2:36 pm
Fat Seth (7/13/2010)
Have you checked to make sure the SQL Server TCP port (default is 1433) is configured correctly in the Configuration Manager, and that there is no firewall blocking connectivity?
Hi, that was I though at the first place : firewall, but I had already completely deactivated the firewall in order to remove this possibility. TCP port is standard 1433.
sqlbuddy123 (7/13/2010)
HiThe error means that the Login is correct but the server couldn't be accessed.
Create an Alias using SQL Server Configuration Manager by giving the SQL Server Name and its IP Address and try to connect.
Thank You,
Best Regards,
SQLBuddy.
I've tried like you've proposed 2 different aliases (separately and by restarting the server each time) : both with server netbios name, but one with localhost in server field, and the other with IP address.
Is it correct to do so. In any case, it didn't resolve the issue :crying: but already, many thanks for your answers ! 😉
It it gives more information, here is the complete error proposed by Management Studio :
===================================
Cannot connect to VM-XXXXXX-X.
===================================
Login failed for user 'XXXXXX\YYYYYYY'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
Server Name: VM-XXXXXX-X
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()
July 13, 2010 at 5:26 pm
Hi
Are you able to connect to this SQL Server from any external server using 1. SQL Server Name 2. IP Address?
Are you able to ping the Windows server hosting this SQL Server from any external server ?
Thank You,
Best Regards,
SQLBuddy
July 14, 2010 at 5:16 am
I didn't test it yet from another computer but I've tried with Administrator login (domain one) and I was able to connect to database service.
The user I was using till now for my daily administration was the user who installed the server and he don't have access anymore since the IP change. User is domain admin as well, so how can that be ?
Other problem for the moment, my WSUS that was relying on this server can't connect to the SQL server also (despite I can see the database and see the WSUS server machine account in security.
When the WSUS server is trying to connect, I have exactly the same message in log file :
2010-07-14 13:06:50.78 Logon Error: 18456, Severity: 14, State: 11.
2010-07-14 13:06:50.78 Logon Login failed for user 'XXXXXX\VM-XXXXXX-Y$'. [CLIENT: 192.168.X.X]
As I've ground knowledge only about SQL, I can't understand why these permissions are not valid anymore. Can you help setting things back ?
July 14, 2010 at 5:37 am
More info : I can connect to the database remotelly with my user, but not locally.
When I browse from server, there is nothing appearing in local servers.
So this problem remains, and the WSUS one also.
July 14, 2010 at 11:43 am
Hi
Suppose that we have a Server A with SQL Server A.
And if we change the name form Server A to Server B, then we have to run the following query in the SSMS to reflect that change for the SQL Server Default instance (Local)
sp_dropserver {old_name}
GO
sp_addserver {new_name}, local
GO
In this case old_name = new_name
But I am not sure whether this works or not? Try to test this your laptop or on a test server.
Thank You,
Best Regards,
SQLBuddy
July 15, 2010 at 2:14 am
Hi,
I've tried, but it didn't worked.
Any other idea ? I can try anything, no problem.
July 15, 2010 at 8:13 am
Can you try creating a SQL Server login on the SQL Server (you are using mixed mode, right?) and test that login? Is the problem on the server or the client? Can you ping the server from any client? Have you tried logging in from a different client? Some of these questions have been asked, but your answers seemed unclear.
_________________________________
seth delconte
http://sqlkeys.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply