How to login to SQL Server if all logins with sysadmin role are disabled

Introduction

SQL Server 2016 creates a built-in login called "sa" with sysadmin server role. Additionally, several other built-in logins such as NT Service\MSSQLSERVER and NT Service\SQLSERVERAGENT are also created with the sysadmin fixed server role during the instance creation process (SQL Server installation).

Because it is not recommended to perform database administration with the "sa" login, you may have already created an additional login (new login) with the sysadmin role and and named it as sqlserver.

This article explains how to login to SQL Server if you have accidentally disabled the "sa" login and any other new logins created with the sysadmin role (aka "locked yourself") scenario.

Assumptions

The instance id is the default instance id MSSQLSERVER.

Solution

1)  Stop SQL Server (MSSQLSERVER) database engine Windows service.

2) Create a new Windows operating system (OS) user, for example, sqlserver_new and make it part of the Administrators OS group.

3) Login to the database server using Remote Desktop Connection as this new OS user sqlserver_new and then launch SQL Server 2016  Configuration Manager => SQL Server Services => SQL Server (MSSQLSERVER) => right click => Properties

Then add the -m startup parameter, which helps us restart SQL Server in single user mode. Please see below screenshot.

4) Click on Apply and close the window. Now right click => Restart

This will restart the SQL Server (MSSQLSERVER) database engine in Single user mode.

5) Make sure SQL Server Agent service is stopped. If it is started, then it will consume the single user by itself. We don't want that.

6) Now launch SQL Server Management studio (SSMS) as administrator. ( Right click => Run as administrator) .  You can login with the new sqlserver_new OS user even though this account is not created as an instance login inside the database instance.

Then once inside SSMS => Security => Logins and then enable,grant access to those existing sysadmin role accounts which were accidentally disabled (or) simply create a new login with a sysadmin role.

7) Now launch SQL Server 2016  Configuration Manager  again, this time remove the -m startup parameter and restart the SQL Server (MSSQLSERVER) service. This will help us get back to the multi-user mode again.

8) You can remove the OS user sqlserver_new if you no longer require it. This account was created specifically to fix the problem, so you may not need it.

9) And finally, start the SQL Server Agent service again.

Oracle 12c R2 upgrade dbua ORA-00837

Introduction

Oracle 12c R2 base release (12.2.0.1) supports direct upgrade from Oracle 11.2.0.3, 12.1.0.1 and 12.1.0.2 respectively.

While we were performing a non-CDB database upgrade from 12.1.0.2 to 12.2.0.1, we were facing the ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET error during the Pre Upgrade step. Our database name is CORPDB and when we checked the parameter values, the MEMORY_TARGET value was 800 MB and it is less than the value of MEMORY_MAX_TARGET which is 1000 MB. But we were still facing this error.

Solution

1) Open the /u01/app/oracle/cfgtoollogs/CORPDB/preupgrade/preupgrade.log

2) Search for memory_target inside this log file.

3) You will notice that the 12.2.0.1.0 minimum value for memory_target* parameters is 1535115264. (value in bytes)

4) Abort the upgrade. Close the dbua program by clicking on the Abort button.

5) Now set the MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameter values to a minimum of 1500 MB and restart the upgrade candidate database (CORPDB in this case.)

6) Launch the dbua tool again and you will notice that the Pre Upgrade Step is completed successfully.

Conclusion

The minimum size of 1500 MB for memory_target* parameters seems to be only a requirement while upgrading from Oracle 12c R1 to 12c R2. If you create a new 12c R2 database directly using the dbca tool (Database Configuration Assistant), then this requirement does not apply. We were able to create a new database using just 1000 MB for memory_target (Automated Memory Management). This unique requirement while performing the database upgrade is surprising and it is not documented in the Oracle 12c R2 upgrade guide.

Oracle 12c DBA Training

DBA University is excited to announce Oracle DBA Training course in the latest Oracle 12c R1 and 12c R2 versions Each training schedule runs for a period of 64 hours spread over 8 weekends. The live online training sessions are held from 8:00 am to 12:30 pm central time (with a 30 minute break) every Saturday and Sunday. We also have an on demand training (recorded training) offering for this course.

