SQL Server - quick notes.

I am currently being asked to look after more and more SQL Server boxes so with a couple of hours spare thought I would build SQL Server on Linux via Oracle Virtual Box (might one day try VM Workstation). I had already built SQL Server on Linux ages ago and had no issues but can only think newer versions of Red Hat have changed somewhat - thought it would be really easy 😕

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017

However, after talking to the team, the general opinion was that SQL Server was more suited to Windows and that would be the better option. Anyway, after fighting Red Hat for what seemed an age (disabled SE Linux and IPV6 thinking that was the issue) - SQL Server just would not play.

Configure SELINUX=disabled in the /etc/selinux/config file: # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # ...

Reboot your system. After reboot, confirm that the getenforce command returns Disabled.

Disable IPV6
# sysctl -w net.ipv6.conf.all.disable_ipv6=1
net.ipv6.conf.all.disable_ipv6 = 1
# sysctl -w net.ipv6.conf.default.disable_ipv6=1
net.ipv6.conf.default.disable_ipv6 = 1

Firewalld needs to be disabled too.

[root@usnyspmtaoem01 ~]# systemctl stop firewalld
[root@usnyspmtaoem01 ~]# systemctl status firewalld

SQL Server was happy enough to start up but I had no joy with getting connected - can only think this was an issue with ipv6 but never found the reason, I reckon there is some bug issue that needs to be fixed that I would love to get to the bottom of but just don't have the time. Eventually gave up and went down the Windows road and downloaded Windows Server 2019 and an evaluation copy of SQL Server 2017.

https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-2019 

https://www.microsoft.com/en-gb/sql-server/sql-server-downloads

Relatively straight forward, but when trying to connect hit a few problems. Disabled the Windows Firewall (would not normally do this but machine already behind a firewall anyway, should have just created a rule to "punch through" on 1433).  Also, had to enable "SQL Server and Windows Authentication mode" as the server was only set up with Windows Authentication and not applicable for my test environment. Even then had to enable the SA account as this is disabled by default for security -  everything working ✔

Just need to build another server and get high availability working now - more to follow.

SA user enabled

Enable SQL Server and Windows Authenticaion mode

Password expiration is dead long live your password!

Good old Microsoft - somebody has finally seen the light. I have thought this for a very long time.

Password-expiration-is-dead-long-live-your-password


I still think passwords have a place in IT but with two factor authentication such as www.duo.com this obsession with changing passwords and having users writing them down or storing in the likes of KeePass needs to end; just let the user remember a decent password and go with what Microsoft are saying.

"Recent scientific research calls into question the value of many long-standing password-security practices such as password expiration policies, and points instead to better alternatives … If a password is never stolen, there’s no need to expire it. And if you have evidence that a password has been stolen, you would presumably act immediately rather than wait for expiration to fix the problem.

…If an organization has successfully implemented banned-password lists, multi-factor authentication, detection of password-guessing attacks, and detection of anomalous logon attempts, do they need any periodic password expiration? And if they haven’t implemented modern mitigations, how much protection will they really gain from password expiration? …Periodic password expiration is an ancient and obsolete mitigation of very low value."

Login to Oracle when max sessions exceeded

Quick tip - thanks to Russell Willcox for this one :)

See also :

 http://www.dba-oracle.com/t_connecting_hung_instance_using_prelim_option.htm

Login when max sessions exceeded (and you can’t log in using standard methods)

This is the backdoor entry method to access the database in a hung situation where no other methods are allowing you to create new sessions. 

This will not create any session in the database and connects to the SGA directly.

cmd> sqlplus –prelim “/ as sysdba”  (or… sqlplus –prelim sys/password as sysdba)

note: just try typing sqlplus –prelim / as sysdba (don’t cut and paste – it doesn’t work)

sql> shutdown abort

sql> startup restrict

sql> exit

cmd> sqlplus / as sysdba

sql> alter system set sessions=600 scope=spfile;

sql> alter system set processes=600 scope=spfile;

sql> alter system set parallel_max_servers=0 scope=both; (IF NECESSARY... i.e. parallel processes swamping database)

sql> alter database mount;

sql> alter database open;

sql> create pfile from spfile;

sql> alter system disable restricted session;