Sql-server – Connecting to a local MS SQL Server from a GUI client

connectivitylinuxsql server

I am very new to SQL and database servers. I have recently been using SQL Server Express and accessing my database using SQL Server Management Studio on Windows. I am now trying to achieve a similar setup on a Linux machine.

I would like to create a local SQL Server instance on the machine, and then use SQLectron as a GUI client for querying my database. I am using Manjaro Linux and so install my packages from the AUR.

So far I have installed mssql-server, mssql-tools and sqlectron-gui. I have run the sudo /opt/mssql/bin/mssql-conf setup command in order to (I believe) set up a local server. After doing so, running the command systemctl status mssql-server returns this:

[kev@XPS-Manjaro ~]$ systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2017-08-23 13:43:49 IST; 2h 42min ago
    Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 9130 (sqlservr)
    Tasks: 165
CGroup: /system.slice/mssql-server.service
        ├─9130 /opt/mssql/bin/sqlservr
        └─9144 /opt/mssql/bin/sqlservr

Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [145B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [66B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [96B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [100B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [71B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [124B blob data]
Aug 23 13:49:03 XPS-Manjaro sqlservr[9130]: [156B blob data]
Aug 23 13:49:03 XPS-Manjaro sqlservr[9130]: [194B blob data]
Aug 23 13:52:31 XPS-Manjaro sqlservr[9130]: [74B blob data]
Aug 23 13:52:31 XPS-Manjaro sqlservr[9130]: [199B blob data]

I am now attempting to create a local connection to my server through SQLectron on the following screen:

As I have very little experience with SQL Servers, I am completely lost with where to get all of these credentials to connect to my local server. The only one I know of is the password which I set in the sudo /opt/mssql/bin/mssql-conf setup command.

How do I find the server name, host address (127.0.0.1?), port, domain, Unix socket path and username?

Best Answer

If you are running the GUI on the same machine as the mssql-server service, then you can use localhost for the address (127.0.0.1). If you are running the GUI from a different workstation, you'll need the IPv4 address of the machine where the mssql-server service is running. You can obtain that by running ifconfig from a Linux terminal prompt. Sample output from ifconfig:

eth0: flags=4163  mtu 1500
        ether 00:15:5d:89:45:01  txqueuelen 1000  (Ethernet)
        RX packets 423  bytes 137827 (134.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth1: flags=4163  mtu 1500
        inet 192.168.200.11  netmask 255.255.255.0  broadcast 192.168.200.255
        inet6 fe80::2f70:9d15:8e7d:16cb  prefixlen 64  scopeid 0x20
        ether 00:15:5d:89:45:04  txqueuelen 1000  (Ethernet)
        RX packets 20138  bytes 2006000 (1.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 19756  bytes 30125657 (28.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1  (Local Loopback)
        RX packets 3239  bytes 361340 (352.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3239  bytes 361340 (352.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

In the output above, the bit that is important is the "inet" address, which in my case is 192.168.200.11.

The default port for SQL Server is 1433 - unless you chose a different port in mssql-conf use that port number. Ensure the firewall on the Linux box is allowing outside connections via 1433, if you intend to connect to SQL Server via the network.

Use sa as the login, and the password you specified during SQL Server setup via the sudo /opt/mssql/bin/mssql-conf setup command.

I would leave the domain and unix socket path blank.

Once you have connected to the instance, you may want to configure a non-sa account. Do that with the CREATE LOGIN statement.

Just an FYI, you can use SQL Server Management Studio to connect to SQL Server on Linux, if that's your desire. Alternately, you can download Microsoft's native GUI client for Linux (and Windows & Mac), Azure Data Studio, here.

Related Solutions

Sql-server – Local SSMS client is not connecting one particular server

Due to your comments on the main answer, I would write my suggestions below. You described that your neighbor can still connect to the machine but you not. This doesn't seem like a normal behavior of an SQL Server. This just brings me to another conclusion. It seems to be more likely a network error. In fact, you also described that you could connect to the server too, just before an hour.

I would suggest the following solution approaches:

Check if your neighbor still can connect to the server.
Check if you can connect to all other systems your normally used to connect with.
Logon on the PC of your neighbor, try to connect to Server X. If this will work, it's nothing with your permissions or with your user.
Due to the fact, that it seems to be network related, I would suggest to check the network in your company. Check at your neighbor's PC which IP is assigned to Server X. You can do this by using the cmd and typing nslookup serverx. Compare it with the IP which was provided after you tried to ping serverx. Do they match? If not, you probably have a DNS error. Which means that Server X has another IP, but your PC hasn't recognized it in his ARP. You may want to flush your your dns-cache. You can do this using ipconfig /flushdns on the command line.
If this 4 doesn't apply or help, you can try an tracert to server x from your neighbor using tracert serverx. Check which hops occur. Try the same on your machine. Do they match or is there any difference? If you found a timeout on a special ip, you can try to ping the corresponding ip from your pc and from the pc of your neighbor. If you can't reach the ip, but your neighbor can - it looks like a port error on a specific switch between you and your neighbor. By the way, the whole story at all seems to be a defect port in my opinion. Just due to the fact that this just occurs from one minute to the next is kind of specific for an port error. Even the fact that just you cannot connect to the server. You should try this!
If all of the above doesn't help, you still check the firewalls between you and server x. Some companies use smart switches or IDS application (Intrusion detection system) which will detect misbehavior and close specific ports. If your company use such a system and it's not proper configured it may cause such a port close. I've had a similar solution in my old company in the first days of our IDS. You can also try to connect to server x from your neighbors PC using RDP and try to ping your PC from the side of the Server.

If nothing else helps, you need to contact your system administrator (if your not the one :-D) and try to figure out which cable or port is broken.

I really don't think that this is an SQL Server specific error. I would guess that this is some hardware or maybe software error in your network/pc.

Sql-server – Can not connect to SQL Server from Ubuntu sqlcmd client

I think the right command should be:

sqlcmd -S <IP>\\SQLEXPRESS,1433 -U SA

Best Answer

Related Solutions

Sql-server – Local SSMS client is not connecting one particular server

Sql-server – Can not connect to SQL Server from Ubuntu sqlcmd client

Related Question