0

I have a SQL Server setup on one machine, where I have created a user and assigned a database to the user.

Now I want to access that server from client machine using management studio.

What I did:

  1. I enabled remote access from Management Studio on the server machine from server properties and set authentication mode as "Windows auth and SQL Server auth"
  2. I set TCP port as "1433" for IPAll in SQL Server Network Configuration and restarted SQL Server Services
  3. I created an inbound rule in firewall settings to accept all connections on port "1433"
  4. I added port forwarding in my router settings as follows: Router Port Forwarding (That's my private IP address in destination address)
  5. I types public IP (49.36.55.132\SQLEXPRESS) in client machine SQL Server Management Studio along with username and password with SQL Server authentication mode.

Getting this error on client machine:

Cannot connect to 49.36.55.132\SQLEXPRESS.

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rohit Patil
  • 11
  • 3
  • For `\SQLEXPRESS` to be recognized, the browser service must be enabled on the host. If you are sure the service is running on port 1433, you should be able to connect just by IP (but since your firewall is controlled by your company, you will have to use an internal IP address I suspect, not a public one - exposing a SQL Server via a public IP address is scary and reckless). – Aaron Bertrand Jan 15 '22 at 13:47
  • You could use `49.36.55.132,1433` instead of providing the instance name, assuming the port has been set to static at 1433. You probably want to find out whether it is actually exposed on the public IP, as mentioned – Charlieface Jan 15 '22 at 19:58
  • Okay, I think I need to find out about this internal IP address – Rohit Patil Jan 16 '22 at 19:10

3 Answers3

1

First you have to completely disable firewall in server machine and check if client machine sees server. After that if it's ok, check connecting with sql server management studio from client and at last enable firewall with rules to check if error is from firewall port settings.

Reza Akraminejad
  • 1,412
  • 3
  • 24
  • 38
  • I tried disabling firewall. It didn't happen as it's managed by my organization. – Rohit Patil Jan 15 '22 at 13:39
  • So you connected successfully without any problem when firewall was disabled? – Reza Akraminejad Jan 15 '22 at 13:46
  • No, actually I couldn't disable firewall as it's managed by my organization. – Rohit Patil Jan 15 '22 at 13:50
  • Try pinging for port 1433 and telnet 49.36.55.132 1433 if it will be connected or not. If no, ask from your network admin to open port. Client doesn't need to open port. Just server. Good luck – Reza Akraminejad Jan 15 '22 at 13:59
  • And also look at this tutorial and request from sql admin to do that for you. https://support.timextender.com/hc/en-us/articles/360042584612-Enable-Remote-Connections-to-SQL-Server-using-IP-address – Reza Akraminejad Jan 15 '22 at 14:00
  • Okay, tried telnet from client, couldn't establish connection. But found 1433 already open after scanning locally. Could this be because of organization VPN my machine is connected to? Also I checked the tutorial and made sure everything is as per the instructions, but still no luck. – Rohit Patil Jan 15 '22 at 14:48
  • 1
    @Rohit we can’t help you diagnose (never mind fix) your organization’s VPN, firewall, or routing. Please check in with your helpdesk. – Aaron Bertrand Jan 15 '22 at 15:10
  • Yes. I think that you have to get access from your network administrator. – Reza Akraminejad Jan 15 '22 at 18:30
  • Thanks Hamed, Aaron. I'll try to reach out to admin and see what is stopping it to pass through. – Rohit Patil Jan 16 '22 at 18:28
0

Check out your port forwarding on your Server machine, you should add your port on your modem. change your port, 1433 is default port.so you need to have Dynamic Port for you service.

Milad Chenari
  • 1
0

I encountered same problem, so if it can help somebody, here's a short summary of what needs to be done :

  • The SQL Server Browser service must be running on the server
  • TCP IP protocol must be enabled on the server (in SQL Server Configuration Manager, SQL Server Network Configuration, enable TCP IP for the current instance)
  • Allowed remote connections in the properties of the SQL Server instance (doable from SSMS. Normally the option is already checked by default but you can verify it)
  • Opened required ports on the inbound rules of server's firewall. If it does not work, you can also allow incoming traffic on all ports for right paths of ...\sqlservr.exe and ...\sqlbrowser.exe, opening 1433 TCP (sql server) and 1434 UDP (sql browser) may not be enough.
  • When the connection will be ok, if you are not over a domain/workgroup with network users, you will probably need to enable Mixed authentication for credentials management and create appropriate logins.

Some of those tips can also be found here : Unable to connect to SQL Server instance remotely

AFract
  • 8,868
  • 6
  • 48
  • 70