CODEWITHSUNDEEP
c#sql-servernetwork-programming
Fylix
Fylix

Reputation: 2723

Unable to connect in SQL Management Studio

My application is throwing error from a client machine of not able to connect to the database. So as a test I use SQL Management Studio 2008 on that same client machine to try and connect to the database and I found out that if I use IP\InstanceName then I can connect but if I use ServerComputerName\InstanceName then I can't connect. I have verified the following:

  1. The server has the same gateway and subnet mask as the client.
  2. I can ping the server using either its IP address or its computer name.
  3. No Firewall on either server or client machine.
  4. Verify Server SQL browser service is running.
  5. Verify Server is set to allow remote connection.
  6. Verify Server TCP/IP is enabled via SQL SErver Configration Manager.
  7. I can connect from a different client machine using ServerComputerName\InstanceName
  8. Both server and client are using static IP setup with no DNS server under IPV4 properties.
  9. Tracert from client to server shows only one hop, that is directly to the server machine IP.
    1. I can remote access the client machine from the server machine or viceversa using host name.

I then go to the server machine and connect using LNKLAB8\Xmark (LNKLAB8 is the host name and Xmark is my instance name) and I connected successfully. However, when I run the command SELECT @@SERVERNAME, the result I got back is LNKLABARIAL8\XMARK

This baffled me, because if I right click on the database properties as per the screenshot below, it shows LNKLAB8/Xmark .. am I going crazy or something weird going on here? after taking at the steps I've taken, do you have any suggestion of how I can get the client machine to connect to the database using the server host name instead of the server IP address?

I've kind of hit the wall on this issue, I'm unsure if I am facing a network related issue or it is a database setting issue.

Edit: When I tried to connect using host name to the server DB I would get the following:

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)

enter image description here

Upvotes: 3

Views: 3204

Answers (3)

Nikita
Nikita

Reputation: 422

Try add servers IP and Name to c:\WINDOWS\system32\drivers\etc\hosts

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

I noticed this:

8: Both server and client are using static IP setup with no DNS server under IPV4 properties.

How is name resolution happening, then? WINS? NetBIOS? Sql Server may not like that. Try checking that you have a working DNS server with the appropriate entry for your Sql Server system, and things may work better.

Upvotes: 1

paulsm4
paulsm4

Reputation: 121881

1) Make sure you've enabled TCP/IP (Configuration manager), and restarted MSSQL <= it sounds like you've done this.

2) Make sure you can "ping" by hostname and by IP <= It sounds like you've done this, too

3) Make sure MSSQL Browser is running

4) If you're using Windows authentication (vs. SQL Mixed), then make sure you can authenticate to the remote server:

net use \\remote-server /user:<name> <password>

5) Failing all else, you might want to consider reinstalling MSSQL-related components on either/both client or server (for example, MDAC)

Links:

'Hope that helps!

Upvotes: 2

Related Questions