This course will help students prepare for majority of the topics required to pass the Oracle Database 12c Administration 1Z0-062 examination towards Oracle OCA certification (Oracle Certified Associate). It contains various other topics with practical exercises that are required for real world success.

Course Highlights

1) 64 hours course duration.

2) Training will be on the latest Oracle 12c R2 and 12c R1 versions.

3) Saturday, Sunday - 8:00 am to 12:30 pm central time (with a 30 minute break).

4) 500+ pages training material available for email download through Dropbox link.

Lab work

1) Each student will get a remote lab computer to practice the course work.  

2) The training will be on both Oracle 12c R2 and 12c R1 versions with Linux 7 64-bit as the operating system.

3) 16 hours Remote LAB access on Saturday, Sunday

4) 6 hours Remote LAB access on weekday evenings.

5) Students can use the Windows Remote Desktop Connection program to connect to the remote lab server.

Links and Resources

1) Download Oracle DBA Training course brochure

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the live online training course
4) Students who are interested in the on demand training (recorded training) should click here to purchase.

How to configure and start vncserver on Linux 7

Introduction

The procedure to enable vncserver and startup automatically upon server reboot has been changed in Red Hat compatible linux operating systems such as Red Hat Linux 7.x, Oracle Enterprise Linux 7.x and CentOS etc. If your operating system has version 6.x, then please follow this article instead.

Details

1) Login as root operating system user.

2) rm -rf  /tmp/.X11-unix/

3) cd  /lib/systemd/system

4) Make a copy of vncserver@.service

5) Edit vncserver@.service and replace <USER>  with the actual operating system user name that owns the vnc service. Leave the remaining lines of the file unmodified.

6) systemctl  daemon-reload

7) systemctl  enable  vncserver@:1.service

8) systemctl  start  vncserver@:1.service

9) Edit /etc/rc.d/rc.local and add the line service iptables restart

10) chmod  +x  /etc/rc.d/rc.local

11) Optionally, sudo su - <USER>  and then execute the vncpasswd command to change the vncserver password.
12) Reboot the server.

If the vncserver program is still not getting started automatically, then add the below lines directly in the /etc/rc.d/rc.local file.

rm -rf  /tmp/.X11-unix/
systemctl  daemon-reload
systemctl  enable  vncserver@:1.service
systemctl  start  vncserver@:1.service
service iptables restart

Verification

1) Launch TigerVNC Viewer

2) IP address:5901 

It should prompt for Password and then it should connect successfully

How to install X Windows on Linux

The following packages (a.k.a RPMs) are required to setup X Windows on Linux, specifically Oracle Enterprise Linux (OEL) with the UEK kernel. It is tested successfully on Oracle Linux 7 update 3 for x86_64. These packages also install KDE as the default desktop environment on this Linux operating system.

Login as root and run the yum install package command for each such package below.

yum install vnc
yum install vnc-server 
yum install xterm
yum groupinstall "X Window System"  
yum install kdebase
yum install gnome-terminal
yum groupinstall "KDE"

TNS-01189: The listener could not authenticate the user

In older versions of Oracle database such as Oracle 9i etc, one can stop a listener remotely. This was a serious security problem because all that is needed by any client or end user is the listener name and a TNS entry(listener port and host name) to shutdown the listener sitting from that client machine !!!. This can be prevented by securing the listener with a password. We published a blog post about this security issue a few years ago.

But this password feature is deprecated since Oracle database 11g R2 release. This is no security problem because the listener is now secured using local operating system authentication. Therefore, no client or end user can remotely shutdown a database listener anymore.  If one attempts to stop a database listener process from a remote machine, one receives the error "TNS-01189: The listener could not authenticate the user".  A listener can be only be stopped after logging into the database server (and connecting to the Oracle software owner account) that hosts the listener process. This experience is similar to how Oracle does not allow remote connections to the sys built-in administrative database account.

Therefore, there is no need of protecting Oracle database listeners with passwords since Oracle database 11g R2 release.