Sql server connects remotely from any network but not local network

Question

1.00/5 (1 vote)

See more:

I have a server with two SQL Server instances on it.

I can connect to both of them remotely from any ware in the world however I can't connect to one of them in my local network.

I can't find any problem I can connect to both of them out side my network but one of them is not working in my local network.

I can connect to both instance from out side my network using my public IP address so there is no problem with my firewall and ports. If I'm out side my network i can login to both instance with "sa" user, but when I'm in my local network i can only connect to one of them and the other is not working.

Microsoft.Data.SqlClient.SqlException (0x80131904): 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) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen() --- End of stack trace from previous location --- at Microsoft.Data.SqlClient.SqlRetryLogicProvider.ExecuteAsync(Object sender, Func1 function, CancellationToken cancellationToken) at Microsoft.Data.SqlClient.SqlRetryLogicProvider.ExecuteAsync(Object sender, Func1 function, CancellationToken cancellationToken) at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c_DisplayClass30_0.<b0>d.MoveNext() in //src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/ReliableSqlConnection.cs:line 323 --- End of stack trace from previous location --- at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in /_/src/Microsoft.SqlTools.ServiceLayer/Connection/ConnectionService.cs:line 755

What I have tried:

1- Set Port from manger TCP/IP

2- Restart the server

3- Disable the Firewall

Posted 30-Apr-24 14:47pm

Member 7912784

Updated 30-Apr-24 14:49pm

Dave Kreskowiak

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2024-04-30T17:25:00

Solution 1

Since you can connect to the server from outside your network, the SQL Server IS listening to whatever ports are configured, usually 1433.

This comes down to some configuration of your network, not the server. You need to describe your network, its configuration, and where the SQL Server is located, be it either on premises, or a cloud-based setup.

Posted 30-Apr-24 17:25pm

Dave Kreskowiak

Comments

Member 7912784 6-May-24 19:33pm

Hi
I'm using rackspace server provider, so it's cloud base server and i don't have any info about the network, how ever if i ping form any sever i get normal response

Dave Kreskowiak 6-May-24 22:43pm

I was talking about YOUR network, not any cloud-based network.

Member 7912784 8-May-24 17:35pm

Hi
There is nothing relate to my network i'm using Remote disk top to login to one server and from it i login to other server database in the other server is the two sql server instance i can log in to one but not to another how ever if i'm using my pc i can log in to both using the public ip win i'm in the main server i can only login to one of them even the servers is in same network

Dave Kreskowiak 8-May-24 18:30pm

I'm sure that made a lot of sense to you because you have everything in front of you, but to those of us on the other side of the chat window are doing this completely blind and have only what you type in a textbox to work with.

What you said made no sense whatsoever.