locked
remote access SQL Server problem RRS feed

  • Question

  • I installed SQL Server 2014 standard version on one server with Windows 2012 R2 standard version and one Windows 7 workstation. Both of them belong to workgroup and use DHCP to get their IP address. Although I connected them to the same switch but the IPs they got belong to different subnets. I configured the firewall and set the server with mix authentication mode.

    I tried to use workstation's Management Studio to remotely connect to my Database running on server. I opened the "Connect to Server" dialog window and clicked the "Server Name" drop-down box, then choose "<Browse for more ...>". in the "Browse for Servers" window, choose "Network Servers". After a few seconds, it shows some SQL Server names but the one database running on my server is not in the list. I also tried to use "sqlcmd -L" and still I couldn't find my remote SQL server. The weird things is that sqlcmd got a server name I already deleted one day before. I also tried to create a ODBC source on another PC but it couldn't find the server either.

    After that, I gave both server and workstation with same 192.168.1.*/24 subnet address and all of them three (management studio, sqlcmd, ODBC) can find the server now. They can still access the server even I changed the server's IP address.

    I am wondering what happened before the first successful access and how clients can still access the server with a new IP address after the first successful access. Is there any one can explain how the SQL Server can be recognized or fetched by clients?

    Thanks a lot.

    Friday, May 1, 2015 7:50 PM

Answers

  • 1) First check on local machine.

    2) Sql port is open ?

    3)Open cliconfg from a RUN prompt and make sure TCP/IP is an enabled protocol.

    4)Use netstat –an from the command prompt to verify that the server is listening for SQL traffic on the correct ports

    5)If the server is not listening for SQL traffic on the correct ports, use SQL Server Configuration Manager to change the ports.

    For MS SQL 2014 go to Start > All Programs > Microsoft SQL Server 2014) > Configuration Tools > SQL Server Configuration Manager.

      • For MS SQL 2014: Use the Windows key or hover over the left lower corner of the desktop and select All Programs Microsoft SQL Server 2014 > Configuration Tools > SQL Server Configuration Manager.

      • Open the + next to SQL Server Network Configuration.
      • Right-click TCP/IP and select Properties.
      • Select IP Addresses.
      • All TCP ports mentioned on all interfaces should be 1433. Change this to reflect the correct port number and restart the SQL services.

    .

    • Marked as answer by Psymerlin Friday, May 8, 2015 8:18 PM
    Friday, May 8, 2015 7:10 PM

All replies

  • can check with hostname ?
    Tuesday, May 5, 2015 3:57 PM
  • I didn't try that. I am not sure if it is caused by SQL browser service or not.
    Tuesday, May 5, 2015 4:05 PM
  • start sql browser services  then check.
    Wednesday, May 6, 2015 12:34 AM
  • 1) First check on local machine.

    2) Sql port is open ?

    3)Open cliconfg from a RUN prompt and make sure TCP/IP is an enabled protocol.

    4)Use netstat –an from the command prompt to verify that the server is listening for SQL traffic on the correct ports

    5)If the server is not listening for SQL traffic on the correct ports, use SQL Server Configuration Manager to change the ports.

    For MS SQL 2014 go to Start > All Programs > Microsoft SQL Server 2014) > Configuration Tools > SQL Server Configuration Manager.

      • For MS SQL 2014: Use the Windows key or hover over the left lower corner of the desktop and select All Programs Microsoft SQL Server 2014 > Configuration Tools > SQL Server Configuration Manager.

      • Open the + next to SQL Server Network Configuration.
      • Right-click TCP/IP and select Properties.
      • Select IP Addresses.
      • All TCP ports mentioned on all interfaces should be 1433. Change this to reflect the correct port number and restart the SQL services.

    .

    • Marked as answer by Psymerlin Friday, May 8, 2015 8:18 PM
    Friday, May 8, 2015 7:10 PM
  • Thanks a lot. I basically did all of that and now it is running well.

    I think it is caused by SQL server browser service.

    Friday, May 8, 2015 8:18 PM