Link to home
Create AccountLog in
Avatar of Marthaj
MarthajFlag for United States of America

asked on

Using Sql Cmd to connect to a Windows 10 MS SQL Server From a Ubuntu 20.04 server

I have tried several solutions that I found for this problem, but Inone have been successful. I am trying to use sqlcmd from a AWS Ubuntu server TO a windows laptop (at home) that has the MS SQL Server database.

I have a AWS EC2 instance  with: Ubuntu 20.04, Apache2.4, Microsoft ODBC Driver 17 for SQL Server. I have verified that OpenSSH and Microsoft ODBC Driver 17 for SQL Server are installed. I have verified that the sql cmd is installed correctly (sqlcmd -?)

Microsoft (R) SQL Server Command Line Tool Version 17.10.0001.1 Linux Copyright (C) 2017 Microsoft Corporation. All rights reserved.)

Open in new window

The Apache2 firewall is disabled. 

On the Windows 10 computer, I have verified in the SQL Server Configuration Manager, that the SQL Server, SQL Server Browser and SQL Server Agent are all running - they set to automatic.

I also made sure that in the SQL Server Network Configuration and the SQL Native Client 11.0 Configuration, that the Client Protocols of tcp/ip are enabled. 

 I also executed a query in MS SQL Server via xp_readerrorlog, to display the ports the server is listening on - for IPv4 and IPv6 - it is the default port of 1433.

 

On the windows 10 computer, I added rules to the Windows Defender using this link:

https://docs.driveworkspro.com/topic/howtoconfigurewindowsfirewallforsqlserver#CreateInbound

Open in new window

I did a hard restart of the Windows computer.

This is the command I am trying to use - actual address is x'd out:

sqlcmd -S xxx.xxx.x.xxx,1433 -U myuser -P mypwd  -Q 'select @@Version'

Open in new window

I have also tried to connect with the Windows Defender turned off.

I have tried variations of the sqlcmd to no avail.

These are the rules I added to my AWS Server - I am not very good at doing rules. 

Inbound:  IPv4   MSSQL   TCP   1433   xxx.xxx.x.xxx/32   - the tcp/ip if for windows computer
Outbound: IPv4   MSSQL   TCP   1433   xxx.xxx.x.xxx/32   - the tcp/ip if for windows computer

Open in new window


What am I doing wrong ?

Any help appreciated.


Avatar of arnold
arnold
Flag of United States of America image

First thing to make sure is that the SQL instance on the wondows 10 is setup with tcp/ip
SQL server surface.... config


netstat -an | findstr /I listen

Then confirm the port 1433 is allowed through the windows firewall. Inbound

See if you can telnet on port 1433 from ubuntu to win10.

How did you configure ms SQL login mixed mode might be required.
Confirm the scope for your inbound rules matches the active connection.

In AWS you may have

Do you have a VPN from home to the AWS instance?
On the hone network firewall do you have a port forward that limits access to the AWS IP?
Avatar of waynezhu
waynezhu

To troubleshoot connection
1) on your windows server, open CMD terminal, and run
   sqlcmd -S xxx.xxx.x.xxx,1433 -U myuser -P mypwd
2) if above command successful, that means your SQL is fine
3) at AWS ubuntu terminal, run
    telnet xxx.xxx.x.xxx 1433
4) if above not successful, that is FW issue.
Avatar of Marthaj

ASKER

thank you for responding.
The MS SQL server is mixed authentication.
No vpn yet.
I have no port forwarding.
The sqlcmd does not work from the AWS instance. From any of my home computer - both Ubuntu and Window based, the sqlcmd works fine.
But that is not what I need to resolve.
What do you mean, how do you expect it to connect back to your system?

are you using SSH to connect to your aws instance?
Are you familiar with the Remot SSH TUNNEL?

i.e.

ssh -R 1433:localhost:1433 username@awsinstance


now when you use sqlcmd use localhost:1433 to connect to and you should land on your windows 10 SQL server.

note the AWS local port 1433 is defined in the first part.
The localhost refers to localhost on the windows 10 system along with the port 1433.



