Can't connect an instance!

Question

Dear Experts:

I have two instances: one is built on SQL Server 2008, and another is upgraded to 2008 R2. From SQL Server management studio, I can connect both instances with computer name. Problem is: I can connect only old instance with IP address instead of computer name, but I can't connect new instance with IP address.

Thank you in advance.

 

Answer 1

Each SQL Instance uses a different port on the IP address.

For PHP, when you create an instance, you must assign a fixed port number *.

If your instance already has a fixed port number, all you need to do is append it to the IP address...

E.g. if you had instance Computer\InstA, it might be assigned to use port 2000, on IP address 1.2.3.4.  To connect to it, you use this syntax:

1.2.3.4,2000

$conn=sqlsrv_connect("1.2.3.4,2000", $params);

(The default SQL Server port is 1433, and this port does not need to be appended to the IP address)

* The port can be fixed or dynamic, and I think SQL Instances use dynamic port numbers by default.  It uses port 1434 for a browser service which maps instance names to port numbers.  You could connect to your instance in Management Studio, then run the command NETSTAT in a command window to see what port numbers have been used.

 

---

Rob

Answer 2

Thank you for your suggestion. I’ve found the port of SQL new instance using netstat command. I’ve used according to your suggestion, but still it generates error message.

On development, I use SQL Server 2008 R2 in where I have to use sqlsrv_connect function for establishing connection. But on the live database, we have SQL 2008 what I upgraded into 2008 R2. On live database, mssql_connect is used for connection setting, and still it is OK with old instance. Which connection function should I use for on the live SQL 2008 R2? 

Thank you again.

Answer 3

You should be able to use sqlsrv_connect. Can you post your PHP connection code and the error details you are receiving? That might help us understand what is going on.

Thanks.

-Brian

---

This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

Answer 4

Sorry for my late reply. Thank you for your reply. 

The error message is:

"A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. "

 

Answer 5

The error message means that either:

1. Your SQL Server was not found, so maybe you have *not* specified the correct parameters to sqlsrv_connect().
2. Your firewall or network configuration prevented your PHP server from communicating with the SQL Server instance.
3. You have not set up your instance correctly, e.g. disabled remote connections, disabled TCP/IP connections, or specified a dynamic (therefore, unknown) port number for the instance, etc.

This is not a problem with the driver.

Here are some basic rules for server names in sqlsrv_connect(), using examples for a theoretical instance MYINSTANCE running on computer MYSERVER at address 10.20.30.40 on port 5555:

1. With an IP address - to connect remotely over TCP/IP: sqlsrv_connect('10.20.30.40,5555', $params);
2. With server and/or instance name * - to connect using named pipes or TCP/IP on a LAN: sqlsrv_connect('MYSERVER\\MYINSTANCE', $params);

* When using server\instance names, these 2 rules may help:

1. Encapsulate the text in single-quotes (not: ")
2. Replace backslash \ with \\

---

Rob

Answer 6

I believe my firewall would prevent this. Using netstat, I found the port (for example: 55012). I added it to the exemption list of firewall, but it didn't work.

So, I solved it by applying default SQL port on new instance and added all databases of old instance to the new instance.  

Thank you so much both Johnson and Brian .