In this day and age of security breaches and data leaks, IT departments continue to find new ways to prevent these issues. I work for a company in the financial sector and we are always undergoing a security audit of some kind. Once these audits are complete there are always new procedures and policies around security and some times this includes new security appliances and tools.
This finally snuck up on us DBA’s due to the lack of inclusion in the security process discussion.
Here is the story that as I experienced it.
I was contacted by an end user of a specific stand alone SQL Server where users could not load SSRS reports or connect to SQL server. After starting with the basics of troubleshooting I made sure the services were running, I made sure I could connect to the server via RDP then I validated that I could load SSMS from the server and also open the SSRS Report Manager page from the server. So far all tests were successful.
Now on to further troubleshooting. I attempted to connect from my workstation and was not able to connect to SQL Server or SSRS Report Manager.
Now a little further troubleshooting had me do a ping and telnet which resulted in mixed emotions.
With this result I proceeded to contact our security department and see if they had recently made any firewall changes and their response was “not to this part of the network”. With that I said can you trace my traffic to see if it is being blocked by the firewalls. So the security engineer said give me the source and destination IP and let me turn on a trace. After providing the information I tested my connection to the server and low and behold nothing is being blocked from my laptop to the ports in question. Whaaa!
Something has to be blocking my traffic. Nothing has changed with the server in years.
So let’s backtrack a second. I verified with the end user that this problem started on a Friday evening. Ahh ha I know, we did Windows patching that Friday. Maybe something did not come up in sequence during patching. Long shot, but I am grasping at straws. So I scheduled a server reboot with the end user after deciding on a good maintenance window we pulled the trigger. I was positive this would fix the issue. Voila, NOT!! Same result.
Dang I am losing it over this. Now to get a list of Windows updates applied to the server to see if something could be conflicting, nope!! I guess it is time to get the experts at Microsoft on the line to show me what I have overlooked.
Well before doing that this is a virtual machine, everything we have are virtual machines. Virtual machines have networks! Maybe this server failed over to a host that does not connect to the VLan this server is supposed to be on. So I proceed to explain my issue to the Infrastructure engineer. And as soon as I tell him the server name he responds, oh that server was just moved to the new segregated network required by the last audit results. What the heck is going on around this place! So now we have traffic blocked by firewalls, and other traffic being blocked by a VMware appliance called a VNX.
So the long and short of it, learn your environment and hopefully you can keep up with what software technologies are used and any other systems that may impact administration of your SQL servers.