https://docs.ssh.com/manuals/server-admin/64/server-tunnel-remote.html
Avatar of Marthaj

ASKER

Thank you for responding.
Yes, I am very familiar with ssh.
In my home network, I ssh from one computer to another. My home network has 1 Ubuntu computer and 2 windows 10 computers.
I normally always ssh onto the Aws EC2 instance - usually always from my Ubuntu home computer.
From the AWS EC2 instance - I am not able to ssh into any the home computers.
I am going to try ssh with -R.



it is a pain but you could have your ubuntu server handle the routing using combinations of local and remote tunnels between computers.


https://learn.microsoft.com/en-us/windows-server/administration/openssh/openssh_install_firstuse

from your ubuntu home server with IP 192.168.0.15 as an example and the windows 10 system having the IP 192.168.0.25

ssh -R 1433:192.168.0.25:1433 username@awsinstance

this should allow your SQLCMD on the AWS ubuntu instance to connect to localhost:1433 and reach the SQL server instance on your home computer having the IP 192.168.0.25 port 1433.
Avatar of Marthaj

ASKER

Thank you for responding.
arnold - I can ssh into my Ubuntu server just fine from the windows computer. I tried your cmd and and received "Permission denied (publickey)"
 - which I understand. I have the pem file accessible on my windows computer and it can ssh into the Ubuntu server. But I need the Ubuntu server to connect to the MS SQL Server on the windows computer. I have a php application that needs to connect - which does not because of the connection string. I decided to debug the problem by using sqlcmd to see if I could connection, hence either validating or not my connection string.
Looking into the link you provided. But I am thinking maybe need to configure the sshd_config on the Windows computer file to allow the ubuntu server. ?


waynezhu - I tried telnet on the AWS Ubuntu server and it times out.

I am not sure why you are going from your windows computer to your ubuntu computer.


To be clear your setup
AWS Instance hosting Ubuntu

Your HOme network
Windows 10
Ubuntu system

to facilitate access from your AWS Ubuntu Server to your windows 10 system,

my suggestion to use the Remote Tunnel.
Home UBUNTU ssh -R 1433:homewindows10IP:1433 username@AWSUbuntuinstance

this will establish a Connection from Home Ubuntu to the AWS Ubuntu while creating a Remote Tunnel on port 1433 on the AWS Ubuntu that will pass to the windows 10 1433(MSSQL) port.

If you are sshing from the windows 10, why not go directly to the AWS Ubuntu instance...
Avatar of Marthaj

ASKER

Thank you for responding.

The AWS is a Ubuntu 20.04 EC2 instance.

My home computer is Windows 10 with MS SQl Server.

I can, from the Windows 10 computer, ssh into the AWS Ubuntu server just fine.

But I need to go the opposite way - the AWS Ubuntu server to the Windows  MS SQL Database.

If I can not connect from the AWS Ubuntu server from the command line to the Windows 10 MS SQL database via the sqlcmd from the AWS Ubuntu server,  php application certainly is not going to connect.
And it doesn't.

Using the sqlcmd line from the AWS Ubuntu Server is way to test my connection string for the application. I have verified that the  Microsoft ODBC Driver 17 for SQL Server are installed correctly on the AWS Ubuntu server. I have verified that sql cmd line is installed correctly too.

The error - which as I understand it, a generic error message.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Open in new window


I am puzzled on whether I am not cleary pointing out a solution to your predicament.

one option it to setup a service/startup script that when the computer starts/user logs in, it open and establishes an SSH connection to your ubuntu server with a remote tunnel that provides the remote Ubuntu an ability to connect to the local mssql.


To have the ability from Ubuntu to connect to your windows 10 system, install the openssh server for windows 10 and configure it to start at bootup.
Configure the windows firewall and your external firewall to allow connections from your AWS ubuntu server to your windows 10 ssh server.
On the ubuntu server, you would need to use a local tunnel to the connection
ssh -P windows_10_wan_port_for_ssh -L 1433:localhost:1433 username@mywindows10_PublicIP

when either tunnel, remote Connection iniitated from the windows 10
or local AWS ubuntu initiates a connection to the home system,

