Category Archives: MS Windows
Notes about various technical issues of supporting and administering Windows Servers.
MS Access 2016 — a bug finally fixed after 16 years – NOT! (Part 2)
Only recently, I realized that I was dead wrong (two years ago), claiming that Microsoft finally fixed a bug in Microsoft Access 2016 that caused wrong results whenever we use a combination of:
- linked tables via Oracle ODBC driver,
- Oracle table contains attribute(s) with data type NUMBER(n,m),
- and Windows client OS is properly using whatever (Eastern Europe) regional settings is valid for the country. In Slovenia we’re using comma as a decimal separator.
If above requirements are met, then MS Access (any version!) interprets the decimal number as integer (2342,45 becomes 234245). This can be a potential hefty salary raise for someone.
When I reported that Microsoft finally fixed a bug, I didn’t realize that I was working on Windows 10 VirtualBox VM, where I freshly installed Oracle Client 12c, but didn’t bother to check NLS_LANG in the registry. As it is usually the case, Oracle installer puts (incorrect) NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 in the registry, instead of correct one, NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250.
While preparing some new standard PC configuration for our current customer, I was surprised when I found out that an old bug is back. It was “back” because on this “matrix” machine Oracle client was properly setup with NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250. Since going back to 32-bit MS Office 16 and Microsoft ODBC driver for Oracle (only 32-bit version is available!) was not an option, we were left to figure out some workaround. And we did. We found out that MS Access interprets decimal data type in Oracle table correctly, if we change territory from Slovenia to America (without changing *any* other regional settings at OS level!).
Something like this:
-- open cmd, setup NLS_LANG, then run MS Access cmd> SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 cmd> "C:\Program Files\Microsoft Office\Office16\msaccess.exe"
Changing NLS_LANG variable at registry (machine) level to SLOVENIAN_AMERICA.EE8MSWIN1250 doesn’t sound as a smart move, just for the sake of fixing a bug in one faulty product, so we decided to write some simple launcher for MS Access that can fix NLS_LANG variable behind a scene, leaving NLS_LANG in registry intact.
Below is a simple Go code that’ll run MS Access after properly setting up territory part of NLS_LANG variable. Note that we deliberately used absolute paths to MS Access binary that works for our Windows 10 standard configuration, something that you might want to change if you decide to use it. You can change language and character set part of NLS_LANG variable as well.
To build executable from the code follow these steps:
- download and install Go
- save code in some file, such as G:\SRC\runAccess.go
- compile code, open cmd and execute:
cd G:\SRC & go build - ** or, you can compile the code with:
go build -ldflags -H=windowsgui
** The alternative build with the -ldflags -H=windowsgui is optional but recommended. It will cause console window to close after it launches MS Access.
// runAccess.go -- MS Access launcher
package main
import (
"fmt"
"os"
"os/exec"
"flag"
)
func main() {
var version *bool
var help *bool
version = flag.Bool("version",false,"18.02")
help = flag.Bool("help",false,"MS Access 2016 Launcher")
flag.Parse()
if *version {
fmt.Printf("February 2018 by AlesK\nVersion %s\n", (flag.Lookup("version")).Usage)
os.Exit(0)
}
if *help {
fmt.Println("I'm here to silently run this code for a user:")
fmt.Println("cmd /c SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:\\Program Files\\Microsoft Office\\Office16\\msaccess.exe")
os.Exit(0)
}
if (os.Args != nil && len(os.Args) > 1) {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe", os.Args[1])
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
} else {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe")
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
}
}
You can do two things with runAccess.exe. You can run it as a standalone program. It’ll open MS Access with territory part of NLS_LANG set to AMERICA. Or, you can associate *.accdb or *.mdb file extension with this launcher and it’ll correctly start MS Access if user double clicks on MS Access database file.
If you’re wondering what is perhaps a negative consequence of tweaking territory part of NLS_LANG variable, then you can refer to Oracle official NLS lang FAQ, a quote:
What does the TERRITORY component of the NLS_LANG parameter control?
The territory component of the NLS_LANG parameter controls the operation of a subset of globalization support features. It specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA , FRANCE , or CANADA . If the territory is not specified, then the value is derived from the language value.
We can certainly leave with “wrong” monetary value and having american “default date” is easy to fix with a proper NLS_DATE_FORMAT.
UPDATE: April 11, 2018
I added a small troubleshooting feature to my Golang code that allows end users to easily check if they run instance of MS Access spawned from my runAccess app. I added /cmd at the end of the command line with text NLS_TERRITORY=AMERICA. This MS Access related “command line switch” is usually read by VBA function Command(), but can also be viewed from:
File -> Options -> Client Settings -> Advanced -> Command-line arguments NLS_TERRITORY=AMERICA
package main
import (
"fmt"
"os"
"os/exec"
"flag"
)
func main() {
var version *bool
var help *bool
version = flag.Bool("version",false,"18.03")
help = flag.Bool("help",false,"MS Access 2016 Launcher")
flag.Parse()
if *version {
fmt.Printf("February 2018 by AlesK\nVersion %s\n", (flag.Lookup("version")).Usage)
os.Exit(0)
}
if *help {
fmt.Println("I'm here to silently run this code for a user:")
fmt.Println("cmd /c SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:\\Program Files\\Microsoft Office\\Office16\\msaccess.exe /cmd NLS_TERRITORY=AMERICA")
os.Exit(0)
}
if (os.Args != nil && len(os.Args) > 1) {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe " + os.Args[1] + " /cmd NLS_TERRITORY=AMERICA")
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
} else {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe /cmd NLS_TERRITORY=AMERICA")
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
}
Windows 10 – disable Windows Update automatic reboot
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…”:
MS Access 2016 — a bug finally fixed after 16 years
[UPDATE on February 2018 — make sure that you read Part 2 as well!]
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.
Setup OpenSSH (Cygwin) for public key authentication
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
Installing OpenSSH (Cygwin 1.7.35) on Windows 2012 R2
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.
1) INSTALLATION
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) CONFIGURATION
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:
notepad c:\cygwin\cygwin.bat
@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:
$ ssh-host-config
*** 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
Open cygwin.bat:
$ /bin/mkpasswd -l -u alesk >> /etc/passwd
The above command will write something like this in the passwd file:
ACMEHOST+alesk:*:197610:197121:U-ACMEHOST\alesk,S-1-5-21-1611771360-925472152-3555864332-1002:/home/alesk:/bin/bash
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:
alesk:*:197610:197121:U-ACMEHOST\alesk,S-1-5-21-1611771360-925472152-3555864332-1002:/home/alesk:/bin/bash
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:
notepad C:\cygwin\etc\sshd_config
or within cygwin.bat:
nano /etc/sshd_config
at the end of file add:
KexAlgorithms diffie-hellman-group-exchange-sha1,diffie-hellman-group1-sha1
I’m not sure what is the true reasoning for that, but probably for hardening the sshd security!?
2.6 Start SSH daemon:
Cygwin.bat
$ /usr/bin/sshd
or
$ 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.
dbaportal.eu 
You must be logged in to post a comment.