SQL Server 2008 R2 Remote Connection
We had a post about Remote Connection for SQL Server 2008 R2 Express, but this time we work on a SQL Server 2008 R2 version, which is not an free express version.
We installed the SQL Server 2008 R2 in Windows Server 2008 R2 Enterprise system. We tried to access this SQL Server from another computer using SQL Server Management Studio Express, an error message pop up:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The Server was not found or was not accessible…. Error: 1326
Then we went back to Windows Server 2008 R2 pc to change some configurations which we recorded as the following steps:
1: SQL Server Connections Settings:
On the SQL Server database installed server, Run SQL Server Management Studio, Right click the database which you want to remote connect, and Select Properties :
Select Connections page, make sure the Connections Maximum number set to right number, also check on Allow remote connections to this server;
In our case the above settings are correct, but still not be able to remote accessed, so we continue the next step:
2: SQL Server Network Configuration:
Run SQL Server Configuration Manager, expande SQL Server Network Configuration and select Protocols for MSSQLServer, make sure the right side TCP/IP is Enabled.
But we still could not get working, so we try next step:
3: Firewall on SQL Server Database installed Server:
Actually, if you want to follow Microsoft official firewall settings to Allow SQL Server Access, please visit it, however, I do not think it is easy to read and understand fast, so let me try to friendly and easier way:
For the firewall setting, we first consider the Firewall blocked the port which SQL Server protocols are using. then let us check what the port number is.
Still in SQL Server Configuration **Manager, **right click TCP/IP of Protocols for MSSQL Server, select Properties:
Go to IP Address tab page, Now you see the following window which includes IP address and port information.
We simply select IPALL and set TCP Port number to 50000 because we think port number 1433 is normally blocked by Windows firewall, so we change to another number.
Of course if you do not want to change port number, just keep the default number 1433 and make sure all left steps you use 1433 but not 50000 then.
About port number range changing in new Windows system, we can read here, Microsoft said:
To comply with Internet Assigned Numbers Authority (IANA) recommendations, Microsoft has increased the dynamic client port range for outgoing connections in Windows Vista and in Windows Server 2008. The new default start port is 49152, and the default end port is 65535. This is a change from the configuration of earlier versions of Windows that used a default port range of 1025 through 5000.
Also, we can check the port numbers that are assigned to specific applications by IANA.
Every time when we changed TCP/IP configurations, the system reminds we need to restart SQL Server Service otherwise the new settings will not work until restarted.
_Here__ is Microsoft official Configure a Server to Listen on a Specific TCP Port in SQL Server Configuration Manager._
How to restart SQL Server Services:
You do NOT need to restart Windows Server but just keep in SQL Server Configuration Manager windows, select SQL Server Services, and then select SQL Server (MSSQLSERVER) in right panel, right click and select Restart.
Now we need to disable Firewall blocking the port 50000 which we just set above:
On SQL Server installed server, input "Firewall" in search box which is above Start button, and select Windows Firewall with Advanced security to open firewall settings advanced windows
In Advanced Firewall setting window, select Inbound Rules in left panel, and click New Rule… in right panel to add a new rule
We are adding a Port so we select Port:
Click Next button, Fill our new port number:
Click Next button, Allow the connection:
Click Next button, set Pofile (Note: if your remote computer is in different domain, then you have to check on all there option here Domain, Private and Public !!)
Click Next button, Give the rule a name:
After you click Finish button, you will see the new rule was added.
By here you should be able to access the SQL Server 2008R2 which you installed on Windows Server 2008R2 remotely.
Note:
Other articles mentioned we should turn on the SQL Server Browser services, which I found I did not need to turn on it, also, it is more safety if do not turn on SQL Server Browser. But if your case still does not work, try to turn on SQL Server Browser services, which can be turn on in SQL Server Configuration Manager.