Initial impressions of SQL Server v.Next Public Preview

Microsoft announced its SQL Server on Linux public preview yesterday. I’m really excited to check it out. Here are some interesting things I found during my testing. I’ll write more as I play with it further.

  • If you want to play it on Ubuntu, it needs to be 16.04 or above. I didn’t pay attention to that initially, and started installing on Ubuntu 14. Below is a typical message you would get:

    The following packages have unmet dependencies:
     mssql-server : Depends: openssl (>= 1.0.2) but 1.0.1f-1ubuntu2.21 is to be installed
    E: Unable to correct problems, you have held broken packages.
    

    Running sudo apt-get dist-upgrade brought my Ubuntu to 16.04. The install was smooth afterwards.

  • Instruction for Red Hat Enterprise Linux also works for Fedora. I tested it on Fedora 23. I think it should also work on CentOS, although I didn’t test it myself.
  • The machine needs to have at least 3.25 GB of memory. On Ubuntu, install won’t continue if that condition is not satisfied:
    Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
    ERROR: This machine must have at least 3.25 gigabytes of memory to install Microsoft(R) SQL Server(R).
    dpkg: error processing archive /var/cache/apt/archives/mssql-server_14.0.1.246-6_amd64.deb (--unpack):
     subprocess new pre-installation script returned error exit status 1
    Processing triggers for libc-bin (2.21-0ubuntu4.3) ...
    Errors were encountered while processing:
     /var/cache/apt/archives/mssql-server_14.0.1.246-6_amd64.deb
    E: Sub-process /usr/bin/dpkg returned an error code (1)
    

    On Fedora, installation finishes, but you won’t be able to start the service:

    [hji@localhost ~]$ sudo /opt/mssql/bin/sqlservr-setup 
    Microsoft(R) SQL Server(R) Setup
    
    You can abort setup at anytime by pressing Ctrl-C. Start this program
    with the --help option for information about running it in unattended
    mode.
    
    The license terms for this product can be downloaded from
    http://go.microsoft.com/fwlink/?LinkId=746388 and found
    in /usr/share/doc/mssql-server/LICENSE.TXT.
    
    Do you accept the license terms? If so, please type "YES": YES
    
    Please enter a password for the system administrator (SA) account: 
    Please confirm the password for the system administrator (SA) account: 
    
    Setting system administrator (SA) account password...
    sqlservr: This program requires a machine with at least 3250 megabytes of memory.
    Microsoft(R) SQL Server(R) setup failed with error code 1. 
    Please check the setup log in /var/opt/mssql/log/setup-20161117-122619.log
    for more information.
    
  • Some simple testing :) From the output below, we learn that: 1)in sys.sysfiles, full file name is presented like “C:\var\opt\mssql\data\TestDb.mdf”; 2) Database name, at least inside sqlcmd, is not case-sensitive. By the way, login is also case-insensitive: SA is sA.
    1> create database TestDb;
    2> go
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total       447  avg   447.0 (2.2 xacts per sec.)
    1> use testdb;
    2> go
    Changed database context to 'TestDb'.
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total         3  avg   3.0 (333.3 xacts per sec.)
    1> select filename from sys.sysfiles
    2> go
    filename                                                                                                                                                                                                                                                            
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    C:\var\opt\mssql\data\TestDb.mdf                                                                                                                                                                                                                               
    C:\var\opt\mssql\data\TestDb_log.ldf                                                                                                                                                                                                                                
    
  • I then did a quick testing of advanced feature, like Clustered Columnstore Index (CCI). Yes, it’s available in SQL Server for Linux!
    1> create table Person (PersonID int, LastName nvarchar(255), FirstName nvarchar(255))
    2> go
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total        28  avg   28.0 (35.7 xacts per sec.)
    1> create clustered columnstore index Person_CCI on Person;
    2> go
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total        25  avg   25.0 (40.0 xacts per sec.)
    1> 
    
    Network packet size (bytes): 4096
    1 xact[s]:
    Clock Time (ms.): total         1  avg   1.0 (1000.0 xacts per sec.)
    
    

Overall, it looks pretty nice! I’ve got to say, I’m really impressed with Microsoft’s embrace of Linux. By the way, if you use Windows 10, I recommend Bash on Ubuntu on Windows. It’s in beta, but it works for me pretty well so far.

Stay tuned for more. I’ll definitely write more as I play with this new toy!

anacron and defunct logrotate configuration file

I’ve been hacking Linux for years, and today is the first time that I came across anacron. So one lives and learns…

One of my fellow OCEF volunteers keeps receiving the following email message, even after he removed fail2ban:

/etc/cron.daily/logrotate:
logrotate_script: 2: logrotate_script: fail2ban-client: not found
error: error running non-shared postrotate script for /var/log/fail2ban.log of '/var/log/fail2ban.log '
run-parts: /etc/cron.daily/logrotate exited with return code 1

