March 1, 2017 at 10:38 am
Lynn Pettis - Wednesday, March 1, 2017 9:45 AMBeatrix Kiddo - Wednesday, March 1, 2017 9:18 AMWhere do you all stand on those third party privileged access management systems that RDP you onto the server and record everything you do so that it can be played back later? I understand the need to keep data secure, but it means that we can't run SSMS from our desktops any more (because your privileged account password is passed through by the application so you never know what it is). It's very frustrating not to have SSMS on my desktop machine any more. Back to the point of the thread, there's a noticeable lag too, so there must be more overhead on the server (?).Any more info on this as I have never heard of such things, Beatrix.
Back to the original topic, I strongly fight using RDP to a server to do work. Most servers I have worked with only have 2 management RDP sessions and I strongly feel those should be used only for doing things that require you to be on the server. I can do 99% or more of my work without ever touching an active server. Now, at a previous employer we did have one SSIS package that if it needed to be modified had to be done on the server it was deployed because the third party software we were using could not use UNC paths.
I want my tools on my desktop/laptop and connect to my servers remotely. This prevents me from having to install them on multiple systems when it is not required. I do want SSMS on the servers just in case I do need to RDP into a server to do some work.
About the only time I RDP into any of my servers is when we think there might be a network slowdown affecting performance. I'll try running the customers' query both on my local workstation via SSMS, then RDP into the server and try it from the SSMS on the server. It's not a sure-fire proof (our network can be slow for some people and blazing fast for others at the same time.)
But I'd say I can count on one hand the number of times this week I've RDPed into one of my servers, and even then it was only to check if the AV was or was not running. I'd love to be able to use SSMS when I work from home, but currently our VPN blocks the port and there's been a lot of back-and-forth on getting it opened up / standing up a dedicated Terminal Server (with CALs) / opening up the port / rinse-and-repeat...
So I don't work from home...
🙁
March 2, 2017 at 6:44 am
Phil Parkin - Wednesday, March 1, 2017 10:15 AMPerry Whittle - Wednesday, March 1, 2017 10:05 AMLynn Pettis - Wednesday, March 1, 2017 9:45 AMMost servers I have worked with only have 2 management RDP sessionsYes, this is the old TS admin mode
I think it's the standard setting.
I hope that there are very few organisations which have bought CALs for TS access direct on their SQL box.
yes it is standard
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2017 at 6:49 am
Beatrix Kiddo - Wednesday, March 1, 2017 9:18 AMWhere do you all stand on those third party privileged access management systems that RDP you onto the server and record everything you do so that it can be played back later?
If you need something like that, then set up a 'jump server' with that management system on and with all the necessary dev tools on, and RDP into that not into the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2017 at 7:28 am
Yes, I've asked for jump servers.
Lynn, this is quite popular in banking environments. So far I've used CyberArc and Osirium, plus one other that I can't remember. I think it's a regulatory requirement in the UK (and possibly elsewhere). The idea is that every activity on every server is recorded and can be played back like a video.
March 4, 2017 at 5:30 am
One of the early things I did as SQL Server DBA back in the early noughties was to request a SQL Administration server that would be used to host all the required SQL client tools. All access to SQL Server was by RDP to the Admin server, and using Query Analyzer (pre SQL2005!) to get to SQL Server. Just about the only time a direct log on to a SQL box was done was for OS maintenance of extreme troubleshooting where a remote session was not good enough. The drivers for this were mainly security and stability, particularly when working from home (also pre SQL2005).
Moving on to today and a few employers later, I still aim to do all SQL Server access remotely. RDP logon to the SQL box is still needed for SQL installation and patching, but virtually nothing else is done direct on the server. The drivers are still the same - security and stability, especially when working from home.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 7, 2017 at 3:03 pm
I think I'm agreement with several of the other posters.
RDP to a SQL Server is BAD, UNLESS you are a systems DBA doing maintenance or OS level investigation. I would very deliberately only allow systems DBA's and the Windows admin team from even being allowed to do this, and make sure whoever hands out security know that when end users ask for this explicitly, they will be given something else that meets their actual business need instead of this, regardless of their rank in the organization. I routinely RDP into SQL Servers to check Resource Monitor (resmon), to install critical patches, and rarely to validate that the antivirus solution is working, or perform low-level disk or network benchmarking (sqlio, iperf3). Almost all of a dedicated SQL Server's resources should be geared to running SQL Server... and on SSMS 2016, you don't want to be patching it every month or so anyway - leave SQLCMD on the server for emergency work.
RDP to a Terminal Server with client tools is good, especially when working remotely*. Pulling the data from the SQL Server to a terminal server over internal, low latency, high throughput network links and then just looking at what you want is much better for big queries, and there are many additional security benefits even before adding in session recording and so on and so forth.
*Assuming the RDP access from the remote site is over a VPN, not directly over the internet. Exposed RDP ports on the internet are a serious security risk.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply