can not log in to sql server named instance

Question

Hi,

   on my sever i have installed sql server default server. and it is working well and good, and developer can access the default instance.

   now i have installed named instance on the server for development, but developer are not able to access the named instance. below is the error. any help on this.

TITLE: Connect to Server
------------------------------

Cannot connect to 10.0.0.18\sql.

------------------------------
ADDITIONAL INFORMATION:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Answer 1

HI,

1) Check if the SQL Server of Named instance is up and running

2) check if the tcp/ip is enables for named instance

3) If tcp/ip is enabled, check if firewall is blocking the named instance bcoz named instance will use dynamic ports

4) if firewall disabled on the server:

5) try to connect from SSMS for the local machine

Please share to us, which version of sql u r using...

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 2

Perhaps the SQL Server browser service isn't started? Or firewalls block port 1423 UDP? Try checking the errorlog file and connect using the port number instead of instance name: servername,portnumber

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 3

Make sure that TCP/IP is enabled (From SQL Server Configuration Manager), the SQL SERVER BROWSER service is up an running and check if the Windows Firewall is blocking the TCP Ports (Nothe that the named instances don't use TCP port 1433 by default)

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

we are using sql 2008

how do i check,

3) If tcp/ip is enabled, check if firewall is blocking the named instance bcoz named instance will use dynamic ports.

i have sql instance in firewall.

we are running windows server enterprise edition.

Answer 5

I check all the settings mentioned above,

as we can access default instance from the same server not named instance.

Answer 6

Did you see my suggestion?

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 7

yes , i tried suggestions but no luck

i tried loging using sever:port

Answer 8

what port names instance uses, how can i get this information.

Answer 9

Hi Ron,

As informed earlier, Named instance will used dynamic ports....

Please connect to configuration manager of SQL Server (or) from SQL server Logs and make sure you are checking on named instance only...

For reference please find the below snap shot: (here in my snap hot i m showing in default instance, please make sure u r checking on named instance)

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 10

I used following port in configuration manager. Still getting Same error

i am also getting following in sql log

Answer 11

Sorry did  attached the pics

Answer 12

From where you are trying to connect by using SSMS or from an application.

Can you try to connect (named instance) by using SSMS from Local Machine and let me know the results...

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 13

HI its working with tcp:<cmpname>,port

but why its not working with cmpname\instance name, as developer can not use tcp:<cmpname>,port.

any other way to get it work.

Answer 14

yes i am able to connect with windows authentication as well as sa.

Answer 15

Hi Ron,

U r able to connect from Local Machine and NOT from remote machine right?

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 16

I didn't suggest using server:port. I suggested using server,port. Comma, not colon. Big difference.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 17

So, you are saying that it *does* work using TCP:server,port? OK, fine.

Does it also work using only server,port?

Is the SQL Server Browser service started on the SQL Server machine. This is the one that translates the instance name to port number. That service not being started would explain why it work connecting using port number and not instance name (and this was the reason I asked whether connecting using port number works, in the first place).

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 18

yes its working with server,portnumber, sql server browser service was running as well.

Answer 19

Correct, I am not able to do using remote machine.

Answer 20

I did restart sql server browser service, but no luck with instance name.

Answer 21

Ron,

Can u please telnet the SQL Server Instance Port number from remote machine.

http://blogs.msdn.com/b/sql_protocols/archive/2006/09/30/sql-server-2005-remote-connectivity-issue-troubleshooting.aspx

http://msdn.microsoft.com/en-us/library/ms378845(v=sql.90).aspx

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 22

Can the client connect to port 1434 UDP on the server? This is what the SQL Server browser is listening to, to translate instance name to port number.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 23

Thank you for your response. I may not have had the same issue as yours, but I kept receiving a

TITLE: Connect to Server
------------------------------

Cannot connect to (local).

------------------------------
ADDITIONAL INFORMATION:

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: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

error, and your suggestion helped. I opened the config manager and enabled TCP/IP and now it is able to connect. So, thanks :)

Answer 24

The answers for me are lost in the big thread.  Here is what specifically fixed my issues when not being able to access SQL 2012 remotely.

0. Let's assume you have remote connections configured.

1. You have to explicitly set the port to 1433 in the SQL Server network configuration..TCPIP..Properties as shown by Satish.  In SQL 2012 this was a problem for me.  Use the "IPALL" at the bottom.

2. Use your TCPIP address that you can get from running IPCONFIG on the server box in case DNS does not know your server name.  If you have a named instance, still use the \instance name after the IP address.



---

Chip