SQL Server best practice: grant permissions to per-service SID

Since Windows Server 2008/Windows Vista, from SQL Server 2008 onward, SQL Server installation process automatically generates per-service security identifier (SID). Whenever possible, it is recommended to grant rights to this service SID for security reasons, instead of your SQL Server’s startup account, which typically is domain user account.

For example, for performance reasons, I always want to SQL Server to have the following rights: Instant File Initialization and Lock Pages in Memory. The former enables instantaneous data (not log) file growth; whereas the later prevents Windows system from paging SQL Server data to virtual memory on disk.

Those rights can be granted via the Local Security Policy application, secpol.msc. Navigate to Security Settings -> Local Policies -> User Rights Assignment, you’ll find them there. Please note that Instant File Initialization is actually called “Perform volume maintenance tasks”.

Before service SID was introduced, I always granted those rights to SQL Server’s startup account. In my case it was typically a domain\user account. With the introduction of service SID, SQL Server’s resource access rights is the sum of both its startup account and service SID. Therefore it is recommended to grant rights to service SID, for obvious security reasons.

To prove that’s the case, let’s conduct the following experiment. For default instance of SQL Server, its service SID is NT Service\MSSQLSERVER. For named instance, its service SID is NT Service\MSSQL$InstanceName. Please note instant file initialization, once enabled, only works for SQL Server data files, not logs.

1. Assume your SQL Server instance is running under a domain\user account without “Perform volume maintenance tasks”;
2. Run the following code:

dbcc traceon(3004,3605,-1)
go
 
create database TestDb
go
 
exec sp_readerrorlog
go
 
drop database TestDb
go
 
dbcc traceoff(3004,3605,-1)
go

Pay attention to the output of “exec sp_readerrorlog”. You should see something similar to this:

2016-05-19 23:39:35.830 spid51 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf from page 0 to 1024 (0x0 to 0x800000)
2016-05-19 23:39:35.890 spid51 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf (elapsed = 66 ms)

3. Now using secpol.msc, grant your service SID, in my case, NT Service\MSSQLSERVER, the right of “Perform volume maintenance tasks”;
4. Restart SQL Server instance;
5. Repeat step 2, you shouldn’t see entry similar to the one listed above in the error log, indicating that SQL Server has the combined rights of its startup account and its service SID.

By the way, this also applies to data and log folder permissions. Only grant data and log folder permissions to service SID, not its startup account. I have automated that process here.

Happy learning!

Accessing modem status information with Netgear router

If, like me, you bought your own cable modem and Netgear wireless router for Comcast service, the way to access your modem’s status information is different. When using the equipment provided by Comcast, in my case the Technicolor TC8305C, which is a combo of modem, router, and voice, I can easily see modem information while accessing the router page, because it is one device.

To stop paying Comcast’s 10 dollar monthly equipment leasing fee, I purchased my own cable modem and router. I am currently using Netgear Nighthawk R7000. In this combo, to see the modem status information, you need to go to:

http://192.168.100.1

By the way, I am thinking about returning the Nighthawk R7000, for the following reasons:
1. Its web interface is really slow and clunky;
2. Its range, according to this page, is not as good as ASUS;
3. More importantly, when assigning static IP address to a device, the device name cannot be more than 20 characters!

If I do return the Nighthawk R7000, I think I’ll try ASUS RT-AC68U. Do you have suggestions?

Eclipse PDT PHP Web Application Run Configuration

I had some trouble setting up Run Configurations in Eclipse for PHP (PDT plugin). Here is my note for future reference.

Machine: Ubuntu 15 64-bit, with Eclipse Mars. sudo apt-get install php5 installed apache2 for me, so no additional web server install is necessary.

  • Enable userdir mod:
    sudo a2enmod userdir
  • Your /etc/apache2/mods-enabled/userdir.conf should look like this, if not, make it so:
    <ifmodule mod_userdir.c>
            UserDir public_html
            UserDir disabled root
    
            <directory /home/*/public_html>
                    AllowOverride FileInfo AuthConfig Limit Indexes
                    Options MultiViews Indexes SymLinksIfOwnerMatch IncludesNoExec
                    <limit GET POST OPTIONS>
                            Require all granted
                    </limit>
                    <limitexcept GET POST OPTIONS>
                            Require all denied
                    </limitexcept>
            </directory>
    </ifmodule>
    
    
  • Comment out the user directories section in /etc/apache2/mods-available/php5.conf, like so:
    # Running PHP scripts in user directories is disabled by default
    # 
    # To re-enable PHP in user directories comment the following lines
    # (from <ifmodule ...> to </ifmodule>.) Do NOT set it to On as it
    # prevents .htaccess files from disabling it.
    #<ifmodule mod_userdir.c>
        #<directory /home/*/public_html>
            #php_admin_flag engine Off
        #</directory>
    #</ifmodule>
    
  • Create a public_html directory under your home directory;
  • Run sudo apt-get install php5-xdebug;
  • Run sudo apt-get install php5-mysql, as necessary;
  • Modify /etc/php5/mods-available/xdebug.ini so it has the following lines:
    zend_extension=xdebug.so
    xdebug.remote_enable=1
    
  • In Eclipse, create your PHP web application project under public_html in your home directory;
  • In Eclipse, while under PHP Perspective, click the drop down next to the green run button, and select “Run Configurations…”;
  • Create a new configuration for PHP Web Application. Pay attention to the following two things:
    a. In the Server tab, Server section, the PHP Server should be “Default PHP Web Server”, this is fine.
    eclipsePDT1
    b. Click the “Configure…” button, you’ll see this. Fill in proper “Document Root” value, in my case, /home/haidong/public_html
    eclipsePDT2
    c. Click the Debugger tab, and pick “XDebug”
    eclipsePDT3
    d. Go back to the original configuration screen, pick the proper file, and fill in th URL info, like below
    eclipsePDT1

By the way, Happy 2016 all!

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