access to 1433 will end up on the windows 10 MSSQL server.


Your data access is from the Ubuntu to the windows 10 MSSQL.

The establishment of the tunnel through which the request were to go, are another story.

If not mistaken, AWS provides for a way to setup VPN

Does your home router/firewall capable of handling VPN connections?

What is the Sqlcmd timeout window?

Are you using an SSH tunnel with remote port to then use sqlcmd to access the localhost:1433 inorder to get to the windows 10 mssql instance?

is the MSSQL using default instance or named?
telnet failing means it is FW/network issue from your AWS Ubuntu to your home Windows SQL Server.
I'd suggest the following:
Let's assume,
   your home network gateway address is yyy.yyy.yyy.yyy
  and
  your window machine address is zzz.zzz.zzz.zzz
Logon your router admin page, and add a port forwarding entry, for example,
   from yyy.yyy.yyy.yyy port 1433 to zzz.zzz.zzz.zzz port 1433
At AWS side: modify your FW rules as
Inbound:  IPv4   MSSQL   TCP   1433   yyy.yyy.yyy.yyy/32   - the tcp/ip  (gateway address)
Outbound: IPv4   MSSQL   TCP   1433 yyy.yyy.yyy.yyy/32   - the tcp/ip  (gateway address)

Now you can test
On AWS Ubuntu, run
     sqlcmd -S yyy.yyy.yyy.yyy -U myuser -P mypwd

The source port will likely not be 1433.

But the guidance to restrict the port forward to the WAN ip of AWS ubuntu is right.

See if you have lynx on the ubuntu, install ifmissing, the go lynx http://whatismyip.com.

ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Marthaj

ASKER

Thank you for responding.
Arnold - Yes, I have ssms installed on the windows computer. On the cmd you provided I don't understand what to use for 1234 values in front of each localhost section.
I looked in the current SQL Server log and filtered for listening on:
This is what is displayed:
Server is listening on [1276.0.0.1 <ipv4> 1434] accept sockets 1
Server is listening on [::1 <ipv6> 1434] accept sockets 1

Open in new window

Why is port 1433 not listening ?
Seems to me this is at least part of the problem.
After enable port forwarding, on your home network, run
      sqlcmd -S yyy.yyy.yyy.yyy -U myuser -P mypwd
Can you connect?
Please use the numbers as referenced.

The test in the windows to connect to the local.tunnel which will return the connection over the remote tunnel.to the local sql.server.

I.e. scratchthe left her using your right hand.

Just to confirm remote tunnel.functionality.

The. You can look at the SQLcmd and whether you are missing a detail on the connection.

The use of localhist bypasses the windows defender.

By establishing the connectio. As written here is what happens
The AWS ubuntu has a remote tunnel.on port 9876 that comes back to the windows SQL server.

On the windows system the local.port 1234 is the entry to the tunnel that connects to the AWS port 9876.

Does your windows SQL require encrypted communication?



Avatar of Marthaj

ASKER

Arnold - Thank you!!! It worked!! I copied the key pem file to my ssh folder, added the pem file,
i.e. -i  path/to/pem/file - to your command and it connected to my aws ec2 instance. From the aws server cmd line to windows 10 ms sql server , the sqlcmd still fails but atleast I have a connection.

Is this a permanent tunnel until removed ??  Need to do more reading on tunneling.
Next step is to figure out what my connection string should be in my php apps to connect to the ms sql server. I am thinking that the port, 9876, would be used instead of the default port of 1433.
As long as the SSH connection from the windows 10 is there the tunnel is active though you have the constraint of firewalls that would often terminate idle connections.

SSh tunnels rely on the remote side allowing tunneling within the SSHD configuration.
The port to which your PHP running on AWS ubuntu will connect is the Local port on the remote Tunnel, in the example
from the windows 10
ssh -R 1234:localhost:1433 user@awsinstance_ubuntu

PHP would be connecting to localhost:1234

9876 is a local port on the windows 10 for the tunnel to the AWS ubuntu. -L9876:localhost:1234 (unnecessary beyond the Tunnel loop test)
Avatar of Marthaj

ASKER

Thank you all for responding and being so patience.