I would never ever recommend this, but…
isqlw connecting to a Linux SQL Server
I didn’t do anything to set this up. I just searched for ISQLW and for some reason this ancient one showed up in the search path, and it connected. I didn’t notice it at first until it didn’t like the newer shift insert/delete operations, as back then you needed to use Control C/V ..Â
Not being able to stop there, I fired up the admin tool. It complains that the stored procedure sp_MSAdmin_version is missing. However you can go ahead and create it…
create procedure sp_MSAdmin_version as
select “Microsoft SQL Administrator script version 4.20.22.1”
go
And it’ll connect.
Oddly enough things that talk to the server work okay. Things related to the databases don’t work at all.
I even can admin users from 4.21’s admin program.
I guess the sp_MSAdmin_* scripts could be fixed up for 2017, allowing for a more robust experience, but I really can’t think of any reason why to do it. I’m more surprised that all the new ODBC drivers since Vista won’t talk to SQL Server 4.21, 6.0, & 7.0, but it seems the client tools can talk to the new server.
I’ve even created the infamous ‘PUBS’ database from the 4.21a script as well. Again not very useful, but all the more fun!
Installation wasn’t too hard, but a little weird to re-produce. Anyways you’ll need to trust the MS key
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
And then I added this into the /etc/apt/sources.list:
deb [arch=amd64] https://packages.microsoft.com/debian/9/prod stretch main deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial main
And then run the following to download MSSQL & the needed bits. It’ll prompt a few times to agree to the License:
apt-get update;apt-get upgrade apt-get install apt-transport-https ACCEPT_EULA=Y apt-get install mssql-tools mssql-server && /opt/mssql/bin/mssql-conf setup
And if everything goes correctly you will then be prompted for the edition to use, the SA password, and then you can start the server with:
systemctl restart mssql-server.service
And away you go.
My output was like this:
# cat /etc/issue Debian GNU/Linux 9 \n \l root@Junk:/# apt-get update;apt-get upgrade Hit:1 http://security.debian.org stretch/updates InRelease Ign:2 http://debian.uchicago.edu/debian stretch InRelease Hit:3 http://debian.uchicago.edu/debian stretch Release Hit:4 https://dl.yarnpkg.com/debian stable InRelease Hit:5 http://ftp.debian.org/debian stretch-backports InRelease Hit:7 https://deb.nodesource.com/node_8.x stretch InRelease Hit:8 https://packages.microsoft.com/debian/9/prod stretch InRelease Hit:9 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial InRelease Reading package lists... Done Reading package lists... Done Building dependency tree Reading state information... Done Calculating upgrade... Done 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. root@Junk:/# apt-get install mssql-tools mssql-server Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: libc++1 libodbc1 libsss-nss-idmap0 libunwind8 msodbcsql17 odbcinst odbcinst1debian2 unixodbc Suggested packages: clang libmyodbc odbc-postgresql tdsodbc unixodbc-bin The following NEW packages will be installed: libc++1 libodbc1 libsss-nss-idmap0 libunwind8 msodbcsql17 mssql-server mssql-tools odbcinst odbcinst1debian2 unixodbc 0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded. Need to get 0 B/181 MB of archives. After this operation, 932 MB of additional disk space will be used. Do you want to continue? [Y/n] y Preconfiguring packages ... Selecting previously unselected package libc++1:amd64. (Reading database ... 53362 files and directories currently installed.) Preparing to unpack .../0-libc++1_3.5-2_amd64.deb ... Unpacking libc++1:amd64 (3.5-2) ... Selecting previously unselected package libodbc1:amd64. Preparing to unpack .../1-libodbc1_2.3.4-1_amd64.deb ... Unpacking libodbc1:amd64 (2.3.4-1) ... Selecting previously unselected package libunwind8. Preparing to unpack .../2-libunwind8_1.1-4.1_amd64.deb ... Unpacking libunwind8 (1.1-4.1) ... Selecting previously unselected package odbcinst1debian2:amd64. Preparing to unpack .../3-odbcinst1debian2_2.3.4-1_amd64.deb ... Unpacking odbcinst1debian2:amd64 (2.3.4-1) ... Selecting previously unselected package odbcinst. Preparing to unpack .../4-odbcinst_2.3.4-1_amd64.deb ... Unpacking odbcinst (2.3.4-1) ... Selecting previously unselected package unixodbc. Preparing to unpack .../5-unixodbc_2.3.4-1_amd64.deb ... Unpacking unixodbc (2.3.4-1) ... Selecting previously unselected package libsss-nss-idmap0. Preparing to unpack .../6-libsss-nss-idmap0_1.15.0-3_amd64.deb ... Unpacking libsss-nss-idmap0 (1.15.0-3) ... Selecting previously unselected package msodbcsql17. Preparing to unpack .../7-msodbcsql17_17.2.0.1-1_amd64.deb ... Unpacking msodbcsql17 (17.2.0.1-1) ... Selecting previously unselected package mssql-server. Preparing to unpack .../8-mssql-server_14.0.3037.1-2_amd64.deb ... Unpacking mssql-server (14.0.3037.1-2) ... Selecting previously unselected package mssql-tools. Preparing to unpack .../9-mssql-tools_17.2.0.1-1_amd64.deb ... Unpacking mssql-tools (17.2.0.1-1) ... Setting up libsss-nss-idmap0 (1.15.0-3) ... Setting up libodbc1:amd64 (2.3.4-1) ... Setting up libunwind8 (1.1-4.1) ... Processing triggers for libc-bin (2.24-11+deb9u3) ... Processing triggers for man-db (2.7.6.1-2) ... Setting up libc++1:amd64 (3.5-2) ... Setting up mssql-server (14.0.3037.1-2) ... Setting up odbcinst1debian2:amd64 (2.3.4-1) ... Setting up odbcinst (2.3.4-1) ... Setting up unixodbc (2.3.4-1) ... Setting up msodbcsql17 (17.2.0.1-1) ... Setting up mssql-tools (17.2.0.1-1) ... Processing triggers for libc-bin (2.24-11+deb9u3) ... root@Junk:/# /opt/mssql/bin/mssql-conf setup Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) 7) Enterprise Core (PAID) 8) I bought a license through a retail sales channel and have a product key to enter. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. Enter your edition(1-8): 2 The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from: https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409 The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409 Do you accept the license terms? [Yes/No]:yes Enter the SQL Server system administrator password: Confirm the SQL Server system administrator password: Configuring SQL Server... ForceFlush is enabled for this instance. ForceFlush feature is enabled for log durability. Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /lib/systemd/system/mssql-server.service.
Additionally you may not want to listen on every single IP address, but rather only on the loopback. So you would run this to configure the listening address:
/opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1
I also use the SQL Agent, to enable that just simply run this:
/opt/mssql/bin/mssql-conf set sqlagent.enabled true systemctl restart mssql-server
Many more settings for the /var/opt/mssql/mssql.conf file can be found here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-2017. I would take a look at them, and possible enable stuff like TLS so that someone with management tools circa 1993 can’t just login to your server. Then again maybe that is the kind of thing you want.
And if you don’t want Microsoft SQL Server, just do the following to uninstall MSSQL, destroying all data as well.
apt-get purge mssql-tools mssql-server msodbcsql17 apt-get auto-remove rm -rf /var/opt/mssql
I kept on getting this error which I didn’t see any way to cleanly resolve to fix for running MSSQL on Debian. The best hint is the OpenSSL is either too new (unlikely) or too old (far too likely). Instead I just changed distros as that is what people do, they don’t troubleshoot problems in Linux, just change distros so why bother fighting it?
# /opt/mssql-tools/bin/sqlcmd -Usa -PMYPa55w0rd!# -S127.0.0.1 Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.OpenSSL?
Going further though, as much as I liked Debian it really does run better on Ubuntu. So as an addendum, use these sources (at the moment!). Since the SQL Agent wouldn’t run, and I couldn’t connect locally it was worse than useless.
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04//prod xenial main deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial main
Now the first time I tried to do anything on Ubuntu I got this lovely error:
# /opt/mssql-tools/bin/sqlcmd terminate called after throwing an instance of 'std::runtime_error' what(): locale::facet::_S_create_c_locale name not valid
And it just hung the process. I had to control-Z & kill -9 %1 it to get it out of the way. Well it turns out that this VM didn’t have it’s locale set. Fixing that was pretty simple, once you know how:
apt-get install locales && dpkg-reconfigure locales
Another thing that really bugs me is the lack of cryptography by default. So I found this nice recipie to for setting it up quickly. Just watch your hostname!
systemctl stop mssql-server cat /var/opt/mssql/mssql.conf mkdir /var/opt/mssql/ssl mkdir /var/opt/mssql/ssl/certs/ mkdir /var/opt/mssql/ssl/private/ cd /var/opt/mssql/ chown -R mssql:mssql * cd openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=HOSTNAME' -keyout mssql.key -out mssql.pem -days 3650 chown mssql:mssql mssql.pem mssql.key chmod 600 mssql.pem mssql.key mv mssql.pem /var/opt/mssql/ssl/certs/ mv mssql.key /var/opt/mssql/ssl/private/ /opt/mssql/bin/mssql-conf set network.tlscert /var/opt/mssql/ssl/certs/mssql.pem /opt/mssql/bin/mssql-conf set network.tlskey /var/opt/mssql/ssl/private/mssql.key
This will build out a self signed certificate for 10 years and put them into the local MSSQL directory where it can read them.