Category Archives: MS Windows
Notes about various technical issues of supporting and administering Windows Servers.
Despite of the Linux (Mint & Fedora) dominance on my desktops and laptops, I nevertheless need at least one Windows workstation at work. Recently, I started to move part of my Windows (only) application stack from Windows 7 Enterprise to Windows 10 Enterprise. One thing that bothers me from day one on Windows 10 is automatic updating and rebooting. I’m fine with the automatic update part, but certainly don’t like auto-reboot part, my main desktop simply must run non-stop, usually for several months in a row. Here is a mini “how-to” that I used to change default behavior:
Open Local Security Policy Editor
Make a search for “Edit group policy”, then open “Computer Configuration” -> “Administrative Templates” -> “Windows Components” -> “Windows Update”
Double clikc on “Configure Automatic Updates”….
…and click on “Enabled” and select the way you want updates to be downloaded and installed. Here is my choice…
After reboot, you can check Windows Update Settings, where you should see warning that “Some settings are managed by your organization”….
Another policy option that you probably want to enable is “No auto-restart with logged on users…”:
Around ~2001/2002 we found a bug in Microsoft MS Access in a combination with Oracle ODBC.
Bug is finally fixed in MS Access 2016.
Imagine this Oracle table:
create table salary (name varchar2(10), salary number(7,2), salary2 number); insert into salary values ('King',12345.55,12345.55); 1 row created. commit; Commit complete. select * from salary; NAME SALARY SALARY2 ---------- ---------- ---------- King 12345,55 12345,55
Note that salary table has salaries stored in NUMBER(n,m) and plain NUMBER data types.
Let’s see what we get if we use MS Access 2010 with Microsoft ODBC driver for Oracle to link the salary table:
Everything is OK. Now, let’s try to link the same table, but this time with Oracle ODBC driver:
Oooppsss. MS Access converted decimal number 12345,55 to an integer 1234555. Someone would argue that this can also be a bug in Oracle ODBC. But it isn’t, because Excel (or any other ODBC app) has no problem handling decimal data over Oracle ODBC driver. This bug persisted in all recent releases MS Office 2000, 2002, 2003, 2007, 2010 and 2013. Using Oracle ODBC with any of those MS Access versions was a big NO for us.
The problem is that the only workaround was to link tables using Microsoft ODBC driver for Oracle, which is 32-bit only, deprecated by Microsoft and not enhanced in at least a decade. You can imagine my surprise after I installed 64-bit version of MS Office 2016 and trying to link to Oracle table with Oracle12c ODBC driver (also tested with older 11g driver) and realized that Microsoft finally nailed the bug after 16+ years.
Oh, and if you wonder if I submitted bug report to Microsoft and Oracle sixteen years ago? Yes, I did. Microsoft product manager simply redirected me to Oracle Support and Oracle support told me that they think that the problem are undocumented hooks in MS Access, hence, MS ODBC driver for Oracle somehow works and driver written by Oracle according to official ODBC specs doesn’t. Oracle suspicion was correct anyway, because the bug was always limited to MS Access.
It took me awhile to realize that my SSHD installation on my Windows 2012R2 was not ready yet to accept public key authentication, which is a key feature. If we want to automate some tasks we need passwordless authentication to our Windows 2012 R2 server.
By default sshd is running under Local System account and this is in conflict with implementation of public key authentication of OpenSSH in Cygwin. If you recollect from my last post, special account was created, called cyg_server:
*** Info: On Windows Server 2003, Windows Vista, and above, the *** Info: SYSTEM account cannot setuid to other users -- a capability *** Info: sshd requires. You need to have or to create a privileged *** Info: account. This script will help you do so. *** Info: It's not possible to use the LocalSystem account for services *** Info: that can change the user id without an explicit password *** Info: (such as passwordless logins [e.g. public key authentication] *** Info: via sshd) when having to create the user token from scratch. *** Info: For more information on this requirement, see *** Info: https://cygwin.com/cygwin-ug-net/ntsec.html#ntsec-nopasswd1 *** Info: If you want to enable that functionality, it's required to create *** Info: a new account with special privileges (unless such an account *** Info: already exists). This account is then used to run these special *** Info: servers. *** Info: Note that creating a new user requires that the current account *** Info: have Administrator privileges itself. *** Info: No privileged account could be found. *** Info: This script plans to use 'cyg_server'. *** Info: 'cyg_server' will only be used by registered services. *** Query: Do you want to use a different name? (yes/no) no *** Query: Create new privileged user account 'ACMEHOST\cyg_server' (Cygwin name: 'cyg_server')? (yes/no) yes *** Info: Please enter a password for new user cyg_server. Please be sure *** Info: that this password matches the password rules given on your system. *** Info: Entering no password will exit the configuration. *** Query: Please enter the password: mysecret *** Query: Reenter: *** Info: User 'cyg_server' has been created with password 'mysecret'.
The important info is this:
*** Info: It’s not possible to use the LocalSystem account for services
*** Info: that can change the user id without an explicit password
*** Info: (such as passwordless logins [e.g. public key authentication]
*** Info: via sshd) when having to create the user token from scratch.
So, the first thing that we need to do is to stop “CYGWIN sshd” service and change logon account for the sshd service as shown on the following screenshots:
At this point you can not start the service yet, if you try you’ll find in /var/log/sshd.log the following error:
/var/empty must be owned by root and not group or world-writable.
What we need to do is to change owner of the directory /var/empty from SYSTEM to cyg_server. Open cygwin.bat and run:
// check current permissions $ ls -al /var/empty total 0 drwx------+ 1 SYSTEM ACMEHOST+None 0 Mar 6 12:10 . drwxr-xr-x+ 1 alesk ACMEHOST+None 0 Mar 6 12:10 .. or with: $ getfacl /var/empty # file: /var/empty # owner: SYSTEM # group: ACMEHOST+None user::rwx group::--- other:--- default:user::rwx default:group::r-x default:other:r-x // So I tried to change the owner to cyg_server: $ chown cyg_server /var/empty invalid user cyg_server // What? At first I didn't know why is cyg_server invalid, but then // I realized that I added only one (my) account in /etc/passwd, so I // need to append cyg_server user as well: $ mkpasswd -l -u cyg_server >> /etc/passwd // restared cygwin.bat shell and run $ chown ACMEHOST+cyg_server /var/empty Remember that by default username is prefixed by hostname. // if you ever wish to change back to Local System account, // all that is needed is to again change Service account // under which CYGWIN sshd is running and owner of /var/empty: $ chown SYSTEM /var/empty
At this point you should be able to start “CYGWIN sshd” service under local cyg_server account. The last thing you need to do is to test the connection with public key authentication. I used Mobaxterm for that on my workstation ACMEWKS, but you could as easily use Cygwin SSH on your workstation:
In MobaXterm window:
We need to generate some public-private keys. Note that in our case we need PK keys for passwordless authentication, so we didn't password protect rsa private key in the example that follows. [alesk.ACMEWKS] ? ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/mobaxterm/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/mobaxterm/.ssh/id_rsa. Your public key has been saved in /home/mobaxterm/.ssh/id_rsa.pub. The key fingerprint is: 9a:da:35:e8:ca:6a:14:e4:f0:8c:10:1c:21:41:fe:81 alesk@ACMEWKS The key's randomart image is: +--[ RSA 2048]----+ |**o | |=.o | |.E . | |. * . | | o S | | . + | | . + o | | .. + . . | | ...+.o | +-----------------+ Remember, that /home/mobxterm/.ssh is volatile, after you close the MobaXterm this directory is gone, so you should copy public/private keys to some local, permanent directory.... [alesk.ACMEWKS] ? ls -al /home/mobaxterm/.ssh total 5 drwx------ 1 alesk UsersGrp 0 Mar 5 15:46 . drwx------ 1 alesk UsersGrp 0 Mar 5 11:09 .. -rw------- 1 alesk UsersGrp 1675 Mar 5 15:46 id_rsa -rw-r--r-- 1 alesk UsersGrp 396 Mar 5 15:46 id_rsa.pub -rw-r--r-- 1 alesk UsersGrp 171 Mar 5 09:11 known_hosts [alesk.ACMEWKS] ? mkdir /cygdrive/g/ssh [alesk.ACMEWKS] ? cp /home/mobaxterm/.ssh/id_rsa* /cygdrive/g/ssh // copy public key to remote host [alesk.ACMEWKS] ? scp /cygdrive/g/ssh/id_rsa.pub alesk@ACMEHOST:~/.ssh // append previoulsy copied public key to authorized_keys on remote host $ ssh alesk@ACMEHOST 'cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys'
Now, we can try to connect from ACKMEWKS to ACMEHOST with PK authentication:
[alesk.ACMEWKS] ssh -i /cygdrive/g/ssh/id_rsa alesk@ACMEHOST
You should be logged on ACMEHOST without prompt for a password. Properly protect private key. Better yet, you should limit what someone can execute execute via passwordless ssh connection with the help of prefix in authorized_keys file for each public key (see this for an example).
And finally, you can troubleshoot ssh connection by turning on verbose mode (-v, -vvv), for example:
[alesk.ACMEWKS] ssh -vvv -i /cygdrive/g/ssh/id_rsa alesk@ACMEHOST
Whenever I need some Cygwin functionality on my Windows hosts, I always prefer pre-packaged solutions (Mobaxterm for ssh client on Windows, CopSSH for SSHD, etc.). This time around I needed a free SSH server for Windows 2012R2 host without the limits imposed by recent CopSSH free version. I decided that I’ll try to use SSHD from cygwin.com directly, something that I tried to avoid in the last decade.
First you’ll need to download installer from cygwin.com, save the file in some temporary directory. I downloaded 64-bit version because I’ll be installing 64-bit packages. More precisely, I selected the following packages and their dependencies: nano, zip, unzip, OpenSSH, openssl and rlwrap.
Run the installer and follow the wizard, here are screenshots from my host:
2.1 Should we edit cygwin.bat?
You’ll probably find numerous recommendation on the net (and even in Oracle documentation — “Enterprise Manager Cloud Control Basic Installation Guide”) that first thing you should do is to open cygwin.bat and insert line set CYGWIN=binmode ntsec, so that bat would look like:
@echo off C:ssh chdir C:\cygwin\bin set CYGWIN=binmode ntsec << INSERTED LINE bash --login -i
Both parameters are obsolete in cygwin 1.7, so I believe we can safely skip this step and leave cygwin.bat in original state.
2.2 Test installation
Open C:\cygwin\cygwin.bat and run:
$ cygrunsrv -h
and you should see help for cygwrunsrv, otherwise something went wrong and you’ll likely have to re-install.
2.3 Configure SSHD service by opening cygwin.bat with “Run as Administrator” privilege:
*** Info: Generating missing SSH host keys ssh-keygen: generating new host keys: RSA1 RSA DSA ECDSA ED25519 *** Info: Creating default /etc/ssh_config file *** Info: Creating default /etc/sshd_config file *** Info: StrictModes is set to 'yes' by default. *** Info: This is the recommended setting, but it requires that the POSIX *** Info: permissions of the user's home directory, the user's .ssh *** Info: directory, and the user's ssh key files are tight so that *** Info: only the user has write permissions. *** Info: On the other hand, StrictModes don't work well with default *** Info: Windows permissions of a home directory mounted with the *** Info: 'noacl' option, and they don't work at all if the home *** Info: directory is on a FAT or FAT32 partition. *** Query: Should StrictModes be used? (yes/no) yes *** Info: Privilege separation is set to 'sandbox' by default since *** Info: OpenSSH 6.1. This is unsupported by Cygwin and has to be set *** Info: to 'yes' or 'no'. *** Info: However, using privilege separation requires a non-privileged account *** Info: called 'sshd'. *** Info: For more info on privilege separation read /usr/share/doc/openssh/README.privsep. *** Query: Should privilege separation be used? (yes/no) yes *** Info: Note that creating a new user requires that the current account have *** Info: Administrator privileges. Should this script attempt to create a *** Query: new local account 'sshd'? (yes/no) yes *** Info: Updating /etc/sshd_config file *** Query: Do you want to install sshd as a service? *** Query: (Say "no" if it is already installed as a service) (yes/no) yes *** Query: Enter the value of CYGWIN for the daemon:  binmode ntsec *** Info: On Windows Server 2003, Windows Vista, and above, the *** Info: SYSTEM account cannot setuid to other users -- a capability *** Info: sshd requires. You need to have or to create a privileged *** Info: account. This script will help you do so. *** Info: It's not possible to use the LocalSystem account for services *** Info: that can change the user id without an explicit password *** Info: (such as passwordless logins [e.g. public key authentication] *** Info: via sshd) when having to create the user token from scratch. *** Info: For more information on this requirement, see *** Info: https://cygwin.com/cygwin-ug-net/ntsec.html#ntsec-nopasswd1 *** Info: If you want to enable that functionality, it's required to create *** Info: a new account with special privileges (unless such an account *** Info: already exists). This account is then used to run these special *** Info: servers. *** Info: Note that creating a new user requires that the current account *** Info: have Administrator privileges itself. *** Info: No privileged account could be found. *** Info: This script plans to use 'cyg_server'. *** Info: 'cyg_server' will only be used by registered services. *** Query: Do you want to use a different name? (yes/no) no *** Query: Create new privileged user account 'ACMEHOST\cyg_server' (Cygwin name: 'cyg_server')? (yes/no) yes *** Info: Please enter a password for new user cyg_server. Please be sure *** Info: that this password matches the password rules given on your system. *** Info: Entering no password will exit the configuration. *** Query: Please enter the password: mysecret *** Query: Reenter: *** Info: User 'cyg_server' has been created with password 'mysecret'. *** Info: If you change the password, please remember also to change the *** Info: password for the installed services which use (or will soon use) *** Info: the 'cyg_server' account. *** Warning: Expected privileged user 'cyg_server' does not exist. *** Warning: Defaulting to 'SYSTEM' *** Info: The sshd service has been installed under the LocalSystem *** Info: account (also known as SYSTEM). To start the service now, call *** Info: `net start sshd' or `cygrunsrv -S sshd'. Otherwise, it *** Info: will start automatically after the next reboot. *** Info: Host configuration finished. Have fun!
Note the line 33:
*** Query: Enter the value of CYGWIN for the daemon:  binmode ntsec
Here you should enter any env. variables for cygwin daemon if any. I did NOT set those two variables, they’re here only as a note point. According to cygwin documentation, both parameters are obsolete since Cygwin 1.7, I believe the only reason both parameter are still listed, even in Oracle current documentation, is that someone found some old “how-to” on the net (which was valid before 1.7 release) and took it as granted.
After configuration you should check services, you should see CYGWIN sshd….
and two new local accounts, cyg_server and sshd…
2.4) Enable existing local user account to connect to SSH daemon
// take a backup if by any chance you already have a passwd file (not the case for fresh installation!)
copy C:\cygwin\etc\passwd C:\cygwin\etc\passwd.bak
$ /bin/mkpasswd -l -u alesk >> /etc/passwd
The above command will write something like this in the passwd file:
It means that you can connect to ACMEHOST from some remote workstation with:
$ ssh ACMEHOST+alesk@ACMEHOST
Not cool. I prefer using, simple:
$ ssh alesk@acmehost
so, I changed the username in /etc/passwd to:
And for a domain account you would execute:
$ /bin/mkpasswd -d -u alesk >> /etc/passwd $ mkdir -p /home/alesk $ chown alesk /home/alesk
2.5) Edit ssh config file:
or within cygwin.bat:
at the end of file add:
I’m not sure what is the true reasoning for that, but probably for hardening the sshd security!?
2.6 Start SSH daemon:
$ cyrunsrv -S sshd
or start Windows service
Open cmd.exe with “Run as admninistrator”:
cmd> net stop "CYGWIN sshd" cmd> net start "CYGWIN sshd"
If service doesn’t start check the log at C:\cygwin\var\log\sshd.log.
If you need to add some package afterwards, simply re-run setup.exe installer and check additional packages that you want to install.
Hands down, an old “GUI” version of Oracle SQL*Plus (sqlplusw.exe) which was available only on Windows, was always a bit awkward to use compared to a command line version of the same tool. Nevertheless, it was GUI version of the SQL*Plus that I was using most often for ad hoc access to database. Despite of obvious shortcomings in the area of command line history and editing, it felt familiar environment to many developers and DBA’s and now that it’s gone forever with the release of Oracle 11g, I thought I would prepare a “replacement” that will somehow visually resemble old tool but also bring some goodies not available in sqlplusw.exe.
First thing that we need to do, is to correctly setup environment. Since I’m using Windows 7 with regional settings for Slovenia and with Oracle NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250, it means that I can not simply create a shortcut to sqlplus.exe, because command line version of sqlplus.exe is not able to deal with code page CP1250. Before we run sqlplus.exe we need to change NLS settings to NLS_LANG=SLOVENIAN_SLOVENIA.EE8PC852 (or whatever MS DOS codepage variant you’re using in your country for Window command line). This “limitation” in particular was the #1 reason for sticking with sqlplusw.exe all those years and since “MS DOS” LATIN 2 codepage (CP852) is a subset of CP1250 it means that we must be aware that some characters can’t be displayed. For example euro symbol (€) is not supported by CP852, you won’t see properly € character and whenever you’ll want to insert € you must do it with Oracle function CHR(128) instead of typing € on keyboard — otherwise the character will be lost during client-server characterset translation. The simplest approach is to prepare a “special” launch script just for sqlpluse.exe, here is mine (saved in E:\Oracle):
@echo off :: :: RunSQLPlus.cmd :: title ORA11 x64 SET ORACLE_HOME=E:\ORACLE\ORA11P3 SET PATH=%ORACLE_HOME%\BIN;%PATH% SET SQLPATH=E:\ORACLE\scripts SET NLS_LANG=SLOVENIAN_SLOVENIA.EE8PC852 cmd /k sqlplus /nolog
By default we have a boring sqlplus command prompt window. Let’s change some properties, open context menu of the window, then “Properties”. Make sure both “Quick Edit Mode” and “Insert mode” are checked, also increase “Buffer Size” size from 50 to something bigger, like 200.
On the next tab select font of your choice, my favorite font is “Lucida Console”, size 16.
On the “Layout” tab increase “Screen Buffer Size” and if you don’t like 80 x 25 character sized default window, change the proportion to something else.
On the “Colors” tab pick a color of your choice, since I want to achieve the look of old GUI sqlplusw I changed background to white and treated myself with a blue color for text, something I could not do in GUI variant.
Let’s close (and save changes) and again launch sqlplus to see the changes…
This is much better. The last tidbit of tweaking our new “king” is making SQL*PLus display customized sql prompt with user and database that we’re connected to. If you recall cmd batch script from above you’ll see SQLPATH pointing to E:\ORACLE\scripts. In this directory create your customized login.sql script, like this one:
-- login.sql host title &_user@&_connect_identifier set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER~SQL> " set linesize 2000 set pagesize 500 set truncate on
This script will properly set both sqlplus prompt as well as title of the command line window to reflect user and db to which we’re connected. Even if command prompt is in minimized state on taskbar, we can easily determine to which database we’re connected by hovering mouse pointer over the icon.
And finally, I would again like to warn about properly setting up NLS_LANG environment variable (in my case to MS DOS Latin 2 codepage), otherwise you’ll end up with data corruption due to incorrect code page conversion between the client and server.
That’s pretty much all. One thing that you must be extra careful about is when you just want to use sqlplus to run some script written in Notepad/Notepad++ (written likely in cp1250 in CE region) in which case you should run sqlplus.exe with EE8MSWIN1250 settings. The instructions above are targeted to interactive ad-hoc querying.
Even if you’re a die-hard user of sqlplusw it’s just a question of time when you’ll appreciate the benefits of cmd.exe:
- Arrows Up and Down allow you to navigate between the commands in the buffer
- Arrows Left and Right allows you to navigate inside current command line
- F7 brings a menu with commands in the buffer. Select and press Enter to execute the command line selected, or Select and Right Arrow to put the selected line on the command prompt where you can edit it
- F8 allows you to search command line buffer. For example start writing your query, then press F8 and cmd will try to pull the line from the buffer.