0

I am trying to remotely connect to a Microsoft SQL Server from Node (using node-mssql).

var sql = require('mssql')

var config = {
  server:   '**',
  user:     '**',
  password: '**!',
  database: '**'
}

sql.connect(config, function(err) {
  var request = new sql.Request()
  request.query("select *", function(err, recordset) {
    console.log(err)
    console.log(recordset)
  })
  console.log('end')
})

I ran the following query from the server:

SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

based on SQL - Query to get server's IP address

Do I use the client_net_address to remote connect, or some other IP for the server parameter?

KVNA
  • 847
  • 1
  • 11
  • 24
  • The `server` property defines the remote server address (The remote MS-SQL server). – TamirNahum Dec 23 '17 at 22:34
  • Yes, but how do I locate the correct address? – KVNA Dec 23 '17 at 23:43
  • Currently I have to VPN into the network and then use another IP to connect to to the SQL database. – KVNA Dec 23 '17 at 23:43
  • Why wouldn't you try it with the `client_net_address` property? I can't really understand what you mean.. – TamirNahum Dec 23 '17 at 23:47
  • If you have to use a VPN to access the network of the SQL database, are you sure the SQL database is even exposed to the internet? – Jeffrey Dec 23 '17 at 23:57
  • @jkaufman No, I'm not sure if it's exposed to the internet. I'm trying to figure out the process to set up an external IP for the db and access it remotely. – KVNA Dec 24 '17 at 02:56
  • @TamirNahum I tried using ```client_net_address``` and the connection was refused. – KVNA Dec 24 '17 at 02:58
  • I'm afraid you're confused as to how this works. You shouldn't be trying to attempt to determine the IP address from the MSSQL server, as it will only be able to provide you with a local IP address specific to your network, hence the need for you to use a VPN to access it. You must determine the public IP of the machine that the server is running on, and ensure that it has internet access. If it does, then you need to ensure the port that MSSQL listens on is open, as well as allowing traffic through the firewall. – Jeffrey Dec 24 '17 at 05:12
  • OK. Do I use the VPN username and password for the ```config``` connection string? – KVNA Dec 24 '17 at 05:37
  • Do I need to use IIS to configure anything? – KVNA Dec 24 '17 at 05:45
  • You would not use the VPN username or password for the MSSQL connection string, no. You would want to use the username and password for the MSSQL server. But before you can do that, you must make sure that the server is exposed to the internet. That may be through IIS as a reverse proxy, or it may be directly via the system itself. – Jeffrey Dec 24 '17 at 09:14
  • I setup a reverse proxy with the ```client_net_address``` to the remote connection ip changing only the last digits ```xxx.xxx.xxx.100``` and using the SQL database credentials, and it's still not connecting. – KVNA Dec 24 '17 at 16:07
  • Using these instructions: https://blogs.msdn.microsoft.com/friis/2016/08/25/setup-iis-with-url-rewrite-as-a-reverse-proxy-for-real-world-apps/ – KVNA Dec 24 '17 at 16:08

1 Answers1

1

If connecting from an application on the same machine, use the local_net_address.

KVNA
  • 847
  • 1
  • 11
  • 24