It turned out this server’s log files rotation is handled by anacron job. Even though fail2ban service has been removed, there is still a configuration file, fail2ban, under /etc/logrotate.d/

/var/log/fail2ban.log {
   
    weekly
    rotate 4
    compress

    delaycompress
    missingok
    postrotate
	fail2ban-client set logtarget /var/log/fail2ban.log >/dev/null
    endscript

    # If fail2ban runs as non-root it still needs to have write access
    # to logfiles.
    # create 640 fail2ban adm
    create 640 root adm
}

Removing this file took care of it.

自建vpn之四:安装启动客户端

openvpn服务器和客户profile建立之后,安装设置客户端软件后就可以使用了!这篇博客介绍如何在Windows、Linux、和Mac上使用openvpn。我们假定你已经安全地把profile,即.ovpn文件转移到电脑上。

Windows

  1. 到这里下载客户端软件。一般来讲你要下载64位的。目前的文件名是openvpn-install-2.3.8-I601-x86_64.exe
  2. 下载完毕后安装。它可能会问要不要安装一个叫TAP的东西。你需要安装;
  3. 安装完毕,把那个.ovpn文件挪到C:\Program Files\OpenVPN\config之下
  4. Run OpenVPN GUI as administrator。这只是开启了程序,现在还没有连接到openvpn服务器上;
  5. 在屏幕右下角的空间里,找到OpenVPN GUI的图标,然后选择“connect/连接”;
  6. 连接成功。打开浏览器,试试访问以前不能访问的网站,看效果如何。

Windows 10注意事项
我发现一些不是通过安装而是通过升级到Windows 10的机器会碰到一些问题,我猜和IPv6兼容有关。我发现如果我用火狐浏览器,就可以避免这个问题。全新的Windows好像没这个问题。

Linux

  1. 打开命令行,安装openvpn:
    apt-get install openvpn
  2. sudo openvpn --config /EnterPathTo/xxx.ovpn
  3. 你可以看到vpn连接的信息。在使用openvpn的时候,你不能中断或关闭这个程序。你可以把这个Window缩小
  4. 连接成功。打开浏览器,试试访问以前不能访问的网站,看效果如何。
  5. 用完了openvpn,按Ctrl-C就可以终止了。

Mac
Tunnelblick是一个免费开源的openvpn客户端。我目前没有Mac机器,但这个客户端应当可以。

iPhone
在App Store里安装免费的OpenVPN Connect。之后你可以把爱疯连到iTunes上,用File Sharing,点击OpenVPN,然后把那个profile的.ovpn的文件拖进来。之后打开这个app,你可以看到一个新的profile可以import。之后就可以顺利连接。

Android
在App Store里找OpenVPN Connect后安装。然后把profile的.ovpn文件传到Android上。打开后利用Import功能,把profile引进后就可以很方便的应用。

祝玩得开心!

PS. 本系列其它文章
自建vpn之一:挑选供应商
自建vpn之二:保护你的机器
自建vpn之三:搭建openvpn service和生成客户端Profile

自建vpn之三:搭建openvpn service和生成客户端Profile

给虚机设定了基本的防护措施后,我们来安装openvpn服务器终端并生成profile文件。以下指令都是root级别。我假定你已经通过命令行连到服务器上。请根据需要在命令行前自行添加sudo或变成root。

我原计划给读者提供一步一步的说明,但那样会太繁琐。前两天注意到github上有人已经把这一切打包成一个shell脚本。我今天看了下,觉得写得很好。经过我成功测试后,推荐给你使用。并且这个脚本对Ubuntu,Debian, CentOS, RedHat啥的都管用。

  1. 请到 https://github.com/Nyr/openvpn-install 下载openvpn-install.sh脚本文件;
  2. 打开命令行,运行:
    bash /EnterRightPathHere/openvpn-install.sh
  3. 脚本程序会自动探测到你的IP网址,按回车键;
  4. 脚本程序让你选择DNS。我不建议第一个选项(Current system resolvers)。2和6均可.
  5. 脚本程序让你命名客户端名字。默认是client。我不建议你用默认。建议你根据其所用的设备和数据中心命名,如androidFrankfurt,iphoneFrankfurt,或winPcFrankfurt等等。请用英文字母来命名;
  6. 再敲一次回车,程序就开始运行。运行时间差不多三五分钟,请等待,稍安毋躁。
  7. 运行结束后,openvpn服务器已经搭建完成并开始运行。接下来你要把它产生的.ovpn文件(profile 文件),比如winPcFrankfurt.ovpn输送到你的Windows/MacBook/Linux/Android/iPhone设备上。强烈建议你用WinSCP(Windows)或scp(Linux/Mac)来输送文件,防止在传送途中被偷窥;
  8. 如你需要产生更多的.ovpn文件,只要重新运行
    bash /EnterRightPathHere/openvpn-install.sh
    并选择第一个选项即可。

