A bit of minor negligence can cause havoc at times. This article presents a practical situation that I faced in my organization.
In our working environment, we use a VDI (Virtual Desk Interface) to connect to the client environment. From this VDI, we make a remote RDP connection to the various database servers. Once we log into the database server, we use SSMS to connect to SQL Server instances.
As part of regular support, one day when I tried to connect to a SQL Server instance, I received the below error:
In the process of troubleshooting, I tried the following:
- Checked with other administrators who have a similar level of access to see if they are facing the same issue but they were able to connect without any issues.
- Checked if my personal administrator access was lost to determine why my active user id has stopped working all of a sudden.
- Checked tech forums to find out if someone else faced a similar issue but no use in resolving the issue.
Finally, I logged off from the database server and tried re-connecting to the database server with the domain password that I had changed on that same day morning in accordance with the security suggestion from our domain controller. Then tried to connect to the SQL Server instance through SSMS and I was able to connect successfully this time and was able to do all my day to day administration tasks.
Root Cause of the Issue
At times, especially when in a hurry, administrators forget to disconnect/logoff from the database servers properly at the end of the day. Instead they just disconnect the database server in one of two ways. They can click the window close button, which is available in the right-hand corner, or just leave the session as open. The previous day, I clicked close to end my work.
On the day when I faced this issue, while connecting to the VDI, the session prompted me to change my domain password to connect and I had to change the password to login to the client environment.
The actual root cause is the abrupt closing of the session. When I connected to the VDI with the newly changed credentials, SSMS was trying to connect to the SQL istance using my old domain password because the database server session and SSMS were not closed on the previous day when I disconnected from the VDI. This was the reason SSMS was not to connecting to the instance successfully.
I logged off from database server at the OS level after disconnecting all the instances in SSMS that were connected using my old password. When I re-connected to the database server using my new password, everything worked as expected and I was able to connect to the SQL Server instance without any issues.
So next time spend some time to logoff appropriately and not abruptly so that you won’t mess the fresh start next day! Hope this article was helpful.