0

So I have a SQL Server set up on a remote server machine. And I am trying to connect to this remote SQL Server via the local pc. I can connect with no problem using ServerDnsName, or ServerDnsName,1433\SQLEXPRESS.

But, what i would like is to connect with the same name for a Server name as on a remote server. This I want because I am using PowerBi on-premises gateway on a remote server. And I think it wants to use in PowerBI Desktop the same name for server login as on the remote server for login in SSMS.

These are the different names for a server that works either on local pc, or on the remote server, but none works on both:

  • ServerDnsName,1433\SQLEXPRESS works on local, not on remote
  • ServerDnsName works on local, not on remote
  • ComputerName\SQLEXPRESS works on remote server, not on local
  • ServerIp works on remote server, not on local
  • ServerIp,1433\SQLEXPRESS works on remote server, not on local

How can I connect with the same name for a Server Name, in a Sql Server Managment Studio, on both local machine and remote Sql Server?

Thanks

dat
  • 9
  • 3
  • I'd be curious to see the result of `select @@servername` after connecting with `ServerDnsName,1433\SQLEXPRESS`. I expect it's connecting to the default `MSSQLSERVER` instance. If you're needing to use a port number because the `\InstanceName` isn't working then it sounds like the SQL Browser Service isn't running on the target server or it isn't accessible via udp/1434 due to firewall rules. – AlwaysLearning Feb 10 '21 at 09:51
  • To answer the intent of your question, I think, have you tried adding an entry to your HOSTS file with your local IP address resolving to the remote server name? Comment out the line when you want to connect to the real production server, uncomment the line when you want to connect to the local development server. – AlwaysLearning Feb 10 '21 at 09:56
  • I would consider doing it the other way round, put a HOSTS line on the SQL Server to resolve it's own ServerDNSName – Charlieface Feb 10 '21 at 10:25
  • @AlwaysLearning When doing the 'select @@servername' after connecting with 'ServerDnsName,1433\SQLEXPRESS', I get returned 'ComputerName\SQLEXPRESS'. SQL Browser Service is running. Both TCP 1433 and UDP 1434 are in firewall exception for inbound and outbound. – dat Feb 10 '21 at 10:40
  • @AlwaysLearning and Charlieface I added a new line to hosts file. IP address of a server, resolving to ServerDNSName. It is working, now i can connect with server DNS name on both sides. Thank you both! – dat Feb 10 '21 at 10:55

0 Answers0