-1

I am using the "test.udl" to test the connect.

When I use the IP which is from the router, it can connection test is succeeded.

Then I try to use the real IP for testing connect, it got fall.

The router has set as below: Archer C2>forwarding>Virtual Server:

Service Port=1433,1434,49172

Ip address=192.168.0.100(Permanent)

And I have closed the firewall in windows.

The Sql server configuration manager setting:

TCP IP Properties>IP Address> IP1:

Active:yes

Enabled:yes

IP Address:119.246.x.x

TCP Dynamic ports:0

TCP port:null(unset)

IP2:

Active:yes

Enabled:yes

IP Address:192.168.0.100

TCP Dynamic ports:0

TCP port:null(unset)

test.udl test information:

Fail test

server name:119.246.x.x\server

Use Windows NT integrated security

result:

Test connection failed because of an error in initializing provider.

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

succeed test

server name:192.168.0.100server

Use Windows NT integrated security

result:

Test connection succeeded.

Does anyone know where i got wrong?

Community
  • 1
  • 1
ram wong
  • 1
  • 1
  • 2
  • You shouldn't be using references to external screenshots: They tend to get lost after some time and render the question and answer useless for other readers. Preferably include all needed information as properly formatted text in the question – tofro Sep 11 '16 at 15:20
  • I have removed all the screenshots and write in formatted text. Thank you. – ram wong Sep 11 '16 at 15:38

2 Answers2

1

It is a bug.

SYMPTOMS

When you try to connect to a clustered Microsoft SQL Server 2005 or Microsoft SQL Server 2000 named instance by using the "servername\instancename" syntax, you receive the following error message: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. You may receive this error message when the following conditions are true:

SQL Server 2005 or SQL Server 2000 is installed on a cluster.
You are connecting to a SQL Server named instance by using TCP/IP sockets.
IPSec policy is enabled on the client domain.
IPSec policy is not enabled on the server domain.

CAUSE

This problem occurs during the discovery phase of the connection. The IPSec policy on the client drops packets from the server when the source IP changes.

WORKAROUND

To work around this problem, you have to hardcode the TCP port or the Named Pipe of the SQL Server named instance. To do this, use a connection string that is similar to one of the following:

[oledb] ; Hardcoded TCP OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security >Info=False;User ID=clientID;Data Source=tcp:TcpIpAddress,port

[oledb] ; Hardcoded Named Pipes OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security >Info=False;User ID=clientID;Data Source=np:\ServerName\pipe\MSSQL$InstanceName\sql\query

*Source taken from https://support.microsoft.com/en-us/kb/888228

Hope it helped

Community
  • 1
  • 1
Paulkokos
  • 36
  • 5
  • But i am using sql server 2016 – ram wong Sep 11 '16 at 15:44
  • hi, i have the same problem with sql server 2016. i setted tcp/ip and other options like sql 2008 instance, but nothing, not working. i m trying to connect with sql Management. always error and can t reach it. – elle0087 Jan 26 '17 at 13:19
0

I have solved it. It may cause of the default port is not 1433. I checked the log file viewer(SQL server Management studio>SQL Server Agent>Error logs>current(archive is also work))

only tick on the sql server check box

find "server is listening on[ 127.0.0.1 xxxx].

In my case it is 9662/9663

I add them all in my router and succeed to connect.

Thank you all above.

Community
  • 1
  • 1
ram wong
  • 1
  • 1
  • 2