Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
guildsbounty
Starting Member
3 Posts |
 Posted - 2010-06-08 : 12:01:46
|
| Okay, this is the first server I have administered, and I'm running into a pretty nasty problem. To start, we are working with a SQL Server on a remote server (not on our location or network). Mixed authentication is turned on and I have verified that the user accounts work properly by remote desktop into the server, and I have enabled remote access and cleared the port that SQL wants to use for remote connections (including through our server providers hardware firewall). So, here is the problem: Any time I try to log in on a remote machine, I immediately get back this login error:===================================Cannot connect to *.*.*.*.===================================Login failed for user '****'. (.Net SqlClient Data Provider)------------------------------For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476------------------------------Server Name: *.*.*.*Error Number: 18456Severity: 14State: 1------------------------------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()=============I know what this error means, it is a general login error, and the reason for it should be locatable within the server error logs. However, when I go to the log, there is no sign at all that a connection was even attempted, I only see the logs from on-server logins (I tested logging in with a bad PW on the server, and it logged it properly)Any ideas would be appreciated, I'm pretty stuck. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-08 : 12:12:56
|
"Any time I try to log in on a remote machine, I immediately get back this login error:"How are you logging on? SSMS? or using SQL connecting to, say, a LinkedServer?I generally think that "immediately get back this login error" is A Good Thing as it indicates that the target server was reached, and it rejected the credentials - whereas a significant delay, and then error, means that the packets aren't getting through at all.(Could also mean that the packet isn't leaving the building of course  )My connectivity process is usually:Create SQL Client Alias (locally) for the remote server (friendly name + Physical IP address [usually, unless there is DNS available to resolve the name for me], set port to 1433 or actual port if different, make sure that TCP/IP is selected [and not named pipes])Use Terminal Services to connect into the remote machine and prove my SQL ServerLoginID / password works on that SQL Server.If that is OK then try doing same using SSMS locally and trying to login to the "Friendly Server Name" set up as alias earlier.If that is OK then move to setting up a Linked Server (if that is required ... which won't be the case if its just remote admin, but will be if the local & remote databases need to exchange data)Being able to successfully PING the remote server is A Good Thing too |
 |
|
|
guildsbounty
Starting Member
3 Posts |
Posted - 2010-06-08 : 12:42:08
|
| Thanks for the input...the catch is, I already tried all that. I'm connecting using the right port, it's cleared through the firewalls, I know it is at least seeing the server, because if I try just a random server connection, it rejects it saying that it can't find the server. On the other hand, whenever I try to log in to the actual server I'm trying to get at, I get that login failed error message. I have tested the login on the server itself and it works perfectly. I test it on a remote machine and it gives me that error. I've also tried logging in as the sa and the dbo, none of which work remote either. I have the alias, I have the connection on TCP/IP, I can connect locally, I just can't get the SMSS to connect.I think my biggest lead here is the lack of error reporting. The login is failing, but not being recorded, which makes me think that there is still something wrong in the actual connection to the database. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-08 : 13:41:55
|
| Long-shot: launch SSMS with "run as Administrator"? |
|
|
|
guildsbounty
Starting Member
3 Posts |
Posted - 2010-06-09 : 09:31:12
|
| No luck there either...I'm going to see what happens if I pop a VPN to the server |
|
|
|
|
|
|
|
|
Remote Connection Difficulty