September 18, 2014 at 9:50 am
I have a server running serveral instances that was recently moved from one location, and server, to another. Since the server's name did not previously comply with company's naming standards, it was renamed. But that's when we started getting errors connecting to the server from a remote location. Instances are available from the host server console using Management Studio. You can ping the server and it responds. TCP/IP is enabled and SQL Browser is running. Ports 1433 and 1434 are open but the error is always the same:
===================================
Cannot connect to NewServerName\SQLDEV1.
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
------------------------------
Error Number: -1
Severity: 20
State: 0
------------------------------
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.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
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 am not used to working with instances because all our servers are dedicated to a specific task but this one I inherited from a company purchase and, even though it would be split with each instance getting its own VM, for the migration I need to make it work. Any help will be greatly appreciated.
September 18, 2014 at 7:51 pm
Check which port each instance is running on in SQL Configuration Manager - SQL Server Network Configuration - TCP/IP - IP Addresses - IPAll - TCP Dynamic Ports
Connect using the IP address, port number (eg. 111.111.111.111,1433) and run:
select @@servername
sp_dropserver 'Old Server Name\Instance_name'
go
sp_addserver 'New Server Name\Instance Name','local'
go
Then restart SQL services for that instance including SQL Browser.
September 19, 2014 at 9:46 am
Thanks, Andrew, but I already did that and it is still not reachable.
September 21, 2014 at 6:16 pm
Have you tried connecting to the DAC or running SMSS locally on the desktop and using localhost,port ?
September 22, 2014 at 4:52 am
From the server I have no problem accessing the instances. It is when someone attempts a remote access to either one of them that the error occurs. Pinging the server succeeds but the instances are still not available from a remote computer.
September 22, 2014 at 5:22 am
The instances will not be running on the default port 1433. Check which port they're running on in configuration manager, then try connect remotely using the ip address and port number. If you can't get a connection, try telnet to the ip and port. If that fails, it's a firewall or antivirus issue.
September 22, 2014 at 9:10 am
I did check the ports and tried accessing to no avail. I have contacted my sysadmin to look into it.
Thanks
September 22, 2014 at 9:22 am
Just a thought - SQL browser service is running?
September 22, 2014 at 9:27 am
Yes, it is but under a different account than SQL Server. Does that make a difference?
September 22, 2014 at 9:47 am
I just noticed one thing, this server was cloned from one outside our network and the accounts for SQL carried over. I am working with the sysadmin out of suspicion that this might be the real issue, that those accounts have no access in our netowork.
September 22, 2014 at 2:54 pm
That didn't make a difference at all. What I did find though was that the server was accessible, painfully slow, from the old server in a different network across the country. It is not accessible from our network. I am not familiar with TCP/IP and cloning is not the way I requested this server be built but that's the way systems wanted it for speed. Speed that took them longer that it would have taken me to set up each instance as an individual SQL Server VM. At this point I don't know what to do, telnet IP port works from the old location and SSMS can connect to it using IP,port but the same does not happen on our network.
September 22, 2014 at 9:16 pm
Check with your networking guys, if you tell them you cant telnet to port x from this IP to this IP but can elsewhere then they should be able to track it down. (telnet is good way to suggest to them that it's not a SQL Server issue)
Would be good to know what the cause is either way if you find out.
September 23, 2014 at 7:34 am
Yes, unfortunately our network group takes a long time to respond. I have a meeting today with systems who still thinks it is a SQL issue but that is largely because they were the ones who insisted on cloning the servers and not installing fresh to save themselves time. As it turned out, it took them a lot longer than expected, and I don't mean days, and it has taken me a lot longer than it should. What was going to be well ahead of schedule is now running close to late.
Thanks
September 23, 2014 at 3:01 pm
We got it working partially. It works with IP,port, although there are some issues with some servers that don't seem to connect that way, but not with names. The problem was with SQLBrowser that was defaulting to an instance that was not recovered when it was restarted. I restarted SQLBrowser and then connection was possible again. I will keep posting updates.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply