11

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:

enter image description here

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?

  • I've only worked with SQL Server on Windows, but if some terms would help: Name would be the name of the server (and the instance, if needed); from what you say, address as 127.0.0.1` should work; port should have been set when you set up the server (default is usually 1433); user would be the username you want to log into (probably could be a Linux user account, or a SQL Server account (like sa), and password is the password for that account. Database and Schema would be the default database and schema you would connect to; try master and blank, or master and dbo. – RDFozz Aug 23 '17 at 16:44
7

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.

  • With regards to your FYI, how can I get the SSMS GUI on Linux? I have installed both mssql-server and mssql-tools - neither have which seem to have any sort of GUI application or anything installed with regards to SSMS. Since I am most familiar with SSMS, I would prefer to continue using it, but didn't think it was available on Linux. – KOB Aug 23 '17 at 15:50
  • oh, no, I meant you could run SSMS on a Windows workstation, and connect that to SQL Server on Linux. – Max Vernon Aug 23 '17 at 15:53
  • Oh ok, I thought so. No, I am just experimenting and would just like to have everything running from my Linux machine. – KOB Aug 23 '17 at 15:54
  • One last follow on: If I now wanted to access my database from within a Python script, would I just need to configure my non-sa account as you mentioned, and then install some driver? I used the ODBC Driver when working on Windows. In my Pyton script on Windows, I got my server name for the connection engine from SSMS which was in the form of CPX-9GL9XXXXXXX\SQLEXPRESS. How can I find this equivalent server name without having access to SSMS? – KOB Aug 23 '17 at 15:58
  • SQL Server Browser is used to locate SQL Server instances without knowing the port number in advance... so for CPX-9GL9XXXXXXX\SQLEXPRESS -> SQLEXPRESS is the instance name, which would be translated by SQL Server Browser service into a port number. Unfortunately, SQL Server Browser is not yet supported on Linux, so you need to know the port number, which is 1433 by default. – Max Vernon Aug 23 '17 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.