March 31, 2010 at 5:43 pm
Hello All,
My server is SQL Server 2008 Ent 64-bit on Windows Server 2008 Std 64-bit, default instance name. I changed the default port number from 1433 to 1533 or others, then I couldn't make remote connection with SSMS 2008 from my client machine, which is Win XP Pro SP3 32-bit. The err message is:
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I have verified this:
1. The SQL Browser service is running and has been restarted several times.
2. The firewall is disabled and I can telnet the 1533 port from the client machine.
3. The instance allows remote connection - 1433 port works fine.
Any help is appreciated greatly! Thank you!
March 31, 2010 at 5:49 pm
Are you able to connect if you specify the port number?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 5:55 pm
If I use machine_name,1533 in SMSS, it works. It seems the SQL Browser doesn't work?
March 31, 2010 at 6:03 pm
rainleta (3/31/2010)
If I use machine_name,1533 in SMSS, it works. It seems the SQL Browser doesn't work?
I am not sure the Browser service works that way. If there are multiple named instances on the machine that use dynamic ports, then the browser service interprets them. However, once you assign specific ports to each instance then the browser service does not fetch the instances. Each instance would have to be specified with the correct port number. That has been my experience.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 6:12 pm
I only have one default instance on the server. And before I specified the 1533 port, I double-checked it is not occupied by other applications.
What do you mean by "correct port number"? Thanks!
March 31, 2010 at 6:15 pm
The port number that you manually assigned to it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 6:19 pm
I think 1533 is a valid or correct port number
:crying:
March 31, 2010 at 6:24 pm
rainleta (3/31/2010)
I think 1533 is a valid or correct port number:crying:
It is.
Correct port number simply means the port number that you manually assigned to that instance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 6:48 pm
Another finding:
I enabled the dynamic port on the server, restart the SS and SQL Browser Service, I cannot make connection from client. I have to find and use the port in the connection.
It seems the SQL Browser doesn't work at all. Is there any way to diagnose and repair?
March 31, 2010 at 9:08 pm
rainleta (3/31/2010)
Another finding:I enabled the dynamic port on the server, restart the SS and SQL Browser Service, I cannot make connection from client. I have to find and use the port in the connection.
It seems the SQL Browser doesn't work at all. Is there any way to diagnose and repair?
When you say you enabled the dynamic port, did you remove the static port mapping?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 10:03 pm
The error you've posted states that you aren't able to connect with named pipes, can you check that you have SQL listening on TCP/IP?
April 1, 2010 at 10:17 am
CirquedeSQLeil (3/31/2010)
rainleta (3/31/2010)
Another finding:I enabled the dynamic port on the server, restart the SS and SQL Browser Service, I cannot make connection from client. I have to find and use the port in the connection.
It seems the SQL Browser doesn't work at all. Is there any way to diagnose and repair?
When you say you enabled the dynamic port, did you remove the static port mapping?
I don't know what static port mapping is. I simply go to the SQL Server Configuration Manager, open the TCP/IP protocols properties, IP addresses tab, set TCPALL, TCP Dynamics to 0, TCP Port is blank. Then reboot the services.
April 1, 2010 at 10:18 am
Nicholas Cain (3/31/2010)
The error you've posted states that you aren't able to connect with named pipes, can you check that you have SQL listening on TCP/IP?
Yes. From netstat command, I can see the port is listening and I can telnet from client.
April 1, 2010 at 10:22 am
How about your client? Checked to make sure that's attempting to use TCP/IP?
April 1, 2010 at 10:45 am
Nicholas Cain (4/1/2010)
How about your client? Checked to make sure that's attempting to use TCP/IP?
I think there is no problem with my client. If I specify connection as server,port I could make the connection. Apparently, the SQL Server Browser cannot translate the service port, so I have to explicitly give it my client.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply