Sql-server – How to configure an instance to listen on port 1433 to allow remote connection

clusteringsql serversql-server-2012

Trying to connect using SQL Server Management Studio 2012 on a Windows 8.1 Client computer from outside the cluster's domain.

Cluster is a two node Virtual Cluster made from Windows Server 2012 R2.

We can connect to the cluster from within the cluster's domain by using the Failover Cluster Virtual Network Name Account with SSMS running on another Virtual Computer from within the cluster's domain.

We have created a new Inbound Rule with port 1433 on each of the two nodes – but still don't understand how to connect to the SQL Instance from outside the domain.

Best Answer

If you are connecting from outside the domain you can either

1) Use SQL authentication to login. If you have enabled mixed mode during the install you will have an sa account and you can create a sql login for yourself

2) There are other tricks, such as enabling named pipes and then authenticating against the IPC$ share with your domain account and then you can login using np:ipaddress in SSMS. But beware it is a bit slow and most people don't want named pipes enabled on a production system.

But if you are connecting from outside the domain the easiest way is use SQL authentication.

Related Solutions

Sql-server – Clients require instance name to connect to clustered instance

From further research, it appears the issue may be a result of older SQL clients performing an "InstanceValidity" check when connecting to SQL Server (specifically sqlsvr32.dll driver). The driver passes "MSSQLServer" as the instance to verify for the InstanceValidity check. In this case, since the instance name listening on the Default port (1433) is named "Instance1", the check fails because the Instance names do not match the InstanceValidity check.

Specifying the port number or instance name explicitly in the connection string works.

Sql-server – SQL Server is started but didn’t listen on any port

What made my SQL server listen for TCP connections on port 1433 was the following...

Configuration Tools / SQL Server Configuration Manager
click the instance name in SQL Server Network Configuration
enable TCP communication
Right-click the TCP communication entry, choose Properties
Click the IP addresses tab
Enable all non-VMnet IP addresses and enter 1433 in the Port entry for "IPAll".
Restart the SQL Server on SQL Server Services tab

Check your configuration with netstat -an:

C:\>netstat -an | find "1433"
  TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
  TCP    [::]:1433              [::]:0                 LISTENING

Best Answer

Related Solutions

Sql-server – Clients require instance name to connect to clustered instance

Sql-server – SQL Server is started but didn’t listen on any port

Related Question