祝玩得开心。下一篇,我们来谈谈怎样设立PC、Mac、和Linux客户端来使用vpn。

PS. 本系列其它文章
自建vpn之一:挑选供应商
自建vpn之二:保护你的机器
自建vpn之四:安装启动客户端

自建vpn之二:保护你的机器

选完虚拟机供应商、机器规格、操作系统啥的,机器启动后的第一件事是保护它的安全,尽可能减少被攻击面。我在这里只提供一些最基本建议,主要是在防火墙的设置方面。这里提供的一些指令假定你是用Debian 7或8。你如果用Ubuntu,这些指令基本上可以直接拿过来用。CentOS/Red Hat/Fedora方面的指令类似,请自行查询。如有需要,等我有空专门写CentOS/Red Hat/Fedora的设置。

以下指令都是root级别。我假定你已经通过命令行连到服务器上。请根据需要在命令行前自行添加sudo或变成root。

  1. 给服务器做软件更新,打补丁:
    apt-get update
    apt-get upgrade
    (yum update)
  2. 调整ssh接口端,防范ssh攻击:
    Linux服务器默认ssh接口端是22。很多网络攻击就从这个接口强攻,用程序频繁自动发起无数次的连接申请,所谓的dictionary attack和brute-force attack。把默认的接口改成其它如50683,是防止这种攻击的有效手段。
    用你熟悉的编辑器,打开/etc/ssh/sshd_config,然后查找”Port 22″,把22改成50683后保存文件。
    接下来请重新启动ssh服务:
    service ssh restart
    注意以后的远程连接你要记得加-p 50683
  3. 建防火墙
    apt-get install iptables
    apt-get install iptables-persistent (运行这个指令,系统会问你要不要把现在的设置存下来,说要)
  4. 基本的防火墙设定,IPv4
    用你熟悉的编辑器,打开/etc/iptables/rules.v4,删除里面所有的内容,然后加下面的设置:
  5. *filter
    #  Allow all loopback (lo0) traffic and drop all traffic to 127/8 that doesn't use lo0
    -A INPUT -i lo -j ACCEPT
    -A INPUT -d 127.0.0.0/8 -j REJECT
    
    #  Accept all established inbound connections
    -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
    
    #  Allow all outbound traffic - you can modify this to only allow certain traffic
    -A OUTPUT -j ACCEPT
    
    #  VPN port and forwarding.
    -A INPUT -p udp -m state --state NEW -m udp --dport 1194 -j ACCEPT
    -A FORWARD -s 10.8.0.0/24 -j ACCEPT
    -A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
    
    #  Allow SSH connections
    #
    #  The -dport number should be the same port number you set in sshd_config
    #
    -A INPUT -p tcp -m state --state NEW --dport 50683 -j ACCEPT
    
    #  Allow ping
    -A INPUT -p icmp -j ACCEPT
    
    #  Log iptables denied calls
    -A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7
    
    #  Drop all other inbound - default deny unless explicitly allowed policy
    -A INPUT -j DROP
    -A FORWARD -j DROP
    
    COMMIT
    
  6. 基本的防火墙设定,IPv6
    用你熟悉的编辑器,打开/etc/iptables/rules.v6,删除里面所有的内容,然后加下面的设置:
  7. *filter
    
    # Allow all loopback (lo0) traffic and reject traffic
    # to localhost that does not originate from lo0.
    -A INPUT -i lo -j ACCEPT
    -A INPUT ! -i lo -s ::1/128 -j REJECT
    
    # Allow ICMP
    -A INPUT  -p icmpv6 -j ACCEPT
    
    #  Allow SSH connections
    #
    #  The -dport number should be the same port number you set in sshd_config
    #
    -A INPUT -p tcp -m state --state NEW --dport 50683 -j ACCEPT
    
    # Accept inbound traffic from established connections.
    -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
    
    # Log what was incoming but denied (optional but useful).
    -A INPUT -m limit --limit 5/min -j LOG --log-prefix "ip6tables_INPUT_denied: " --log-level 7
    
    # Reject all other inbound.
    -A INPUT -j REJECT
    
    # Log any traffic which was sent to you
    # for forwarding (optional but useful).
    -A FORWARD -m limit --limit 5/min -j LOG --log-prefix "ip6tables_FORWARD_denied: " --log-level 7
    
    # Reject all traffic forwarding.
    -A FORWARD -j REJECT
    
    COMMIT
    
    
  8. 激活防火墙
    iptables-restore < /etc/iptables/rules.v4
    ip6tables-restore < /etc/iptables/rules.v6
    

以上是一些很基本的保护措施。让服务器更安全,建议你以后安装Fail2Ban,使用ssh key认证,而不是简单的用户名密码认证,等等等等。接下来,我们就可以设置vpn服务器了。

PS. 本系列其它文章
自建vpn之一:挑选供应商
自建vpn之三:搭建openvpn service和生成客户端Profile
自建vpn之四:安装启动客户端